RECOVERY SCENARIOS IN DATA GUARD ENVIRONMENT.pdf

RECOVERY SCENARIOS IN DATA GUARD ENVIRONMENT | 11g

RECOVERY OPERATIONS IN DATAGUARD

In this article, I am demonstrating some of the following recovery scenarios that may help you in hard situations. Its good practice to test recovery scenarios on DG environment. 

Loss of data file recovery on the Primary database.



Loss of data file recovery on the Standby database.



Recovery from loss of the Primary database control file.



Recovery from loss of a Standby database control file.



Recovery from loss of Online redo log file.



Incomplete recovery of the Primary database.

DATAGUARD WITH RMAN

A Recovery Catalog is required when you use RMAN in Data Guard environment. RMAN uses a Recovery Catalog to track file names for all database files in DR environment. A Recovery Catalog Schema is used by RMAN to store metadata about all registered databases. The Primary database is explicitly registered to the RMAN Catalog; and Physical Standby databases are registered automatically if they are connected as target while connected to the Recovery Catalog. RMAN is using db_unique_name parameter to identify one database from another; but both the Primary and Standby database have the same database ID number. INTERCHANGEABILITY OF BACKUPS

In DR environment, you can backup a tablespace on a Physical Standby database; it’s possible to restore and recover it on the Primary database. Similarly you can backup a tablespace on a Primary database and restore and recover it on a Physical Standby database. DISASTER RECOVERY ENVIRONMENT

Primary Database 

crms



SERVER1



192.168.222.133



Primary Server

Standby Database 

crms



SERVER2



192.168.222.134



Standby Server

Catalog Database 

catdb 

SERVER3



192.168.222.135



Catalog Server

Primary Database Unique name



crms

Standby Database Unique name



stbycrms

LOSS OF DATAFILES RECOVERY ON THE PRIMARY

Let's simulate a case where a datafile is lost by (renaming or moving to some other location) one of the datafile on the Primary database. Two options are available.  You can recover it on the Primary database by using backups.  You can recover it on the Primary database by using the file on a Standby database.

Exploring the Oracle DBA Technology by Gunasekaran , Thiyagu


CREATE TBS1 TABLESPACE ON PRIMARY

SYS> create tablespace tbs1 datafile '/u01/app/oracle/oradata/crms/tbs01.dbf' size 100m; Tablespace created. SYS> create user usr1 identified by usr1 default tablespace tbs1; User created. SYS> grant connect, resource to usr1; Grant succeeded. SYS> select name from v$datafile; NAME -------------------------------------------------------------------------------/u01/app/oracle/oradata/crms/system01.dbf /u01/app/oracle/oradata/crms/sysaux01.dbf /u01/app/oracle/oradata/crms/undotbs01.dbf /u01/app/oracle/oradata/crms/users01.dbf /u01/app/oracle/oradata/crms/example01.dbf /u01/app/oracle/oradata/crms/tbs01.dbf 6 rows selected.

TAKE LEVEL 0 BACKUP

$ rman target sys/passwd@CRMSDB .. RMAN> backup incremental level 0 database plus archivelog TAG='INC-LEV-0'; .. RMAN> report schema; Report of database schema for database with db_unique_name CRMS List of Permanent Datafiles =========================== File Size(MB) Tablespace

RB segs Datafile Name

---- -------- -------------------- ------- -----------------------1

680

SYSTEM

***

/u01/app/oracle/oradata/crms/system01.dbf

2

520

SYSAUX

***

/u01/app/oracle/oradata/crms/sysaux01.dbf

3

60

UNDOTBS1

***

/u01/app/oracle/oradata/crms/undotbs01.dbf

4

5

USERS

***

/u01/app/oracle/oradata/crms/users01.dbf

5

100

EXAMPLE

***

/u01/app/oracle/oradata/crms/example01.dbf

6

10

TBS1

***

/u01/app/oracle/oradata/crms/tbs01.dbf

...



CREATING TABLES UNDER USER ( USR1)

SYS> create table usr1.employees as select * from hr.employees; Table created. SYS> create table usr1.departments as select * from hr.departments; Table created. SYS> create table usr1.regions as select * from hr.regions; Table created.

RENAMEING DATAFILE NAME

$ mv /u01/app/oracle/oradata/crms/tbs01* /u01/app/oracle/oradata/crms/tbs01.dbf.bkp $ ls /u01/app/oracle/oradata/crms/tbs* /u01/app/oracle/oradata/crms/tbs01.dbf.bkp

TAKE LEVEL 1 INCREMENTAL BACKUP LEVEL

RMAN> backup incremental level 1 database TAG='INC-LEV-I'; .. ... input datafile file number=00001 name=/u01/app/oracle/oradata/crms/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/crms/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/crms/example01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/crms/undotbs01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/crms/tbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/crms/users01.dbf ...



IDENTIFYING THE MISSING DATAFILE

SYS> select * from usr1.employees; select * from employees * ERROR at line 1: ORA-01116: error in opening database file 6 ORA-01110: data file 6: '/u01/app/oracle/oradata/crms/tbs01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 We can see the error message ORA-27041, it clearly says No such file or directory, so we need to recover it; let’s start the recovery process. USING A STANDBY DATABASE TO RECOVER THE DATAFILE

Connect to the Standby database as the Target database. Connect to the Primary database as the Auxiliary database. Take backup Imagecopy of the datafile (tbs01.dbf) using Standby database. Verify that Imagecopy has been created on the Primary database Server. Exit from RMAN Prompt then connect RMAN utility target as Primary database, and make the datafile to offline status. Recover the datafile and Make it online. PRIMARY DATABASE AS TARGET & STANDBY AS AUXILIARY

$ rman .. ... RMAN> connect target sys/passwd@STBY_CRMSDB connected to target database: CRMS (DBID=1611053225) RMAN> connect auxiliary sys/passwd@CRMSDB connected to auxiliary database: CRMS (DBID=1611053225)

It's NOT mandatory to connect RMAN Catalog because we'll register the backup file to the Primary database's control file manually. Now take IMAGECOPY backup of tbs01.dbf datafile using Standby database so that backup file is created on Primary database Server. The datafile copy must be created in some other location and NOT in datafile’s original location. A copy cannot exist in the same location and name as the source of which it is a copy. If I place the filecopy in datafile’s original location, let’s see what would happen.



RMAN> backup as copy datafile 6 auxiliary format '/u01/app/oracle/oradata/crms/tbs01.dbf'; Starting backup at 20-APR-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1channel ORA_DISK_1 channel ORA_DISK_1: SID=42 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=/u01/app/oracle/oradata/stbycrms/tbs01.dbf output file name=/u01/app/oracle/oradata/crms/tbs01.dbf tag=TAG20160420T152638 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16 Finished backup at 20-APR-16 The Image copy has been created on the Primary database server. More over image copies can be transported over network using RMAN. $ ls /u01/app/oracle/oradata/crms/tbs* /u01/app/oracle/oradata/crms/tbs01.dbf /u01/app/oracle/oradata/crms/tbs01.dbf.old

CATALOG THE DATAFILE COPY

Connect to Primary database as Target database.

$ rman target sys/passwd@CRMSDB ... connected to target database: CRMS (DBID=1611053225) RMAN> catalog datafilecopy '/u01/app/oracle/oradata/crms/tbs01.dbf'; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of catalog command on default channel at 04/20/2016 15:29:06 ORA-19657: cannot inspect current datafile /u01/app/oracle/oradata/crms/tbs01.dbf

When we try to catalog a file with same name "/u01/app/oracle/oradata/crms/tbs01.dbf" as backup copy, hence we are getting the ORA-19657. Oracle still thinks that this is our current datafile, and we are trying to overwrite and mark it as a datafile copy. A copy cannot exist in the same location and name as the source of which it is a copy. As we understand from an error – (ORA-19657), the imagecopy of datafile needs to be created in some other location instead of datafile’s original location.



RMAN> backup as copy datafile 6 auxiliary format '/u03/crmsdb-dbfiles/tbs01.dbf'; Starting backup at 20-APR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=/u01/app/oracle/oradata/stbycrms/tbs01.dbf output file name=/u03/crmsdb-dbfiles/tbs01.dbf tag=TAG20160420T161841 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04 Finished backup at 20-APR-16 $ ls /u03/crmsdb-dbfiles/tbs* /u03/crmsdb-dbfiles/tbs01.dbf/tbs01.dbf We need to register the backup file to the Primary database control file with the RMAN CATALOG command; so that Primary database controlfile gets updated with this backup copy. Connect the Primary database as target and to the recovery catalog.

$ rman target / .. ... connected to target database: CRMS (DBID=1611053225) RMAN> catalog datafilecopy '/u03/crmsdb-dbfiles/tbs01.dbf'; cataloged datafile copy datafile copy file name=/u03/crmsdb-dbfiles/tbs01.dbf RECID=22 STAMP=909678350 RMAN> list copy of datafile 6; List of Datafile Copies ======================= Key

File S Completion Time Ckp SCN

Ckp Time

------- ---- - --------------- ---------- --------------22

6

A 20-APR-16

1391073

20-APR-16

Name: /u03/crmsdb-dbfiles/tbs01.dbf Tag: TAG20160420T161841

Switch the datafile 6 to the backup copy that we registered in the previous step. RMAN> switch datafile 6 to copy; datafile 6 switched to datafile copy "/u03/crmsdb-dbfiles/tbs01.dbf" RMAN> sql 'alter database datafile 6 offline'; sql statement: alter database datafile 6 offline



RESTORE AND RECOVER THE DATAFILE

RMAN> restore datafile 6; Starting restore at 20-APR-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=56 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00006 to /u03/crmsdb-dbfiles/tbs01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/CRMS/backupset/2016_04_20/o1_mf_nnnd0_TAG20160420T 003124_ckdj9ycy_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/CRMS/backupset/2016_04_20/o1_mf_nnnd0_TAG20 160420T003124_ckdj9ycy_.bkp tag=TAG20160420T003124 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 20-APR-16 RMAN> recover datafile 6; Starting recover at 20-APR-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=52 device type=DISK channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00006: /u03/crmsdb-dbfiles/tbs01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/CRMS/backupset/2016_04_20/o1_mf_nnnd1_INC_LEV_I_ck dkqksj_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/CRMS/backupset/2016_04_20/o1_mf_nnnd1_INC_L EV_I_ckdkqksj_.bkp tag=INC-LEV-I channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 20-APR-16 RMAN> sql 'alter database datafile 6 online'; sql statement: alter database datafile 6 online



RMAN> report schema; Report of database schema for database with db_unique_name CRMS List of Permanent Datafiles =========================== File Size(MB) Tablespace

RB segs Datafile Name

---- -------- -------------------- ------- -----------------------1

680

SYSTEM

***

/u01/app/oracle/oradata/crms/system01.dbf

2

530

SYSAUX

***

/u01/app/oracle/oradata/crms/sysaux01.dbf

3

60

UNDOTBS1

***

/u01/app/oracle/oradata/crms/undotbs01.dbf

4

5

USERS

***

/u01/app/oracle/oradata/crms/users01.dbf

