Restoring an RMAN Backup to Another Node /Clone Database

In certain circumstances, it may be desirable to restore a database from an RMAN backup onto a machine other than the original host. 
For example,Restore and recover data at a given point in time for UAT/Testing, or to duplicate a production instance.
The example assumes:
the target database is on host A
the database is to be restored onto host B
the directory structure of host B is different to host A
the ORACLE_SID will not change for the restored database
a recovery catalog is being used
the backups were carried out to disk (for illustrative purposes, and to disassociate from any media manager specific issues)

The following steps are required:
1. backup the target on host A
2. list the datafile locations on host A
3. make the backup available to host B
4. make a copy of the init.ora available to host B
5. edit the init.ora to reflect directory structure changes
6. configure SQL*Net connectivity from host to the recovery catalog and duplicated database
7. set up a password file for the duplicated database
8. startup nomount the duplicated database
9. RMAN restore the controlfile(s)
10. mount the database
11. restore and rename the datafiles
12. recover and open the database
These steps are expanded further below.

1.Backup the Target on Host A
The target database needs to be backed up using RMAN. The following  commands of RMAN doing an online database backup. In this example, the backup sets are written to disk.

RMAN>
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
#backup the whole db
backup tag whole_database_open format '/oracle/backups/df_%u' database;
# switch out of the current logfile
sql 'alter system archive log current';
#backup the archived logs
backup archivelog all format '/oracle/backups/al_%u';
# backup a copy of the controlfile that contains records for the other backups just made
backup current controlfile tag = cf1 format '/oracle/backups/cf_%u';
}

2.List Datafile Locations on Host A
The datafile numbers and location on host A are required. These datafile locations will change on host B 
select file#, name from v$datafile;
file#   name
-----   -----------------------------------------------------------
1       /oracle/OFA_base/u01/oradata/TEST/system01.dbf
2       /oracle/OFA_base/u01/oradata/TEST/rbs01.dbf
3       /oracle/OFA_base/u01/oradata/TEST/temp01.dbf
4       /oracle/OFA_base/u01/oradata/TEST/tools01.dbf
5       /oracle/OFA_base/u01/oradata/TEST/users01.dbf
6       /oracle/OFA_base/u01/oradata/TEST/users02.dbf
7       /oracle/OFA_base/u01/oradata/TEST/rbs02.dbf
8       /oracle/OFA_base/u01/oradata/TEST/rcvcat.dbf

The log file names should also be recorded
select group#, member from v$logfile;
group#    member
---------   -----------------------------------------------------------
1            /oracle/OFA_base/u01/oradata/TEST/redo01.log
2            /oracle/OFA_base/u01/oradata/TEST/redo02.log
3            /oracle/OFA_base/u01/oradata/TEST/redo03.log

3 Make the Backups Available to Host B
3.1 Disk Backups
During restore, RMAN will expect the backup sets to be located in the same directory as written to during the backup. For disk backups, the DBA can accomplish this in many ways: set up an NFS directory, mounted on both host A and host B create the same directory structure on host A and host B, use of symbolic links on host B,FTP backup to host B.
If backup directory is not same at HOST B then catalog backup files using RMAN.
3.2 Tape Backups
The media management software must be configured such that host B is a media manager client, and can read the backup sets. The media management vendor should be consulted for support on this issue.

4. and 5. init.ora on host B
The "init.ora" needs to be made available on host B. Any location specific parameters must be ammended. For example,
*_dump_dest
log_archive_dest*
control_files

6. SQL*Net configuration
If running rman from host A:
a. connectivity to the catalog remains unchanged
b. configure tnsnames.ora on host A to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database
If running rman from host B:
a. configure tnsnames.ora on host B to connect to catalog listener.ora on catalog host remains unchanged
b. configure tnsnames.ora on host B to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database
If running rman from host C (ie, neither host A or host B):
a. connectivity to the catalog remains unchanged
b. configure tnsnames.ora on host C to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database

7. Setup Password File
In order to allow RMAN remote connections, a password file must be setup for the duplicated database. 
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password1

8. Startup nomount the database
SVRMGR> startup nomount pfile=<location of init.ora>

9. RMAN restore the controlfile(s)
Restore the controlfile(s). 
RMAN>
run{
allocate channel c1 type disk;
restore controlfile;
}

10. Mount the database
SVRMGR> alter database mount;

