Dataguard Posts

Dataguard Setup

• Dataguard Setup
  
• DG-Resync (GAP <=10 archs): Manual-registering-of-missing-files
  
• DG-Resync (GAP <= 50 archs):Recovering-standby-database
  
• DG-Resync (GAP <=1000 archs):rman-inremental
  
• DG Resync (Corruption): Active RMAN Clone
  

Dataguard Troubleshooting [ORA-]

• corrupted-archive-redo-logs-on-dataguard
  
• Unnamed-file-error-in-standby-after
  
• Data Block Corruption
  
• Cannot-extend-datafile
  

RMAN Inremental

2.3.1.              Steps to follow:

1. Connect to TRAXSP database in SYDDC1TRX01 Server using sqlplus tool and stop the automatic applying of archived log files.

SQL> alter database recover managed standby database cancel;

2. Find the current SCN (System Change Number) in TRAXSP

SQL> select current_scn from v$database;

3. Shut down the Standby database.

SQL> shut immediate

4. On the Production side, connect to TRAXMP as the target database through RMAN.

[oracle@meldc1trx01 ~]$ rman target sys@TRAXMP

5. Create an incremental backup of TRAXMP from the SCN found in TRAXSP.

RMAN> backup incremental from scn <current_scn> database tag 'FORSTANDBY' FORMAT ‘/backup/export/FOR_STANDBY_%U';

6. Then, backup the controlfile of TRAXMP for Standby in the RMAN prompt

RMAN> backup current controlfile for standby format '/backup/export/FORSTDBYCTRL.bkp';

7. Copy the incremental backup from MELDC1TRX01 Server to SYDCC1TRX01 Server to the location ‘/backup/’ through scp command as:

·         Connect to SYDDC1TRX01 server and fire the below command from OS prompt.

scp oracle@meldc1trx01:/backup/export/<incremental backup name> /backup/

OR

·         Connect to meldc1trx01 server and fire the below command from OS prompt.

scp /backup/export/<incremental backup name>  oracle@meldc1trx02:/backup/

Similarly copy the controlfile backup from MELDC1TRX01 Server to SYDCC1TRX01 Server to the location ‘/backup/’ through scp command.

8.       Now, open two putty terminals for SYDDC1TRX01 Server. In the first terminal, connect to TRAXSP database and start the database in nomount stage.

SQL> startup nomount

9.       In the second terminal, connect TRAXSP as target database through RMAN.

[oracle@syddc1trx01 ~]$ rman target sys@TRAXSP

10.    Restore the controlfile using its backup.

RMAN> RESTORE STANDBY CONTROLFILE FROM ‘/backup/FORSTDBYCTRL.BKP’;

               

11.    Now, in the first terminal, bring the database to the mount stage.

SQL>alter database mount standby database;

12.    In the second terminal, Catalog the Incremental Backups on the Standby Server

RMAN> catalog start with ‘/backup’;

13.    Recover the Database and Cleanup Redologs on the Standby Server

RMAN> recover database noredo;

14.    In the first terminal, start the automatic applying of archived log files on TRAXSP.

                SQL> alter database recover managed standby database disconnect from session;

15.    Then check for gaps.

Rebuilding Standby database

2.3.1.              Steps to follow:

1. Connect to TRAXSP database in SYDDC1TRX01 Server using sqlplus tool and stop the automatic applying of archived log files.

SQL> alter database recover managed standby database cancel;

2. Find the current SCN (System Change Number) in TRAXSP

SQL> select current_scn from v$database;

3. Shut down the Standby database.

SQL> shut immediate

4. On the Production side, connect to TRAXMP as the target database through RMAN.

[oracle@meldc1trx01 ~]$ rman target sys@TRAXMP

5. Create an incremental backup of TRAXMP from the SCN found in TRAXSP.

RMAN> backup incremental from scn <current_scn> database tag 'FORSTANDBY' FORMAT ‘/backup/export/FOR_STANDBY_%U';

6. Then, backup the controlfile of TRAXMP for Standby in the RMAN prompt

RMAN> backup current controlfile for standby format '/backup/export/FORSTDBYCTRL.bkp';

7. Copy the incremental backup from MELDC1TRX01 Server to SYDCC1TRX01 Server to the location ‘/backup/’ through scp command as:

·         Connect to SYDDC1TRX01 server and fire the below command from OS prompt.

scp oracle@meldc1trx01:/backup/export/<incremental backup name> /backup/

OR

·         Connect to meldc1trx01 server and fire the below command from OS prompt.

scp /backup/export/<incremental backup name>  oracle@meldc1trx02:/backup/

Similarly copy the controlfile backup from MELDC1TRX01 Server to SYDCC1TRX01 Server to the location ‘/backup/’ through scp command.

8.       Now, open two putty terminals for SYDDC1TRX01 Server. In the first terminal, connect to TRAXSP database and start the database in nomount stage.

