Cloning a Database

I routinely refresh development and test databases, and they will sometimes need to clone databases to test backup and recovery strategies.

• By using the RMAN DUPLICATE command
• By using the OEM Database Control
• By manually performing the copy with SQL

Using RMAN to Clone a Database
RMAN provides the DUPLICATE command, which uses the backups of a database to create a newdatabase. The files are restored to the target database, after which an incomplete recovery is performedand the new database is opened with the OPEN RESETLOGS command.

1. Create a new init.ora file for the auxiliary database. The init.ora file should have the following parameters, with the data files and log file parameters changed to ensure that theoriginal database files arent used for the new database:
   
   - DB_FILE_NAME_CONVERT
   - LOG_FILE_NAME_CONVERT
2. Start the target database instance.
   
   SQL > startup nomount
3. Connect the recovery catalog to the target database and the auxiliary database
   
   RMAN > CONNECT target / catalog rman/rman1@catalog_db auxiliary sys/password@auxiliary_db
4. Issue the RMAN DUPLICATE command, as follows:
   
   RMAN> DUPLICATE TARGET DATABASE TO auxiliary_db
   pfile =/u01/app/oracle/10.2.0/db_1/dbs/init_auxiliary_db;
5. Opens the duplicated database with the RESETLOGS
   
   SQL> alter database open resetlogs

Manually Cloning a Database
To clone a database manually, you need to first use the operating system to copy all of the source database files to the target location.

1. Copy the prod database files to the target location.
2. Prepare a text file for the creation of a control file for the new database as follows:
   
   SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
   
3. On the target location, create all the directories for the various files.
   
4. change SID, path, of the backup control trace file controlfile_open.sql
5. Run the following command
   
   SQL> startup nomount;
   SQL> @controlfile_open.sql

Blocking Locks

Blocking Locks

A blocking lock occurs when a lock placed onfrom accessing the same object or objects. The information—it tells you which sessions are currentlyobject is presently waiting. You can combine in the V$SESSION tables, to find out who is holding

SQL> SELECT a.username, a.program, a.sid,
FROM v$session a, dba_blockers b
WHERE a.sid = b.holding_session;

DBA_BLOCKERS and DBA_WAITERS

SQL> SELECT waiting_session, blocking_session, lock_type
FROM DBA_BLOCKERS;

For 10g there is additional columns to check blocking session

select lpad(' ',3*(level-1)) SID SID, USERNAME, TERMINAL, CLIENT_INFO, EVENT
from V$SESSION
START WITH BLOCKING_SESSION_STATUS='VALID'
connect by prior BLOCKING_SESSION = SID

Tuning the log buffer

To tune the value for LOG_BUFFER first determine the space request ratio, this is the ratio of redo log space requests to redo log requests:

Select name, value from v$sysstat
Where name in ('redo log space requests', 'redo entries');

If the ratio (redo log space requests / redo entries) is greater than 1:5000, then increase the size of the redo log buffer until the space request ratio stops falling.
Alternately, if memory is not a constraint then try to reduce the number of times that a process had to wait for the log cache to be flushed:

Select name, value from v$sysstat
Where name = 'redo log space requests';

The number of waits should always be zero. If not, increase the size of LOG_BUFFER, until the number returns to zero. Typically, there is no advantage in setting this beyond 1M.
If you want to know how long processes had to wait as well as the number of times then try the following script instead:

Select name, value from v$sysstat
Where name in ('redo log space requests', 'redo log space wait time');

This shows the time in units of 10 milliseconds. Be ware that because of the time granularity, 'redo log space requests' may be greater than zero whilst 'redo log space wait time' is zero. This happens if none of the waits were for 10ms or longer. Unless you have a busy server having 'redo log space wait time' of (or near) zero may indicate an acceptable level of tuning.

Link of Oracle tuning
http://www.cryer.co.uk/brian/oracle/tuning.htm

Automatic Startup Scripts on Linux

Automatic Startup Scripts on Linux

Create a file in the /etc/init.d/ directory, in this case the file is called myservice, containing the commands you wish to run at startup and/or shutdown.

Use the chmod command to set the privileges to 750:
chmod 750 /etc/init.d/dbora

Link the file into the appropriate run-level script directories:
ln -s /etc/init.d/myservice /etc/rc0.d/K10dbora
ln -s /etc/init.d/myservice /etc/rc3.d/S99dbora

Associate the myservice service with the appropriate run levels:
chkconfig --level 345 dbora on

The script should now be automatically run at startup and shutdown (with "start" or "stop" as a commandline parameter) like other service initialization scripts.

Method 2

Create a script at /usr/local/bin with the following information (eg dbora)

/home/oracle/Orahome1/bin/lsnrctl start
sqlplus /nolog << EOF
conn / as sysdba;
startup;
exit;
EOF

Add the following lines into /etc/rc.d/rc.local
su - oracle -c "/usr/local/bin/startdb"