1. Home
  2. Knowledge Base
  3. ZDLRA
  4. Zero Data Loss Recovery Appliance (ZDLRA) Hands-On Lab 5

Zero Data Loss Recovery Appliance (ZDLRA) Hands-On Lab 5

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.
Updated on May 18, 2024

Was this article helpful?

Related Articles

Leave a Comment