SQL> startup nomount

9.       In the second terminal, connect TRAXSP as target database through RMAN.

[oracle@syddc1trx01 ~]$ rman target sys@TRAXSP

10.    Restore the controlfile using its backup.

RMAN> RESTORE STANDBY CONTROLFILE FROM ‘/backup/FORSTDBYCTRL.BKP’;

               

11.    Now, in the first terminal, bring the database to the mount stage.

SQL>alter database mount standby database;

12.    In the second terminal, Catalog the Incremental Backups on the Standby Server

RMAN> catalog start with ‘/backup’;

13.    Recover the Database and Cleanup Redologs on the Standby Server

RMAN> recover database noredo;

14.    In the first terminal, start the automatic applying of archived log files on TRAXSP.

                SQL> alter database recover managed standby database disconnect from session;

15.    Then check for gaps.

Recovering the Standby database

 Connect to the standby database (TRAXSP) on SYDDC1TRX01 Server using sqlplus tool and confirm the right database is connected by executing the below SQL command.

SQL> select instance_name,status from v$instance;

2.       Check the missing files in TRAXSP.

a.       To check the number of sequences missing along with the higher and lower sequence:

SQL> select * from v$archive_gap;

b.       To check the missing file sequence number:

SQL> select sequence#,applied,status from v$archived_log where applied='NO';

3. Using the sequence number, copy the missing file from (/arch/oraarchive/traxp/) MELDC1TRX01 Server to the appropriate location (/arch/oraarchive/traxsp/) in SYDDC1TRX01 Server.

It can be copied by using scp command.

·         Connect to SYDDC1TRX01 server and fire the below command from OS prompt.

scp oracle@meldc1trx01:/arch/oraarchive/traxp/<missing sequence name> /arch/oraarchive/traxsp/

OR

·         Connect to meldc1trx01 server and fire the below command from OS prompt.

scp /arch/oraarchive/traxp/<missing sequence name>  oracle@meldc1trx02:/arch/oraarchive/traxsp/

                                                                                                                                                                            

Provide password when asked.

1.       Stop the automatic applying of archived log files in standby database (TRAXSP) by executing the following command:

SQL> alter database recover managed standby database cancel;

2.       Recover the standby database with the copied missing files:

SQL> recover standby database;

3.       Then give the command

AUTO

This will recover TRAXSP with the unapplied archived log files.

4.       When all the archived log files are applied in TRAXSP, once again start the automatic applying by the following command:

SQL> alter database recover managed standby database disconnect from session;

5.       Finally, check whether TRAXSP is in sync with TRAXMP.

Manual registering of missing files

Manual registering of missing files

1.      1.)  Connect to the standby database (TRAXSP) on SYDDC1TRX01 Server using sqlplus tool and confirm the right database is connected by executing the below SQL command.

SQL> select instance_name,status from v$instance;

2.       2.) Check the missing files in TRAXSP.

a.       To check the number of sequences missing along with the higher and lower sequence:

SQL> select * from v$archive_gap;

b.       To check the missing file sequence number:

SQL> select sequence#,applied,status from v$archived_log where applied='NO';

3. )Using the sequence number, copy the missing file from (/arch/oraarchive/traxp/) MELDC1TRX01 Server to the appropriate location (/arch/oraarchive/traxsp/) in SYDDC1TRX01 Server.

It can be copied by using scp command.

·         Connect to SYDDC1TRX01 server and fire the below command from OS prompt.

scp oracle@meldc1trx01:/arch/oraarchive/traxp/<missing sequence name> /arch/oraarchive/traxsp/

OR

·         Connect to meldc1trx01 server and fire the below command from OS prompt.

scp /arch/oraarchive/traxp/<missing sequence name>  oracle@meldc1trx02:/arch/oraarchive/traxsp/

                                                                                                                                                                            

Provide password when asked.

4. )On the Standby database, type the below command to register the missing files manually.

SQL> alter database register logfile ‘/arch/oraarchive/traxsp/<missing sequence name>;

For example:

SQL> alter database register logfile ‘/arch/oraarchive/traxsp/TRAXP_ARC1235911_694072247.1';

Cannot extend datafile

ORA-01237: cannot extend datafile 329

MRP0: Background Media Recovery terminated with error 1237

Errors in file /PRODDG/ora01/oracle/product/11.2.0/log/diag/rdbms/proddg/PRODDG/trace/PRODDG_pr00_51445938.trc:

ORA-01237: cannot extend datafile 329

ORA-01110: data file 329: '/PRODDG/data04/APPS_TS_TX_IDX_1602241600.dbf'

ORA-27072: File I/O error

