Dataguard - Restore standby database Using Full rman backup

Dataguard - Restore standby database Using Full  rman backup 

• Create full rman backup on primary database.
• SCP  backup file to standby database 
• start standby database  in nomount stage
• Restore standby control file from rman 
• start db in mount stage
• Catalog rman backup on standby database
• Restore datbabase
• Apply mrp process

• Create full rman backup on primary database.

RMAN> run

{

allocate channel c1 device type disk format '/u01/rmanbkp/Backup_%d_DB_%u_%s_%p_%T';

2> 3> 4> 5> 6>

allocate channel c2 device type disk format '/u01/rmanbkp/Backup_%d_DB_%u_%s_%p_%T';

crosscheck backup;

crosscheck archivelog all;

report obsolete;

delete noprompt obsolete;

sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

#backup full database format '/u01/rmanbkp/%d_%U.bckp' plus archivelog;

copy current controlfile to '/u01/rmanbkp/control_%d_%T.bak';

backup current controlfile for standby format '/u01/rmanbkp/standbycontrol_%d_%U.ctl';

release channel c1;

release channel c2;

}7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31>

[oracle@srv6 rmanbkp]$ ll

total 697904

-rw-r----- 1 oracle oinstall  11801088 Sep 15 17:04 Backup_ORCL12C_DB_0f0951pg_15_1_20210915

-rw-r----- 1 oracle oinstall   6962176 Sep 15 17:04 Backup_ORCL12C_DB_0g0951pg_16_1_20210915

-rw-r----- 1 oracle oinstall    836608 Sep 15 17:04 Backup_ORCL12C_DB_0h0951pn_17_1_20210915

-rw-r----- 1 oracle oinstall 215261184 Sep 15 17:07 Backup_ORCL12C_DB_0i0951po_18_1_20210915

-rw-r----- 1 oracle oinstall 115064832 Sep 15 17:06 Backup_ORCL12C_DB_0j0951po_19_1_20210915

-rw-r----- 1 oracle oinstall 110133248 Sep 15 17:08 Backup_ORCL12C_DB_0k0951t2_20_1_20210915

-rw-r----- 1 oracle oinstall 110051328 Sep 15 17:08 Backup_ORCL12C_DB_0l0951uf_21_1_20210915

-rw-r----- 1 oracle oinstall  54157312 Sep 15 17:08 Backup_ORCL12C_DB_0m0951vt_22_1_20210915

-rw-r----- 1 oracle oinstall  54157312 Sep 15 17:09 Backup_ORCL12C_DB_0n09521a_23_1_20210915

-rw-r----- 1 oracle oinstall    155136 Sep 15 17:09 Backup_ORCL12C_DB_0o095223_24_1_20210915

-rw-r----- 1 oracle oinstall  18006016 Sep 15 17:09 control_ORCL12C_20210915.bak

-rw-r----- 1 oracle oinstall  18055168 Sep 15 17:09 standbycontrol_ORCL12C_0q095226_1_1.ctl

• Transfer rman backup to standby location 

[oracle@srv6 rmanbkp]$ scp * oracle@srv7:/u01/rmanbkp/

oracle@srv7's password:

Backup_ORCL12C_DB_0f0951pg_15_1_20210915                                                                                             100%   11MB  42.9MB/s   00:00

Backup_ORCL12C_DB_0g0951pg_16_1_20210915                                                                                            100% 6799KB  28.7MB/s   00:00

Backup_ORCL12C_DB_0h0951pn_17_1_20210915                                                                                             100%  817KB  26.0MB/s   00:00

Backup_ORCL12C_DB_0i0951po_18_1_20210915                                                                                             100%  205MB  52.3MB/s   00:03

Backup_ORCL12C_DB_0j0951po_19_1_20210915                                                                                             100%  110MB  48.7MB/s   00:02

Backup_ORCL12C_DB_0k0951t2_20_1_20210915                                                                                             100%  105MB  55.2MB/s   00:01

Backup_ORCL12C_DB_0l0951uf_21_1_20210915                                                                                             100%  105MB  56.2MB/s   00:01

Backup_ORCL12C_DB_0m0951vt_22_1_20210915                                                                                             100%   52MB  62.9MB/s   00:00

Backup_ORCL12C_DB_0n09521a_23_1_20210915                                                                                             100%   52MB  53.6MB/s   00:00

Backup_ORCL12C_DB_0o095223_24_1_20210915                                                                                             100%  152KB  20.6MB/s   00:00

control_ORCL12C_20210915.bak                                                                                                         100%   17MB  60.2MB/s   00:00

