version : 12.1
[please read previous post with no new df before reading this post to get some knowledge on the setup]
step 1) create a tablespace
SQL> alter session set container=pdb2;
Session altered.
SQL> create tablespace tbstest datafile '.../tbstest1.dbf' size 100k autoextend on next 100k;
Tablespace created.
step 2) create a table in the new tablespace with dummy data such as dba_users.
SQL> alter user scott quota unlimited on tbstest;
User altered.
SQL> create table scott.tbstest1 tablespace tbstest as select * from dba_users;
Table created.
step 3) backup current controlfile
piece handle=/oradata_1/backup/cf_ORCL_0jtp04n5_1_1-02 tag=TAG20190203T224244 comment=NONE
step 4) restart the instance using the pfile used with objective (i), since it was a pfile, it wasnt dropped by oracle - 'drop database command'
step 5) restore the controlfile
[oracle@host backup]$ ls -altr /oradata_1/orCL/cf_resorcl.ctl
ls: cannot access /oradata_1/orCL/cf_resorcl.ctl: No such file or directory
[oracle@host backup]$
step 6) mount the db and validate that the controlfile is intact new
SQL> select checkpoint_change#,controlfile_change#,controlfile_time from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_TIME
------------------ ------------------- -------------------
5336799 5336873 03/02/2019 22:42:45
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 5336799 4
2 668608 2
4 5336799 18
see on con_id =>4 we have +1 df, which is pdb2's tbstest.
the actual controlfile backup points to the below...
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_TIME
--------------------- --------------------- -------------------
5332853 5332833 03/02/2019 19:22:33
so this is different and is the latest controlfile used for restore.
step 7) let us do the restore using the same command as objective (i)...
pre - restore:
[oracle@host RESORCL]$ pwd
/oradata_2/orCL/RESORCL
[oracle@host RESORCL]$ ls -altr
total 20
drwxrwxr-x. 3 oracle oracle 4096 Feb 3 22:03 ..
-rw-rw-r--. 1 oracle oracle 4781 Feb 3 22:24 controlresorcl.trc
-rw-rw-r--. 1 oracle oracle 2053 Feb 3 22:26 controlresorcl.sql
drwxrwx---. 2 oracle oracle 4096 Feb 3 22:34 .
[oracle@host RESORCL]$
RMAN> run
{
2> 3> set until time "TO_DATE('02/03/2019 19:25:31', 'MM/DD/YYYY HH24:MI:SS')";
4> allocate channel c1 device type disk;
5> allocate channel c2 device type disk;
6> allocate channel c3 device type disk;
7> allocate channel c4 device type disk;
set newname for database to new;
8> 9> restore database;
}10>
..
restore is complete
step 8) switch database to copy... will rely on the actual dbf... let us see...
RMAN> switch database to copy;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 02/03/2019 23:17:37
RMAN-06571: datafile 49 does not have recoverable copy
RMAN>
so we need to switch individual files.
set lines 300
set pages 3000
select 'switch datafile '||file#||' to copy;' from v$datafile_copy where status='A' order by file#;
RMAN>
datafile 46 switched to datafile copy "/oradata_2/orCL/RESORCL/datafile/o1_mf_test123_g5g05o6w_.dbf"
RMAN>
datafile 47 switched to datafile copy "/oradata_2/orCL/RESORCL/datafile/o1_mf_test123_g5g05o80_.dbf"
..
SQL> select substr(name,1,15),count(1) from v$datafile group by substr(name,1,15);
SUBSTR(NAME,1,15) COUNT(1)
------------------------------------------------------------ ----------
............... 1
/oradata_2/orCL 23
SQL> select file#,name,status from v$datafile where SUBSTR(NAME,1,15)='.....';
FILE#
----------
NAME
--------------------------------------------------------------------------------
STATUS
-------
49
..../tbstest1.dbf
ONLINE
SQL> select checkpoint_change#,controlfile_change#,controlfile_time from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_TIME
------------------ ------------------- -------------------
5336799 5336873 03/02/2019 22:42:45
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 5336799 4
2 668608 2
4 5336799 18
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
4 5337495 1
the only datafile left as is in source. Now let us proceed to recreate the controlfile after removing the #49 file from controlfile trace.
the actual controlfile or coldbackup ckpt info:
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> select con_id,file#,checkpoint_change#,checkpoint_time from v$datafile_header where file#=49;
CON_ID FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ ------------------ ------------------ -------------------
4 49 5337495 03/02/2019 22:44:51
so the rest of the file are intact except the file#49.
step 9) create controlfile trace for resetlogs and edit it.
hint: Please pay attention to the controlfile edit when you remove the last line in the file list, the comma at the end of the line should be taken care for previous line.
Otherwise you will hit the below error...
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01163: SIZE clause indicates 12800 (blocks), but should match header 13
ORA-01110: data file 47:
'/oradata_2/orCL/RESORCL/datafile/o1_mf_test123_g5g05o80_.dbf'
step 10) create controlfile and perform resetlogs togather
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> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
resorcl STARTED
SQL> @/oradata_2/orCL/RESORCL/cntrl_resorcl_3feb_obj2.sql
Control file created.
Database altered.
Pluggable database altered.
SQL>
...
...
Completed: CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
...
2019-02-04 00:10:43.170000 +08:00
Starting background process CJQ0
Completed: ALTER DATABASE OPEN RESETLOGS
step 11) validate the db
SQL> select count(1) from scott.tbstest1;
select count(1) from scott.tbstest1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select con_id,owner,table_name from cdb_tables where lower(table_name)='tbstest1';
no rows selected
SQL> select tablespace_name from dba_tablespaces where upper(tablespace_name)='TBSTEST';
no rows selected
SQL>
SQL> select count(1) from scott.t1;
COUNT(1)
------------------
16
looks fine.
SQL> select checkpoint_change#,controlfile_change#,controlfile_time from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_TIME
------------------ ------------------- -------------------
5332857 5333345 04/02/2019 00:10:43
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 5332857 4
2 668608 2
4 5333332 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 5332857 4
2 668608 2
4 5333332 17
SQL> select status,count(1) from v$datafile group by status;
STATUS COUNT(1)
------- ------------------
ONLINE 20
SYSTEM 3
so no datafiles in offline or recovery mode,this is specifically to verify file#49 causing any issues.
SQL> select count(1) from v$datafile where file#=49;
COUNT(1)
------------------
0
SQL>
also there is no noname entry or tbstest1.dbf found in the db.
[oracle@host RESORCL]$ grep -i noname control_postreset_resorcl.trc
[oracle@host RESORCL]$ grep -i tbstest1.dbf control_postreset_resorcl.trc
so all set with this test as well.
Inference: This is no different from no new df situation, all we need to do is remove all of those newly added datafiles from copntrolfile trace before recreating the controlfile.
Thanks
[please read previous post with no new df before reading this post to get some knowledge on the setup]
step 1) create a tablespace
SQL> alter session set container=pdb2;
Session altered.
SQL> create tablespace tbstest datafile '.../tbstest1.dbf' size 100k autoextend on next 100k;
Tablespace created.
step 2) create a table in the new tablespace with dummy data such as dba_users.
SQL> alter user scott quota unlimited on tbstest;
User altered.
SQL> create table scott.tbstest1 tablespace tbstest as select * from dba_users;
Table created.
step 3) backup current controlfile
piece handle=/oradata_1/backup/cf_ORCL_0jtp04n5_1_1-02 tag=TAG20190203T224244 comment=NONE
step 4) restart the instance using the pfile used with objective (i), since it was a pfile, it wasnt dropped by oracle - 'drop database command'
step 5) restore the controlfile
[oracle@host backup]$ ls -altr /oradata_1/orCL/cf_resorcl.ctl
ls: cannot access /oradata_1/orCL/cf_resorcl.ctl: No such file or directory
[oracle@host backup]$
step 6) mount the db and validate that the controlfile is intact new
SQL> select checkpoint_change#,controlfile_change#,controlfile_time from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_TIME
------------------ ------------------- -------------------
5336799 5336873 03/02/2019 22:42:45
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 5336799 4
2 668608 2
4 5336799 18
see on con_id =>4 we have +1 df, which is pdb2's tbstest.
the actual controlfile backup points to the below...
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_TIME
--------------------- --------------------- -------------------
5332853 5332833 03/02/2019 19:22:33
so this is different and is the latest controlfile used for restore.
step 7) let us do the restore using the same command as objective (i)...
pre - restore:
[oracle@host RESORCL]$ pwd
/oradata_2/orCL/RESORCL
[oracle@host RESORCL]$ ls -altr
total 20
drwxrwxr-x. 3 oracle oracle 4096 Feb 3 22:03 ..
-rw-rw-r--. 1 oracle oracle 4781 Feb 3 22:24 controlresorcl.trc
-rw-rw-r--. 1 oracle oracle 2053 Feb 3 22:26 controlresorcl.sql
drwxrwx---. 2 oracle oracle 4096 Feb 3 22:34 .
[oracle@host RESORCL]$
RMAN> run
{
2> 3> set until time "TO_DATE('02/03/2019 19:25:31', 'MM/DD/YYYY HH24:MI:SS')";
4> allocate channel c1 device type disk;
5> allocate channel c2 device type disk;
6> allocate channel c3 device type disk;
7> allocate channel c4 device type disk;
set newname for database to new;
8> 9> restore database;
}10>
..
restore is complete
step 8) switch database to copy... will rely on the actual dbf... let us see...
RMAN> switch database to copy;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 02/03/2019 23:17:37
RMAN-06571: datafile 49 does not have recoverable copy
RMAN>
so we need to switch individual files.
set lines 300
set pages 3000
select 'switch datafile '||file#||' to copy;' from v$datafile_copy where status='A' order by file#;
RMAN>
datafile 46 switched to datafile copy "/oradata_2/orCL/RESORCL/datafile/o1_mf_test123_g5g05o6w_.dbf"
RMAN>
datafile 47 switched to datafile copy "/oradata_2/orCL/RESORCL/datafile/o1_mf_test123_g5g05o80_.dbf"
..
SQL> select substr(name,1,15),count(1) from v$datafile group by substr(name,1,15);
SUBSTR(NAME,1,15) COUNT(1)
------------------------------------------------------------ ----------
............... 1
/oradata_2/orCL 23
SQL> select file#,name,status from v$datafile where SUBSTR(NAME,1,15)='.....';
FILE#
----------
NAME
--------------------------------------------------------------------------------
STATUS
-------
49
..../tbstest1.dbf
ONLINE
SQL> select checkpoint_change#,controlfile_change#,controlfile_time from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_TIME
------------------ ------------------- -------------------
5336799 5336873 03/02/2019 22:42:45
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 5336799 4
2 668608 2
4 5336799 18
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
4 5337495 1
the only datafile left as is in source. Now let us proceed to recreate the controlfile after removing the #49 file from controlfile trace.
the actual controlfile or coldbackup ckpt info:
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> select con_id,file#,checkpoint_change#,checkpoint_time from v$datafile_header where file#=49;
CON_ID FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ ------------------ ------------------ -------------------
4 49 5337495 03/02/2019 22:44:51
so the rest of the file are intact except the file#49.
step 9) create controlfile trace for resetlogs and edit it.
hint: Please pay attention to the controlfile edit when you remove the last line in the file list, the comma at the end of the line should be taken care for previous line.
Otherwise you will hit the below error...
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01163: SIZE clause indicates 12800 (blocks), but should match header 13
ORA-01110: data file 47:
'/oradata_2/orCL/RESORCL/datafile/o1_mf_test123_g5g05o80_.dbf'
step 10) create controlfile and perform resetlogs togather
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> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
resorcl STARTED
SQL> @/oradata_2/orCL/RESORCL/cntrl_resorcl_3feb_obj2.sql
Control file created.
Database altered.
Pluggable database altered.
SQL>
...
...
Completed: CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
...
2019-02-04 00:10:43.170000 +08:00
Starting background process CJQ0
Completed: ALTER DATABASE OPEN RESETLOGS
step 11) validate the db
SQL> select count(1) from scott.tbstest1;
select count(1) from scott.tbstest1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select con_id,owner,table_name from cdb_tables where lower(table_name)='tbstest1';
no rows selected
SQL> select tablespace_name from dba_tablespaces where upper(tablespace_name)='TBSTEST';
no rows selected
SQL>
SQL> select count(1) from scott.t1;
COUNT(1)
------------------
16
looks fine.
SQL> select checkpoint_change#,controlfile_change#,controlfile_time from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_TIME
------------------ ------------------- -------------------
5332857 5333345 04/02/2019 00:10:43
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 5332857 4
2 668608 2
4 5333332 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 5332857 4
2 668608 2
4 5333332 17
SQL> select status,count(1) from v$datafile group by status;
STATUS COUNT(1)
------- ------------------
ONLINE 20
SYSTEM 3
so no datafiles in offline or recovery mode,this is specifically to verify file#49 causing any issues.
SQL> select count(1) from v$datafile where file#=49;
COUNT(1)
------------------
0
SQL>
also there is no noname entry or tbstest1.dbf found in the db.
[oracle@host RESORCL]$ grep -i noname control_postreset_resorcl.trc
[oracle@host RESORCL]$ grep -i tbstest1.dbf control_postreset_resorcl.trc
so all set with this test as well.
Inference: This is no different from no new df situation, all we need to do is remove all of those newly added datafiles from copntrolfile trace before recreating the controlfile.
Thanks
No comments:
Post a Comment