Oracle DBA tips: 2008

Thursday, October 23, 2008

ASM

Overview of Automatic Storage Management (ASM)
Automatic Storage Management (ASM) simplifies administration of Oracle related files by allowing the administrator to reference disk groups rather than individual disks and files, which are managed by ASM. The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.

The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight.

The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files. The locations and names for the files are controlled by ASM, but user-friendly aliases and directory structures can be defined for ease of reference.

The level of redundancy and the granularity of the striping can be controlled using templates. Default templates are provided for each file type stored by ASM, but additional templates can be defined as needed.

Failure groups are defined within a disk group to support the required level of redundancy. For two-way mirroring you would expect a disk group to contain two failure groups so individual files are written to two locations.

In summary ASM provides the following functionality:

Manages groups of disks, called disk groups.
Manages disk redundancy within a disk group.
Provides near-optimal I/O balancing without any manual tuning.
Enables management of database objects without specifying mount points and filenames.
Supports large files.
Initialization Parameters and ASM Instance Creation
The initialization parameters that are of specific interest for an ASM instance are:

INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.

Create ASM in PC or Laptop

ASM can be implemented without having expensive additional disks/logical volumes/partition for testing purpose on your laptop/desktop.

All you need is a free space on your hard disk to store your database.

It's much intresting to do in practice than to describe in theory, so let's begin!

1) Creating a dummy disks

In 10gR2 installation, Oracle ships an executable called asmtool, and it can be used to create 'solid' files -solid in the sense that they are filled with zeros, as opposed to being full of empty space. These are the sorts of files ASM demands if it is to treat them as hard disks. The utility is found in the ORACLE_HOME\bin.

E:\>mkdir asmdisks

E:\>cd asmdisks

E:\asmdisks>asmtool -create E:\asmdisks\disk1 1024

E:\asmdisks>asmtool -create E:\asmdisks\disk2 1024

E:\asmdisks>asmtool -create E:\asmdisks\disk3 1024

Now you have 3 disks(dummy) of 1 GB each which can be used to create a ASM disk group.

2) Create ASM instance

a) Configure Cluster Synchronization Servie

Before you can create an ASM instance on 10g Release 2, though, you must first run a script which instantiates the Oracle Cluster Registry and its associated processes & services. That's done by issuing the command:

E:\>E:\oracle\product\10.2.0\db_1\BIN\localconfig reset

Step 1: stopping local CSS stack
Step 2: deleting OCR repository
Step 3: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'ap\arogyaa', privgrp ''..
Operation successful.
Step 4: creating new CSS service
successfully created local CSS service
successfully reset location of CSS setup

b) Create Init pfile

Open notepad edit the following parameters and save file as "E:\oracle\product\10.2.0\db_1\database\init+ASM.ora"

INSTANCE_TYPE=ASM
DB_UNIQUE_NAME=+ASM
LARGE_POOL_SIZE=8M
ASM_DISKSTRING='E:\asmdisks\*'
_ASM_ALLOW_ONLY_RAW_DISKS=FALSE

c) Create service and password file

oradim will create an ASM instance and start it automatically.

E:\> orapwd file=E:\oracle\product\10.2.0\db_1\database\PWD+ASM.ora password=asm
E:\> oradim -NEW -ASMSID +ASM -STARTMODE auto

3) Create ASM disk group

a) Create asm disk group
SQL> select path, mount_status from v$asm_disk;

PATH MOUNT_S
--------------------------------
E:\ASMDISKS\DISK1 CLOSED

E:\ASMDISKS\DISK3 CLOSED

E:\ASMDISKS\DISK2 CLOSED

SQL> create diskgroup data external redundancy disk
2 'E:\ASMDISKS\DISK1',
3 'E:\ASMDISKS\DISK2',
4* 'E:\ASMDISKS\DISK3';

Diskgroup created.

b) Change PFILE to SPFILE, Add ASM Diskgroup parameter and your all set to go and use ASM.

SQL> create spfile from pfile;
SQL> startup force;
SQL> alter system set asm_diskgroups=data scope=spfile;
SQL> startup force;

SQL> startup force
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1247420 bytes
Variable Size 57472836 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL>

Now you can go ahead and use your DBCA and create a database and on step 6 of 13, you can use Automatic Storage management as your Filesystem.

How to send email from Oracle server.

When we configure Oracle E-MAIL notification method through OEM then it is not include notification for DB shutdown or startup events. For these two events we have to configure manually.

Connect sys as sysdba user and run two scripts for install and configure utl_mail package

SQL> conn sys@orcl as sysdba
Enter password:
Connected.
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\utlmail.sql

Package created.

Synonym created.

SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\prvtmail.plb;

Package body created.

No errors.

Set SMTP_OUT_SERVER parameter for smtp_exchange_server. This parameter is not modifiable means we have to bounce our database to set this parameter

SQL> alter system set smtp_out_server = 'smtp_exchange_server_name' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

Grant EXECUTE privs to user which use utl_mail package.

SQL> grant execute on utl_mail to scott;

Grant succeeded.

Create two trigger for startup and shutdown event on database which send us email notification

SQL> create or replace trigger scott.db_shutdown
2 before shutdown on database
3 begin
4 sys.utl_mail.send (
5 sender =>'xyz@xyz.com',
6 recipients =>'xyz@xyz.com',
7 subject => 'Oracle Database DOWN',
8 message => 'May be DB Down ’||
‘ but also contact to DBA for further details. ’
9 );
10 end;
11 /

Trigger created.

SQL> create or replace trigger scott.db_startup
2 after startup on database
3 begin
4 sys.utl_mail.send (
5 sender =>'xyz@xyz.com',
6 recipients =>'xyz@xyz.com',
7 subject => 'Oracle Database UP',
8 message => 'DB OPEN .'
9 );
10 end;
11 /

Trigger created.

How to send email from Oracle server.

Wednesday, October 22, 2008

Pre-Install checks for RDBMS

Unix InstallPrep Script
The Unix InstallPrep Script should be run prior to installing the Oracle 8.0.5 to 9.2 DataServer. This script checks for all known items that will cause the install to fail.
Follow these steps to download and run the script:

Change the name of the script to InstallPrep.sh
Ftp the InstallPrep.sh script (in ascii format) to the Unix system that you will be installing the Oracle DataServer on.
Change the permissions of the script to execute for the user running the script:
chmod 777 InstallPrep.sh
Log in as the user that will be installing the Oracle software.
Copy the InstallPrep.sh script to the /tmp directory and run it from this location.
Note: on some Linux platforms you may get the error "bad interpreter", just run the command 'sh InstallPrep.sh' and the try running it. Otherwise, you can try "dos2unix InstallPrep.sh" and then retry it.
Note: on Linux if you get messages like:
'tmp/InstallPrep.sh: line 138: syntax error near unexpected token `in
'tmp/InstallPrep.sh: line 138: `case $OS in
do "dos2unix InstallPrep.sh"
Answer the first few questions, the output from the script will be written to /tmp/InstallPrep.out and the errors written to /tmp/InstallPrep.err.
Resolve any problems found in /tmp/InstallPrep.err and then install the Oracle DataServer software.

Important Note: If you experience difficulties or errors with this script, please do not call Oracle Support or log an iTAR for this issue. Instead, send feedback to RELATED DOCUMENTS

Note 109229.1 UNIX: Quick Start Guide Reference List
Note 169706.1 Oracle RDBMS on AIX,HP-UX,Solaris,Tru64,Linux,MacOSX:Versions, Sizes, Requirements Quick Reference
Note 334563.1 Pre-Install checks for 10gR2 RDBMS (10.2.x) - HPUX Platforms
Note 334562.1 Pre-Install checks for 10gR2 RDBMS (10.2.x) - AIX Platforms
Note 334567.1 Pre-Install checks for 10gR2 RDBMS (10.2.x) - SUN Solaris
Note 283731.1 Pre-install Checks for 10g RDBMS on HPUX Platforms
Note 283743.1 Pre-install Checks for 10g RDBMS on AIX
Note 283747.1 Pre-install Checks for 10g RDBMS on HPUX Itanium Platforms
Note 283748.1 Pre-install Checks for 10g RDBMS on Linux
Note 283749.1 Pre-install Checks for 10g RDBMS on Linux Itanium 64
Note 296665.1 Pre-Install Checks for 10g RDBMS on Linux AMD64/EM64T
Note 283750.1 Pre-install Checks for 10g RDBMS on Sun Solaris
Note 283751.1 Pre-install Checks for 10g RDBMS on Tru64
Note 296661.1 Pre-Install Checks for 10g RDBMS on Apple MAC OS X