standbycontrol_ORCL12C_0q095226_1_1.ctl                                                                                              100%   17MB  53.3MB/s   00:00

[oracle@srv6 rmanbkp]$

• Shut down and start standby database  in nomount stage

[oracle@srv7 rmanbkp]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 15 17:28:17 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1862270976 bytes

Fixed Size                  2925648 bytes

Variable Size             687868848 bytes

Database Buffers         1157627904 bytes

Redo Buffers               13848576 bytes

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

• Restore standby control file using rman backup 

[oracle@srv7 rmanbkp]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Sep 15 17:30:06 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL12C (not mounted)

RMAN>  restore standby controlfile from '/u01/rmanbkp/standbycontrol_ORCL12C_0q095226_1_1.ctl'

2> ;

Starting restore at 2021-09-15:17:30:11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/orcl12cdr/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/orcl12cdr/control02.ctl

Finished restore at 2021-09-15:17:30:13

Open standby database in mount stage

RMAN> alter database mount standby database;

Statement processed

released channel: ORA_DISK_1

RMAN> catalog start with '/u01/rmanbkp';

Starting implicit crosscheck backup at 2021-09-15:17:30:44

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=33 device type=DISK

Crosschecked 18 objects

Finished implicit crosscheck backup at 2021-09-15:17:30:45

Starting implicit crosscheck copy at 2021-09-15:17:30:45

using channel ORA_DISK_1

Crosschecked 2 objects

Finished implicit crosscheck copy at 2021-09-15:17:30:45

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_08_31/o1_mf_1_20_jlw664wb_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_08_31/o1_mf_1_22_jlw6652v_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_08_31/o1_mf_1_14_jlvrvoj6_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_08_31/o1_mf_1_21_jlw6651s_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_08_31/o1_mf_1_25_jlw6cjvw_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_08_31/o1_mf_1_24_jlw6chrb_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_08_31/o1_mf_1_23_jlw6650r_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_15/o1_mf_1_26_jn39jqxc_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_15/o1_mf_1_34_jn3pkflm_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_15/o1_mf_1_27_jn39jpz2_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_15/o1_mf_1_31_jn3b7hkv_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_15/o1_mf_1_28_jn39zbn9_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_15/o1_mf_1_37_jn3pvclg_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_15/o1_mf_1_30_jn3b7ggn_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_15/o1_mf_1_32_jn3myjq8_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_15/o1_mf_1_36_jn3plqmh_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_15/o1_mf_1_33_jn3nmy9l_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_15/o1_mf_1_35_jn3plq71_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_09_15/o1_mf_1_29_jn39zcpj_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_08_27/o1_mf_1_12_jlkjmqwy_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_08_27/o1_mf_1_13_jlkjnw8r_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL12CDR/archivelog/2021_08_27/o1_mf_1_11_jlkjm2o3_.arc

searching for all files that match the pattern /u01/rmanbkp

List of Files Unknown to the Database

=====================================

File Name: /u01/rmanbkp/standbycontrol_ORCL12C_0q095226_1_1.ctl

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/rmanbkp/standbycontrol_ORCL12C_0q095226_1_1.ctl

• When full backup restoration on standby database 

RMAN> restore database;

Starting restore at 2021-09-15:17:30:54

using channel ORA_DISK_1

skipping datafile 5; already restored to file /u01/app/oracle/oradata/orcl12cdr/pdbseed/system01.dbf

skipping datafile 7; already restored to file /u01/app/oracle/oradata/orcl12cdr/pdbseed/sysaux01.dbf

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 00003 to /u01/app/oracle/oradata/orcl12cdr/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl12cdr/users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/rmanbkp/Backup_ORCL12C_DB_0j0951po_19_1_20210915

channel ORA_DISK_1: piece handle=/u01/rmanbkp/Backup_ORCL12C_DB_0j0951po_19_1_20210915 tag=TAG20210915T170456

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

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 00001 to /u01/app/oracle/oradata/orcl12cdr/system01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl12cdr/undotbs01.dbf

channel ORA_DISK_1: reading from backup piece /u01/rmanbkp/Backup_ORCL12C_DB_0i0951po_18_1_20210915

channel ORA_DISK_1: piece handle=/u01/rmanbkp/Backup_ORCL12C_DB_0i0951po_18_1_20210915 tag=TAG20210915T170456

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:55

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 00009 to /u01/app/oracle/oradata/orcl12cdr/pdb12c/sysaux01.dbf

channel ORA_DISK_1: reading from backup piece /u01/rmanbkp/Backup_ORCL12C_DB_0k0951t2_20_1_20210915