11. Rename and restore the datafiles
Rename and Restore the Files, and perform database recovery. RMAN can be used to change the location of the datafiles from the location on host A (see Section 2) to the new location on host B.
RMAN>
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
set newname for datafile 1 to '/oracle/datafiles/system01.dbf';
set newname for datafile 2 to '/oracle/datafiles/rbs01.dbf';
set newname for datafile 3 to '/oracle/datafiles/temp01.dbf';
set newname for datafile 4 to '/oracle/datafiles/tools01.dbf';
set newname for datafile 5 to '/oracle/datafiles/users01.dbf';
set newname for datafile 6 to '/oracle/datafiles/users02.dbf';
set newname for datafile 7 to '/oracle/datafiles/rbs02.dbf';
set newname for datafile 8 to '/oracle/datafiles/rcvcat.dbf';
restore database;
switch datafile all;
}


12. Recover and open the database
Perform incomplete recovery:
SVRMGR> recover database using backup controlfile until cancel;
Forward the database applying archived redo log files until you decide to stop recovery by typing cancel at the prompt (assuming that you have required archived redo log files in the log_archive_dest directory). You may archive the source database redo log files and apply them at the target database if required.
SVRMGR> alter database open resetlogs;
Note: this will create the online redo logs in the same location as that on host A. If this directory location does not exist, then this will fail with:
ora344 : unable to recreate online log <name>
The workaround is to rename the logfiles prior to opening the database:
SVRMGR> alter database rename file '<host A location>' to '<host B location>';
Alternatively, the logfile groups can be dropped and recreated. 

Read more

Full Database Recovery or Disaster Recovery.

You use the RESTORE and RECOVER commands to restore and recover the database.RMAN checks last good backup set and restore the datafiles to the state they were in when that backup set was created. When restoring database files with RMAN, it reads the datafile header and makes the determination as to whether the file needs to be restored.
 
If you cannot restore datafiles to their default locations, then you must update the control file to reflect the new locations of the datafiles. Use the RMAN SET NEWNAME command within a RUN command to specify the new filename. Afterward, use a SWITCH command, which is equivalent to using the SQL statement ALTER DATABASE RENAME FILE, to update the names of the datafiles in the control file. SWITCH DATAFILE ALL updates the control file to reflect the new names for all datafiles for which a SET NEWNAME has been issued in a RUN command.(Restore files to another location will  be demonstrated in another post)

The recovery is done by allocating a channel for I/O and then issuing the RMAN restore database command. The database must be in MOUNT state when restoring or recovering the entire database.

SQL> startup mount;
   or
RMAN> startup force mount;

Then connect to rman and perform:
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

Another option with controlfile restoration
SET DBID <database_id>; # use database id from RMAN, not required if using recovery catalog
CONNECT TARGET <target_connect_string>;
STARTUP NOMOUNT;
RUN
{
# You need to allocate channels if not using recovery catalog.
ALLOCATE CHANNEL CH1 TYPE DISK;
# Optionally you can use SET NEWNAME and SWITCH commands to restore datafiles to a new location.
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

Another Method: Restore Spfile and Controlfile
1 Assuming that we have a full backup under /bkp
2 Start RMAN
$ rman target / nocatalog
3 Start the DB (nomount) and restore the pfile
RMAN> set DBID=248804095;
startup nomount;
run
{
restore spfile to pfile '/oracle/product/10.2.0/dbs/initTEST.ora'  from'/bkp/c2488040952009013004';
shutdown immediate;
}
4 Start the DB with the pfile that we just restore:
RMAN> set DBID=248804095;
RMAN> startup nomount pfile = '/oracle/product/10.2.0/dbs/initTEST.ora';
5 Restore the control files
run
{
restore controlfile from '/bkp/c2488040952009013004';
alter database mount;
}
6 Restore the data files
run
{
restore database;
recover database;
}
7 Start the DB
RMAN> alter database open resetlogs;

Use RESETLOGS after a point in time recovery  or recover a database using a backup of the control file. RESETLOGS will initialize the logs, reset your log sequence number, and start a new "incarnation" of the database. 

During the recovery, RMAN automatically restores backups of any needed archived redo logs. If  RMAN restores archived redo logs to the flash recovery area during a recovery, then it automatically deletes the restored logs after applying them to the datafiles. Otherwise, you can use the DELETE ARCHIVELOG command to delete restored archived redo logs from disk when they are no longer needed for recovery. For example, you can enter the following command: RECOVER DATABASE DELETE ARCHIVELOG;

Read more

Open Physical Standby For Read Write Testing and Flashback

Open the Standby database in read write mode for any reporting or testing and then move it back to standby database using the flashback technology.

Using a combination of Data Guard, restore points, and Flashback Database, a physical standby database can be opened temporarily in read/write mode for development, reporting, or testing purposes, and then flashed back to a point in the past to be reverted back to a physical standby database. When the database is flashed back, Data Guard automatically synchronizes the standby database with the primary database, without the need to re-create the physical standby database from a backup copy of the primary database.

Perform the following steps to activate the physical standby database as a production database and later resynchronize it with the primary database.

Step 1 - In Standby database

A) Set up a flash recovery area. 