How to Identify Oracle 64 bit or 32 bit

Oracle Software is 32 bit or 64 bit.

Method 1:
--------------
Go to $ORACLE_HOME/bin and see.

# cd $ORACLE_HOME/bin
# file oracle
oracle: ELF 64-bit LSB executable AMD64 Version 1, dynamically linked, not stripped

Here it comes 64 bit and hence oracle software is 64 bit. If the output of the "file oracle" command does not say 64-bit explicitly then you are running 32-bit Oracle.

If you had 32 bit oracle software installed then output will be like,

oracle@sol:/db/oracle/bin$ file oracle
oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), not stripped

Method 2:
----------------------
Log on to SQL*plus and see the banner.

-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Thu May 15 02:50:37 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Method 3:Query from v$version.
-------------------------------------
sys@THERAP> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

Method 4: Check for lib, lib32
--------------------------------------

1) $ORACLE_HOME/lib32
2) $ORACLE_HOME/lib

If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit.

If there is only an ORACLE_HOME/lib directory then it is 32 bit client.

How to Identify OS 64 bit or 32 bit

Check whether OS is 64 bit or 32 bit.
-----------------------------------------
On Solaris,

SQL> !/usr/bin/isainfo -kv
64-bit amd64 kernel modules

SQL> !/usr/bin/isainfo -v
64-bit amd64 applications
cx16 mon sse3 pause sse2 sse fxsr mmx cmov amd_sysc cx8 tsc fpu
32-bit i386 applications
cx16 mon sse3 pause sse2 sse fxsr mmx cmov sep cx8 tsc fpu

This output tells us that solaris operating systems allow co-existence of 32-bit and 64-bit files.

On AIX,
$ getconf -a | grep KERN
$ file /usr/lib/boot/unix*
On Linux,

On HP-UX,
/usr/bin/ getconf KERNEL_BITS
/usr/bin/file /stand/vmunix

Linux:

getconf LONG_BIT
gives 32/64 bit..

though it's common sense that 64 bit os cannot sit on 32 bit proc , x86 in uname -m are indication of 32 bit processor rahter than kernel

On linux,

$uname -a
Linux debian 2.6.18-4-686 #1 SMP Wed May 9 23:03:12 UTC 2007 i686 GNU/Linux

If the output is x86_64 then 64-bit and i686 or similar for 32-bit.

On windows,

Start>All Programs>accessories> System Tools>System Information>look for under System summary.

Or start>run>dixdiag>Then check for WHQL digital signature.

Friday, September 19, 2008

Find ORA errors in windows

findstr "ORA-" alert1.log > alert.txt

Wednesday, August 6, 2008

How to use MERGE in Oracle

MERGE is used to select rows from one or more sources, and update or insert into a table or view. You cannot update the same row of the target table multiple times in the same MERGE statement.

Syntax:

MERGE INTO <table/view>
USING <table/view/subquery> ON (condition)

--update

WHEN MATCHED THEN
UPDATE SET column = (value/expr),..
WHERE (condition) | DELETE WHERE (condition)

--insert

WHEN NOT MATCHED THEN
INSERT (column,..) VALUES (value/expr,..)
WHERE (condition)

--error log

LOG ERRORS INTO <table>

If the ON clause condition is true

then UPDATE section gets executed.

else

INSERT section gets executed.

Restrictions on the merge update:

1. You cannot update a column that is referenced in the ON condition clause.

2. Cannot specify DEFAULT when updating a view.

See the example code below.

Create a target table called TEST1 and inset some test data

CREATE TABLE test1 (
co1 VARCHAR2(3),
co2 VARCHAR2(3),
co3 VARCHAR2(3),
co4 NUMBER );

ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY(co1);

INSERT INTO test1 VALUES('1', 'val', 'sd1', 100);
INSERT INTO test1 VALUES('2', 'va2', 'sd2', 100);
INSERT INTO test1 VALUES('3', 'va3', 'sd3', 100);
INSERT INTO test1 VALUES('4', 'va4', 'sd4', NULL);
INSERT INTO test1 VALUES('5', 'va5', 'sd5', 100);

commit;

Create a target table called TEST2 and inset some test data

CREATE TABLE test2 (
a1 VARCHAR2(3),
a2 NUMBER );

ALTER TABLE test2 ADD CONSTRAINT test2_pk PRIMARY KEY(a1);

INSERT INTO test2 VALUES('1', 10);
INSERT INTO test2 VALUES('3', 20);
INSERT INTO test2 VALUES('6', 30);
INSERT INTO test2 VALUES('10', 40);
INSERT INTO test2 VALUES('15', 50);
INSERT INTO test2 VALUES('2', -10);

commit;

Now by using the MERGE statement we are going to update/delete or either insert records to the target table(test1) by using the source (test2).

1. MERGE INTO test1 a
2. USING (select a1, a2
3. from test2 ) b
4. ON (a.co1 = b.a1)
5. WHEN MATCHED THEN
6. update set a.co4 = a.co4 * b.a2,
7. a.co2 = b.a2
8. where co3 = \'sd3\'
9. delete where co3 = \'sd3\'
10. WHEN NOT MATCHED THEN
11. insert (a.co1, a.co2, a.co3, a.co4) values
12. (b.a1, null, null, b.a2);

line 2. uses a subquery to select the source records.
line 4. ON clause specify the condition. This condition controls the MERGE to perform update or insert. If true then update else insert.
line 6., 7. updates the columns by using the source column data.
line 9. deletes the target row after updations when the delete condition satisfied. This delete clause evaluates the updated value not the original values.
This statement executes only when ON clause condition (line 4) returns true.
line 10.When ON clause is false then this insert statement is executed.

How tnsnames.ora and sqlnet.ora file work together?

I would like to bring an article which explains how useful sqlnet and tnsnames files are and how they work together.

The default domain in the Sqlnet file is used for a SCENARIO purpose only.

SCENARIO 1:

in tnsnames.ora

dba.domain = (....)

in sqlnet.ora file

names.default_domain = domain

So, in sqlplus log screen, the following can be typed in: hr/hr@dba

Note the fully qualified dbadomain would not be typed in, because it will be automatically appended to the end of the connect string specified.

SCENARIO 2:

in tnsnames.ora

dba.domain = (....)

SCENARIO.NW.NOS.ORALCE.COM = (....)

in sqlnet.ora file

names.default_domain = domain

What would happen in the SCENARIO where multiple domain have been defined in the tnsnames.ora but with only one sqlnet.ora? Just remember, the key is what is defined in the TNSNAMES.ORA file, sqlnet.ora is for convenience.

The entire connect string can be typed in.

In sqlplus log screen, just type in :

hr/hr@test <--- should again work

hr/hr@test.domain <--- should again work

hr/hr@SCENARIO <--- should *NOT* work, because in fact it will be SCENARIO.domain

hr/hr@SCENARIO.NW.NOS.ORALCE.COM <--- should work

What is being said is that if the domain is added after the name when loginning as defined in the tnsnames.ora file, then don\'t use the default domain that is defined in the sqlnet.ora file.

SCENARIO 3:

In fact a sqlnet.ora file is not really needed for the purposes of login

E.g., in tnsnames.ora

test = (....) <--- note here, no domain

SCENARIO.NW.NOS.BOEING.COM = (....)

no sqlnet.ora file exists anywhere.

In sqlplus log screen, just type in :

hr/hr@test <--- should again work

hr/hr@SCENARIO.NW.NOS.BOEING.COM <--- should work

hr/hr@SCENARIO <--- should *NOT* work

hr/hr@test.domain <--- should *NOT* work