channel ORA_DISK_1: piece handle=/u01/rmanbkp/Backup_ORCL12C_DB_0k0951t2_20_1_20210915 tag=TAG20210915T170456

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

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 00008 to /u01/app/oracle/oradata/orcl12cdr/pdb12c/system01.dbf

channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/orcl12cdr/pdb12c/pdb12c_users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/rmanbkp/Backup_ORCL12C_DB_0m0951vt_22_1_20210915

channel ORA_DISK_1: piece handle=/u01/rmanbkp/Backup_ORCL12C_DB_0m0951vt_22_1_20210915 tag=TAG20210915T170456

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 2021-09-15:17:33:15

• Apply media recovery process to using command below

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

Statement processed

RMAN> exit

Recovery Manager complete.

[oracle@srv7 rmanbkp]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 15 17:53:34 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

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

ORCL12C   MOUNTED              PHYSICAL STANDBY

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

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

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

RFS       IDLE                 38

RFS       IDLE                  0

MRP0      APPLYING_LOG         38

7 rows selected.

SQL>

script - Mount point details on mail with attachements

script - Mount point details on mail  with attachments

• Mail message Template ;

  
 
oracle@test08:/u02/scripts> cat template.tmp

Space disk details df -h on xxxxxxpl08 server!!!

ip address - 10.10.10.10

 

• Script details 

 
oracle@test08:/u02/scripts> cat mount_point_mail.sh



df -h | awk '{print strftime("date +%d_%m_%y_%H_%M_%S"), $1, $2, $3,$4,$5}' | column -t >> /u02/scripts/mountpoint_output.log



mailx -a mountpoint_output.log -s " TESTING MOUNT POINT test08 " varun.yadav@xxxx.com<template.tmp



exit

oracle@test08:/u02/scripts>



 

OUTPUT: 

DATAGUARD - Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set Failed.

 Scenario Preview: Getting  oracle error ORA-16698 dduring creation of dataguard in 19c release.  

Physical standby already created and in sync.  steps to create datagauard on 19c and troubleshooting error as well. 

Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set Failed.

[oracle@srv6 dgbroker]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 17 20:10:53 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='/u01/dgbroker/pr1ORADB.dat';

System altered.

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='/u01/dgbroker/pr2ORADB.dat';

System altered.

SQL> SHOW PARAMETER DG_BROKER_START

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     FALSE

SQL>  ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';

System altered.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@srv6 dgbroker]$ dgmgrl sys/system123@TEST19C

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Aug 17 20:13:48 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected to "TEST19C"

Connected as SYSDBA.

DGMGRL> show configuration;

ORA-16532: Oracle Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

DGMGRL> CREATE CONFIGURATION TEST19C AS PRIMARY DATABASE IS TEST19C CONNECT IDENTIFIER IS TEST19C;

Configuration "test19c" created with primary database "test19c"

DGMGRL> show configuration;

Configuration - test19c

  Protection Mode: MaxPerformance

  Members:

  test19c - Primary database

Fast-Start Failover:  Disabled

Configuration Status:

DISABLED

DGMGRL> ADD DATABASE TESTDR AS CONNECT IDENTIFIER IS TESTDR;

Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.

• Primary database LOG_ARCHIVE_DEST_2 attribute aleady clear but as we noticed standby attribute was not cleared so we set parameterd to emplty in physical standby database. 

SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';

System altered.

SQL>

DGMGRL> ADD DATABASE TESTDR AS CONNECT IDENTIFIER IS TESTDR;

Database "testdr" added

DGMGRL> show configuration;

Configuration - test19c

  Protection Mode: MaxPerformance

  Members:

  test19c - Primary database

    testdr  - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:

DISABLED

DGMGRL>

DGMGRL> ENABLE CONFIGURATION;

Enabled.

DGMGRL> SHOW CONFIGURATION;

Configuration - test19c

  Protection Mode: MaxPerformance

  Members:

  test19c - Primary database

    testdr  - Physical standby database

      Warning: ORA-16853: apply lag has exceeded specified threshold

Fast-Start Failover:  Disabled

Configuration Status:

WARNING   (status updated 2 seconds ago)

DGMGRL>

DGMGRL> EDIT DATABASE TESTDR SET STATE=APPLY-ON;

Succeeded.

DGMGRL> SHOW CONFIGURATION;

Configuration - test19c

  Protection Mode: MaxPerformance

  Members:

  test19c - Primary database

    testdr  - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:

SUCCESS   (status updated 44 seconds ago)

DGMGRL>