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.