5

100

EXAMPLE

***

/u01/app/oracle/oradata/crms/example01.dbf

6

10

TBS1

***

/u03/crmsdb-dbfiles/tbs01.dbf

List of Temporary Files ======================= File Size(MB) Tablespace

Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- -------------------1

20

TEMP

32767

/u01/app/oracle/oradata/crms/temp01.dbf

RMAN> validate check logical datafile 6; Starting validate at 20-APR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00006 name=/u03/crmsdb-dbfiles/tbs01.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:01 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------6

OK

0

1135

1280

1522396

File Name: /u03/crmsdb-dbfiles/tbs01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------Data

0

6

Index

0

0

Other

0

142

Finished validate at 21-APR-16 RMAN> exit Recovery Manager complete.



RECOVERY NON-SYSTEM DATAFILE USING BACKUP

We have discussed it’s possible to recover loss of datafile(s) by using backups.

SYS> select name from v$datafile; NAME -------------------------------------------------------------------------------/u01/app/oracle/oradata/crms/system01.dbf /u01/app/oracle/oradata/crms/sysaux01.dbf /u01/app/oracle/oradata/crms/undotbs01.dbf /u01/app/oracle/oradata/crms/users01.dbf /u01/app/oracle/oradata/crms/example01.dbf /u03/crmsdb-dbfiles/tbs01.dbf 6 rows selected.

ADDNG DATAFILES TO USERS TABLESPACE

SYS> alter tablespace users add datafile '/u01/app/oracle/oradata/crms/users02.dbf' size 100m autoextend on; Tablespace altered. SYS> create table scott.locations as select * from hr.locations; Table created. SYS> create table scott.countries as select * from hr.countries; Table created. SYS> create table scott.regions as select * from hr.regions; Table created.

TAKE INCREMENTAL BACKUP

$ rman target sys/passwd@CRMSDB catalog rmanuser/passwd@CATDB ... Connected to target database: CRMS (DBID=1611053225) Connected to recovery catalog database RMAN> backup incremental level 2 database tag='INC-LEV-II'; Starting backup at 21-APR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental level 2 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set



input datafile file number=00001 name=/u01/app/oracle/oradata/crms/system01.dbf input datafile file number=00006 name=/u03/crmsdb-dbfiles/tbs01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/crms/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/crms/example01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/crms/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 21-APR-16 channel ORA_DISK_1: finished piece 1 at 21-APR-16 piece handle=/u01/app/oracle/flash_recovery_area/CRMS/backupset/2016_04_21/o1_mf_nnnd2_INC_L EV_II_ckjp0r0j_.bkp tag=INC-LEV-II comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 channel ORA_DISK_1: starting incremental level 2 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00007 name=/u01/app/oracle/oradata/crms/users02.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/crms/users01.dbf channel ORA_DISK_1: starting piece 1 at 21-APR-16 channel ORA_DISK_1: finished piece 1 at 21-APR-16 piece handle=/u01/app/oracle/flash_recovery_area/CRMS/backupset/2016_04_21/o1_mf_nnnd2_INC_L EV_II_ckjp0v6q_.bkp tag=INC-LEV-II comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 channel ORA_DISK_1: starting incremental level 2 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 21-APR-16 channel ORA_DISK_1: finished piece 1 at 21-APR-16 piece handle=/u01/app/oracle/flash_recovery_area/CRMS/backupset/2016_04_21/o1_mf_ncsn2_INC_L EV_II_ckjp0xm8_.bkp tag=INC-LEV-II comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 21-APR-16 RMAN> exit Recovery Manager complete.

RELOCATING DATAFILE

$ cd /u01/app/oracle/oradata/crms/ $ mv users* /tmp $ ls users* ls: users*: No such file or directory



SYS> select * from scott.regions; select * from scott.regions * ERROR at line 1: ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/u01/app/oracle/oradata/crms/users01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3

RESTORE AND RECOVER DATAFILE (S)

$ rlrman target sys/passwd@CRMSDB ... connected to target database: CRMS (DBID=1611053225) RMAN> sql 'alter database datafile 4 offline'; sql statement: alter database datafile 7 offline RMAN> sql 'alter database datafile 7 offline'; sql statement: alter database datafile 7 offline RESTORING DATAFILE (S) - 4 & 7

RMAN> restore datafile 4,7; .. ... channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/crms/users02.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/CRMS/backupset/2016_04_21/o1_mf_nnnd2_INC_LEV_II_c kjp0v6q_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/CRMS/backupset/2016_04_21/o1_mf_nnnd2_INC_L EV_II_ckjp0v6q_.bkp tag=INC-LEV-II channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 21-APR-16



RECOVERING DATAFILE(S) - 4 & 7

RMAN> recover datafile 4,7; Starting recover at 21-APR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00004: /u01/app/oracle/oradata/crms/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/CRMS/backupset/2016_04_21/o1_mf_nnnd1_INC_LEV_I_ck hwrvjl_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/CRMS/backupset/2016_04_21/o1_mf_nnnd1_INC_L EV_I_ckhwrvjl_.bkp tag=INC-LEV-I channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00004: /u01/app/oracle/oradata/crms/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/CRMS/backupset/2016_04_21/o1_mf_nnnd2_INC_LEV_II_c kjp0v6q_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/CRMS/backupset/2016_04_21/o1_mf_nnnd2_INC_L EV_II_ckjp0v6q_.bkp tag=INC-LEV-II channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 21-APR-16 RMAN> exit Recovery Manager complete.

MAKE THE DATAFILES TO ONLINE STATUS

RMAN> sql 'alter database datafile 4 online'; sql statement: alter database datafile 7 online RMAN> sql 'alter database datafile 7 online'; sql statement: alter database datafile 7 online



RECOVER STANDBY DATAFILE FROM PRIMARY

SYS> select name, db_unique_name, database_role, open_mode from v$database; NAME

DB_UNIQUE_NAME

DATABASE_ROLE

OPEN_MODE

--------- --------------- ---------------- -------------------CRMS

stbycrms

PHYSICAL STANDBY READ ONLY WITH APPLY

SYS> select name from v$datafile; NAME -------------------------------------------------------------------------------/u01/app/oracle/oradata/stbycrms/system01.dbf /u01/app/oracle/oradata/stbycrms/sysaux01.dbf /u01/app/oracle/oradata/stbycrms/undotbs01.dbf /u01/app/oracle/oradata/stbycrms/users01.dbf /u01/app/oracle/oradata/stbycrms/example01.dbf /u01/app/oracle/oradata/stbycrms/tbs01.dbf /u01/app/oracle/oradata/stbycrms/users02.dbf 7 rows selected.

RELOCATING DATAFILES(S)

$ cd /u01/app/oracle/oradata/stbycrms/ $ mv users* /tmp $ ls –l users* ls: users*: No such file or directory BOUNCE THE STANDBY DATABASE

SYS> startup force; ORACLE instance started. Total System Global Area Fixed Size

912306176 bytes 1340244 bytes

Variable Size

562039980 bytes

Database Buffers

343932928 bytes

Redo Buffers

4993024 bytes

Database mounted. ORA-10458: standby database requires recovery ORA-01196: file 1 is inconsistent due to a failed media recovery session ORA-01110: data file 1: '/u01/app/oracle/oradata/stbycrms/system01.dbf'



CONTENTS OF ALERT LOG FROM STANDBY

$ tail -f /u01/app/oracle/diag/rdbms/stbycrms/stbycrms/tarce/alert_stbycrms.log Errors in file /u01/app/oracle/diag/rdbms/stbycrms/stbycrms/trace/stbycrms_dbw0_13589.trc: ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/u01/app/oracle/oradata/stbycrms/users01.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Errors in file /u01/app/oracle/diag/rdbms/stbycrms/stbycrms/trace/stbycrms_dbw0_13589.trc: ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: '/u01/app/oracle/oradata/stbycrms/users02.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 MRP0: Background Media Recovery terminated with error 1110 ...

MAKE A SWITCH LOGS ON PRIMARY

SYS> alter system switch logfile; System altered. $ dgmgrl ... DGMGRL> connect sys/crms@CRMSDB Connected. DGMGRL> show configuration; Configuration - dgcrms Protection Mode: MaxPerformance Databases: crms

- Primary database

stbycrms - Physical standby database Error: ORA-16766: Redo Apply is stopped Fast-Start Failover: DISABLED Configuration Status: ERROR



CONNECT RMAN

Primary as target database and Standby as auxiliary.

$ rman target / auxiliary sys/crms@STBY_CRMSDB .. ... connected to target database: CRMS (DBID=1611053225) connected to auxiliary database: CRMS (DBID=1611053225, not open) RMAN> backup as copy datafile 4 auxiliary format '/u01/app/oracle/oradata/stbycrms/users01.dbf'; Starting backup at 22-APR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/crms/users01.dbf output file name=/u01/app/oracle/oradata/stbycrms/users01.dbf tag=TAG20160422T060012 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 22-APR-16 RMAN> backup as copy datafile 7 auxiliary format '/u01/app/oracle/oradata/stbycrms/users02.dbf'; Starting backup at 22-APR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/u01/app/oracle/oradata/crms/users02.dbf output file name=/u01/app/oracle/oradata/stbycrms/users02.dbf tag=TAG20160422T060025 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 22-APR-16

RECOVER THE STANDBY DATABASE

SYS> recover managed standby database disconnect; Media recovery complete. SYS> alter database open; Database altered. SYS> select name, database_role, open_mode from v$database; NAME

DATABASE_ROLE

OPEN_MODE

--------- ---------------- -------------------CRMS




CHECK REDO APPLY STATUS

DGMGRL> show configuration; Configuration - dgcrms Protection Mode: MaxPerformance Databases: crms

- Primary database

stbycrms - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS

LOSS OF THE PRIMARY CONTROLFILE

Oracle database is providing to multiplex control files in different locations. If any one of the control file cannot be updated then the database instance is shutdown automatically.

SYS> select name from v$controlfile; NAME -------------------------------------------------------------------------------/u01/app/oracle/oradata/crms/control01.ctl /u01/app/oracle/flash_recovery_area/crms/control02.ctl

RELOCATING CONTROLFILE AT OS LEVEL

$ cd /u01/app/oracle/oradata/crms/control01.ctl $ mv control01.ctl /tmp/ $ ls -l *.ctl ls: *.ctl: No such file or directory

IDENTIFYING THE MISSING CONTROL FILE

SYS> select current_scn from v$database; select current_scn from v$database * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/crms/control01.ctl' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3



SHUTDOWN THE INSTANCE

SYS> select status from v$instance; STATUS -----------OPEN SYS> shut abort; ORACLE instance shut down.

A good copy of the control file can be copied over the failed copy and rename as in control_files parameter and start the database instance without restore or recovery.

$ cd /u01/app/oracle/flash_recovery_area/crms $ cp control02.ctl /u01/app/oracle/oradata/crms/control01.ctl

You should NOT copy the good control file over the bad one when the database is in OPEN or MOUNT state. The copy should only be done in NOMOUNT or SHUTDOWN state. STARTUP THE INSTANCE