Additional information: 2

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string      /PROD/data01/, /PRODDG/data01/

                                                 , /PROD/data02/, /PRODDG/data0

                                                 2/, /PROD/data03/, /PRODDG/dat

                                                 a03/, /PROD/data04/, /PRODDG/d

                                                 ata04/

  Looks like the redo logs are in consuming space in data04.  We need to relocate those to /PRODDG/arch01.  It is sized larger that the production arch01.  So, you need to change standby file management to manual, drop the redo log groups, and re-create them in /PRODDG/arch01.  Then you will have to reset standby file management back to auto.  Please work up steps and send to us for review before implementing. 

We also need to modify the log name file convert parameter in case the Dataguard gets rebuilt in the future.

Corrupted archive redo logs on dataguard

ORA-00332: archived log is too small - may be incompletely archived 

Recently we got find corruption in one of the archives which caused the DG failure.

732001557_1_480267.arc is corrupted

on primary

SQL> select BLOCKS, BLOCK_SIZE from v$archived_log where SEQUENCE# = 480267;

    BLOCKS BLOCK_SIZE

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

   2482577        512

SQL> select BLOCKS, BLOCK_SIZE from v$archived_log where SEQUENCE# = 480268;

    BLOCKS BLOCK_SIZE

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

   1434007        512

On standby

[HPSM]-->dbfsize 732001557_1_480267.arc

732001557_1_480267.arc: Header block file size is bad;            trying raw file format...

732001557_1_480267.arc: Header block magic number is bad

[oracle@amxd:/HPSM/arch01]

[HPSM]-->dbfsize 732001557_1_480268.arc

Database file: 732001557_1_480268.arc

Database file type: file system

Database file size: 1434007 512 byte blocks

[oracle@amxd:/HPSM/arch01]

Excerpt from alert log

Errors in file /HPSM/ora01/10.2.0/admin/HPSM/udump/HPSM_ora_11666286.trc:

ORA-00332: archived log is too small - may be incompletely archived

ORA-00334: archived log: '/HPSM/arch01/732001557_1_480267.arc'

Errors with log /HPSM/arch01/732001557_1_480267.arc

ORA-332 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...

Tue Apr 12 01:05:30 2016

Looks like archive log is not copied properly from production to standby and file is not available in production server

Please help in restoring the archive log 732001557_1_480267.arc from backup.

732001557_1_482215.arc

732001557_1_482216.arc

RMAN> run {

2> allocate channel C1 device type SBT_TAPE  PARMS='BLKSIZE=1048576, SBT_LIBRARY=/GPRD/ora01/10.2.0/lib/libddobk.so, ENV=(STORAGE_UNIT=RMAN,BACKUP_HOST=amxp10ddom01.kci.com,ORACLE_HOME=/GPRD/ora01/10.2.0)';

3>  restore archivelog logseq 482215 ;

4> release channel t1;

5> }

ORA-1237

MOS doc suggest to make more space on data file FS.

We need to request for more space on both the servers (Primary and Standby) as datafile FS have very less free space

UNNAMED File Error in standby after adding new file to primary

ORA-01111: name for data file 29 is unknown - rename to correct file

There was an issue faced on FNPRDDR (dataguard environment) due standby_file_management=MANUAL and standby was unable to create new datafile for SYSAUX tablespace and MRP process was crashing , Dataguard was out of sync.

MRP0: Background Media Recovery terminated with error 1111

Wed Jan 24 07:55:38 2018

Errors in file /u01/app/oracle/diag/rdbms/fnprddr/FNPRDDR1/trace/FNPRDDR1_pr00_13067.trc:

ORA-01111: name for data file 29 is unknown - rename to correct file

ORA-01110: data file 29: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00029'

ORA-01157: cannot identify/lock data file 29 - see DBWR trace file

ORA-01111: name for data file 29 is unknown - rename to correct file

ORA-01110: data file 29: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00029'

Inorder to fix this issue below steps was followed

1.    Took  backup of datafile from production FNPRD.

copy datafile '+DATA/FNPRD/DATAFILE/sysaux.1051.966234907'  to '/stage/operation/backup_file.dbf'

2.    Catalog the datafile on the standby database

Catalog datafilecopy '/stage/backup_file.dbf' ;

3.    Moved the datafile to correct location in  ASM

copy datafilecopy '/stage/backup_file.dbf'  to '+DATA';

4.    Rename the datafile to correct location

Alter database rename file '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00029' to '+DATA/FNPRDDR/DATAFILE/sysaux.1329.966238471' ;

5.    Start the MRP process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 16 DISCONNECT FROM SESSION;

Currently FNPRDDR is in sync with Production

   THREAD#   RECEIVED Last Standby Seq Applied        Gap

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

         1       2018                     2016                                        2

         2       2019                     2019                                          0

Change the standby file standby_file_management from Manual to AUTO

FNPRDDR

NAME                                 TYPE        VALUE

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

standby_file_management              string      AUTO

select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS

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

FNPRDDR1         MOUNTED

FNPRDDR2         MOUNTED