RDA

Source file exported to clipboard (as text)


Remote Diagnostic Agent (RDA)



 



On Monday morning I received a mail from my client regarding installation of oracle 10g on RHEL ES 4 platform. I started the installation and ended up with many errors. While struggling to get required RPMS and going through metalink documents I found usage of RDA. 



I used to hear about RDA even when working with Oracle Corporation but never thought how useful RDA is.

 

Remote Diagnostic Agent (RDA) is a set of command line diagnostic scripts. RDA is used to gather detailed information about an Oracle environment.



Oracle Support encourages the use of RDA because it provides a comprehensive picture of the customer's environment.





Some times in the platform like RHEL to find required RPMs and compatibility RDA looks very great. Eben you might run custom xml scrips with this RDA.



 

All installation instructions are given in metalink note 314422.1.



The Tools can be run in SHELL or PEARL environment.



 



Example 



Ater installation of RDA /home/oracle/rda



 



To start data collection



[oracle@rda]$ ./rda.sh



-------------------------------------------------------------------------------



RDA Data Collection Started 06-Feb-2008 11:50:16 AM



-------------------------------------------------------------------------------



Processing Initialization module ...



Processing CFG module ...



Processing Sampling module ...



Processing OCM module ...



Processing OS module ...



Processing PROF module ...



Processing PERF module ...



Processing RDSP module ...



Processing LOAD module ...



 



 



This will generate many files including a zip file usually required by Oracle Support.



 



 



 



RDA includes health Check Validation Engine 



 



 To check HCVE 



 



Run 



 



Processing HCVE tests ...



Available Pre-Installation Rule Sets:



   1. Oracle Database 10g R1 (10.1.0) PreInstall (Linux-x86)



   2. Oracle Database 10g R1 (10.1.0) PreInstall (Linux AMD64)



   3. Oracle Database 10g R1 (10.1.0) PreInstall (IA-64 Linux)



   4. Oracle Database 10g R2 (10.2.0) PreInstall (Linux AMD64)



   5. Oracle Database 10g R2 (10.2.0) PreInstall (IA-64 Linux)



   6. Oracle Database 10g R2 (10.2.0) PreInstall (Linux-x86)



   7. Oracle Application Server 10g (9.0.4) PreInstall (Linux)



   8. Oracle Application Server 10g R2 (10.1.2) PreInstall (Linux)



   9. Oracle Application Server 10g R3 (10.1.3) PreInstall (Linux AMD64)



  10. Oracle Application Server 10g R3 (10.1.3) PreInstall (IA-64 Linux)



  11. Oracle Application Server 10g R3 (10.1.3) PreInstall (Linux-x86)



  12. Oracle Portal PreInstall (Generic)



Available Post-Installation Rule Sets:



  13. Oracle Portal PostInstall (generic)



  14. RAC 10G DB and OS Best Practices (Linux)



  15. Data Guard PostInstall (Generic)



Enter the HCVE rule set number



Hit 'Return' to accept the default (1)



> 



 



Select your choice and press enter 



 



Test Results



~~~~~~~~~~~~



 



   ID NAME                 RESULT VALUE



===== ==================== ====== ========================================



   10 OS Certified?        PASSED Adequate



   20 User in /etc/passwd? PASSED userOK



   30 Group in /etc/group? PASSED GroupOK



   40 Input ORACLE_HOME    RECORD $ORACLE_HOME



   50 ORACLE_HOME Valid?   PASSED OHexists



   60 O_H Permissions OK?  PASSED CorrectPerms



   70 Umask Set to 022?    PASSED UmaskOK



   80 LDLIBRARYPATH Unset? FAILED IsSet



  100 Other O_Hs in PATH?  PASSED NoneFound



  110 oraInventory Permiss PASSED oraInventoryOK



  120 /tmp Adequate?       PASSED TempSpaceOK



  130 Swap (in MB)         RECORD 4605



  140 RAM (in MB)          PASSED 2007



  150 Swap OK?             PASSED SwapOK



  160 Disk Space OK?       PASSED DiskSpaceOK



  170 Kernel Parameters OK FAILED SHMMAXTooSmall SEMOPMTooSmall



  180 Got ld,nm,ar,make?   PASSED ld_nm_ar_make_found



  190 ulimits OK?          FAILED StackTooSmall MaxLockMemTooSmall



  200 EL4 RPMs OK?         PASSED NotEnterprise



  203 RHEL21 RPMs OK?      PASSED NotRHEL21



  204 RHEL3 RPMs OK?       PASSED NotRHEL3



  205 RHEL4 RPMs OK?       PASSED RHEL4rpmsOK



  208 SUSE SLES8 RPMs OK?  PASSED NotSuSE



  209 SUSE SLES9 RPMs OK?  PASSED NotSuSE



  212 Patch 3006854 Instal PASSED NotRequired



  213 LD_ASSUME_KERNEL Set PASSED NotRHEL3



  214 ip_local_port_range  FAILED LowTooHigh HighTooLow



  220 Tainted Kernel?      PASSED NotVerifiable



  230 Other OUI Up?        PASSED NoOtherOUI



 



This will give you a fair idea of environment before installation.



 



And always RDA will have a result file, some time Oracle Support may ask this file. Usually this will be generated in output folder.



 



So try to learn RDA as quickly as possible. This tool helps in many ways.



 



Purpose of this document was to just highlight what RDA is and how best to use it. Refer Metalink for downloadable, getting started and installation instructions.

upgrade

Source file exported to clipboard (as text)


Upgrade database from 9i to 10g



First you have to upgrade testing database from 9i to 10g. After that you will perform upgradtion activity in production database.





Example.



1.you create sample database in oracle 9i.

2.create os user for oracle10g



su - root

groupadd dba          # group of users to be granted SYSDBA system privilege

groupadd oinstall     # group owner of Oracle files

useradd -c oracle10g -g oinstall -G dba oracle

passwd oracle



3.Download oracle 10g software from oracle web site then install oracle 10 in  that server.

4.Then set environment variables for oracle10g.



For example 9i database name test



the database to be upgraded is called test.



then copy the script from u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql to /tmp directory



Login to 9i database,



$sqlplus "/as sysdba"

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production



sql>spool /tmp/upgrade.txt

sql>@/tmp/utlu102i

Oracle Database 10.2 Upgrade Information Utility    06-20-2006 16:39:48

.

**********************************************************************

Database:

**********************************************************************

--> name:       test

--> version:    9.2.0.6.0

--> compatible: 9.2.0.0.0

--> blocksize:  8192

.

**********************************************************************

Logfiles: [make adjustments in the current environment]

**********************************************************************

--> The existing log files are adequate. No changes are required.

.

**********************************************************************

Tablespaces: [make adjustments in the current environment]

**********************************************************************

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 439 MB

.... AUTOEXTEND additional space required: 109 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 58 MB

.... AUTOEXTEND additional space required: 18 MB

--> DRSYS tablespace is adequate for the upgrade.

.... minimum required size: 5 MB

--> XDB tablespace is adequate for the upgrade.

.... minimum required size: 49 MB

.... AUTOEXTEND additional space required: 4 MB

.

**********************************************************************

Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]

**********************************************************************

WARNING: --> "shared_pool_size" needs to be increased to at least 177861837

WARNING: --> "java_pool_size" needs to be increased to at least   67108864

WARNING: --> "streams_pool_size" is not currently defined and needs a value of at least 50331648

WARNING: --> "session_max_open_files" needs to be increased to at least 20

.

**********************************************************************

Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]

**********************************************************************

-- No renamed parameters found. No changes are required.

.

**********************************************************************

Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora 

                                                           or spfile]

**********************************************************************

--> "hash_join_enabled"

.

**********************************************************************

Components: [The following database components will be upgraded 

                                                         or installed]

**********************************************************************

--> Oracle Catalog Views         [upgrade]  VALID

--> Oracle Packages and Types    [upgrade]  VALID

--> JServer JAVA Virtual Machine [upgrade]  VALID

...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)

...is required to be installed from the 10g Companion CD.

--> Oracle XDK for Java          [upgrade]  VALID