SYS> startup; Oracle instance started .. ... Database mounted. Database opened.

LOSS OF ALL CONTROL FILES ON PRIMARY

If all control files are lost on the Primary, there are three options, depending on the length of acceptable downtime.  Failover to Standby database.  Create a new control file.  Recover using backup control file. FAILOVER TO STANDBY DATABSE: This option minimizes downtime. Flashback is not possible if all control files are lost. So, restore and recover is only option. RECOVERY USING BACKUP CONTROL FILE: It’s possible to restore a backup control file from the Primary database then perform recovery but need to open with RESETLOGS. CREATE A NEW CONTROL FILE: This option requires additional downtime compared to failover. A new control file can be created using the NORESETLOGS option followed by media recovery.



POINTS TO NOTE

If you perform incomplete recovery (resetlogs) on Primary database, you must have flashback enabled on Standby database, then you can flashback Standby database to resetlogs_change# on Primary and can be sync with Primary database. If flashback is not enabled on standby, you may have to rebuild the Standby database. LOST ALL CONTROL FILES & USING RECOVERY CATALOG If we have all online redo logs, then just restore backup control file and then perform recovery; after recovery open the database with the RESETLOGS option. CONNECT TARGET AS PRIAMRY WITH CATALOG DATABASE

$ rman target sys/passwd@CRMSDB catalog rmanuser/passwd@CATDB ... connected to target database: CRMS (DBID=1612000297) connected to recovery catalog database # TAKING LEVEL 0 BACKUP RMAN> backup incremental level 0 database plus archivelog TAG='INC-LEV-0-BKP'; ... Starting Control File and SPFILE Autobackup at 24-APR-16 piece handle=/u01/app/oracle/flash_recovery_area/CRMS/autobackup/2016_04_24/o1_mf_s_91002921 4_ckryw00b_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 24-APR-16

IDENTIFYING CONTROL FILES LOCATION

SYS> select name from v$controlfile; NAME ----------------------------------------------------/u01/app/oracle/oradata/crms/control01.ctl /u01/app/oracle/flash_recovery_area/crms/control02.ctl

RENAMING CONTROL FILES AT OS LEVEL

$ cd /u01/app/oracle/oradata/crms $ mv control01.ctl control01.ctl.bkp $ cd /u01/app/oracle/flash_reocvery_area/crms $ mv control02.ctl control02.ctl.bkp



IDENTIFYING RENAMED CONTROL FILE

SYS> select current_scn from v$database; select current_scn from v$database * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/crms/control01.ctl' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3

SNIPPET FROM DATABASE ALERT LOG FILE

$ tail –f /u01/app/oracle/diag/rdbms/crms/crms/trace/alert_crms.log Errors in file /u01/app/oracle/diag/rdbms/crms/crms/trace/crms_m000_12366.trc: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/crms/control01.ctl' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 # Down the Primary database Instance SYS> shut abort; ORACLE instance shut down.

When we use recovery catalog, it is simple to restore control files. Ensure we connect to both target database and recovery catalog database.

$ rlrman target sys/crms@CRMSDB catalog rmanuser/rmanuser@CATDB ... connected to target database (not started) connected to recovery catalog database RMAN> startup nomount; Oracle instance started Total System Global Area Fixed Size

912306176 bytes 1340244 bytes

Variable Size

612371628 bytes

Database Buffers

293601280 bytes

Redo Buffers

4993024 bytes



When a control file is inaccessible, you can start the instance, but not mount the database. If you attempt to mount the database when the control file is unavailable, you would receive error - (ORA-00205 error in identifying control file, check alert log for more info). RESTORE CONTROL FILES & RECOVER THE DATABASE

RMAN> restore controlfile; Starting restore at 24-APR-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: restoring control file channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/CRMS/autobackup/2016_04_24/o1_mf_s_910029214_ckryw 00b_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/CRMS/autobackup/2016_04_24/o1_mf_s_91002921 4_ckryw00b_.bkp tag=TAG20160424T175334 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 output file name=/u01/app/oracle/oradata/crms/control01.ctl output file name=/u01/app/oracle/flash_recovery_area/crms/control02.ctl Finished restore at 24-APR-16 RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> recover database; Starting recover at 24-APR-16 Starting implicit crosscheck backup at 24-APR-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=27 device type=DISK Crosschecked 13 objects Finished implicit crosscheck backup at 24-APR-16 Starting implicit crosscheck copy at 24-APR-16 using channel ORA_DISK_1 Crosschecked 2 objects Finished implicit crosscheck copy at 24-APR-16 searching for all files in the recovery area cataloging files... cataloging done



List of Cataloged Files ======================= archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/oradata/crms/redo03.log .. ... archived log file name=/u01/app/oracle/flash_recovery_area/CRMS/archivelog/2016_04_24/o1_mf_1_29_ckryy88 8_.arc thread=1 sequence=29 archived log file name=/u01/app/oracle/oradata/crms/redo03.log thread=1 sequence=30 media recovery complete, elapsed time: 00:00:02 Finished recover at 24-APR-16

SNIPPET FROM PRIMARY DATABASE ALERT LOG FILE

$ tail –f /u01/app/oracle/diag/rdbms/crms/crms/trace/alert_crms.log Incomplete recovery applied all redo ever generated. Recovery completed through change 958292 time 04/24/2016 17:59:55 Media Recovery Complete (crms) Completed: alter database recover logfile '/u01/app/oracle/oradata/crms/redo03.log'

OPEN THE DATABASE WITH RESETLOG OPTION

# Whether resetlog option is required or not SYS> select open_resetlogs from v$database; OPEN_RESETLOGS --------------Required. RMAN> alter database open resetlogs; database opened new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete

RECOVERYING THROGH OPEN RESET LOGS STATEMENT

Once the Primary database has been opened with the RSETLOGS option, the incarnation of the database changes then creating a new branch of redo data. When a Physical Standby database receives a new branch of redo data, the Standby database will automatically register the new redo branch and it will automatically follow the new redo branch.



SNIPPET FROM STANDBY DATABASE ALERT LOG FILE

$ tail -f /u01/app/oracle/diag/rdbms/stbycrms/stbycrms/trace/alert_stbycrms.log RFS[7]: New Archival REDO Branch(resetlogs_id): 910030253

Prior: 909913655

RFS[7]: Archival Activation ID: 0x6019490d Current: 0x60182013 RFS[7]: Effect of primary database OPEN RESETLOGS RFS[7]: Managed Standby Recovery process is active RFS[7]: Incarnation entry added for Branch(resetlogs_id): 910030253

(stbycrms)

Setting recovery target incarnation to 5

RECOVERYING THROGH OPEN RESET LOGS STATEMENT

Once the Primary database has been opened with the RSETLOGS option, the incarnation of the database changes then creating a new branch of redo data. When a Physical Standby database receives a new branch of redo data, the Standby database will automatically register the new redo branch and it will automatically follow the new redo branch. To check whether the new redo branch has been registered at the standby, perform the following query at the Primary and Standby and verify that the results match. select resetlogs_id, resetlogs_change# from v$database_incarnationwhere status='CURRENT';

FLASHBACK STANDBY AFTER RESETLOGS ON PRIMARY

The following steps helps to avoid re-creating a Physical Standby database after you issued the OPEN RESETLOGS statement on the primary database. First determine the SCN before the RESETLOGS operation occurred. You can use the following query to obtain the value of the (SCN) that is 2 SCNs before the RESETLOGS operation occurred on the Primary database.

# On Primary database SYS> select (RESETLOGS_CHANGE# -2) from v$database; RESETLOGS_CHANGE# ----------------958291 # On Standby database SYS> select current_scn from v$database; CURRENT_SCN ----------958522



If the value of CURRENT_SCN is larger than the value of , issue the following statement to flash back the standby database.

SYS> recover managed standby database cancel; Media recovery complete. SYS> shut immediate; Database dismounted. ORACLE instance shut down. SYS> flashback database to scn 958291; Flashback complete.

SNIPPET ALERT LOG OF STANDBY

$ tail -f /u01/app/oracle/diag/rdbms/stbycrms/stbycrms/trace/alert_stbycrms.log flashback database to scn 958291 .. ... [Trimmed] Incomplete Recovery applied until change 958292 time 04/24/2016 18:10:53 Flashback Media Recovery Complete Mon Apr 25 13:37:12 2016 Setting recovery target incarnation to 5 Completed: flashback database to scn 958291

CONNECT RMAN AND CHECK

Connect target as Primary with Recovery Catalog database. $ rman target sys/passwd@CRMSDB catalog rmanuser/passwd@CATDB .. ... RMAN> list incarnation; List of Database Incarnations DB Key

Inc Key DB Name

DB ID

STATUS

Reset SCN

Reset Time

------- ------- -------- ---------------- --- ---------- ---------6422

6438

CRMS

1612000297

PARENT

1

13-AUG-09

6422

6423

CRMS

1612000297

PARENT

754488

23-APR-16

6422

6844

CRMS

1612000297

PARENT

860643

23-APR-16

6422

7567

CRMS

1612000297

PARENT

883005

24-APR-16

6422

8141

CRMS

1612000297

CURRENT 958293

24-APR-16



EXECUTE FOLLOWING QUERY ON PRIMARY & STANDBY

SYS> select resetlogs_id, resetlogs_change# from v$database_incarnation where status='CURRENT'; RESETLOGS_ID RESETLOGS_CHANGE# ------------ ----------------910030253

958293

Restoring control files does not mean Incomplete Recovery always. Oracle still can do a Complete Recovery if it has all the Archive logs and Online Redo Logs. Get more information here. LOST OF ALL CONTROL FILES & ALL ONLINE REDO LOG FILES

Enable control file autobackup ON -(RMAN PARAMETERS). Take full backup of database + archivelogs.

$ rman target sys/passwd@CRMSDB catalog rmanuser/passwd@CATDB ... RMAN> show all; ... RMAN> configure controlfile autobackup on; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored RMAN> show controlfile autobackup; RMAN configuration parameters for database with db_unique_name CRMS are: CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> backup database plus archivelog delete input; ... CONTROL FLES & REDO LOG FILES LOCATION

SYS> select member from v$logfile; MEMBER -----------------------------------------/u01/app/oracle/oradata/crms/redo03.log /u01/app/oracle/oradata/crms/redo02.log /u01/app/oracle/oradata/crms/redo01.log



SYS> select name from v$controlfile; NAME -----------------------------------------/u01/app/oracle/oradata/crms/control01.ctl /u01/app/oracle/flash_recovery_area/crms/control02.ctl

RELOCATE CONTTROL FILES & REDLO LOG FILES AT OS LEVEL

$ cd /u01/app/oracle/oradata/crms $ mv redo* /tmp/ $ mv control01.ctl control01.ctl.bkp $ cd /u01/app/oracle/flash_recovery_area/crms/ $ mv control02.ctl control02.ctl.bkp

ERROR FROM PRIMARY DATABASE ALERT LOG

$ tail –f /u01/app/oracle/diag/rdbms/crms/crms/trace/ ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/crms/control01.ctl' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3