If Flash Recovery Area ( FRA ) is not configured in the standby then enable it and make sure to give enough space for to FRA

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G; 

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oracle/flashback';

B) Cancel Redo Apply and create a guaranteed restore point.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  

SQL> CREATE RESTORE POINT Standby_flashback_testing GUARANTEE FLASHBACK DATABASE; 

To Confirm the details of restore point and its scn and time stamp run

SQL> select NAME,SCN,TIME from v$restore_point; 

NAME                          SCN              TIME 

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

STANDBY_FLASHBACK_TESTING     22607810         12-APR-09 01.10.21.000000000 P

Step 2 - In Primary Database

A) On the primary database, switch logs so the SCN of the restore point will be archived on the physical standby database. When using standby redo log files, this step is essential to ensure the database can be properly flashed back to the restore point.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

B) Defer log archive destinations pointing to the standby that will be activated.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

Step 3 - In Standby database

A) Activate the physical standby database:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Once its done you can check the cotnrolfile status will be changed from Stnadby to Current

SQL> select CONTROLFILE_TYPE from v$database; 

CONTROL 

------- 

CURRENT

B) Then open the database.

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; 

SQL> ALTER DATABASE OPEN;

Step 4 - In Standby database

Once the standby database has been activated, you can run reporting tools or perform other testing and activities for days or even weeks, independent of the primary database

NOTE—

Any results stored in the activated database will be lost when you later flash back the database. Results that should be saved must be copied out of the activated database before flashing it back.

For example :

SQL> create table testing ( col1 varchar2 (100)); 

Table created. 

SQL> insert into testing values ( 'testing for flashback on standby database'); 

1 row created. 

SQL> commit; 

Commit complete.

Revert the active standby database back to Physical standby database 

Step 1 - In standby database

A1. Mount the database.

A2. Flashback the database to restore point.

SQL> STARTUP MOUNT FORCE;

ORACLE instance started. 

Total System Global Area  289406976 bytes 

Fixed Size                  1290208 bytes 

Variable Size             159383584 bytes 

Database Buffers          125829120 bytes 

Redo Buffers                2904064 bytes 

Database mounted. 

SQL> FLASHBACK DATABASE TO RESTORE POINT Standby_flashback_testing ;

You can confirm the same by checking the controlfile status. It will be now backup controlfile

SQL> select controlfile_type from v$database; 

CONTROL 

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

BACKUP

B) Convert to Standby database

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;  

SQL> STARTUP MOUNT FORCE;  

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

SQL> select controlfile_type from v$database;

CONTROL 

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

STANDBY

Step 2 - In standby database 

A) Put the standby database in managed recovery mode.Let archive gap resolution fetch all missing archived redo log files and allow Redo Apply to apply the gap. 

Step 3 - In Primary database 

A) Re-enable archiving to the physical standby database:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;  

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 

Step 4 - In Standby database 

A) Open the database in Read only mode and ensure that all the transaction done in active mode are no more

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  

SQL> ALTER DATABASE OPEN READ ONLY;  

SQL> select * from testing;  

select * from testing  

*  

ERROR at line 1:  

ORA-00942: table or view does not exist 

B) Drop the restore point

SQL> STARTUP FORCE MOUNT;  

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;  

SQL> DROP RESTORE POINT Standby_flashback_testing ; 

Caution: 

While the database is activated, it is not receiving redo data from the primary database and cannot provide disaster protection. It is recommended that there be at least two physical standby databases participating in the configuration so that the primary database remains protected against data loss. 

Read more

Disable a table Constraints

