Procedure to take a cold backup of snapshot standby database in oracle

Procedure to take a cold backup of snapshot standby database in oracle

 

Caution: Ensure you have TDE taken care.

 Step 1) Verify if the db is in snapshot standby mode

 

set lines 300
set pages 3000
select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
select process,status,client_process,sequence# from gv$managed_standby;

Actual output:

SQL> select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 NAME      SWITCHOVER_STATUS
--------- -------------------- ---------------- ------------------------------ --------- --------------------
GGSRC04T  MOUNTED              SNAPSHOT STANDBY GGSRC04TSB1                    GGSRC04T  NOT ALLOWED

SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
ARCH      CONNECTED    ARCH              0
DGRD      ALLOCATED    N/A               0
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0

6 rows selected.
 

 

Step 2) Verify the db is in intended state (DV - OFF, TDE - ON)

 

select parameter,value from v$option where upper(parameter) like '%VAULT%';
select * from v$encryption_wallet;
 
Actual output:

SQL> select parameter,value from v$option where upper(parameter) like '%VAULT%';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle Database Vault                                            FALSE

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
------------------------------ -------------------- --------- --------- ----------
FILE
/u01/app/oracle/admin/GGSRC04TSB1/wallet/
OPEN                           AUTOLOGIN            SINGLE    NO                 0

 

Step 3) Ensure the db is in mount state

 

Actual output:

Already verified

Step 4) Perform the rman cold backup

 

mkdir -p /oraarch/GGSRC04TSB1/SnapshotBackup/

ls -ld /oraarch/GGSRC04TSB1/SnapshotBackup/

 

run

{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

allocate channel c3 device type disk;

allocate channel c4 device type disk;

backup as compressed backupset database format '/oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_%d_%U';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

 

Actual Output:

 

[oracle@vcentos79-oracle-ggsrc wallet]$ rman target /
 
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Sep 4 11:46:27 2023
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: GGSRC04T (DBID=4198404018, not open)
 
RMAN> run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
2> 3> 4> 5> 6> 7> backup as compressed backupset database format '/oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_%d_%U';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}8> 9> 10> 11> 12>
 
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=44 device type=DISK
 
allocated channel: c2
channel c2: SID=46 device type=DISK
 
allocated channel: c3
channel c3: SID=43 device type=DISK
 
allocated channel: c4
channel c4: SID=37 device type=DISK
 
Starting backup at 04-SEP-23
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/GGSRC04TSB1/system01.dbf
input datafile file number=00005 name=/oradata/GGSRC04TSB1/encrypt_ts1_01.dbf
channel c1: starting piece 1 at 04-SEP-23
channel c2: starting compressed full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/oradata/GGSRC04TSB1/sysaux01.dbf
input datafile file number=00004 name=/oradata/GGSRC04TSB1/users01.dbf
channel c2: starting piece 1 at 04-SEP-23
channel c3: starting compressed full datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00003 name=/oradata/GGSRC04TSB1/undotbs01.dbf
input datafile file number=00006 name=/oradata/GGSRC04TSB1/gg_data_01.dbf
channel c3: starting piece 1 at 04-SEP-23
channel c3: finished piece 1 at 04-SEP-23
piece handle=/oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3m25h60r_1_1 tag=TAG20230904T114634 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:03
channel c1: finished piece 1 at 04-SEP-23
piece handle=/oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3k25h60r_1_1 tag=TAG20230904T114634 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:25
channel c2: finished piece 1 at 04-SEP-23
piece handle=/oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3l25h60r_1_1 tag=TAG20230904T114634 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:25
Finished backup at 04-SEP-23
 
Starting Control File and SPFILE Autobackup at 04-SEP-23
piece handle=/oraarch/GGSRC04TSB1/autobackup/2023_09_04/o1_mf_s_1146655442_lhcff4yp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 04-SEP-23
 
released channel: c1
 
released channel: c2
 
released channel: c3
 
released channel: c4
 
RMAN> exit
 
 
Recovery Manager complete.