SYS> shut abort; ORACLE instance shut down. SYS> startup nomount; Oracle instance started Total System Global Area Fixed Size

912306176 bytes 1340244 bytes

Variable Size

562039980 bytes

Database Buffers

343932928 bytes

Redo Buffers

4993024 bytes

Connect target as Primary database with catalog database $ rman target sys/passwd@CRMSDB catalog rmanuser/passwd@CATDB ... connected to target database (not mounted) connected to recovery catalog database



RMAN> restore controlfile; Starting restore at 01-MAY-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: restoring control file channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/CRMS/autobackup/2016_04_30/o1_mf_s_910568582_cl9fm 8cp_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/CRMS/autobackup/2016_04_30/o1_mf_s_91056858 2_cl9fm8cp_.bkp tag=TAG20160430T234302 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 output file name=/u01/app/oracle/oradata/crms/control01.ctl output file name=/u01/app/oracle/flash_recovery_area/crms/control02.ctl Finished restore at 01-MAY-16 RMAN> alter database mount; database mounted released channel: ORA_DISK_1

SYS> select group#, sequence#, archived, status, first_change#, next_change# from v$log; GROUP#

SEQUENCE# ARC STATUS

FIRST_CHANGE# NEXT_CHANGE#

---------- ---------- --- ---------------- ------------- -----------1

13 YES ACTIVE

980664

980671

3

12 YES ACTIVE

980456

980664

2

14 NO

980671

2.8147E+14

CURRENT

Status column of v$log Current --

The online redo log is active. The database is currently writing.

Active

The online redo log is active but not the current log.

--

SYS> archive log list; Database log mode

Archive Mode

Automatic archival

Enabled

Archive destination

USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence

12

Next log sequence to archive

14

Current log sequence

14



RESTORE AND RECOVER THE DATABASE