ORA-02297: cannot disable constraint -dependencies exist

Whenever you try to disable a constraint of a table it fails with error message ORA-02297: cannot disable constraint -dependencies exist as below.

SQL> alter table transaction disable constraint TRANSACTION_PK;
alter table transaction disable constraint TRANSACTION_PK
*
ERROR at line 1:
ORA-02297: cannot disable constraint (OMS.TRANSACTION_PK) - dependencies exist

Cause :
Disable constraint command fails as the table is parent table and it has foreign key that are dependent on this constraint.

Solution :
Two solutions exist for this problem.

1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint.

Following query will check dependent table and the dependent constraint name. After that disable child first and then parent constraint.

SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table",
       p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
       FROM user_constraints p
       JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
       WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
       AND c.constraint_type = 'R'
       AND p.table_name = UPPER('&table_name');
Enter value for table_name: transaction
old   7:      AND p.table_name = UPPER('&table_name')
new   7:      AND p.table_name = UPPER('transaction')

Parent Table                   Child Table                    Parent Constraint            Child Constraint
----------------------------   --------------------------     -------------------------    ---------------------
TRANSACTION                    USER_SALARY_RECORD             TRANSACTION_PK               SYS_C005564
TRANSACTION                    TRANSACTION_DETAIL             TRANSACTION_PK               TRNSCTN_DTL_TRNSCTN_FK

SQL> alter table USER_SALARY_RECORD disable constraint SYS_C005564;
Table altered.

SQL> alter table TRANSACTION_DETAIL  disable constraint TRNSCTN_DTL_TRNSCTN_FK;
Table altered.

SQL> alter table transaction disable constraint TRANSACTION_PK;
Table altered.


2)Disable the constraint with cascade option.

SQL> alter table transaction disable constraint TRANSACTION_PK cascade;
Table altered.

Read more

DATABASE HEALTH CHECK

One of the daily task for oracle DBA to measure the health of databases.

This includes the database status,instance name, database open  mode, start up time,DR status,Archive log generation  etc.

The following script can be used to generate the oracle Database health Report. Below script is very basic script written to check   database status, additions can be made to script as per the requirements.

This report generates the following basic checks.

1. DATABASE STATUS

2. DATABASE NAME AND MODE

3. DR DATABASE STATUS

4. ARCHIVE LOG GENERATION LAST WEEK

5. DB PHYSICAL SIZE, DB ACTUAL SIZE

6. TABLESPACE USAGES

7. NO of USER/SESSIONS CONNECTED

8. DICTIONARY/LIBRARY HIT RATIO

9  BLOCKING QUERIES,BLOCKER AND WAITER

10. INVALID OBJECT COUNT LIST

Scripts Start.

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

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

set feedback off

set serverout on

set wrap off

set pages 300

set lines 200

PROMPT ================================================================

PROMPT DATABASE HEALTH CHECK REPORT

PROMPT ================================================================

PROMPT

PROMPT

PROMPT DATABASE STATUS

PROMPT =================

select INSTANCE_NAME,STATUS,DATABASE_STATUS,ACTIVE_STATE,STARTUP_TIME from v$instance;

PROMPT

PROMPT

PROMPT DATABASE NAME AND MODE

PROMPT ========================

select name, open_mode, log_mode from v$database;

PROMPT

PROMPT

PROMPT DR DATABASE STATUS

PROMPT ===========================================================

Col APPLIED_TIME format a20

Col destination format a20

Col Status format a20

SELECT DB_NAME,destination,  APPLIED_TIME, LOG_APPLIED,LOG_ARCHIVED,

(

  CASE

    WHEN ((APPLIED_TIME            IS NOT NULL    AND (LOG_ARCHIVED-LOG_APPLIED) IS NULL)

    OR (APPLIED_TIME               IS NULL    AND (LOG_ARCHIVED-LOG_APPLIED) IS NOT NULL)

    OR ((LOG_ARCHIVED-LOG_APPLIED)  > 1))

    THEN 'Error! Log Gap is '

    ELSE 'OK!'

  END) Status,

   LOG_ARCHIVED-LOG_APPLIED LOG_GAP