--> Oracle Java Packages         [upgrade]  VALID

--> Oracle Text                  [upgrade]  VALID

--> Oracle XML Database          [upgrade]  VALID

--> Oracle Workspace Manager     [upgrade]  VALID

--> Oracle interMedia            [upgrade]  VALID

...The 'Oracle interMedia Image Accelerator' is

...required to be installed from the 10g Companion CD.

--> Spatial                      [upgrade]  VALID

.

**********************************************************************

Miscellaneous Warnings

**********************************************************************

WARNING: --> Database contains stale optimizer statistics.

.... Refer to the 10g Upgrade Guide for instructions to update

.... statistics prior to upgrading the database.

.... Component Schemas with stale statistics:

....   SYS

....   CTXSYS

....   XDB

....   WMSYS

....   ORDSYS

....   MDSYS

.

**********************************************************************

SYSAUX Tablespace:

[Create tablespace in the Oracle Database 10.2 environment]

**********************************************************************

--> New "SYSAUX" tablespace

.... minimum required size for database upgrade: 500 MB

.



PL/SQL procedure successfully completed.



SQL> spool off

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production



Under most circumstances, at this point, the database would be backed up. This demonstration will skip over that part of the upgrade



Next, we create a PFILE from our SPFILE that we can make changes to: 



SQL> create pfile from spfile;



File created.



SQL> exit

$ vi /u01/app/oracle/product/9.2.0/dbs/inittest.ora





Edit the pfile using this the diagnostic output as a guide. Here is my file:



*.background_dump_dest='/u01/app/oracle/admin/test/bdump'

*.compatible='9.2.0.0.0'

*.control_files='/u02/oradata/test/control01.ctl',

                '/u02/oradata/test/control02.ctl',

                '/u02/oradata/test/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/test/cdump'

*.db_block_size=8192

*.db_cache_size=117440512

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='test'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.fast_start_mttr_target=300

#*.hash_join_enabled=TRUE

*.instance_name='test'

#*.java_pool_size=33554432

*.java_pool_size=67108864

*.large_pool_size=28311552

*.open_cursors=300

*.pga_aggregate_target=104857600

*.processes=150

*.query_rewrite_enabled='FALSE'

*.remote_login_passwordfile='EXCLUSIVE'

*.session_max_open_files=20

#*.shared_pool_size=38797312

*.shared_pool_size=177861837

*.sort_area_size=524288

*.star_transformation_enabled='FALSE'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/test/udump'







set pagesize 50000

tti "Database growth per month for last year"

select to_char(creation_time, 'RRRR Month') "Month",

       sum(bytes)/1024/1024 "Growth in Meg"

  from sys.v_$datafile

where creation_time > SYSDATE-365

group by to_char(creation_time, 'RRRR Month')

;

Monday, August 4, 2008

Upgrade database from 9i to 10g

Upgrade database from 9i to 10g

First you have to upgrade testing database from 9i to 10g. After that you will perform upgradtion activity in production database.

Example.
1.you create sample database in oracle 9i.2.create os user for oracle10g
su - root

groupadd dba # group of users to be granted SYSDBA system privilege

groupadd oinstall # group owner of Oracle files

useradd -c oracle10g -g oinstall -G dba oraclepasswd oracle

3.Download oracle 10g software from oracle web site then install oracle 10 in that server.

4.Then set environment variables for oracle10g.

For example 9i database name test.The database to be upgraded is called test.then copy the script from u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql to /tmp directory

Login to 9i database,

$sqlplus "/as sysdba"

Connected to:Oracle9i Enterprise Edition Release 9.2.0.6.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - Production

Run utlu102i.sql

This utility script checks that the database is ready to be upgraded to 10g. It also identifies any actions that need to be taken. The script is located in the 10g oracle home, so you will need to specify the full path to it.
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql

Review the output and make any necessary alterations. Make a note of how many invalid objects there are.

Shut the database down with either normal or immediate
shutdown immediate

Copy the spfile (or pfile) and the password file from the existing home to the 10g one.

