RECOVERY USING STANDBY DATABASE
In this post, I will demonstrate the use of standby database to restore a datafile which got
corrupted on primary.
In the following scenario we have OP1 primary database and standby database is OP2 and catalog
database is CDB. TNS entries for op1 and op2 are there on the machne hosting recovery catalog
database cdb. Now the plan is to recover a lost datafile of primary database from standby
database.....
OVERVIEW:
- Create Recovery Catalog
- Register Primary database with catalog.
- Connect to catalog and primary. Configure persistent RMAN settings for primary .
- Connect to catalog and standby. Configure persistent RMAN settings for standby .
- Switch logs on primary so that tablespace is created on standby also
- Corrupt the datafile fo tbs_Sales on primary.
- Connect to standby database as target database using RMAN.
- Connect to primary database as auxiliary .
- Take backup of tbs_sales from standby database so that backup file is created on primary.
- connect to catalog and primary.
- Catalog the backup file created earlier
- Restore/recover tablespace
IMPLEMENTATION:
-- Create Recovery Catalog --
Log in to CDB catalog and do the following
SQL> grant connect ,resource to catown identified by c;
Grant succeeded.
SQL> grant recovery_catalog_owner to catown;
Grant succeeded.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb/system01.dbf
/u01/app/oracle/oradata/cdb/sysaux01.dbf
/u01/app/oracle/oradata/cdb/undotbs01.dbf
/u01/app/oracle/oradata/cdb/users01.dbf
SQL> create tablespace cattbs datafile '/u01/app/oracle/oradata/cdb/cat1a.dbf' size 300m;
Tablespace created.
SQL> alter user catown default tablespace cattbs quota unlimited on cattbs;
User altered.
[oracle@node1 ~]$ rman catalog catown/c
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 09:28:08 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> create catalog tablespace cattbs ;
recovery catalog created
#### Register OP1 with catalog CDB
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN configuration parameters for database with db_unique_name OP1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_op1.f';
# default
RMAN> report schema for db_unique_name op1;
Report of database schema for database with db_unique_name OP1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 670 SYSTEM YES /u01/app/oracle/oradata/op1/system01.dbf
2 490 SYSAUX NO /u01/app/oracle/oradata/op1/sysaux01.dbf
3 45 UNDOTBS1 YES /u01/app/oracle/oradata/op1/undotbs01.dbf
4 5 USERS NO /u01/app/oracle/oradata/op1/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/op1/temp01.dbf
RMAN> show all for db_unique_name op1;
RMAN configuration parameters for database with db_unique_name OP1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_op1.f';
# default
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
-- Configure persistent setting so that archivelogs are not deleted
-- until they have been applied to standby
RMAN> configure archivelog deletion policy to applied on all standby ;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> show all for db_unique_name op1;
RMAN configuration parameters for database with db_unique_name OP1 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_op1.f';
# default
RMAN> configure db_unique_name op1 connect identifier 'op1' ;
new RMAN configuration parameters:
CONFIGURE DB_UNIQUE_NAME 'op1' CONNECT IDENTIFIER 'op1';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
####REGISTER SERVICE OF STANDBY WITH RMAN
RMAN> configure db_unique_name op2 connect identifier 'op2' ;
new RMAN configuration parameters:
CONFIGURE DB_UNIQUE_NAME 'op2' CONNECT IDENTIFIER 'op2';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> list db_unique_name of database;
List of Databases
DB Key DB Name DB ID Database Role Db_unique_name
------- ------- ----------------- --------------- ------------------
161 OP1 588960404 PRIMARY OP1
161 OP1 588960404 STANDBY OP2
RMAN> report schema for db_unique_name op2;
Report of database schema for database with db_unique_name OP2
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 670 SYSTEM YES
2 490 SYSAUX NO
3 45 UNDOTBS1 YES
4 5 USERS NO
####connect to standby database
[oracle@node1 ~]$ rman target sys@op2
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 09:42:40 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: OP1 (DBID=588960404, not open)
RMAN> connect catalog catown@cdb
recovery catalog database Password:
connected to recovery catalog database
RMAN> show all for db_unique_name op2;
RMAN configuration parameters for database with db_unique_name OP2 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'op1' CONNECT IDENTIFIER 'op1';
CONFIGURE DB_UNIQUE_NAME 'op2' CONNECT IDENTIFIER 'op2';
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_op2.f';
# default
-- Configure persistent settings for Standby database
-- Controlfile autobackup on
-- backup optimization
-- archivelogs are not deleted unless they are backed up once
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON ;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE BACKUP OPTIMIZATION ON ;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE DISK ;
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters are successfully stored
#### On primary create the tablespace and create a table in the tablespace
SQL> create tablespace tbs_sales datafile '/u01/app/oracle/oradata/op1/tbsales.dbf' size 50m;
Tablespace created.
SQL> create table scott.sales3 (id numbeR) tablespace tbs_sales;
Table created.
SQL> insert into scott.sales3 values(90);
1 row created.
SQL> commit;
Commit complete.
-- On primary, corrupt the datafile for tbs_sales tablespace with echo command but before this make
sure the log group should be switched so that tablespace is created on standby also...
SYS@OP1>alter system switch logfile;
[oracle@node1 ~]$ echo > /u01/app/oracle/oradata/op1/tbsales.dbf
-- Flush buffer cache and query newly created table. The query will fail as the
datafile has been corrupted
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from scott.sales3;
select * from scott.sales3
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/op1/tbsales.dbf'
-- Now is the time to recover primary tablespace from standby
[oracle@node1 ~]$ rman target sys@op2
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 09:50:30 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: OP1 (DBID=588960404, not open)
-- Connect to primary database using auxiliary channel
RMAN> connect auxiliary sys@op1
auxiliary database Password:
connected to auxiliary database: OP1 (DBID=588960404)
-- Take image copy backup of tbs_sales tablespace from standby database
on primary database
RMAN> backup as copy datafile 5 auxiliary format '/u01/app/sales_sby.dbf' ;
Starting backup at 29-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/op2/tbsales.dbf
output file name=/u01/app/sales_sby.dbf tag=TAG20120929T095124
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 29-SEP-12
####CONNECT TO PRIMARY
[oracle@node1 ~]$ rman target/
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 10:07:06 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: OP1 (DBID=588960404)
RMAN> connect catalog catown@cdb
recovery catalog database Password:
connected to recovery catalog database
-- Catalog the image copy backup with primary since the backup was taken without
connecting to catalog
RMAN> catalog datafilecopy '/u01/app/sss5.dbf' ;
starting full resync of recovery catalog
full resync complete
cataloged datafile copy
datafile copy file name=/u01/app/sss5.dbf RECID=1 STAMP=795262055
RMAN>alter tablespace tbs_sales offline;
RMAN> run {
restore datafile 5 ;
recover datafile 5 ; }
####CHECK ON PRIMARY
SQL>select * from scott.sales5
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/op1/tbsales5.dbf'
SQL> alter tablespace tbs_sales5 online;
alter tablespace tbs_sales5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/sss5.dbf'
#### DO THE RECOVERY FROM RMAN on Primary
RMAN> recover tablespace tbs_sales5;
Starting recover at 29-SEP-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 29-SEP-12
#### COME BACK TO PRIMARY
SQL> alter tablespace tbs_sales5 online;
Tablespace altered.
SQL> select * from scott.sales5;
ID
----------
90
90
#### ENJOY ####
---------------------------------------------------------------------
Related links:
HOME
11G DATA GUARD NEW FEATURES
DATA GUARD SETUP
2 comments:
Good Work Rajiv !!!!!!!
Keep Posting ......
Thanks,
good way to explain. thanks
oceanapart technologies - databackup & transfer
Post a Comment