FROM

  ( SELECT INSTANCE_NAME DB_NAME FROM GV$INSTANCE WHERE INST_ID = 1 ),

  (SELECT MAX(SEQUENCE#) LOG_ARCHIVED   FROM V$ARCHIVED_LOG    WHERE DEST_ID=1 ),

   (select applied_seq# as LOG_APPLIED,destination as destination  from v$archive_dest_status WHERE DEST_ID=3 ),

  (SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME  FROM V$ARCHIVED_LOG  WHERE DEST_ID=1 );

PROMPT

PROMPT

PROMPT ARCHIVE LOG GENERATION LAST WEEK

PROMPT ===========================================================

SELECT A.*,  ROUND (A.Count# * B.AVG# / 1024 / 1024) Daily_Avg_Mb

FROM

  (SELECT TO_CHAR (First_Time,'YYYY-MM-DD') DAY,COUNT (1) Count#, MIN (RECID) Min#,MAX (RECID) Max#,MIN(sequence#),MAX(sequence#)  

   FROM v$log_history  GROUP BY TO_CHAR (First_Time, 'YYYY-MM-DD')  ORDER BY 1 DESC ) A,

  (SELECT AVG (BYTES) AVG#,COUNT (1) Count#,MAX (BYTES) Max_Bytes,MIN (BYTES) Min_Bytes  

   FROM v$log ) B   where rownum < 8;

PROMPT

PROMPT

PROMPT DB PHYSICAL SIZE

PROMPT =====================================

select sum(bytes/1024/1024/1024) "DB Physical Size(GB)" from dba_data_files;

PROMPT

PROMPT

PROMPT DB ACTUAL SIZE

PROMPT =====================================

select sum(bytes/1024/1024/1024) "DB Actual Size(GB)" from dba_segments;

PROMPT

PROMPT

PROMPT TABLESPACE USAGES

PROMPT ===========================================================

SELECT /* + RULE */  df.tablespace_name "Tablespace",

       df.bytes / (1024 * 1024) "Size (Mb)",

       SUM(fs.bytes) / (1024 * 1024) "Free (Mb)",

       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

  FROM dba_free_space fs,

       (SELECT tablespace_name,SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,df.bytes

UNION ALL

SELECT /* + RULE */ df.tablespace_name tspace,

       fs.bytes / (1024 * 1024),

       SUM(df.bytes_free) / (1024 * 1024),

       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),

       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)

  FROM dba_temp_files fs,

       (SELECT tablespace_name,bytes_free,bytes_used

          FROM v$temp_space_header

         GROUP BY tablespace_name,bytes_free,bytes_used) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

 ORDER BY 4 DESC;

PROMPT

PROMPT

PROMPT NO of USER CONNECTED

PROMPT ===========================================================

select count(distinct username) "No. of users Connected" from v$session where username is not null;

PROMPT

PROMPT

PROMPT NO of SESSIONS CONNECTED

PROMPT ===========================================================

Select count(*) AS "No of Sessions connected" from v$session where username is not null;

PROMPT

PROMPT

PROMPT DISTINCT USERNAME CONNECTED

PROMPT ===========================================================

Select distinct(username) AS "USERNAME" from v$session;

PROMPT

PROMPT

PROMPT DICTIONARY HIT RATIO. THIS VALUE SHOULD BE GREATER 85%

PROMPT ==========================================================

select   (  1 - ( sum (decode (name, 'physical reads', value, 0)) / (  sum (decode (name, 'db block gets',value, 0)) + sum (decode (name, 'consistent gets', value, 0))))) * 100 "Buffer Hit Ratio" from v$sysstat;

PROMPT

PROMPT

PROMPT LIBRARY CACHE HIT RATIO. THIS VALUE SHOULD BE GREATER 90%

PROMPT ===========================================================

select (sum(pins)/(sum(pins)+sum(reloads))) * 100 "Library Cache Hit Ratio" from v$librarycache;

PROMPT

PROMPT

PROMPT BLOCKING QUERY

PROMPT ===========================================================

select s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' )  is blocking '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2;

PROMPT

PROMPT

PROMPT BLOCKER AND WAITER

PROMPT ===========================================================

Select sid , decode(block,0,'NO','YES') Blocker , decode (request ,0,'NO','YES')WAITER from v$lock where request>0 or block>0 order by block desc;

PROMPT

PROMPT

PROMPT INVALID OBJECT COUNT LIST

PROMPT ===========================================================

select count(*),owner ,  object_type from dba_objects where status='INVALID' group by owner ,  object_type order by owner;

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

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

Script End.

Read more