[oracle@vcentos79-oracle-ggsrc wallet]$ ls -altr /oraarch/GGSRC04TSB1/*/
/oraarch/GGSRC04TSB1/archivelog/:
total 0
drwxr-x---. 2 oracle oinstall 134 Sep  3 21:29 2023_09_03
drwxr-x---. 2 oracle oinstall   6 Sep  4 08:00 2023_09_04
drwxr-x---. 4 oracle oinstall  42 Sep  4 08:00 .
drwxr-x---. 6 oracle oinstall  81 Sep  4 11:39 ..
 
/oraarch/GGSRC04TSB1/flashback/:
total 409616
drwxr-x---. 2 oracle oinstall        60 Sep  4 11:05 .
-rw-r-----. 1 oracle oinstall 209723392 Sep  4 11:05 o1_mf_lhcc019o_.flb
-rw-r-----. 1 oracle oinstall 209723392 Sep  4 11:26 o1_mf_lhcbzy0r_.flb
drwxr-x---. 6 oracle oinstall        81 Sep  4 11:39 ..
 
/oraarch/GGSRC04TSB1/autobackup/:
total 0
drwxr-x---. 3 oracle oinstall 24 Sep  4 11:14 .
drwxr-x---. 6 oracle oinstall 81 Sep  4 11:39 ..
drwxr-x---. 2 oracle oinstall 86 Sep  4 11:47 2023_09_04
 
/oraarch/GGSRC04TSB1/SnapshotBackup/:
total 186392
drwxr-x---. 6 oracle oinstall       81 Sep  4 11:39 ..
-rw-r-----. 1 oracle oinstall  4341760 Sep  4 11:39 rman_bkp_GGSRC04T_3j25h5j3_1_1
-rw-r-----. 1 oracle oinstall 60055552 Sep  4 11:39 rman_bkp_GGSRC04T_3i25h5j2_1_1
drwxr-xr-x. 2 oracle oinstall      196 Sep  4 11:46 .
-rw-r-----. 1 oracle oinstall  4341760 Sep  4 11:46 rman_bkp_GGSRC04T_3m25h60r_1_1
-rw-r-----. 1 oracle oinstall 62070784 Sep  4 11:46 rman_bkp_GGSRC04T_3k25h60r_1_1
-rw-r-----. 1 oracle oinstall 60055552 Sep  4 11:46 rman_bkp_GGSRC04T_3l25h60r_1_1
[oracle@vcentos79-oracle-ggsrc wallet]$

 

YouTube Video:

 

Thanks

Objective: Lost controlfile backup - will resetlogs with latest controlfile with no new df work?

version: 12.1
step 1) setup a test table

SQL> create table t1 as select * from user_objects;

Table created.

SQL> select count(1) from t1;

  COUNT(1)
----------
        16


step 2) Perform a cold backup of the db in compressed form
shutdown immediate;
startup mount;

run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
backup as compressed backupset database format '/oradata_1/backup/%d/db_bkp_%d_%U';
backup current controlfile format '/oradata_1/backup/%d/cf_bkp_%d_%U';
}

point in time info:

Recovery Manager: Release 12.1.0.1.0 - Production on Sun Feb 3 19:25:31 2019


step 3) note down the checkpoint change and controlfile time.

SQL> select checkpoint_change#,controlfile_change#,controlfile_time from v$database;

   CHECKPOINT_CHANGE#   CONTROLFILE_CHANGE# CONTROLFILE_TIME
--------------------- --------------------- -------------------
              5332853               5332833 03/02/2019 19:22:33

SQL> select distinct(checkpoint_change#) from v$datafile;

   CHECKPOINT_CHANGE#
---------------------
               668608
              5332833
              5332853

SQL> select con_id,checkpoint_change#,count(1) from v$datafile group by con_id,checkpoint_change#;

               CON_ID    CHECKPOINT_CHANGE#              COUNT(1)
--------------------- --------------------- ---------------------
                    1               5332853                     4
                    2                668608                     2
                    4               5332833                    17

SQL>

step 4) open the db and add some records to the test table

alter database open;
select count(1) from t1;
insert into t1
select * from t1;
commit;

<< here we verified we had 32 records.

step 5) checkpoint the db and note down the checkpoint change in v$database and controlfile_change,controlfile_time

SQL> select checkpoint_change#,controlfile_change#,controlfile_time from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_TIME
------------------ ------------------- -------------------
           5333481             5333482 03/02/2019 19:44:25

SQL> select con_id,checkpoint_change#,count(1) from v$datafile group by con_id,checkpoint_change# order by con_id;

            CON_ID CHECKPOINT_CHANGE#           COUNT(1)
------------------ ------------------ ------------------
                 1            5333481                  4
                 2             668608                  2
                 4            5333481                 17



SQL> select checkpoint_change#,controlfile_change#,controlfile_time from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_TIME
------------------ ------------------- -------------------
           5333481             5333559 03/02/2019 19:46:28

SQL> select con_id,checkpoint_change#,count(1) from v$datafile group by con_id,checkpoint_change# order by con_id;

            CON_ID CHECKPOINT_CHANGE#           COUNT(1)
------------------ ------------------ ------------------
                 1            5333481                  4
                 2             668608                  2
                 4            5333481                 17


step 6) backup the controlfile, create pfile from spfile.

channel ORA_DISK_1: finished piece 1 at 03-FEB-19
piece handle=/oradata_1/backup/cf_ORCL_0htovqbk_1_1 tag=TAG20190203T194556 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

SQL> create pfile='/oradata_1/backup/pfileorcl.ora' from spfile;

File created.

step 7) shutdown the database

step 8) modify the pfile with new db_unique_name and set db_create_file_dest,controlfile_path to something else than original

step 9) Rename the original controlfile backup that was created along with the backupset

piece handle=/oradata_1/backup/ORCL/cf_bkp_ORCL_0ftovps1_1_1 tag=TAG20190203T193736 comment=NONE

[oracle@db12c ORCL]$ ls -altr /oradata_1/backup/ORCL/cf_bkp_ORCL_0ftovps1_1_1
ls: cannot access /oradata_1/backup/ORCL/cf_bkp_ORCL_0ftovps1_1_1: No such file or directory
[oracle@db12c ORCL]$

step 10) startup nomount,restore controlfile from latest cf backup (mimic of no original cf backup)

run
{
restore controlfile from '/oradata_1/backup/cf_ORCL_0htovqbk_1_1';
}

step 11) verify the controlfile time, see to be sure the controlfile isnt the backup controlfile associated with our point in time restore.

RMAN> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL      MOUNTED

RMAN>


SQL> select checkpoint_change#,controlfile_change#,controlfile_time from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_TIME
------------------ ------------------- -------------------
           5333481             5333531 03/02/2019 19:45:56

SQL> select con_id,checkpoint_change#,count(1) from v$datafile group by con_id,checkpoint_change# order by con_id;

            CON_ID CHECKPOINT_CHANGE#           COUNT(1)
------------------ ------------------ ------------------
                 1            5333481                  4
                 2             668608                  2
                 4            5333481                 17

the actual controlfile backup points to the below...

   CHECKPOINT_CHANGE#   CONTROLFILE_CHANGE# CONTROLFILE_TIME
--------------------- --------------------- -------------------
              5332853               5332833 03/02/2019 19:22:33

so it ensures we arent using the controlfile backup associated with the backup. Now let us move on to perform a point in time restore.

step 12) perform DB PITR - Point In Time Restore.

run
{
set until time "TO_DATE('02/03/2019 19:25:31', 'MM/DD/YYYY HH24:MI:SS')";
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
set newname for database to new;
restore database;
}

select OPERATION,status,MBYTES_PROCESSED,START_TIME,END_TIME,INPUT_BYTES/1024/1024 in_mb,OUTPUT_BYTES/1024/1024 out_mb
from v$rman_status
where status='RUNNING'
order by start_time;

hint: always validate the syntax before firing the command in rman using "rman checksyntax"

step 13) switch database to copy

RMAN> switch database to copy;
..>done



step 14) verify the vdf.ckpt,vdb.ckpt,vdb.ctnl_ckpt,vdf_header.ckpt

SQL> select checkpoint_change#,controlfile_change#,controlfile_time from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_TIME
------------------ ------------------- -------------------
           5333481             5333531 03/02/2019 19:45:56

SQL> select con_id,checkpoint_change#,count(1) from v$datafile group by con_id,checkpoint_change# order by con_id;

            CON_ID CHECKPOINT_CHANGE#           COUNT(1)
------------------ ------------------ ------------------
                 1            5333481                  4
                 2             668608                  2
                 4            5333481                 17

SQL> select con_id,checkpoint_change#,count(1) from v$datafile_header group by con_id,checkpoint_change# order by con_id;

            CON_ID CHECKPOINT_CHANGE#           COUNT(1)
------------------ ------------------ ------------------
                 1            5332853                  4
                 2             668608                  2
                 4            5332833                 17


select con_id,fuzzy,ERROR,RECOVER,status, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,count(1) from v$datafile_header group by con_id,fuzzy,ERROR,RECOVER,status, CHECKPOINT_CHANGE#,CHECKPOINT_TIME order by con_id;

            CON_ID FUZ ERROR      REC STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME               COUNT(1)
------------------ --- ---------- --- ------- ------------------ ------------------- ------------------
                 1 NO                 ONLINE             5332853 03/02/2019 19:22:35                  4
                 2 NO                 ONLINE              668608 30/08/2013 15:44:52                  2
                 4 NO                 ONLINE             5332833 03/02/2019 19:22:33                 17


so in short, vdf.ckpt != vdf_header.ckpt. So this is expected since controlfile feeds the vdf view.


step 15) rename the redologs and try to do resetlogs.

resetlogs will throw an error saying the system df needs more recovery. So the resetlogs didnt work.

step 16) work around is to recreate the controlfile...

SQL> startup nomount pfile='/oradata_1/backup/pfileRESORCL.ora';
ORACLE instance started.

Total System Global Area 1319473152 bytes
Fixed Size                  2288056 bytes
Variable Size            1006634568 bytes
Database Buffers          301989888 bytes
Redo Buffers                8560640 bytes
SQL> @/oradata_2/orCL/RESORCL/controlresorcl.sql

Control file created.


Database altered.

SQL>


alert log:
...there were complaints abt temp tbspc being empty on cdb,pdb and other container dbs, anyway they will be added later.
2019-02-03 22:29:20.742000 +08:00
Starting background process CJQ0
Completed: ALTER DATABASE OPEN RESETLOGS
CJQ0 started with pid=33, OS id=3238

SQL> select CON_ID,name,open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         4 PDB2                           READ WRITE

SQL> alter session set container=pdb2;

Session altered.


SQL> select count(1) from scott.t1;

  COUNT(1)
----------
        16

SQL> select con_id,owner,table_name from cdb_tables where owner='SCOTT' and table_name='T1';

    CON_ID
----------
OWNER
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
         4
SCOTT
T1

so this confirms the # of records matches the actual controlfile time.

Inference: So a resetlogs with advanced controlfile isnt going to work event without any new dfs. So we need to recreate the controlfile which is the only option.