Tutorial Objectives
- Recover a database until the exact point of failure with ZERO DATA LOSS!
- Clean up and deinstall the ZDLRA environment
Preparation:
- Check the last archivelog backup and note the sequence #
- Open 3 terminal sessions connecting to the CDB primary database
- Execute scripts ‘Loop1’ and ‘Loop2’ simultaneously in two separate terminal windows
- Identify the PID for SMON and PMON processes
TERMINAL WINDOW #1
for i in {1..10000}
do
echo "Insert Data $i - `date +%d-%m-%Y-%H%M%S`"
sqlplus -s sys/Oracle_4U as sysdba<<EOF
set heading on feedback on;
alter session set container=pdb1;
insert into test_Ins(c1, c2, c3) values ($i, sysdate, 'Loop1');
commit;
EOF
done;
TERMINAL WINDOW #2
for i in {1..100000}
do
echo "Insert Data $i - `date +%d-%m-%Y-%H%M%S`"
sqlplus -s sys/Oracle_4U as sysdba<<EOF
set heading on feedback on;
alter session set container=pdb1;
insert into test_Ins(c1, c2, c3) values ($i, sysdate, 'Loop2');
commit;
EOF
done;
Simulate a database failure by killing SMON and PMON processes
TERMINAL WINDOW #3
[oracle@prdb01 ~]$ ps -ef |grep cdb2|grep pmon
oracle 30529 1 0 Sep08 ? 00:00:49 ora_pmon_cdb2
[oracle@prdb01 ~]$ ps -ef |grep cdb2|grep smon
oracle 30634 1 0 Sep08 ? 00:00:08 ora_smon_cdb2
[oracle@prdb01 ~]$ kill -9 30529 30634
[oracle@prdb01 ~]$ ps -ef |grep sql
oracle 19831 1840 0 13:00 pts/1 00:00:00 sqlplus -s as sysdba
oracle 19835 2068 0 13:00 pts/2 00:00:00 sqlplus -s as sysdba
[oracle@prdb01 ~]$ kill -9 1840 2068
Note the last committed transaction in terminal window 1 (1147) and terminal window 2 (870)
TERMINAL WINDOW #1
...
...
Commit complete.
Insert Data 1147 - 15-09-2021-125850
Session altered.
1 row created.
Commit complete.
Insert Data 1148 - 15-09-2021-125850
ERROR:
ORA-00472: PMON process terminated with error
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 19264
TERMINAL WINDOW #2
....
....
Insert Data 870 - 15-09-2021-125850
Session altered.
1 row created.
Commit complete.
Insert Data 871 - 15-09-2021-125850
alter session set container=pdb1
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number:
On Primary and Standby servers rename directory CDB
[oracle@prdb01 ~]$ cd /u01/app/oracle/oradata
[oracle@prdb01 oradata]$ ls
CDB
[oracle@prdb01 oradata]$ mv CDB CDB.old
[oracle@prdb01 oradata]$ ssh oracle@sbdb01
oracle@sbdb01's password:
Last login: Mon Sep 20 16:02:10 2021 from czdr8nseingest01.mycorp.gov.au
[oracle@sbdb01 ~]$ cd /u01/app/oracle/oradata
[oracle@sbdb01 oradata]$ ls
CDB
[oracle@sbdb01 oradata]$ mv CDB CDB.old
Start the instance in NOMOUNT mode
[oracle@prdb01 oradata]$ sqlplus sys as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 15 13:07:55 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 3221223568 bytes
Fixed Size 9144464 bytes
Variable Size 1392508928 bytes
Database Buffers 1795162112 bytes
Redo Buffers 24408064 bytes
SQL>
Restore the control file
[oracle@prdb01 oradata]$ rman target / catalog /@dr_zdlra
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 15 13:08:40 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB2 (not mounted)
connected to recovery catalog database
recovery catalog schema version 21.01.00.00. is newer than RMAN version
RMAN> list backup of controlfile completed after 'sysdate-1';
specification does not match any backup in the repository
List of Backup Sets
===================
...
...
Handle: c-2200216847-20210920-02 Media: Recovery Appliance (SEZDRDB)
Control File Included: Ckp SCN: 3185980 Ckp time: 20-SEP-21
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
211514 Full 20.00M SBT_TAPE 00:00:00 20-SEP-21
BP Key: 211515 Status: AVAILABLE Compressed: NO Tag: TAG20210920T141647
Handle: RA_SBT_c-2200216847-20210920-03 Media:
Control File Included: Ckp SCN: 3203384 Ckp time: 20-SEP-21
run
{
allocate channel sbt1 device type sbt
format '%d_%U'
PARMS="SBT_LIBRARY=/u01/app/oracle/product/19.0.0.0/dbhome_2/lib/libra.so,
ENV=(RA_WALLET='location=file:/u01/app/oracle/product/19.0.0.0/dbhome_2/network/admin/server_wallet
credential_alias=dr_zdlra')";
allocate channel sbt2 device type sbt
format '%d_%U'
PARMS="SBT_LIBRARY=/u01/app/oracle/product/19.0.0.0/dbhome_2/lib/libra.so,
ENV=(RA_WALLET='location=file:/u01/app/oracle/product/19.0.0.0/dbhome_2/network/admin/server_wallet
credential_alias=dr_zdlra')";
restore controlfile from tag='TAG20210920T141647';
}
...
...
Starting restore at 20-SEP-21
channel sbt1: starting datafile backup set restore
channel sbt1: restoring control file
channel sbt1: reading from backup piece RA_SBT_c-2200216847-20210920-03
channel sbt1: piece handle=RA_SBT_c-2200216847-20210920-03 tag=TAG20210920T141647
channel sbt1: restored backup piece 1
channel sbt1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/CDB/control01.ctl
output file name=/u01/app/oracle/oradata/CDB/control02.ctl
Finished restore at 20-SEP-21
released channel: sbt1
released channel: sbt2
Note the last archive log backup which is available and the SCN#
RMAN> alter database mount;
Statement processed
RMAN> list backup of archivelog all completed after 'sysdate-30/1440';
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
215470 3.86M SBT_TAPE 00:00:01 20-SEP-21
BP Key: 215471 Status: AVAILABLE Compressed: YES Tag: TAG20210920T162443
Handle: $RSCN_1920977_RTIM_1083613330_THRD_1_SEQ_46_CTKEY_211422_BACKUP Media:
List of Archived Logs in backup set 215470
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 46 3203673 20-SEP-21 3232308 20-SEP-21
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
215474 8.00M SBT_TAPE 00:00:20 20-SEP-21
BP Key: 215475 Status: AVAILABLE Compressed: YES Tag: TAG20210920T162443
Handle: RA_SBT_CDB_610847228_112728_1p09i5ab_1_2_116823 Media:
List of Archived Logs in backup set 215474
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 46 3203673 20-SEP-21 3232308 20-SEP-21
Restore database and perform point in time recovery
run
{
allocate channel sbt1 device type sbt
format '%d_%U'
PARMS="SBT_LIBRARY=/u01/app/oracle/product/19.0.0.0/dbhome_2/lib/libra.so,
ENV=(RA_WALLET='location=file:/u01/app/oracle/product/19.0.0.0/dbhome_2/network/admin/server_wallet
credential_alias=dr_zdlra')";
allocate channel sbt2 device type sbt
format '%d_%U'
PARMS="SBT_LIBRARY=/u01/app/oracle/product/19.0.0.0/dbhome_2/lib/libra.so,
ENV=(RA_WALLET='location=file:/u01/app/oracle/product/19.0.0.0/dbhome_2/network/admin/server_wallet
credential_alias=dr_zdlra')";
set until scn 3232308;
restore database;
recover database;
}
....
....
....
09_15/o1_mf_1_2427_jn2r2tfc_.arc thread=1 sequence=2427
archived log file name=/u01/app/oracle/fast_recovery_area/CDB2/archivelog/2021_09_15/o1_mf_1_2428_jn2r4pln_.arc thread=1 sequence=2428
archived log file name=/u01/app/oracle/fast_recovery_area/CDB2/archivelog/2021_09_15/o1_mf_1_2429_jn2r6lqp_.arc thread=1 sequence=2429
archived log file name=/u01/app/oracle/fast_recovery_area/CDB2/archivelog/2021_09_15/o1_mf_1_2430_jn2r8gwq_.arc thread=1 sequence=2430
archived log file name=/u01/app/oracle/fast_recovery_area/CDB2/archivelog/2021_09_15/o1_mf_1_2431_jn2rbhgw_.arc thread=1 sequence=2431
archived log file name=/u01/app/oracle/fast_recovery_area/CDB2/archivelog/2021_09_15/o1_mf_1_2432_jn2t6d29_.arc thread=1 sequence=2432
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/CDB2/archivelog/2021_09_15/o1_mf_1_2432_jn2t6d29_.arc RECID=2505 STAMP=1083331820
media recovery complete, elapsed time: 00:04:34
Finished recover at 15-SEP-21
released channel: sbt1
released channel: sbt2
RMAN> alter database open resetlogs;
Statement processed
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Validate recovered data.
Loop1 – last committed transaction 1147 is present
Loop2 – last committed transaction 870 is present
SQL> alter session set container=pdb1;
Session altered.
SQL> select max(c1) from test_Ins where c3='Loop1';
MAX(C1)
----------
1147
SQL> select max(c1) from test_Ins where c3='Loop2';
MAX(C1)
----------
870
CLEAN UP
FLASHBACK STANDBY AND ENABLE BROKER CONFIGURATION
DGMGRL> edit database cdb_sb set state='APPLY-OFF';
Succeeded.
[oracle@prdb01 admin]$ sqlplus sys as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 20 17:55:19 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL> select dbid,current_scn,RESETLOGS_CHANGE#,flashback_on from v$database;
DBID CURRENT_SCN RESETLOGS_CHANGE# FLASHBACK_ON
---------- ----------- ----------------- ------------------
2200216847 3248470 3232309 YES
SQL> FLASHBACK STANDBY DATABASE TO SCN 3232307;
Flashback complete.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 3221223568 bytes
Fixed Size 9144464 bytes
Variable Size 805306368 bytes
Database Buffers 2382364672 bytes
Redo Buffers 24408064 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
DGMGRL> show configuration;
Configuration - cdb_dg
Protection Mode: MaxAvailability
Members:
cdb - Primary database
cdb_sb - Physical standby database
zdlra_se - Recovery appliance
Members Not Receiving Redo:
zdlra_pr - Recovery appliance (alternate of zdlra_se)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 27 seconds ago)
TRUNCATE table SYS.TEST_INS
[oracle@prdb01 ~]$ sqlplus sys as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 20 18:24:32 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL> alter session set container=pdb1;
Session altered.
SQL> truncate table test_Ins;
Table truncated.
PRIMARY ZDLRA SERVER
[oracle@przdlra01 ~]$ sqlplus rasys/change^Me2
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 16 11:05:35 2021
Version 19.9.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
SQL> exec dbms_ra.revoke_db_access(db_unique_name=>'cdb', username=>'VPC_CDB');
PL/SQL procedure successfully completed.
SQL> exec dbms_ra.delete_db(db_unique_name=>'cdb', wait=>TRUE);
PL/SQL procedure successfully completed.
Verify by querying ra_database
SQL> select DB_UNIQUE_NAME, DB_KEY, DBID from ra_database where DB_UNIQUE_NAME='CDB';
no rows selected
SQL> BEGIN
DBMS_RA.DELETE_PROTECTION_POLICY(
protection_policy_name => 'CDB_PROTECTION_POLICY');
END;
/ 2 3 4 5
PL/SQL procedure successfully completed.
As root user
[root@przdlra01 ~]# racli remove vpc_user --user_name=vpc_cdb
Thu Sep 16 12:00:09 2021: Start: Remove vpc user vpc_cdb.
Thu Sep 16 12:00:10 2021: Remove vpc user vpc_cdb successfully.
Thu Sep 16 12:00:10 2021: End: Remove vpc user vpc_cdb.
[root@przdlra01 ~]#
SECONDARY ZDLRA SERVER
[oracle@sezdlra01 ~]$ sqlplus rasys/change^Me2
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 16 11:17:22 2021
Version 19.9.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Thu Sep 16 2021 11:11:33 +10:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
SQL> exec dbms_ra.revoke_db_access(db_unique_name=>'cdb', username=>'VPC_CDB');
PL/SQL procedure successfully completed.
SQL> exec dbms_ra.delete_db(db_unique_name=>'cdb', wait=>TRUE);
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_RA.DELETE_PROTECTION_POLICY(
protection_policy_name => 'CDB_PROTECTION_POLICY');
END;
/ 2 3 4 5
PL/SQL procedure successfully completed.
[root@sezdlra01 ~]# racli remove vpc_user --user_name=vpc_cdb
Thu Sep 16 12:00:18 2021: Start: Remove vpc user vpc_cdb.
Thu Sep 16 12:00:19 2021: Remove vpc user vpc_cdb successfully.
Thu Sep 16 12:00:19 2021: End: Remove vpc user vpc_cdb.
[root@sezdlra01 ~]#
[oracle@prdb01 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Sep 16 12:02:33 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect / as sysdg
Connected to "cdb"
Connected as SYSDG.
DGMGRL> edit database cdb set property RedoRoutes = '(cdb : cdb_sb sync)' ;
Property "redoroutes" updated
DGMGRL> edit database cdb_sb set property RedoRoutes = '(cdb_sb : cdb sync)';
Property "redoroutes" updated
DGMGRL> remove recovery_appliance zdlra_se;
Removed Recovery Appliance "zdlra_se" from the configuration
DGMGRL> remove recovery_appliance zdlra_pr;
Removed Recovery Appliance "zdlra_pr" from the configuration
DGMGRL> show configuration;
Configuration - cdb_dg
Protection Mode: MaxAvailability
Members:
cdb - Primary database
cdb_sb - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 7 seconds ago)
[oracle@prdb01 ~]$ sqlplus sys as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 16 12:04:56 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL> alter system set log_archive_dest_3='' scope=both;
System altered.
SQL> alter system set redo_transport_user='' scope=both;
System altered.