cp ${ORACLE_HOME}/dbs/*${ORACLE_SID}* /dbs/

Upgrade the database

sqlplus "/ as sysdba"

SQL> startup upgrade
ORACLE instance started.
Total System Global Area 398458880 bytes
Fixed Size 1261068 bytes
Variable Size 276824564 bytes
Database Buffers 117440512 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
sql>

CREATE TABLESPACE sysaux
DATAFILE '/u01/ora9R2/oradata/sysaux01.dbf'
SIZE 1024M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

This next bit is the upgrade itself. It takes roughly half an hour to complete.
Spool the output to a file so that you can review it afterward.

@?/rdbms/admin/catupgrd.sql

Create a sysaux tablespace

Having a sysaux tablespace is a requirement in 10g. So, if you haven't already got one, create one now.

create tablespace sysaux
datafile '' size 512M
extent management local
segment space management auto
/

Recompile any invalid objects

SQL> select count(*) from dba_objects where status='INVALID';

@?/rdbms/admin/utlrp.sql

Then check the status of the upgrade

@?/rdbms/admin/utlu102s.sql

SGA

SGA:
The SGA is Oracle's structural memory area that facilitates the transfer of data and information between clients and the Oracle database.

sql>SHOW SGA

Total System Global Area 168783792 bytes
Fixed Size 737200 bytes
Variable Size 134217728 bytes
Database Buffers 33554432 bytes
Redo Buffers 274432 bytes

Total System Global Area : - Total in bytes of all the sub-divisions that makes up the SGA.

Fixed Size: Fixed size contains general information about the state of the database and the instance, which the background processes need to access. This does not store user data. Usually this area is less than 100k in size.

Variable Size: This part is influenced by the following init.ora parameters. shared_pool_size large_pool_size java_pool_size Database Buffers:

This holds data blocks copies that are read from datafiles and can be calculated by using following formula. size = db_block_buffers * block size Redo Buffers: This is another buffer in the SGA that holds information about changes made to the database.

Approximating size calculation of the SGA

Oracle 9i: To approximate size of the SGA (Shared Global Area), use following formula: db_cache_size + db_keep_cache_size + db_recycle_cache_size + db_nk_cache_size + shared_pool_size + large_pool_size + java_pool_size + log_buffers + 1mb

How Deadlock occurs?

Dealdlock

Deadlock is a situation that occurs when two or more users are waiting for data locked by each other. Oracle automatically detects a deadlock and resolves them by rolling back one of the statements involved in the deadlock.

Example:
create table sample(id number,Country varchar2(10) );

insert into sample values ( 1, 'China' );
insert into sample values ( 2, 'France' );
commit;

SQL> Select * from sample ;
id country
1 China
2 France

Issue the following command in session1:
SQL> update sample set country='Italy' where id=1;

Issue the following command in Session 2:
SQL> update sample set country='Canada' where id=2;
SQL> update sample set country='Canada' where id=1;

Session2 is now waiting for the lock held by Session1

Issue the following command in session1:
SQL> update sample set country='Italy' where id=2;

This update would cause Session1 to wait on the lock held by Session2, but Session2 is already waiting on this session. This causes a deadlock

Wednesday, July 30, 2008

Standby Database

create Standby Database

Architecture

Data Guard architecture incorporates the following items:

Primary Database - A production database that is used to create standby databases. The archive logs from the primary database are transferred and applied to standby databases. Each standby can only be associated with a single primary database, but a single primary database can be associated with multiple standby databases.

Standby Database - A replica of the primary database.

Log Transport Services - Control the automatic transfer of archive redo log files from the primary database to one or more standby destinations.

Network Configuration - The primary database is connected to one or more standby databases using Oracle Net.

Log Apply Services - Apply the archived redo logs to the standby database. The Managed Recovery Process (MRP) actually does the work of maintaining and applying the archived redo logs.

Role Management Services - Control the changing of database roles from primary to standby. The services include switchover, switchback and failover.

Data Guard Broker - Controls the creation and monitoring of Data Guard. It comes with a GUI and command line interface.

The services required on the primary database are:

Log Writer Process (LGWR) - Collects redo information and updates the online redo logs. It can also create local archived redo logs and transmit online redo to standby databases.

Archiver Process (ARCn) - One or more Archiver processes make copies of online redo logs either locally or remotely for standby databases.

Fetch Archive Log (FAL) Server - Services requests for archive redo logs from FAL clients running on multiple standby databases. Multiple FAL servers can be run on a primary database, one for each FAL request. .

The services required on the standby database are:

Fetch Archive Log (FAL) Client - Pulls archived redo log files from the primary site. Initiates transfer of archived redo logs when it detects a gap sequence.

Remote File Server (RFS) - Receives archived and/or standby redo logs from the primary database.

Archiver (ARCn) Processes - Archives the standby redo logs applied by the managed recovery process (MRP).

Managed Recovery Process (MRP) - Applies archive redo log information to the standby database

Data Guard Operational Prerequisites :
• Same Oracle software release must be used for both primary and standby databases. The operating system running on primary and standby locations must be same, but operating system release may not need to be same.
• The Primary Database must run in ARCHIVELOG mode.
• The hardware and Operating system architecture on primary and standby location must be same.
• Each primary and standby database must have its own control file.
• If primary and standby databases are placed on the same system, initialization parameters must be adjusted correctly.
• Primary database must be FORCE LOGGING mode.

Preparing Primary Database for Standby Database creation
Ensure the primary database in ARCHIVELOG mode

Ensure the primary database in ARCHIVELOG mode using following command.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:/oracle/ora92/database/archive/PRIM
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11

Enable database in FORCE LOGGING mode
Place primary database in FORCE LOGGING mode using following SQL statement:
SQL> alter database force logging;
Database altered.

Identify the primary database Datafiles
SQL> select name from v$datafile;

NAME
-------------------------------
/U01/ORACLE/PRIM/SYSTEM01.DBF
/U01/ORACLE/PRIM/UNDOTBS01.DBF
/U01/ORACLE/PRIM/CWMLITE01.DBF
/U01/ORACLE/PRIM/DRSYS01.DBF
/U01/ORACLE/PRIM/EXAMPLE01.DBF
/U01/ORACLE/PRIM/INDX01.DBF
/U01/ORACLE/PRIM/ODM01.DBF
/U01/ORACLE/PRIM/TOOLS01.DBF
/U01/ORACLE/PRIM/USERS01.DBF
/U01/ORACLE/PRIM/XDB01.DBF

10 rows selected.

Make Backup of Primary Database
Make a closed backup copy of primary database by performing following steps:

Shutdown the Primary Database

Issue the following statement to shutdown the primary database.
SQL> shutdown immediate;

Copy the Data files to standby location

Copy the redo log files and Data files to standby location.

Note: Primary Database must be shutdown while coping the files.

Restart the Primary Database

Execute following command to restart the Primary Database.
SQL> startup;

Create Control file for Standby Database

Issue the following command on primary database to create control file for the standby database.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
2 ‘/u02/oracle/oradata/stby/control_sb01.ctl’;

Database altered.

The filename for newly created standby control file must be different of current control file of the primary database. Also control file for standby database must be created after the last timestamp for the backup Datafiles.

Create pfile from for standby database from the primary database
Create pfile from the server parameter file of the primary database; this pfile can be copied to standby location and modified.
SQL> CREATE PFILE=‘/u01/oracle/ora92/database/initstby.ora’ from spfile;

File created.

Set initialization parameters on physical standby database
Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made. Edit created pfile from primary database.

db_name - Not modified. The same name as the primary database.

compatible - Not modified. The same as the primary database, 9.2.0.0.0.

control_files - Specify the path name and filename for the standby control file.

log_archive_start - Not modified. The same as the setting for the primary database, TRUE

standby_archive_dest - Specify the location of the archived redo logs that will be received from the primary database.

db_file_name_convert - Specify the location of the primary database datafiles followed by the standby location of the datafiles. This parameter will convert the filename of the primary database datafiles to the filename of the standby datafile filenames. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site then this parameter is required.

log_file_name_convert - Specify the location of the primary database logs followed by the standby location of the logs. This parameter will convert the filename of the primary database log to the filenames of the standby log. If the standby database is on the same system as the primary database or if the directory structure where the logs are located on the standby site is different from the primary site then this parameter is required.

log_archive_dest_1 - Specify the location where the redo logs are to be archived on the standby system. (If a switchover occurs and this instance becomes the primary database, then this parameter will specify the location where the online redo logs will be archived.)

standby_file_management - Set to AUTO.

remote_archive_enable - Set to TRUE.

instance_name - If this parameter is defined, specify a different value for the standby database than the primary database when the primary and standby databases reside on the same host.

lock_name_space - Specify the standby database instance name. Use this parameter when you create the physical standby database on the same system as the primary database. Change the INSTANCE_NAME parameter to a value other than its primary database value, and set this LOCK_NAME_SPACE initialization parameter to the same value that you specified for the standby database INSTANCE_NAME initialization parameter.

Also change the values of the parameters background_dump_dest, core_dump_dest and user_dump_dest to specify location of the standby database.

(Refer Annexure for initialization parameter settings for primary and standby database.)
Create a Window service
If standby database is running on windows system, then oradim utility is used to create windows service. Issue following command from the command prompt window
C:/>oradim -new -sid stby -intpwd stby -startmode manual

Configure listeners & tnsnames for standby and primary databases
Configure listeners in listeners.ora as follows
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u02/oracle/ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = Prim)
(ORACLE_HOME = /u02/oracle/ora92)
(SID_NAME = Prim)
)
(SID_DESC =
(GLOBAL_DBNAME = stby)
(ORACLE_HOME = /u02/oracle/ora92)
(SID_NAME = stby)
)
)
Restart the listeners using LSNRCTL utility.
% lsnrctl stop
% lsnrctl start
Also make an entry into tnsnames.ora for standby database.
stby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)

Start Physical standby database
Start up the stand by database using following commands
C:/>set oracle_sid=stby

C:/>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 25 17:13:26 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile=‘/u01/oracle/ora92/database/initstby.ora’ nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

SQL> alter database mount standby database;
Database altered.

Enabling archiving to Physical Standby Database
To configure archive logging from the primary database to the standby site the LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n parameters must be defined.

Issue following commands from primary database session:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=‘SERVICE=stby’ SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

System altered.

Initiate Log apply services
The example includes the DISCONNECT FROM SESSION option so that log apply services run in a background session.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL> recover managed standby database cancel;
Media recovery complete.

Now go to primary database prompt

SQL> alter system switch logfile;

Go to stand by database prompt

SQL> alter database open read only;

Database altered.

Verifying the Standby Database
On standby database query the V$ARCHIVED_LOG view to verify that redo log received.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME
-------------- ------------------- ----------------------
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52

Archive the current log on the primary database using following statement.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
On standby database query the V$ARCHIVED_LOG view
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME
-------------- ------------------- ----------------------
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52
17 25-APR-05 16:51:52 25-APR-02 17:34:00
Now connect scott/tiger@prim on primary database and create table or insert row in any table.

Now connect as sys on primary database and execute following SQL statement
SQL> alter system switch logfile;
On standby database execute following SQL statements
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database open read only;

Database altered.
And check whether the changes applied on the standby database or not.

Parameter file for Primary Database

*.aq_tm_processes=1
*.background_dump_dest=‘/u02/oracle/admin/Prim/bdump’
*.compatible=‘9.2.0.0.0’
*.control_files=‘/u01/oracle/prim/CONTROL01.CTL’,
‘/u01/oracle/prim/CONTROL02.CTL’,’/u01/oracle/prim/CONTROL03.CTL’
*.core_dump_dest=‘/u02/oracle/admin/Prim/cdump’
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=‘‘
*.db_file_multiblock_read_count=16
*.db_name=‘Prim’
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=PrimXDB)’
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name=‘Prim’
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1=‘LOCATION=C:/oracle/ora92/database/archive/PRIM MANDATORY’
*.log_archive_dest_2=‘SERVICE=stby LGWR SYNC AFFIRM’

The LGWR SYNC AFFIRM keywords indicate that the Log writer should synchronously write updates to the online redo logs to this location and wait for confirmation of the write before proceeding. The remote site will process and archive these standby redo logs to keep the databases synchronized. This whole process can impact performance greatly but provides maximum data security.

*.log_archive_dest_state_2=‘ENABLE’
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled=‘FALSE’
*.remote_login_passwordfile=‘EXCLUSIVE’
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled=‘FALSE’
*.timed_statistics=TRUE
*.undo_management=‘AUTO’
*.undo_retention=10800
*.undo_tablespace=‘UNDOTBS1’
*.user_dump_dest=‘/u02/oracle/admin/Prim/udump’

Parameter file for Standby Database

*.aq_tm_processes=1
*.background_dump_dest=‘/u02/oracle/admin/stby/bdump’
*.compatible=‘9.2.0.0.0’
*.control_files=‘/u02/oracle/oradata/stby/CONTROL_SB01.CTL’
*.core_dump_dest=‘/u02/oracle/admin/stby/cdump’
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=‘‘
*.db_file_multiblock_read_count=16
*.db_name=‘Prim’
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=PrimXDB)’
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name=‘stby’
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1=‘LOCATION=/u02/oracle/admin/stby/archive’
*.log_archive_dest_state_1=enable
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled=‘FALSE’
*.remote_login_passwordfile=‘NONE’
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled=‘FALSE’
*.timed_statistics=TRUE
*.undo_management=‘AUTO’
*.undo_retention=10800
*.undo_tablespace=‘UNDOTBS1’
*.user_dump_dest=‘/u02/oracle/admin/stby/udump’
*.standby_archive_dest=‘/u01/oracle/ora92/database/archive/PRIM’
*.db_file_name_convert=‘/u01/oracle/prim’,’/u02/oracle/oradata/stby’
*.log_file_name_convert=‘/u01/oracle/prim’,’/u02/oracle/oradata/stby’
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.lock_name_space=stby

Protect Primary Database
Now that Data Guard is configured and running the primary database can be prevented from applying updates unless the update has been sent to at least one standby location. Connect to the primary database and execute:

ALTER DATABASE SET STANDBY DATABASE PROTECTED;

Cancel Managed Standby Recovery
To stop managed standby recovery:

SQL> -- Cancel protected mode on primary
SQL> CONNECT sys/password@prim AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;
SQL>
SQL> -- Cancel recovery if necessary
SQL> CONNECT sys/password@stby1 AS SYSDBA
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
The database can subsequently be switched back to recovery mode as follows:

SQL> -- Startup managed recovery
SQL> CONNECT sys/password@stby1 AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT PFILE==‘/u01/oracle/ora92/database/initstby.ora’
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQL> -- Protect primary database
SQL> CONNECT sys/password@prim AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE PROTECTED;

Activating A Standby Database
If the primary database is not available the standby database can be activated as a primary database using the following statements:

SQL> -- Cancel recovery if necessary
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Since the standby database is now the primary database it should be backed up immediately. The previous primary database can then be configured as a standby.

Backup Standby Database
Backups of the standby database can only be performed if the database is shut down or in read only mode. Read only mode is best for managed recovery systems as archive logs will still be transfered during the backup process, thus preventing gap sequences. Once the server is in the desired mode simply copy the appropriate database files.

Database Switchover
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements:

-- Convert primary database to standby
CONNECT sys/change_on_install@prim1 AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT PFILE==‘/u01/oracle/ora92/database/initstby.ora’
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

-- Convert standby database to primary
CONNECT sys/change_on_install@stby1 AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP PFILE==‘/u01/oracle/ora92/database/initstby.ora’
This process has no affect on alternative standby locations. The process of converting the instances back to their original roles is known as a Switchback. The switchback is accomplished by performing another switchover.

Database Failover
Graceful Database Failover occurs when database failover causes a standby database to be converted to a primary database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
This process will recovery all or some of the application data using the standby redo logs, therefore avoiding reinstantiation of other standby databases. If completed successfully, only the primary database will need to be reinstatiated as a standby database.

Forced Database Failover changes one standby database to a primary database. Application data may be lost neccessitating the reinstantiation of the primary and all standby databases.

Sunday, July 27, 2008

Clone the database using RMAN

Clone an Oracle database using RMAN duplicate (same server)

This procedure will clone a database onto the same server using RMAN duplicate.

1. Backup the source database.
To use RMAN duplicate an RMAN backup of the source database is required. If there is already one available, skip to step 2. If not, here is a quick example of how to produce an RMAN backup. This example assumes that there is no recovery catalog available:

rman target sys@targetdb nocatalog

backup database plus archivelog format '/u01/ora_backup/rman/%d_%u_%s';
This will backup the database and archive logs. The format string defines the location of the backup files. Alter it to a suitable location.

2. Produce a pfile for the new database
This step assumes that the source database is using a spfile. If that is not the case, simply make a copy the existing pfile.

Connect to the source database as sysdba and run the following:

create pfile='init.ora' from spfile;
This will create a new pfile in the $ORACLE_HOME/dbs directory.

The new pfile will need to be edited immediately. If the cloned database is to have a different name to the source, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary.

Because in this example the cloned database will reside on the same machine as the source, Oracle must be told how convert the filenames during the RMAN duplicate operation. This is achieved by adding the following lines to the newly created pfile:

db_file_name_convert=(,)
log_file_name_convert=(,)
Here is an example where the source database scr9 is being cloned to dg9a. Note the trailing slashes and lack of quotes:

db_file_name_convert=(/u01/oradata/scr9/,/u03/oradata/dg9a/)
log_file_name_convert=(/u01/oradata/scr9/,/u03/oradata/dg9a/)

3. Create bdump, udump & cdump directories
Create bdump, udump & cdump directories as specified in the pfile from the previous step.

4. Add a new entry to oratab, and source the environment
Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.

Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:

echo $ORACLE_SID
If this doesn't output the new database sid go back and investigate why not.

5. Create a password file
Use the following command to create a password file (add an appropriate password to the end of it):

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=
6. Duplicate the database
From sqlplus, start the instance up in nomount mode:

startup nomount
Exit sqlplus, start RMAN and duplicate the database. As in step 1, it is assumed that no recovery catalog is available. If one is available, simply amend the RMAN command to include it.

rman target sys@targetdb nocatalog auxiliary /

duplicate target database to clonedb;

This will restore the database and apply some archive logs. It can appear to hang at the end sometimes. Just give it time - I think it is because RMAN does a 'shutdown normal'.

If you see the following error, it is probably due to the file_name_convert settings being wrong. Return to step 2 and double check the settings.

RMAN-05001: auxiliary filename '%s' conflicts with a file used by the target database
Once the duplicate has finished RMAN will display a message similar to this:

database opened
Finished Duplicate Db at 26-FEB-05

RMAN>
Exit RMAN.

7. Create an spfile
From sqlplus:

create spfile from pfile;

shutdown immediate
startup
Now that the clone is built, we no longer need the file_name_convert settings:

alter system reset db_file_name_convert scope=spfile sid='*'
/

alter system reset log_file_name_convert scope=spfile sid='*'
/
8. Optionally take the clone database out of archive log mode
RMAN will leave the cloned database in archive log mode. If archive log mode isn't required, run the following commands from sqlplus:

shutdown immediate
startup mount
alter database noarchivelog;
alter database open;

Wednesday, July 23, 2008

RMAN

Recovery Manager (RMAN)
Recovery manager is a platform independent utility for coordinating your backup and restoration procedures across multiple servers. In my opinion it's value is limited if you only have on or two instances, but it comes into it's own where large numbers of instances on multiple platforms are used. The reporting features alone mean that you should never find yourself in a position where your data is in danger due to failed backups.
CONNECT sys/password@rmandb AS SYSDBA

-- Create tablepsace to hold repository
CREATE TABLESPACE "RMAN" DATAFILE '/u01/oracle/oradata/rmandb/RMAN01.DBF' SIZE 6208K EXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTO;
-- Create rman schema owner
CREATE USER rman IDENTIFIED BY rman
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;

GRANT connect, resource, recovery_catalog_owner TO rman;
Then create the recovery catalog:
C:>rman
catalog=rman/rman@rmandb
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to recovery catalog databaserecovery catalog is not installed
RMAN> create catalog tablespace "RMAN";
recovery catalog created
RMAN> exit
Recovery Manager complete.

Register DatabaseEach database to be backed up by RMAN must be registered:

$rman
catalog=rman/rman@rmandb target=sys/password@test
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: test (DBID=1371963417)connected to recovery catalog database
RMAN> register database;
database registered in recovery catalogstarting full resync of recovery catalogfull resync complete

RMAN>Existing user-created backups can be added to the catalog using:

RMAN> catalog datafilecopy '/u01/oracle/oradata/TSH1.dbf';RMAN> catalog archivelog 'log1', 'log2', 'log3', ... 'logN';
Full BackupFirst we configure several persistant parameters for this instance:
RMAN> configure retention policy to recovery window of 7 days;
RMAN> configure default device type to disk;
RMAN> configure controlfile autobackup on;
RMAN> configure channel device type disk format '/uo1\test\Backup%d_DB_%u_%s_%p';
Next we perform a complete database backup using a single command:

RMAN> run
{
2> backup database plus archivelog;
3> delete noprompt obsolete;
4> }
The recovery catalog should be resyncronized on a regular basis so that changes to the database structure and presence of new archive logs is recorded. Some commands perform partial and full resyncs implicitly, but if you are in doubt you can perform a full resync using the follwoing command:

RMAN> resync catalog;
Restore & Recover The Whole DatabaseIf the controlfiles and online redo logs are still present a whole database recovery can be achieved by running the following script:

run
{
shutdown immediate;
# use abort if this fails
startup mount;
restore database;
recover database;
alter database open;
}
This will result in all datafiles being restored then recovered.
RMAN will apply archive logs as necessary until the recovery is complete.
At that point the database is opened. If the tempfiles are still present you can issue a command like like the following for each of them:

sql "ALTER TABLESPACE temp ADD TEMPFILE ''/u01/oracle/oradata/test/temp01.dbf'' REUSE";
If the tempfiles are missing they must be recreated as follows:

sql "ALTER TABLESPACE temp ADD TEMPFILE ''/u01/oracle/oradata/test/temp01.dbf'' SIZE 100M AUTOEXTEND ON NEXT 64K";

*******************************************************************************************************************************

Backup script For NON RAC

run
{
allocate channel c1 type disk;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/u01/rman_%d_%u_%s_%T';
release channel c1;
allocate channel c1 type disk;
backup as compressed backupset archivelog all FORMAT '/u01/rman_arch_%d_%u_%s_%T';
release channel c1;
}

Backup script For RAC

run
{
allocate channel c1 type disk connect 'syst/syst@car1';
allocate channel c2 type disk connect 'syst/syst@car2';
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '+DATA/rman_%d_%u_%s_%T';
release channel c1;
release channel c2;
allocate channel c1 type disk connect 'syst/syst@car1';
allocate channel c2 type disk connect 'syst/syst@car2';
backup as compressed backupset archivelog all FORMAT '+DATA/rman_arch_%d_%u_%s_%T';
release channel c1;
release channel c2;
}

Tuesday, July 22, 2008

Errors

How to enable autotrace?

SQL> set autotrace on

Cannot find the Session Identifier. Check PLUSTRACE role is enabled

Error enabling STATISTICS report

Action:
A PLAN_TABLE table must exist in schema and PLUSTRACE role granted beforeAUTOTRACE usage.

1. Login as system

sqlplus system/system

2. Then run following script
@$ORACLE_HOME/sqlplus/admin/plustrce.sql

3. grant plustrace to <>;

4.Then issue following commad

grant select any dictionary to <> ;

About Crontab

Crontab(Cron Table) :

Crontab is is a file which contains the schedule of cron entries to be run and at specified times.

crontab -l : Display crontab file

crontab -e : Edit existing cron file or create if it doesn't already exist.

crontab -r : Remove crontab file

Syntax:

* * * * * command to be executed

First * -------->minutes(0-59)

Second * -------->Hour(0-23)

Third * -------->Day of Month(1-31)

Fourth * -------->Month (1-12)

Fifth * --------->Weeks (0-6) 0=sunday

Ex:

10 20 * * * ls -lrt /home/test ------------------> This command will be executed at 8.10 pm daily

Generate Log file:

10 20 * * * ls -lrt /home/test 2>&1 >> /home/test/list.log

Friday, July 18, 2008

Cloning

Clone an Oracle database using a cold backup

This procedure will clone a database using a cold copy of the source database files. If a cold backup of the database is available, restore it to the new location and jump to step 2.

Step 1. Identify and copy the database files

With the source database started, identify all of the database's files. The following query will display all datafiles, tempfiles and redo logs:

set lines 100 pages 999
col name format a50
select name, bytes
from (select name, bytes
from v$datafile
union all
select name, bytes
from v$tempfile
union all
select lf.member "name", l.bytes
from v$logfile lf
, v$log l
where lf.group# = l.group#) used
, (select sum(bytes) as poo
from dba_free_space) free
/

Make sure that the clone databases file-system is large enough and has all necessary directories. If the source database has a complex file structure, you might want to consider modifying the above sql to produce a file copy script.

Stop the source database with:

shutdown immediate

Copy, scp or ftp the files from the source database/machine to the target. Do not copy the control files across. Make sure that the files have the correct permissions and ownership.

Start the source database up again

startup

Step 2. Produce a pfile for the new database

This step assumes that you are using a spfile. If you are not, just copy the existing pfile.

From sqlplus:

create pfile='init.ora' from spfile;

This will create a new pfile in the $ORACLE_HOME/dbs directory.

Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.

Note. Pay particular attention to the control locations.

Step 3. Create the clone controlfile

Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:

alter database backup controlfile to trace as '/home/oracle/cr_.sql'
/

The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:

Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).
Remove any lines that start with –
Remove any lines that start with a #
Remove any blank lines in the 'CREATE CONTROLFILE' section.
Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'
Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.

If the file paths are being changed, alter the file to reflect the changes.

Here is an example of how the file would look for a small database called dg9a which isn't in archivelog mode:
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "Test" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/u03/oradata/test/redo01.log' SIZE 100M,
GROUP 2 '/u03/oradata/test/redo02.log' SIZE 100M,
GROUP 3 '/u03/oradata/test/redo03.log' SIZE 100M
DATAFILE
'/u03/oradata/test/system01.dbf',
'/u03/oradata/test/undotbs01.dbf',
'/u03/oradata/test/cwmlite01.dbf',
'/u03/oradata/test/drsys01.dbf',
'/u03/oradata/test/example01.dbf',
'/u03/oradata/test/indx01.dbf',
'/u03/oradata/test/odm01.dbf',
'/u03/oradata/test/tools01.dbf',
'/u03/oradata/test/users01.dbf',
'/u03/oradata/test/xdb01.dbf',
'/u03/oradata/test/andy01.dbf',
'/u03/oradata/test/psstats01.dbf',
'/u03/oradata/test/planner01.dbf'
CHARACTER SET WE8ISO8859P1
;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oradata/test/temp01.dbf'
SIZE 104857600 REUSE AUTOEXTEND OFF;

Step 4. Add a new entry to oratab and source the environment
Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:

echo $ORACLE_SID

If this doesn't output the new database sid go back and investigate.

Step 5. Create the a password file

Use the following command to create a password file (add an appropriate password to the end of it):

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password =

Step 5. Create the new control file(s)

Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:

sqlplus "/ as sysdba"

@/home/oracle/cr_

It is quite common to run into problems at this stage. Here are a couple of common errors and solutions:

ORA-01113: file 1 needs media recovery
You probably forgot to stop the source database before copying the files. Go back to step 1 and recopy the files.
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: controlfile could not be created
ORA-00202: controlfile: '/u03/oradata/dg9a/control01.ctl'
ORA-27038: skgfrcre: file exists

Double check the pfile created in step 2. Make sure the control_files setting is pointing at the correct location. If the control_file setting is ok, make sure that the control files were not copied with the rest of the database files. If they were, delete or rename them.

Step 6. Perform a few checks

If the last step went smoothly, the database should be open. It is advisable to perform a few checks at this point:

Check that the database has opened with:

select status from v$instance;
The status should be 'OPEN'

Make sure that the datafiles are all ok:
select distinct status from v$datafile;
It should return only ONLINE and SYSTEM.

Take a quick look at the alert log too.

Step 7. Set the databases global name

The new database will still have the source databases global name. Run the following to reset it:

alter database rename global_name to <>

Step 8. Create a spfile
From sqlplus:

create spfile from pfile;

Step 9. Change the database ID

If RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.

From sqlplus:
shutdown immediate
startup mount
exit

From unix:
nid target=/
NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:
shutdown immediate
startup mount
alter database open resetlogs
/

Step 10. Configure TNS

Add entries for new database in the listener.ora and tnsnames.ora as necessary.

Clone an Oracle database using an online/hot backup

This procedure will clone a database using a online copy of the source database files. Before beginning though, there are a few things that are worth noting about online/hot backups:
When a tablespace is put into backup mode, Oracle will write entire blocks to redo rather than the usual change vectors. For this reason, do not perform a hot backup during periods of heavy database activity - it could lead to a lot of archive logs being created.

This procedure will put all tablespaces into backup mode at the same time. If the source database is quite large and you think that it might take a long time to copy, consider copying the tablespaces one at a time, or in groups. While the backup is in progress, it will not be possible to take the tablespaces offline normally or shut down the instance. Ok, lets get started...

1. Make a note of the current archive log change number

Because the restored files will require recovery, some archive logs will be needed. This applies even if you are not intending to put the cloned database into archive log mode. Work out which will be the first required log by running the following query on the source database. Make a note of the change number that is returned:

select max(first_change#) chngfrom v$archived_log
/

2. Prepare the begin/end backup scripts

The following sql will produce two scripts; begin_backup.sql and end_backup.sql. When executed, these scripts will either put the tablespaces into backup mode or take them out of it:

set lines 999
pages 999
set verify off
set feedback off
set heading off

spool begin_backup.sql

select 'alter tablespace ' tablespace_name ' begin backup;'
from dba_tablespaces where contents != 'TEMPORARY'
order by tablespace_name
/
spool off

spool end_backup.sql

select 'alter tablespace ' tablespace_name ' end backup;' from dba_tablespaces
where contents != 'TEMPORARY' order by tablespace_name
/
spool off

3. Put the source database into backup mode

From sqlplus, run the begin backup script created in the last step:
@begin_backup
This will put all of the databases tablespaces into backup mode.

4. Copy the files to the new locationCopy, scp or ftp the files from the source database/machine to the target.

Do not copy the control files across. Make sure that the files have the correct permissions and ownership.

5. Take the source database out of backup modeOnce the file copy has been completed, take the source database out of backup mode. Run the end backup script created in step 2.
From sqlplus:
@end_backup

6. Copy archive logsIt is only necessary to copy archive logs created during the time the source database was in backup mode. Begin by archiving the current redo:

alter system archive log current;

Then, identify which archive log files are required. When run, the following query will ask for a change number. This is the number noted in step 1.

select namefrom v$archived_logwhere first_change# >= &change_noorder by name
/
Create an archive directory in the clone database.s file system and copy all of the identified logs into it.

7. Produce a pfile for the new databaseThis step assumes that you are using a spfile. If you are not, just copy the existing pfile.
From sqlplus:
create pfile='init.ora' from spfile;
This will create a new pfile in the $ORACLE_HOME/dbs directory.

Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.

Ensure that the archive log destination is pointing to the directory created in step 6.

8. Create the clone controlfileCreate a control file for the new database.
To do this, connect to the source database and request a dump of the current control file.
From sqlplus:
alter database backup controlfile to trace as '/home/oracle/cr_.sql'
/

The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:

Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).

Remove any lines that start with --Remove any lines that start with a #Remove any blank lines in the 'CREATE CONTROLFILE' section.
Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'
Remove the line 'ALTER DATABASE OPEN RESETLOGS;'
Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file.

Make sure that you hang onto the command, it will be used later.

Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.

If the file paths are being changed, alter the file to reflect the changes.
Here is an example of how the file would look for a small database called test which isn't in archivelog mode:

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "test" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE GROUP 1 '/u03/oradata/test/redo01.log' SIZE 100M,
GROUP 2 '/u03/oradata/test/redo02.log' SIZE 100M,
GROUP 3 '/u03/oradata/test/redo03.log' SIZE 100M
DATAFILE '/u03/oradata/test/system01.dbf',
'/u03/oradata/test/undotbs01.dbf',
'/u03/oradata/test/cwmlite01.dbf',
'/u03/oradata/test/drsys01.dbf',
'/u03/oradata/test/example01.dbf',
'/u03/oradata/test/indx01.dbf',
'/u03/oradata/test/odm01.dbf',
'/u03/oradata/test/tools01.dbf',
'/u03/oradata/test/users01.dbf',
'/u03/oradata/test/xdb01.dbf',
'/u03/oradata/test/andy01.dbf',
'/u03/oradata/test/psstats01.dbf',
'/u03/oradata/test/planner01.dbf'
CHARACTER SET WE8ISO8859P1;

9. Add a new entry to oratab and source the environmentEdit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:

echo $ORACLE_SID

If this doesn't output the new database sid go back and investigate.

10. Create the a password fileUse the following command to create a password file (add an appropriate password to the end of it):

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=

11. Create the new control file(s)Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:

sqlplus "/ as sysdba"
@/home/oracle/cr_

If all goes to plan you will see the instance start and then the message 'Control file created'.

12. Recover and open the databaseThe archive logs that were identified and copied in step 6 must now be applied to the database. Issue the following command from sqlplus:

recover database using backup controlfile until cancel

When prompted to 'Specify log' enter 'auto'. Oracle will then apply all the available logs, and then error with ORA-00308. This is normal, it simply means that all available logs have been applied. Open the database with reset logs: alter database open resetlogs;

13. Create temp filesUsing the 'ALTER TABLESPACE TEMP...' command from step 8,
create the temp files. Make sure the paths to the file(s) are correct, then run it from sqlplus.

14. Perform a few checksIf the last couple of steps went smoothly, the database should be open. It is advisable to perform a few checks at this point:

Check that the database has opened with:
select status from v$instance;
The status should be 'OPEN'
Make sure that the datafiles are all ok:
select distinct status from v$datafile;
It should return only ONLINE and SYSTEM.
Take a quick look at the alert log too.

15. Set the databases global nameThe new database will still have the source databases global name. Run the following to reset it:

alter database rename global_name to

16. Create a spfileFrom sqlplus:

create spfile from pfile;

17. Change the database ID
If RMAN is going to be used to back-up the database, the database ID must be changed.
If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.
From sqlplus:

shutdown immediate
startup mount
exit

From unix:
nid target=/
NID will ask if you want to change the ID.
Respond with 'Y'. Once it has finished,

start the database up again in sqlplus:

shutdown immediate
startup mount

alter database open resetlogs/

18. Configure TNSAdd entries for new database in the listener.ora and tnsnames.ora as necessary.

Oracle DBA tips

Thursday, October 23, 2008

ASM

How to send email from Oracle server.

How to send email from Oracle server.

Wednesday, October 22, 2008

Pre-Install checks for RDBMS

How to Identify Oracle 64 bit or 32 bit

How to Identify OS 64 bit or 32 bit

Friday, September 19, 2008

Find ORA errors in windows

Wednesday, August 6, 2008

How to use MERGE in Oracle

How tnsnames.ora and sqlnet.ora file work together?

RDA

upgrade

Monday, August 4, 2008

Upgrade database from 9i to 10g

SGA

How Deadlock occurs?

Wednesday, July 30, 2008

Standby Database

Sunday, July 27, 2008

Clone the database using RMAN

Wednesday, July 23, 2008

RMAN

Tuesday, July 22, 2008

Errors

About Crontab

Friday, July 18, 2008

Cloning

Labels

About Me