RMAN> run { set until sequence 15; #(always set one number higher than the current log sequence) restore database; recover database; }

RMAN.txt

RMAN> alter database open resetlogs; database opened new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete

# On Primary database SYS> select resetlogs_id, resetlogs_change# from v$database_incarnation where status='CURRENT'; RESETLOGS_ID RESETLOGS_CHANGE# ------------ ----------------910659267

982626

# On Standby database SYS> select current_scn from v$database; CURRENT_SCN ----------985287

Now Standby database has diverged from Primary database. Now the Standby database SCN is ahead of reset logs change# so it needs to be flashback.

# RESETLOGS_CHANGE# on Standby database SYS> select resetlogs_id, resetlogs_change# from v$database_incarnation where status='CURRENT'; RESETLOGS_ID RESETLOGS_CHANGE# ------------ ----------------910659267

982626



ALERT LOG OF STANDBY DATABASE

RFS[5]: New Archival REDO Branch: 910659267 Current: 910542819 RFS[5]: Selected log 6 for thread 1 sequence 1 dbid 1612532377 branch 910659267 RFS[5]: New Archival REDO Branch(resetlogs_id): 910659267

Prior: 910542819

RFS[5]: Archival Activation ID: 0x60202af7 Current: 0x601eb786 RFS[5]: Effect of primary database OPEN RESETLOGS RFS[5]: Managed Standby Recovery process is active RFS[5]: Incarnation entry added for Branch(resetlogs_id): 910659267 (stbycrms) Sun May 01 00:55:23 2016 Setting recovery target incarnation to 6 Sun May 01 00:55:23 2016 MRP0: Incarnation has changed! Retry recovery... ORA-19906: recovery target incarnation changed during recovery Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 985288 Errors in file /u01/app/oracle/diag/rdbms/stbycrms/stbycrms/trace/stbycrms_mrp0_32657.trc: ORA-19906: recovery target incarnation changed during recovery Serial Media Recovery started Managed Standby Recovery starting Real Time Apply Warning: Recovery target destination is in a sibling branch of the controlfile checkpoint. Recovery will only recover changes to datafiles. Datafile 1 (ckpscn 985288) is orphaned on incarnation#=5 MRP0: Detected orphaned datafiles! Recovery will possibly be retried after flashback...

SYS> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS> startup mount; ORACLE instance started. Total System Global Area Fixed Size

912306176 bytes 1340244 bytes

Variable Size

562039980 bytes

Database Buffers

343932928 bytes

Redo Buffers

4993024 bytes

Database mounted.



Flashback Standby database to 2 SCN earlier than resetlogs_scn i.e. 988626 – 2 = 982624 SYS> flashback database to scn 982624;

FB-DB.txt

SYS> alter database open; Database altered. SYS> alter database recover managed standby database using current logfile disconnect; Database altered.

LOSS OF STANDBY CONTROL FILE

SYS> select name from v$controlfile; NAME ----------------------------------------------------------------/u01/app/oracle/oradata/stbycrms/control01.ctl /u01/app/oracle/flash_recovery_area/stbycrms/control02.ctl

RENAMING CONTROL NAME AT OS LEVEL

$ cd

/u01/app/oracle/oradata/stbycrms/

[oracle@OEL5 stbycrms]$ mv control01.ctl control01.ctl.bkp

IDENTIFYING THE MISSING CONTROL FILE

SYS> select current_scn from v$database; select current_scn from v$database * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/stbycrms/control01.ctl' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 SYS> shut abort; ORACLE instance shut down.



We can copy an intact copy of the control file over the lost copy, then restart the Standby instance using the following commands.

$ cd /u01/app/oracle/flash_recovery_area/stbycrms/ [oracle@OEL5 stbycrms]$ cp control02.ctl /u01/app/oracle/oradata/stbycrms/control01.ctl # If Active Data Guard NOT enabled SYS> startup mount; ORACLE instance started. .. ... Database mounted. SYS> alter database recover managed standby database disconnect from session; Database altered. # If Active Data Guard already enabled SYS> startup open; ORACLE instance started. .. ... Database mounted. Database opened. SYS> alter database recover managed standby database using current logfile disconnect; Database altered.

LOSS OF ALL STANDBY CONTROL FILES

Steps to recreate control file for Standby database in Data Guard environment.

SYS> select name from v$controlfile; NAME -------------------------------------------------------------------------------/u01/app/oracle/oradata/stbycrms/control01.ctl /u01/app/oracle/flash_recovery_area/stbycrms/control02.ctl

RENAMED CONTROL FILES AT OS LEVEL

$ cd /u01/app/oracle/flash_recovery_area/stbycrms $ mv control01.ctl control01.ctl.bkp $ cd /u01/app/oracle/oradata/stbycrms $ mv control02.ctl control02.ctl.bkp



SNIPPET OF ALERT LOG ERROR FROM STANDBY DATABASE

ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/stbycrms/control01.ctl' ORA-27041: unable to open file Linux Error: 2: No such file or directory SYS> shutdown abort; ORACLE instance shut down.

CREATE PHYSICAL STANDBY CONTROL FILE

# On Primary database SYS> alter database create standby controlfile as '/u03/rman-bkp/standby.ctl'; Database altered.

Copy the created controlfile from the Primary database server to the Standby server. $ cd /u03/rman-bkp/ $ scp standby.ctl [email protected]:/tmp [email protected]'s password: standby.ctl

100% 9904KB

9.7MB/s

00:00

Rename copied controlfile(s) as per control_files parameter of Standby database. # On Standby database $ cd /tmp $ cp standby.ctl /u01/app/oracle/oradata/stbycrms/control01.ctl $ cp standby.ctl /u01/app/oracle/flash_recovery_area/stbycrms/control02.ctl

Startup the Standby database Instance and mount it using newly created controlfile.

SYS> startup nomount; ORACLE instance started. .. ... SYS> alter database mount standby database; Database altered. SYS> alter database recover managed standby database disconnect; Database altered.



LOSS OF ONLINE REDLOG FILE ON PRIMARY

As we know Oracle recommends to multiplex redo log files in different locations. If the online redo log files are multiplexed, then the loss of one member will not impact the database. You can then shut down the database and copy the other members of the same group over the missing or damaged member. If all of the members of an inactive group that has been archived are lost, the group can be dropped and re-created. If the Current group or an Inactive group that has not yet been archived is damaged or missing, you must failover to the Standby database. Let’s jump to action immediately.

LOSS OF INACTIVE GROUP MEMBERS ON PRIMARY

SYS> select member from v$logfile where type='ONLINE'; MEMBER -----------------------------------------------------/u01/app/oracle/oradata/crms/redo03.log /u01/app/oracle/oradata/crms/redo02.log /u01/app/oracle/oradata/crms/redo01.log SYS> select group#, sequence#, archived, status, first_change#, next_change# from v$log; GROUP#

SEQUENCE# ARC STATUS

FIRST_CHANGE# NEXT_CHANGE#

---------- ---------- --- ---------------- ------------- -----------1

28 NO

2 3

CURRENT

1064971

2.8147E+14

26 YES INACTIVE

1062436

1063210

27 YES ACTIVE

1063210

1064971

DGMGRL> connect sys/passwd@CRMSDB Connected. DGMGRL> show configuration; Configuration - dgcrms Protection Mode: MaxPerformance Databases: crms

- Primary database

stbycrms - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS



RELOCATED ALL REDO LOG FILES

$ cd /u01/app/oracle/oradata/crms/ $ mv redo0* /tmp $ ls redo*

ALERT LOG OF PRIMARY DATABASE

Wed May 04 16:39:42 2016 Errors in file /u01/app/oracle/diag/rdbms/crms/crms/trace/crms_arc2_5403.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/crms/redo01.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory

FAILOVER USING DATA GUARD BROKER

DGMGRL> failover to stbycrms; Performing failover NOW, please wait... Failover succeeded, new primary is "stbycrms" DGMGRL> show configuration; Configuration - dgcrms Protection Mode: MaxPerformance Databases: stbycrms - Primary database crms

- Physical standby database (disabled)

ORA-16661: the standby database needs to be reinstated Fast-Start Failover: DISABLED Configuration Status: SUCCESS

SYS> select standby_became_primary_scn "FAILOVER_SCN", resetlogs_change#, current_scn, database_role from v$database; FAILOVER_SCN RESETLOGS_CHANGE# CURRENT_SCN DATABASE_ROLE ------------ ----------------- ----------- ---------------1043150

1043153

1043991 PRIMARY

If flashback option was not enabled on Primary, you need to rebuild Physical Standby.



FLASHBACK DATABASE WITH DATA GUARD

When the database is logically corrupted we can go for Incomplete Recovery of the database. Flashback database offers simple way to perform point-in-time recovery. Flashback feature enables you to rewind an entire database to a past TIME/SCN to correct any problems caused by Physical/Logical corruptions. Using command Flashback database to rewind the database to a Target Time, SCN or LSN (Log Sequence Number). A Flashback database operation applies to the whole database; cannot use flashback on the individual tablespaces. In SGA a buffer which is periodically logs before images of data blocks is called Flashback Buffer. This buffer records images of all changed data blocks in the database. Whenever a data block is altered, oracle writes a before image of that block to the flashback buffer. This before image can be used to reconstruct a data file to the particular point of time.

# Current Size of Flashback buffer SYS> select * from v$sgastat where name = 'flashback generation buff'; POOL

NAME

BYTES

------------ -------------------------------- ---------shared pool flashback generation buff 3981204

The background process RCWR (Recovery Writer) writes contents of the flashback buffer to flashback database log files. This process is started automatically when flashback database future is enabled. Flashback log files can be created under Flash Recovery Area (FRA). The Parameter db_recovery_file_dest defines the location of the Flash Recovery Area. SYS> show parameter db_recovery; RVWR process creates flashback log files into a directory named 'flashback' under FRA. V$flashback_database_logfile view can be used to check & monitor generated flashback logs.

SYS> select * from v$flashback_database_logfile;

V$RECOVER_FILE_DEST displays info about disk quota and current disk usage in the FRA. I.e. (The physical location allocated space, space reclaimable, number of files in the directory.

SYS> select * from v$recovery_file_dest;

V$flash_recovery_area_usage displays usage information about flash recovery areas. I.e. (Percent of recovery area that is in use, percent of recovery area that is reclaimable, number of files in the Recovery area). SYS> select * from v$flash_recovery_area_usage;



INCOMPLETE RECOVERY OF THE PRIMARY DATABASE

If any Logical or Physical corruption in the Primary database would cause an immediate corruption in the Standby database. To avoid such a pitfall, we can enable flashback database on Primary and Standby. Its better at-least enable flashback feature on the Standby database Server. Let’s see some examples.  Dropped Tables.  Truncated Tables.  Logical data corruption by insert/update/delete. Flashback database can only undo changes to a datafile made by an Oracle database. It can’t be used to repair media failures, or to recover from accidential deletion of datafiles. Configuring the flashback database future on the Primary database eliminates the need for recreating the database after a failover operation. If logical corruption has propagated to the Standby database, flashback Primary and Standby databases prior to the logical corruption, open RESETLOGS on primary database, and re-start apply on Standby database. If flashback option configured on the Standby database server, perform flashback prior to logical corruption on that server, failover, and activate the Standby database as the new Primary database.

SYS> show parameter db_recovery; NAME -------------------------

TYPE -----------

VALUE ------------------------------

db_recovery_file_dest

string

/u01/app/oracle/flash_recovery_area

db_recovery_file_dest_size

big integer

12000M

SYS> show parameter flashback; NAME

TYPE

VALUE

------------------------------------ ----------- -----------------------------db_flashback_retention_target

integer

1440

# 1 day  1440 mins (default)

# Find the earliest Flashback Point SYS>select oldest_flashback_scn, to_char(oldest_flashback_time,'DD.MM.YYYY HH24:MI:SS') oldest_flashback_time from v$flashback_database_log; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME -------------------- ---------------------1068414 12.05.2016 14:16:09



SCENARIO 1 : RECOVRING DROPPED SCHEMA

I have a schema USR1 and contains few tables.

Let’s see how to recover dropped schema

(including schema objects) using flashback feature.

# On Primary database (NO Flashback database enabled) SYS> select db_unique_name, flashback_on, database_role from v$database; DB_UNIQUE_NAME

FLASHBACK_ON

DATABASE_ROLE

------------------------------ ------------------ ---------------crms

NO

PRIMARY

# On Standby database (Flashback database enabled) SYS> select db_unique_name, flashback_on, database_role from v$database; DB_UNIQUE_NAME

FLASHBACK_ON

DATABASE_ROLE

------------------------------ ------------------ ---------------stbycrms

YES

PHYSICAL STANDBY

# On Primary and Standby database SYS> select table_name, owner from dba_tables where owner='USR1'; TABLE_NAME

OWNER

------------------------------ -----------------------------OBJS1

USR1

OBJS2

USR1

OBJS3

USR1

SYS> select count(*) from USR1.OBJS1; COUNT(*) ---------2552 SYS> select count(*) from USR1.OBJS2; COUNT(*) ---------2873 SYS> select count(*) from USR1.OBJS3; COUNT(*) ---------7244



SYS> select segment_name, sum(bytes/1024/1024) from dba_segments where segment_name like 'OBJS%' group by segment_name; SEGMENT_NAME

SUM(BYTES/1024/1024)

-------------------- -------------------OBJS2

188

OBJS3

422

OBJS1

256

# On Primary database (note the SCN before drop the schema usr1) SYS> select current_scn from v$database; CURRENT_SCN ----------1295300 SYS> drop user usr1 cascade; User dropped. SYS> select * from usr1.objs1; select * from usr1.objs1 * ERROR at line 1: ORA-00942: table or view does not exist

# On Standby database SYS> select * from usr1.objs1; select * from usr1.objs1 * ERROR at line 1: ORA-00942: table or view does not exist SYS> alter database recover managed standby database cancel; Database altered. SYS> shut immediate; Database dismounted. ORACLE instance shut down. SYS> startup mount; ORACLE instance started. .. ... Database mounted.



SYS> flashback database to scn 1295300; Flashback complete. $ cd /u01/app/oracle/diag/rdbms/stbycrms/stbycrms/trace/ $ tail –f alert_stbycrms.log flashback database to scn 1295300 Flashback Restore Start Flashback Restore Complete Flashback Media Recovery Start Serial Media Recovery started Flashback Media Recovery Log /u01/app/oracle/flash_recovery_area/STBYCRMS/archivelog /2016_05_20/o1_mf_1_76_cmx62v27_.arc Sat May 21 06:06:41 2016 Incomplete Recovery applied until change 1227821 time 05/21/2016 05:57:51 Flashback Media Recovery Complete Completed: flashback database to scn 1295300 SYS> alter database open; Database altered. SYS> conn usr1/passwd Connected. USR1> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------OBJS1 TABLE OBJS2

TABLE

OBJS3

TABLE

# On Primary database SYS> create user usr1 identified by usr1; User created. SYS> alter user usr1 default tablespace tbs1 quota unlimited on tbs1; User altered. SYS> grant connect, resource, create public database link to usr1; Grant succeeded. SYS> create public database link exp_crms connect to system identified by passwd using 'stby_crmsdb'; Database link created.



VERIFYING COUNT USING DATABASE LINK

SYS> conn usr1/usr1 Connected. USR1> select * from tab; no rows selected SYS> select count(*) from usr1.objs1@exp_crms; ... SYS> select count(*) from usr1.objs2@exp_crms; ... SYS> select count(*) from usr1.objs3@exp_crms; ...

Directory object named dpdir that is mapped to a directory located at '/u03/datapump/' so need to grant read and write privilege on the directory to user usr1 to load objects.

SYS> grant read, write on directory dpdir to system; Grant succeeded.

Now we have to export the schema (usr1) from the standby database to the Primary database. Once we create a database link in the Primary database pointing to the standby database, using NETWORK_LINK its possible to export the schema (usr1) from the Standby database.

# On Primary Database Server # Export the Schema from the Standby database $ expdp system/passwd directory=dpdir network_link=exp_crms schemas=usr1 dumpfile=schema_usr1.dmp logfile=schema_usr1.log Export: Release 11.2.0.1.0 - Production on Sat May 21 06:14:46 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates.

All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":

system/******** directory=dpdir

network_link=exp_crms schemas=usr1 dumpfile=schema_usr.dmp logfile=schema_usr.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 988 MB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA



Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "USR1"."OBJS1"

256 MB

2552 rows

. . exported "USR1"."OBJS2"

188 MB

2873 rows

. . exported "USR1"."OBJS3"

422 MB

7244 rows

Master table "USR1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for USR1.SYS_EXPORT_SCHEMA_01 is: /u03/datapump/schema_usr1.dmp Job "USR1"."SYS_EXPORT_SCHEMA_01" successfully completed at 06:26:17

IMPORT THE DUMPFILE IN PRIMARY DATABASE

Next process is need to import the dumpfile using remap_schema

# Import the dumpfile into usr1 in the Primary database $ impdp system/passwd directory=dpdir remap_schema=usr1:usr1 dumpfile=schema_usr1.dmp Import: Release 11.2.0.1.0 - Production on Sat May 21 06:42:06 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates.

All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "USR1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01":


remap_schema=usr1:usr1 dumpfile=schema_usr1.dmp Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "USR1"."OBJS1"

256 MB

2552 rows

. . imported "USR1"."OBJS2"

188 MB

2873 rows

. . imported "USR1"."OBJS3"

422 MB

7244 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "USR1"."SYS_IMPORT_FULL_01" successfully completed at 06:48:04



# On Primary database USR1> select * from tab; TNAME

TABTYPE

CLUSTERID

------------------------------ ------- ---------OBJS1

TABLE

OBJS2

TABLE

OBJS3

TABLE

USR1> select count(*) from objs1; ... USR1> select count(*) from objs2; ... USR1> select count(*) from objs3; ...

# On Standby database (Start the MRP process) SYS> alter database recover managed standby database using current logfile disconnect; Database altered. SYS> conn usr1/passwd Connected. USR1> select * from tab; TNAME

TABTYPE

CLUSTERID

------------------------------ ------- ---------OBJS1

TABLE

OBJS2

TABLE

OBJS3

TABLE

# On Primary database SYS> select current_scn from v$database; CURRENT_SCN ----------1313692 # On Standby database SYS> select current_scn from v$database; CURRENT_SCN ----------1313687



SCENARIO 3 : RECOVER SINGLE TABLE AFTER MASSIVE CHANGES

# On Primary database (NO Flashback database enabled) SYS> select db_unique_name, flashback_on, database_role from v$database; DB_UNIQUE_NAME

FLASHBACK_ON

DATABASE_ROLE

------------------------------ ------------------ ---------------crms

NO

PRIMARY

# On Standby database (Flashback database enabled) SYS> select db_unique_name, flashback_on, database_role from v$database; DB_UNIQUE_NAME

FLASHBACK_ON

DATABASE_ROLE

------------------------------ ------------------ ---------------stbycrms

YES

PHYSICAL STANDBY

# Find the earliest Flashback Point SYS>select oldest_flashback_scn, to_char(oldest_flashback_time,'DD.MM.YYYY HH24:MI:SS') oldest_flashback_time from v$flashback_database_log; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME -------------------- ---------------------1670128 22.05.2016 14:16:09

# On Primary Database Server SYS> select current_scn from v$database; CURRENT_SCN ----------1705286 SONY> drop table sony.pspruf_khb; Table dropped. SONY> purge recyclebin; Recyclebin purged. SYS> select scn_to_timestamp(1705286) from dual; SCN_TO_TIMESTAMP(1640599) --------------------------------------------------------------------------23-MAY-16 03.51.01.000000000 AM



Verify whether the table is existing or NOT on (Primary and Standby database).

# on Primary database and Standby database SYS> select * from sony.pspruf_khb; select * from sony.pspruf_khb * ERROR at line 1: ORA-00942: table or view does not exist

# On Standby (Stop MRP) SYS> alter database recover managed standby database cancel; Database altered. # Shutdown the database SYS> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. # Startup the database (Mount Phase) SYS> startup mount; ORACLE instance started. .. ... Database mounted.

# On Standby database - (Start Time based Flashback) SYS> flashback database to timestamp to_date('23-MAY-2016 03:51:00', 'DD-MON-YYYY HH24:MI:SS'); Flashback complete. # Snippet from alert log of Standby database $ cd /u01/app/oracle/diag/rdbms/stbycrms/stbycrms/trace $ tail –f alert_stbycrms.log Mon May 23 04:05:40 2016 flashback database to timestamp to_date('23-MAY-2016 03:51:00','DD-MON-YYYY HH24:MI:SS') Flashback Restore Start Flashback Restore Complete Flashback Media Recovery Start



Serial Media Recovery started Flashback Media Recovery Log /u01/app/oracle/flash_recovery_area/STBYCRMS/archivelog/2016_05_23/o1_mf_1_324_cn3x580 n_.arc Mon May 23 04:05:59 2016 Incomplete Recovery applied until change 1705283 time 05/23/2016 03:51:03 Flashback Media Recovery Complete Completed: flashback database to timestamp to_date('23-MAY-2016 03:51:00','DD-MON-YYYY HH24:MI:SS')

# Open the Standby database SYS> alter database open; Database altered. SONY> select count(*) from sony.pspruf_khb; ... # Checking Index details of the table(pspruf_khb) SONY> select * from v$object_usage; INDEX_NAME

TABLE_NAME

MON USE START_MONITORING

END_MONITORING

---------------------- ------------ --- --- ------------------- ------------------PSP_INDX_PORT_NAME

PSPRUF_KHB

YES YES

05/22/2016 18:35:40

PSP_INDX_PORT_NUMBER

PSPRUF_KHB

YES YES

05/22/2016 18:40:26

PSP_INDX_PORT_REFTYPE

PSPRUF_KHB

YES YES

05/22/2016 18:42:45

PSP_INDX_ZCID

PSPRUF_KHB

YES YES

05/22/2016 18:44:22

# Checking Index name along with Column_name of the table SONY> select a.table_name, a.index_name, a.UNIQUENESS, b.column_name, b.column_position, b.DESCEND from user_indexes a join all_ind_columns b on (b.table_owner = a.table_owner and b.index_name = a.index_name and b.table_name = a.table_name) order by 1,2,4,5; TABLE_NAME

INDEX_NAME

UNIQUENES COLUMN_NAME

COLUMN_POSITION DESC

------------ ---------------------- --------- --------------- --------------- ----PSPRUF_KHB

PSP_INDX_PORT_NAME

NONUNIQUE

SYS_NC00007$

1

ASC

PSPRUF_KHB


UNIQUE

PORTAL_NUMBER

1

ASC

PSPRUF_KHB


NONUNIQUE

PORTAL_REFTYPE

1

ASC

PSPRUF_KHB

PSP_INDX_ZCID

UNIQUE

ZCID

1

ASC



Creating public database link pointing to the Standby database on Primary database # Create Public Database link SYS> create public database link exp_crms_tab connect to system identified by manager using 'stby_crmsdb'; Database link created. SYS> select count(*) from sony.pspruf_khb@exp_crms_tab; ...

EXPORTING THE TABLE FROM THE STANDBY USING NETWORK_LINK

Already we have created a DB link in the primary database pointing to the Standby database, we can use NETWORK_LINK to export the table from the Standby database.

# Export the table from the Standby Server to Primary Server $ expdp system/manager directory=dpdir network_link=exp_crms_tab dumpfile=pspruf_khb_tab.dmp tables=sony.pspruf_khb logfile=pspruf_khb_tab.log .. ... Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TABLE_01":


network_link=exp_crms_tab dumpfile=pspruf_khb_tab.dmp tables=sony.pspruf_khb logfile=pspruf_khb_tab.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 793 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SONY"."PSPRUF_KHB"

628.15 MB

340344 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /u03/datapump/pspruf_khb_tab.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 05:22:09



IMPORT THE TABLE IN THE PRIMARY DATABASE

Import the pspruf_khb_tab.dmp file into the sony schema of the Primary database. # Import the .dmp file in the Primary database $ impdp system/manager dumpfile=pspruf_khb_tab.dmp tables=sony.pspruf_khb logfile= pspruf_khb_tab.log directory=dpdir .. ... Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TABLE_01":

system/******** dumpfile=pspruf_khb_tab.dmp

tables=sony.pspruf_khb logfile=pspruf_khb_tab.log directory=dpdir Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SONY"."PSPRUF_KHB"

628.15 MB

340344 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 06:08:33

# On Primary database SONY> select count(*) from pspruf_khb; COUNT(*) ---------340344 SONY> select * from v$object_usage; no rows selected SONY> spool enable_monitoring.sql select 'set ech on' from dual; select 'alter index

' || index_name || '

monitoring usage ;'

from user_indexes order by 1; SONY> spool off; We can start Index Monitoring using alter index statement.



SONY>@enable_monitoring.sql; Index altered. Index altered. Index altered. Index altered. SONY> select * from v$object_usage; INDEX_NAME

TABLE_NAME

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

MON

USE

START_MONITORING

END_MONITORING

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


PSPRUFDEF

YES

NO

05/23/2016 06:17:56

PSP_INDX_PORT_NAME

PSPRUFDEF

YES

NO

05/23/2016 06:18:10


PSPRUFDEF

YES

NO

05/23/2016 06:18:20

PSP_INDX_ZCID

PSPRUFDEF

YES

NO

05/23/2016 06:18:32

If the column USED is YES when the index is used to access the table. -- due to an update, merge, delete or SELECT statement(s).

# On Standby database - Enabling MRP (Active Standby) SYS> alter database recover managed standby database using current logfile disconnect; Database altered.

RESTORE CORRUPTED FILE FROM PRIMARY OR STANDBY

SYS> select name, db_unique_name, database_role from v$database; NAME

DB_UNIQUE_NAME

DATABASE_ROLE

--------- ------------------------------ ---------------CRMS

crms

PRIMARY

SYS> select name, db_unique_name, database_role from v$database; NAME

DB_UNIQUE_NAME

DATABASE_ROLE

--------- ------------------------------ ---------------CRMS

stbycrms

PHYSICAL STANDBY

# On Primary database SYS> select thread#, max(sequence#) from v$archived_log group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------1

358



# On Standby database SYS> select thread#, max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------1

358

# On Primary database SYS> select tablespace_name, table_name from dba_tables where owner='ROSE'; TABLESPACE_NAME

TABLE_NAME

------------------------------ -----------------------------TBS1

TAB1

SYS> select * from rose.tab1; .. ... ERROR: ORA-01578: ORACLE data block corrupted (file # 7, block # 243) ORA-01110: data file 7: '/u01/app/oracle/oradata/crms/tbs01.dbf' SYS> select * from v$database_block_corruption; FILE#

BLOCK#

BLOCKS CORRUPTION_CHANGE# CORRUPTION

---------- ---------- ---------- ------------------ --------7

243

13

0 ALL ZERO

7

240

1

0 ALL ZERO

7

224

1

0 ALL ZERO

7

208

1

0 ALL ZERO

7

192

1

0 ALL ZERO

7

176

1

0 ALL ZERO

7

160

1

0 ALL ZERO

7

144

1

0 ALL ZERO

7

128

2

0 ALL ZERO

# On Standby database SYS> select count(*) from rose.tab1; COUNT(*) ---------131072 SYS> select * from v$database_block_corruption; no rows selected



CONNECT PRIMARY AS TARGET WITH CATALOG DATABASE

$ rman target sys/passwd@CRMSDB catalog rmanuser/passwd@CATDB .. ... connected to target database: CRMS (DBID=1613387466) connected to recovery catalog database RMAN> list failure; starting full resync of recovery catalog full resync complete List of Database Failures ========================= Failure ID Priority Status

Time Detected Summary

---------- -------- --------- ------------- ------1722

HIGH

OPEN

24-MAY-16

Datafile 7:

'/u01/app/oracle/oradata/crms/tbs01.dbf' contains one or more corrupt blocks

CONNECT STANDBY AS TARGET WITH CATALOG DATABASE

I am taking a backup copy of the datafile #7 (image copy) from the standby database

$ rman target sys/passwd@STBY_CRMSDB catalog rmanuser/passwd@CATDB .. ... connected to target database: CRMS (DBID=1613387466) connected to recovery catalog database RMAN> backup as copy datafile 7 format '/u03/bkp/tbs01.dbf'; Starting backup at 24-MAY-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/u01/app/oracle/oradata/stbycrms/tbs01.dbf output file name=/u03/bkp/tbs01.dbf tag=TAG20160524T221155 RECID=19 STAMP=912723116 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:22 Finished backup at 24-MAY-16; Copy the backup piece to the Primary database Server from the Standby database Server. $ ls /u03/bkp/ tbs01.dbf [oracle@OEL5 bkp] $ scp tbs01.dbf [email protected]:/u03/rman-bkp [email protected]'s password: tbs01.dbf

100%

140MB

14.0MB/s


00:10


# On Primary database SYS> alter database datafile 7 offline; Database altered.

We need to register the backup file using RMAN catalog command to the Primary database controlfile; so that the controlfile of the Primary gets updated with this backup copy.

$ rman target sys/passwd@CRMSDB .. ... connected to target database: CRMS (DBID=1613387466) RMAN> catalog datafilecopy '/u03/rman-bkp/tbs01.dbf'; using target database control file instead of recovery catalog cataloged datafile copy datafile copy file name=/u03/rman-bkp/tbs01.dbf RECID=10 STAMP=912725119 RMAN> restore datafile 7; Starting restore at 24-MAY-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=52 device type=DISK channel ORA_DISK_1: restoring datafile 00007 input datafile copy RECID=10 STAMP=912725119 file name=/u03/rman-bkp/tbs01.dbf destination for restore of datafile 00007: /u01/app/oracle/oradata/crms/tbs01.dbf channel ORA_DISK_1: copied datafile copy of datafile 00007 output file name=/u01/app/oracle/oradata/crms/tbs01.dbf RECID=0 STAMP=0 Finished restore at 24-MAY-16 RMAN> recover datafile 7; Starting recover at 24-MAY-16 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:06 Finished recover at 24-MAY-16 We done recovery process so bring back the datafile #7 online.

RMAN> sql 'alter database datafile 7 online'; sql statement: alter database datafile 7 online



# Validate the datafile (If any corruption exists) RMAN> validate check logical datafile 7; Starting validate at 24-MAY-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00007 name=/u01/app/oracle/oradata/crms/tbs01.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:01 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------7

OK

0

129

1792

1825330

File Name: /u01/app/oracle/oradata/crms/tbs01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------Data

0

1504

Index

0

0

Other

0

159

Finished validate at 24-MAY-16

# On Primary database SYS> select count(*) from rose.tab1; COUNT(*) ---------131072 SYS> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------364 # On Standby database SYS> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------364



HOW TO FIX ORA-01274 : CANNOT ADD DATAFILE

If STANDBY_FILE_MANAGEMENT = MANUAL, it creates issue when adding datafile. Let’s test a scenario when I create a tablespace on Primary database and STANDBY_FILE_MANAGEMENT is set MANUAL on the Physical Standby database. If STANDBY_FILE_MANAGEMENT is set AUTO, whenever files added/dropped on Primary database are reflected automatically to the Standby database. I.e.(File addition & deletions on the Primary database will be replicated on the Standby database. The default value is MANUAL.

# On Primary database SYS> select name, db_unique_name, database_role from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE --------- ------------------------------ ---------------CRMS crms PRIMARY # On Standby database SYS> select name, db_unique_name, database_role from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE --------- ------------------------------ ---------------CRMS stbycrms PHYSICAL STANDBY

# On Primary database SYS> select * from v$tablespace; TS# NAME

INC BIG FLA ENC

---------- ------------------------------ --- --- --- --0 SYSTEM

YES NO

YES

1 SYSAUX

YES NO

YES

2 UNDOTBS1

YES NO

YES

4 USERS

YES NO

YES

3 TEMP

NO

NO

YES

6 EXAMPLE

YES NO

YES

7 TBS1

YES NO

YES

7 rows selected. # Create a new tablespace mytbs SYS> create tablespace mytbs datafile '/u01/app/oracle/oradata/crms/mytbs01.dbf' size 100m autoextend on; Tablespace created.



# Create a user and assign mytbs tablesace to the user (myusr) SYS> create user myusr identified by passwd default tablespace mytbs quota 1g on mytbs; User created. SYS> grant create session, resource to myusr; Grant succeeded. SYS> create table myusr.mytab as select * from user_objects; Table created. SYS> insert into myusr.mytab select * from myusr.mytab; ... SYS> insert into myusr.mytab select * from myusr.mytab; ... SYS> / ... SYS> / ... SYS> select count(*) from mytab; COUNT(*) ---------4096 SYS> alter system switch logfile; System altered.

# On Primary database SYS> select file#, name from v$datafile; FILE# NAME ---------- -----------------------------------------1 /u01/app/oracle/oradata/crms/system01.dbf 2 /u01/app/oracle/oradata/crms/sysaux01.dbf 3 /u01/app/oracle/oradata/crms/undotbs01.dbf 4 /u01/app/oracle/oradata/crms/users01.dbf 5 /u01/app/oracle/oradata/crms/example01.dbf 6 /u01/app/oracle/oradata/crms/example02.dbf 7 /u01/app/oracle/oradata/crms/tbs01.dbf 8 /u01/app/oracle/oradata/crms/mytbs01.dbf 8 rows selected.



# On Standby database SYS> select file#, name from v$datafile; FILE# NAME ---------- -------------------------------------------------------------1 /u01/app/oracle/oradata/stbycrms/system01.dbf 2 /u01/app/oracle/oradata/stbycrms/sysaux01.dbf 3 /u01/app/oracle/oradata/stbycrms/undotbs01.dbf 4 /u01/app/oracle/oradata/stbycrms/users01.dbf 5 /u01/app/oracle/oradata/stbycrms/example01.dbf 6 /u01/app/oracle/oradata/stbycrms/example02.dbf 7 /u01/app/oracle/oradata/stbycrms/tbs01.dbf 8 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008 8 rows selected.

Here we can notice that datafile #8 on the Standby database is created at the location $ORACLE_HOME/dbs with the name as UNNAMED rather than getting created at the specified location as per db_file_name_convert parameter (check value of the parameter on Primary). SNIPPET OF ALERT LOG FROM STANDBY DATABASE SERVER

When I check standby_file_management parameter, it shows default value (MANUAL). SYS> show parameter standby_file_management; NAME TYPE VALUE ------------------------------------ ----------- -----------------------------standby_archive_dest string ?/dbs/arch standby_file_management

string

MANUAL

Now we got the file# and name from the Primary database and check the filename UNNAMED00008 that is created on the Standby database. We need to recreate on Standby database.



# Recreate unnamed datafile at specific location on Standby database SYS> alter database create datafile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008

as

/u01/app/oracle/oradata/stbycrms/mytbs01.dbf; Database altered. # Snippet of Standby alert log file $ cd /u01/app/oracle/diag/rdbms/stbycrms/stbycrms/trace $ tail –f alert_stbycrms.log Wed May 25 06:29:59 2016 alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008' as '/u01/app/oracle/oradata/stbycrms/mytbs01.dbf' Completed: alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008' as '/u01/app/oracle/oradata/stbycrms/mytbs01.dbf'

# Change Standby_file_management to auto SYS> alter system set standby_file_management=auto; System altered. # Restart MRP SYS> alter database recover managed standby database disconnect from session; Database altered. SYS> select count(*) from myusr.mytab; COUNT(*) ---------4096 SYS> select max(sequence#) from v$archived_log;

#(On Primary)

MAX(SEQUENCE#) -------------367 SYS> select max(sequence#) from v$archived_log where applied='YES';

#(On Standby)

MAX(SEQUENCE#) -------------367



AUTOMATIC BLOCK MEDIA RECOVERY - ABMR

Automatic block media requires an Oracle Active Data Guard option. You can use this AUTO BMR feature, if active Data Guard is enabled. Block repair requires an active Data Guard that means separate license is required. Corrupt blocks on the Primary database or Physical Standby database to be automatically repaired as soon as the corruption is detected by transferring good blocks from the other destination; which means, If block corruption occurs on the Primary database, block media recovery is performed automatically by transferring good copy of the block from the Standby database; similarly if a block corruption occurs on the Standby database, will be repaired using the data block on the Primary database. (Vice versa this BMR operation is possible). First we have to enable Active Standby; in my case, I am using Active Standby.

SYS> select DEST_NAME, RECOVERY_MODE from v$archive_dest_status where dest_id=1; DEST_NAME

RECOVERY_MODE

---------------------- ----------------------LOG_ARCHIVE_DEST_1

MANAGED REAL TIME APPLY

STEPS TO ENABLE ACTIVE DATA GUARD

SYS> select database_role, open_mode from v$database; DATABASE_ROLE

OPEN_MODE

---------------- -------------------PHYSICAL STANDBY MOUNTED SYS> alter database recover managed standby database disconnect; Database altered. SYS> alter database open; Database altered. SYS> select database_role, open_mode from v$database; DATABASE_ROLE

OPEN_MODE

---------------- -------------------PHYSICAL STANDBY READ ONLY SYS> alter database recover managed standby database using current logfile disconnect; Database altered. SYS> select database_role, open_mode from v$database; DATABASE_ROLE

OPEN_MODE

---------------- -------------------PHYSICAL STANDBY READ ONLY WITH APPLY



SYS> select dest_name, recovery_mode from v$archive_dest_status where dest_id=1; DEST_NAME

RECOVERY_MODE

---------------------- ----------------------LOG_ARCHIVE_DEST_1

MANAGED REAL TIME APPLY

Points to Note: READ ONLY WITH APPLY - A Physical Standby database is open in real-time query mode. MANAGED REAL TIME APPLY - Log apply services recover redo data from the Standby redo logs at the same time the logs are being written to, as opposed to recovering redo from archived redo logs when a log switch occurs.

# On Primary database SYS> create table scott.mytab as select * from user_objects; Table created. SYS> select table_name, tablespace_name from dba_tables where table_name='MYTAB' and owner='SCOTT'; TABLE_NAME

TABLESPACE_NAME

------------------------------ -----------------------------MYTAB USERS

# Finding the least block occupied by scott.mytab table SYS> select min(dbms_rowid.rowid_block_number(rowid)) from scott.mytab; MIN(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) ----------------------------------------787 # On Primary database SYS> select count(*) from scott.mytab; COUNT(*) ---------61958 # On Standby database SYS> select count(*) from scott.mytab; COUNT(*) ---------61958



# Calling dbv utility (DB_VERIFY) $ which dbv /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbv # Physical Structure Integrity check of the users01.dbf file $ dbv file=/u01/app/oracle/oradata/crms/users01.dbf .. ... DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/crms/users01.dbf DBVERIFY - Verification complete Total Pages Examined

: 10880

Total Pages Processed (Data) : 7554 Total Pages Failing

(Data) : 0

Total Pages Processed (Index): 2693 Total Pages Failing

(Index): 0

Total Pages Processed (Other): 498 Total Pages Processed (Seg)

: 0

Total Pages Failing

: 0

(Seg)

Total Pages Empty

: 135

Total Pages Marked Corrupt

: 0

Total Pages Influx

: 0

Total Pages Encrypted

: 0

Highest block SCN

: 1971919 (0.1971919)

CORRUPT THE BLOCK USING dd COMAMND

$ dd if=/dev/zero of=/u01/app/oracle/oradata/crms/users01.dbf bs=8192 conv=notrunc seek=822 count=2 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 0.000127939 seconds, 128 MB/s

FIND CORRUPTED BLOCKS USING DBV UTILITY

$ dbv file=/u01/app/oracle/oradata/crms/users01.dbf .. ... DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/crms/users01.dbf Page 822 is marked corrupt Corrupt block relative dba: 0x01000336 (file 4, block 822) Completely zero block found during dbv:



Page 823 is marked corrupt Corrupt block relative dba: 0x01000337 (file 4, block 823) Completely zero block found during dbv: DBVERIFY - Verification complete Total Pages Marked Corrupt

: 2

FLUSH BUFFER CACHE & EXECUTE THE QUERY

SYS> alter system flush buffer_cache; System altered. SYS> select count(*) from scott.mytab; ... # Snippet from alert log of Primary database $ tail -f alert_crms.log Thu May 26 14:55:53 2016 Hex dump of (file 4, block 822) in trace file /u01/app/oracle/diag/rdbms/crms/crms/trace/crms_ora_7312.trc Corrupt block relative dba: 0x01000336 (file 4, block 822) Completely zero block found during multiblock buffer read Reading datafile '/u01/app/oracle/oradata/crms/users01.dbf' for corruption at rdba: 0x01000336 (file 4, block 822) Reread (file 4, block 822) found same corrupt data Requesting Auto BMR for (file# 4, block# 822) Hex dump of (file 4, block 823) in trace file /u01/app/oracle/diag/rdbms/crms/crms/trace/crms_ora_7312.trc Corrupt block relative dba: 0x01000337 (file 4, block 823) Completely zero block found during multiblock buffer read Reading datafile '/u01/app/oracle/oradata/crms/users01.dbf' for corruption at rdba: 0x01000337 (file 4, block 823) Reread (file 4, block 823) found same corrupt data Requesting Auto BMR for (file# 4, block# 823) Waiting Auto BMR response for (file# 4, block# 822) Auto BMR successful Waiting Auto BMR response for (file# 4, block# 823) Auto BMR successful

$ dbv file=/u01/app/oracle/oradata/crms/users01.dbf blocksize=8192 ... DBVERIFY - Verification complete Total Pages Marked Corrupt

: 0



A detected corrupt block through a user’s SQL query is repaired automatically by using good block from a Physical Standby database. Let’s check count of scott.mytab table.

# On Primary and Standby database SYS> select count(*) from scott.mytab; COUNT(*) ---------61958

PONTS TO NOTE

If block media recovery to work automatically, the Physical Standby must be in Real-Time query mode. If automatic repair is not possible, an ORA-1578 error is returned. If a corrupt data block is discovered on a Physical Standby database, the Server attempts to repair the corruption by obtaining a good copy of the block from the primary database. The repair is performed in the background. Automatic block repair is attempted if the following database initialization parameters are configured on the Standby database. The

LOG_ARCHIVE_CONFIG

parameter

is

configured

with

a

DG_CONFIG

list

and

a

LOG_ARCHIVE_DEST_n parameter is configured for the primary database. (Or) The FAL_SERVER parameter is configured and its value contains an Oracle Net service name for the primary database. When using (log_archive_dest_2 or fal_server) parameters Oracle internally uses tnsnames.ora file to connect to the Primary database.

# On Standby database SYS> show parameter LOG_ARCHIVE_CONFIG; NAME TYPE VALUE ------------------------------------ ----------- -----------------------------log_archive_config string DG_CONFIG=(crms,stbycrms)

SYS> show parameter LOG_ARCHIVE_DEST_2; NAME TYPE VALUE ------------------------------------ ----------- -----------------------------log_archive_dest_2 string service=crmsdb LGWR ASYNC VALI D_FOR=(ONLINE_LOGFILES,PRIMARY _ROLE) DB_UNIQUE_NAME=crms SYS> show parameter fal_server; NAME

TYPE

VALUE

------------------------------------ ----------- -----------------------------fal_server

string

CRMSDB



STANDBY DISK RUNS OUT OF SPACE WHEN ADDING A DATAFILE

The Standby database Server disk space is insufficient at specific mount point (/u01), When you create a data file on the Primary site, how the Standby database going to react.

# On Primary database SYS> select name, db_unique_name, database_role from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE --------- ------------------------------ ---------------CRMS

crms

PRIMARY

SYS> select thread#, max(sequence#) from v$archived_log group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------1

493

# On Standby database SYS> select name,db_unique_name,

database_role, open_mode from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ------------ ------------------------------ ---------------- -------------------CRMS

stbycrms


SYS> select thread#, max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------1

493

# Diskspace of /u01 on Standby Server $ df -TH /u01/ Filesystem

Type

Size

Used

/dev/sda2

ext3

27G

26G

Avail

Use%

Mounted on

20M

100%

/u01

# On Primary and Standby database SYS> select file_name from dba_data_files where tablespace_name='USERS'; FILE_NAME -------------------------------------------------------------------------------/u01/app/oracle/oradata/crms/users01.dbf



# On Primary database SYS> alter tablespace users add datafile '/u01/app/oracle/oradata/crms/users02.dbf' size 100m; Tablespace altered. SYS> select file_name from dba_data_files where tablespace_name='USERS'; FILE_NAME -------------------------------------------------------------------------------/u01/app/oracle/oradata/crms/users01.dbf /u01/app/oracle/oradata/crms/users02.dbf

# On Standby database SYS> select file_name from dba_data_files where tablespace_name='USERS'; FILE_NAME -------------------------------------------------------------------------------/u01/app/oracle/oradata/crms/users01.dbf

# Snippet of alert log from Standby database $ tail -f alert_stbycrms.log .. File #7 added to control file as 'UNNAMED00007'. Originally created as: '/u01/app/oracle/oradata/crms/users02.dbf' Recovery was unable to create the file as: '/u01/app/oracle/oradata/stbycrms/users02.dbf' MRP0: Background Media Recovery terminated with error 1274 Errors in file /u01/app/oracle/diag/rdbms/stbycrms/stbycrms/trace/stbycrms_mrp0_19139.trc: ORA-01274: cannot add datafile '/u01/app/oracle/oradata/crms/users02.dbf' - file could not be created .. ... Recovery interrupted! Recovered data files to a consistent state at change 2528425 Errors in file /u01/app/oracle/diag/rdbms/stbycrms/stbycrms/trace/stbycrms_mrp0_19139.trc: ORA-01274: cannot add datafile '/u01/app/oracle/oradata/crms/users02.dbf' - file could not be created MRP0: Background Media Recovery process shutdown (stbycrms)



** Space issue with data files Data Guard always create the UNNAMED datafiles. ** # On Standby database SYS> select file#,name from v$datafile where name like '%UNNAMED%'; FILE# NAME ---------- -------------------------------------------------------------7 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00007 SYS> show parameter standby_file; NAME

TYPE

VALUE

------------------------------------ ----------- -----------------------------standby_archive_dest

string

?/dbs/arch

standby_file_management

string

AUTO

It’s possible to extend /u01, if you can use lvm + enough free disk space. If possible, delete unwanted files to reclaim disk space or move to some other locations. Once you get free space on /u01 mount point, you can proceed following steps. SYS> alter system set standby_file_management=manual; System altered. SYS> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00007' as '/u01/app/oracle/oradata/stbycrms/users02.dbf'; Database altered SYS> alter system set standby_file_management=auto; System altered. SYS> shut immediate; Database closed. Database dismounted. SYS> alter database recover managed standby database using current logfile disconnect; Database altered. SYS> select file_name from dba_data_files where tablespace_name='USERS'; FILE_NAME -------------------------------------------------------------------------------------/u01/app/oracle/oradata/stbycrms/users01.dbf /u01/app/oracle/oradata/stbycrms/users02.dbf Best Solution: It would be best to avoid this kind of issues by monitoring the space on the Standby(s) daily since that takes minutes.



LOSS OF CONTROL FILE ON PRIMARY

# On Primary database SYS> select name, db_unique_name, database_role from v$database; NAME

DB_UNIQUE_NAME

DATABASE_ROLE

--------- ------------------------------ ---------------CRMS

crms

PRIMARY

# On Standby database SYS> select name, db_unique_name, database_role from v$database; NAME

DB_UNIQUE_NAME

DATABASE_ROLE

FLASHBACK_ON

------------ ----------------------- ---------------- --------------CRMS

stbycrms

PHYSICAL STANDBY

YES

# On the Primary database SYS> select name from v$controlfile; NAME -------------------------------------------------------------------------------/u01/app/oracle/oradata/crms/control01.ctl /u01/app/oracle/flash_recovery_area/crms/control02.ctl # Control file backup in Human Readable format at Primary SYS> alter database backup controlfile to trace as '/home/oracle/ctrl.sql'; Database altered.

# On Primary database SYS> select thread#, max(sequence#) from v$archived_log group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------1

499


499



RENAME CONTROL FILES AT OS LEVEL

$ cd /u01/app/oracle/oradata/crms/ $ mv control01.ctl control01.ctl.bkp $ cd /u01/app/oracle/flash_recovery_area/crms/ $ mv control02.ctl control02.ctl.bkp

SYS> select current_scn from v$database; select current_scn from v$database * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/crms/control01.ctl' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 # Down the Primary database instance SYS> shut abort; ORACLE instance shut down.

$ cat ctrl.sql CREATE CONTROLFILE REUSE DATABASE "CRMS" NORESETLOGS FORCE LOGGING ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 584 LOGFILE GROUP 1 '/u01/app/oracle/oradata/crms/redo01.log'

SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/crms/redo02.log'

SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/crms/redo03.log'

SIZE 50M BLOCKSIZE 512

DATAFILE '/u01/app/oracle/oradata/crms/system01.dbf', '/u01/app/oracle/oradata/crms/sysaux01.dbf', '/u01/app/oracle/oradata/crms/undotbs01.dbf', '/u01/app/oracle/oradata/crms/users01.dbf', '/u01/app/oracle/oradata/crms/example01.dbf', '/u01/app/oracle/oradata/crms/users02.dbf', '/u01/app/oracle/oradata/crms/mytbs01.dbf' CHARACTER SET WE8MSWIN1252 ;



Let’s start the Instance without control files, but instance cannot be mounted. SYS> startup nomount; ORACLE instance started. Total System Global Area

912306176 bytes

Fixed Size

1340244 bytes

Variable Size

620760236 bytes

Database Buffers

285212672 bytes

Redo Buffers

4993024 bytes

SYS> alter database mount; alter database mount * ERROR at line 1: ORA-00205: error in identifying control file, check alert log for more info

Above error clearly states if the control files are missing, then the database cannot be mounted, so at most, the database state is NOMOUNT. Any attempt to mount would fail because of missing control files. Let’s check status of the instance. SYS> select status from v$instance; STATUS -----------STARTED SYS> @/home/oracle/ctrl.sql; Control file created. SYS> alter database open; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/u01/app/oracle/oradata/crms/system01.dbf' SYS> recover database; Media recovery complete. SYS> select open_resetlogs from v$database; OPEN_RESETL ----------NOT ALLOWED



SYS> alter database open; Database altered. SYS> select thread#, max(sequence#) from v$archived_log group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------1

502


502

If all control files have been lost but all online redo log members remain intact then it’s possible to recover the database after creating new control files. It’s NOT required to open the database with RESETLOGS option after the recovery. Control files will get metadata from database files. Reference doc here # On Primary database SYS> select name from v$tempfile; no rows selected # On Standby database SYS> select name from v$tempfile; NAME -------------------------------------------------------------------------------/u01/app/oracle/oradata/stbycrms/temp01.dbf

# On Primary database SYS> select * from tab2 order by name; select * from tab2 order by name * ERROR at line 1: ORA-25153: Temporary Tablespace is Empty SYS> alter tablespace temp add tempfile '/u01/app/oracle/oradata/crms/temp01.dbf' size 100m reuse; Tablespace altered.



POINTS TO NOTE

Restoring a control file does not mean Incomplete Recovery. Oracle can still do a Complete Recovery if it has all the Archive logs and Online Redo Logs. If we perform incomplete recovery (reset logs) on Primary, you must have flashback enabled on Standby database, you can then flashback Standby database to resetlogs_change# on primary and it’s possible to sync with Primary database. If flashback is NOT enabled on the Standby database, you have to rebuild the Standby database. ORACLE FLASHBACK DATABASE

Using this feature you can recover the database from logical corruptions to specified point in TIME/SCN. Flashback logs contain only "before-images of data blocks" related to some SCN so Flashback logs are NOT independent. They can be used only with redo data that contains database changes around the desired SCN. The view v$FLASHBACK_DATABASE_LOGFILE displays info about flashback logs. There is a record section within the control file header named as FLASHBACK LOGFILE RECORDS, this section contains info about the lowest and highest SCN of all flashback log files. You can query v$FLASHBACK_DATABASE_LOGFILE to find about flashback logs.

SYS> select * from v$flashback_database_logfile;

When you use (flashback database to timestamp) command, (Oracle reads flashback logfile section in the control file and checks availability of required flashback logs) it rewinds a database to a past target time; Oracle restores old version blocks (which blocks has changed after the target time and restores them from the flashback logs). The database restores the version of each block before the target time. Then the database uses redo logs to reapply changes that were made after these block were written to the flashback logs Without that redo data, the flashback operation cannot succeed. If desired restore point of time is 10:00 AM and the oldest restored data block is from 09:47 AM then you need all archived log files that contain redo data since 09:47 AM to 10:00 AM. To get additional information Why flashback on Data Guard here.


RECOVERY SCENARIOS IN DATA GUARD ENVIRONMENT.pdf

Recommend Documents