本文主要是介绍pdb restore flashback recover 的三个办法 + CDB 级还原 注意数据库实际时间，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
Recover可以drop掉PDB，另外两个不行！！ 除非CDB级还原
千万要注意好数据库时间
RMAN> recover pluggable database pdb until time "to_date('16-JUN-2024 19:00:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '+data1';
Starting recover at 16-JUN-24
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/16/2024 07:04:44
RMAN-05110: Pluggable Database pdb must be closed.
RMAN> alter pluggable database pdb close;
Statement processed
RMAN> recover pluggable database pdb until time "to_date('16-JUN-2024 19:00:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '+data1';
Starting recover at 16-JUN-24
current log archived
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/16/2024 07:05:29
RMAN-06617: UNTIL TIME (16-JUN-24) is ahead of last NEXT TIME in archived logs (16-JUN-24)
RMAN> recover pluggable database pdb until time "to_date('16-JUN-2024 18:57:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '+data1';
Starting recover at 16-JUN-24
current log archived
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/16/2024 07:05:51
RMAN-06617: UNTIL TIME (16-JUN-24) is ahead of last NEXT TIME in archived logs (16-JUN-24)
RMAN> run{
2> set until time "to_date('16-JUN-2024 19:00:00','DD-MON-YYYY HH24:MI:SS')";
3> restore pluggable database pdb ;
4> }
executing command: SET until clause
Starting restore at 16-JUN-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/system.260.1171775211
channel ORA_DISK_1: restoring datafile 00012 to +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/sysaux.261.1171775211
channel ORA_DISK_1: restoring datafile 00013 to +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undotbs1.270.1171775211
channel ORA_DISK_1: restoring datafile 00014 to +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_3.274.1171775211
channel ORA_DISK_1: restoring datafile 00015 to +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_4.266.1171775211
channel ORA_DISK_1: restoring datafile 00016 to +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.301.1171775211
channel ORA_DISK_1: reading from backup piece +DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/BACKUPSET/2024_06_16/nnndf0_fulldbbackup0617_0.324.1171781855
channel ORA_DISK_1: piece handle=+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/BACKUPSET/2024_06_16/nnndf0_fulldbbackup0617_0.324.1171781855 tag=FULLDBBACKUP0617
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 16-JUN-24
RMAN> run{
2> set until time "to_date('16-JUN-2024 19:00:00','DD-MON-YYYY HH24:MI:SS')";
3> recover pluggable database pdb ;
4> }
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'；
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2024-06-16 07:10:39
1. flashback
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'；
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2024-06-16 07:10:39
SQL> flashback pluggable database pdb to timestamp to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS');
Flashback complete.
SQL> alter pluggable database pdb open resetlogs;
--------------------flashback 多次 不可以的---------------------------
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
SQL> alter pluggable database pdb close abort;
Pluggable database altered.
SQL> flashback pluggable database pdb to timestamp to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS');
flashback pluggable database pdb to timestamp to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS')
*
ERROR at line 1:
ORA-39885: flashback/PITR of a pluggable database before its creation is not
allowed
SQL> flashback pluggable database pdb to timestamp to_date('16-JUN-2024 06:55:00','DD-MON-YYYY HH24:MI:SS');
flashback pluggable database pdb to timestamp to_date('16-JUN-2024 06:55:00','DD-MON-YYYY HH24:MI:SS')
*
ERROR at line 1:
ORA-39885: flashback/PITR of a pluggable database before its creation is not
allowed
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB READ WRITE NO
SQL> alter pluggable database pdb close abort;
Pluggable database altered.
SQL> flashback pluggable database pdb to timestamp to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS');
flashback pluggable database pdb to timestamp to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS')
*
ERROR at line 1:
ORA-39885: flashback/PITR of a pluggable database before its creation is not
allowed
2.resotre -----------------------------------------------------
RMAN> alter pluggable database pdb close;
Statement processed
RMAN> run{
2> set until time "to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS')";
3> restore pluggable database pdb ;
4> recover pluggable database pdb ;
5> }
executing command: SET until clause
Starting restore at 16-JUN-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/system.260.1171775211
channel ORA_DISK_1: restoring datafile 00012 to +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/sysaux.261.1171775211
channel ORA_DISK_1: restoring datafile 00013 to +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undotbs1.270.1171775211
channel ORA_DISK_1: restoring datafile 00014 to +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_3.274.1171775211
channel ORA_DISK_1: restoring datafile 00015 to +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_4.266.1171775211
channel ORA_DISK_1: restoring datafile 00016 to +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.301.1171775211
channel ORA_DISK_1: reading from backup piece +DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/BACKUPSET/2024_06_16/nnndf0_fulldbbackup0617_0.324.1171781855
channel ORA_DISK_1: piece handle=+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/BACKUPSET/2024_06_16/nnndf0_fulldbbackup0617_0.324.1171781855 tag=FULLDBBACKUP0617
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 16-JUN-24
Starting recover at 16-JUN-24
using channel ORA_DISK_1
starting media recovery
archived log for thread 3 with sequence 19 is already on disk as file +DATA1/CDB/ARCHIVELOG/2024_06_16/thread_3_seq_19.260.1171781905
archived log for thread 3 with sequence 20 is already on disk as file +DATA1/CDB/ARCHIVELOG/2024_06_16/thread_3_seq_20.332.1171782329
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-JUN-24
RMAN> alter database open resetlogs;
3. recover database---------------------------------------
RMAN> alter pluggable database pdb close abort
2> ;
Statement processed
RMAN> recover pluggable database pdb until time "to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '+data1';
Starting recover at 16-JUN-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-JUN-24
RMAN> drop pluggable database pdb including datafiles;
Statement processed
RMAN> recover pluggable database pdb until time "to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '+data1';
Starting recover at 16-JUN-24
using channel ORA_DISK_1
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Tablespace UNDOTBS2
Tablespace UNDOTBS3
Creating automatic instance, with SID='ytjy'
initialization parameters used for automatic instance:
db_name=CDB
db_unique_name=ytjy_pitr_pdb_CDB
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
db_domain=cj.com
sga_target=2272M
processes=200
db_create_file_dest=+data1
log_archive_dest_1='location=+data1'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance CDB
Oracle instance started
Total System Global Area 2382363816 bytes
Fixed Size 9165992 bytes
Variable Size 520093696 bytes
Database Buffers 1845493760 bytes
Redo Buffers 7610368 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 16-JUN-24
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=184 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +DATA1/CDB/AUTOBACKUP/2024_06_16/s_1171781905.267.1171781907
channel ORA_AUX_DISK_1: piece handle=+DATA1/CDB/AUTOBACKUP/2024_06_16/s_1171781905.267.1171781907 tag=TAG20240616T065825
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA1/CDB/CONTROLFILE/current.303.1171782971
Finished restore at 16-JUN-24
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 7 to new;
set newname for datafile 11 to new;
set newname for datafile 12 to new;
set newname for datafile 13 to new;
set newname for datafile 14 to new;
set newname for datafile 15 to new;
set newname for datafile 16 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 9, 10, 3, 7, 11, 12, 13, 14, 15, 16;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 16-JUN-24
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +data1
channel ORA_AUX_DISK_1: restoring datafile 00004 to +data1
channel ORA_AUX_DISK_1: restoring datafile 00009 to +data1
channel ORA_AUX_DISK_1: restoring datafile 00010 to +data1
channel ORA_AUX_DISK_1: restoring datafile 00003 to +data1
channel ORA_AUX_DISK_1: restoring datafile 00007 to +data1
channel ORA_AUX_DISK_1: reading from backup piece +DATA1/CDB/BACKUPSET/2024_06_16/nnndf0_fulldbbackup0617_0.322.1171781819
channel ORA_AUX_DISK_1: piece handle=+DATA1/CDB/BACKUPSET/2024_06_16/nnndf0_fulldbbackup0617_0.322.1171781819 tag=FULLDBBACKUP0617
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00012 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00013 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00014 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00015 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00016 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece +DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/BACKUPSET/2024_06_16/nnndf0_fulldbbackup0617_0.324.1171781855
channel ORA_AUX_DISK_1: piece handle=+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/BACKUPSET/2024_06_16/nnndf0_fulldbbackup0617_0.324.1171781855 tag=FULLDBBACKUP0617
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 16-JUN-24
datafile 11 switched to datafile copy
input datafile copy RECID=22 STAMP=1171783047 file name=+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/system.284.1171783023
datafile 12 switched to datafile copy
input datafile copy RECID=23 STAMP=1171783047 file name=+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/sysaux.285.1171783023
datafile 13 switched to datafile copy
input datafile copy RECID=24 STAMP=1171783047 file name=+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undotbs1.293.1171783023
datafile 14 switched to datafile copy
input datafile copy RECID=25 STAMP=1171783047 file name=+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_3.286.1171783023
datafile 15 switched to datafile copy
input datafile copy RECID=26 STAMP=1171783047 file name=+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_4.267.1171783023
datafile 16 switched to datafile copy
input datafile copy RECID=27 STAMP=1171783047 file name=+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.263.1171783023
datafile 1 switched to datafile copy
input datafile copy RECID=28 STAMP=1171783047 file name=+DATA1/CDB/DATAFILE/system.304.1171782977
datafile 4 switched to datafile copy
input datafile copy RECID=29 STAMP=1171783047 file name=+DATA1/CDB/DATAFILE/undotbs1.317.1171782977
datafile 9 switched to datafile copy
input datafile copy RECID=30 STAMP=1171783047 file name=+DATA1/CDB/DATAFILE/undotbs2.318.1171782977
datafile 10 switched to datafile copy
input datafile copy RECID=31 STAMP=1171783047 file name=+DATA1/CDB/DATAFILE/undotbs3.325.1171782977
datafile 3 switched to datafile copy
input datafile copy RECID=32 STAMP=1171783047 file name=+DATA1/CDB/DATAFILE/sysaux.257.1171782977
datafile 7 switched to datafile copy
input datafile copy RECID=33 STAMP=1171783047 file name=+DATA1/CDB/DATAFILE/users.270.1171782977
contents of Memory Script:
{
# set requested point in time
set until time "to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 9 online";
sql clone "alter database datafile 10 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 7 online";
sql clone 'PDB' "alter database datafile
11 online";
sql clone 'PDB' "alter database datafile
12 online";
sql clone 'PDB' "alter database datafile
13 online";
sql clone 'PDB' "alter database datafile
14 online";
sql clone 'PDB' "alter database datafile
15 online";
sql clone 'PDB' "alter database datafile
16 online";
#recover pdb
recover clone database tablespace "SYSTEM", "UNDOTBS1", "UNDOTBS2", "UNDOTBS3", "SYSAUX", "USERS" pluggable database
'PDB' delete archivelog;
#open in read write mode
sql clone 'alter database open resetlogs';
#unplug dropped pdb into temp file
sql clone "alter pluggable database PDB unplug into ''
/u01/app/oracle/product/19.0.0/db_1/dbs/_rm_pdb_pitr_2_ytjy.xml''";
#create pdb using temp file of recovered pdb
sql "create pluggable database PDB using ''
/u01/app/oracle/product/19.0.0/db_1/dbs/_rm_pdb_pitr_2_ytjy.xml'' nocopy tempfile reuse";
alter pluggable database PDB open;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 10 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 7 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 12 online
sql statement: alter database datafile 13 online
sql statement: alter database datafile 14 online
sql statement: alter database datafile 15 online
sql statement: alter database datafile 16 online
Starting recover at 16-JUN-24
using channel ORA_AUX_DISK_1
Executing: alter database datafile 5, 6, 8 offline
starting media recovery
archived log for thread 3 with sequence 19 is already on disk as file +DATA1/CDB/ARCHIVELOG/2024_06_16/thread_3_seq_19.260.1171781905
archived log for thread 3 with sequence 20 is already on disk as file +DATA1/CDB/ARCHIVELOG/2024_06_16/thread_3_seq_20.332.1171782329
archived log file name=+DATA1/CDB/ARCHIVELOG/2024_06_16/thread_3_seq_19.260.1171781905 thread=3 sequence=19
archived log file name=+DATA1/CDB/ARCHIVELOG/2024_06_16/thread_3_seq_20.332.1171782329 thread=3 sequence=20
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-JUN-24
sql statement: alter database open resetlogs
sql statement: alter pluggable database PDB unplug into ''/u01/app/oracle/product/19.0.0/db_1/dbs/_rm_pdb_pitr_2_ytjy.xml''
sql statement: create pluggable database PDB using ''/u01/app/oracle/product/19.0.0/db_1/dbs/_rm_pdb_pitr_2_ytjy.xml'' nocopy tempfile reuse
Statement processed
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /u01/app/oracle/product/19.0.0/db_1/dbs/_rm_pdb_pitr_2_ytjy.xml deleted
auxiliary instance file +DATA1/CDB/DATAFILE/sysaux.257.1171782977 deleted
auxiliary instance file +DATA1/CDB/CONTROLFILE/current.303.1171782971 deleted
Finished recover at 16-JUN-24
RMAN>
RMAN>
RMAN>
RMAN>
RMAN>
RMAN>
RMAN>
RMAN> alter pluggable database pdb close abort;
recover pluggable database pdb until time "to_date('16-JUN-2024 06:50:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '+data1';
Statement processed
RMAN>
Starting recover at 16-JUN-24
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/16/2024 07:19:05
RMAN-20513: UNTIL TIME or SCN is before Pluggable Database CREATION SCN
--------------不能recover 更早了-- DROP 后还能回来----------
RMAN> drop pluggable database pdb including datafiles;
Statement processed
RMAN> recover pluggable database pdb until time "to_date('16-JUN-2024 06:10:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '+data1';
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/16/2024 07:54:46
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore
RMAN> recover pluggable database pdb until time "to_date('16-JUN-2024 06:50:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '+data1';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/16/2024 07:55:05
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06101: no channel to restore a backup or copy of the control file
RMAN> recover pluggable database pdb until time "to_date('16-JUN-2024 07:10:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '+data1';
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /u01/app/oracle/product/19.0.0/db_1/dbs/_rm_pdb_pitr_2_zFwq.xml deleted
auxiliary instance file +DATA1/CDB/DATAFILE/sysaux.272.1171785333 deleted
auxiliary instance file +DATA1/CDB/CONTROLFILE/current.316.1171785327 deleted
Finished recover at 16-JUN-24
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL>
RMAN> drop pluggable database pdb including datafiles;
Statement processed
RMAN> recover pluggable database pdb until time "to_date('16-JUN-2024 06:55:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '+data1';
Starting recover at 16-JUN-24
using channel ORA_DISK_1
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Tablespace UNDOTBS2
Tablespace UNDOTBS3
Creating automatic instance, with SID='wAlj'
initialization parameters used for automatic instance:
db_name=CDB
db_unique_name=wAlj_pitr_pdb_CDB
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
db_domain=cj.com
sga_target=2272M
processes=200
db_create_file_dest=+data1
log_archive_dest_1='location=+data1'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance CDB
Oracle instance started
Total System Global Area 2382363816 bytes
Fixed Size 9165992 bytes
Variable Size 520093696 bytes
Database Buffers 1845493760 bytes
Redo Buffers 7610368 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('16-JUN-2024 06:55:00','DD-MON-YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 16-JUN-24
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 device type=DISK
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/16/2024 07:20:20
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06101: no channel to restore a backup or copy of the control file
RMAN> recover pluggable database pdb until time "to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '+data1';
Starting recover at 16-JUN-24
using channel ORA_DISK_1
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Tablespace UNDOTBS2
Tablespace UNDOTBS3
Creating automatic instance, with SID='kxhD'
initialization parameters used for automatic instance:
db_name=CDB
db_unique_name=kxhD_pitr_pdb_CDB
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
db_domain=cj.com
sga_target=2272M
processes=200
db_create_file_dest=+data1
log_archive_dest_1='location=+data1'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance CDB
Oracle instance started
Total System Global Area 2382363816 bytes
Fixed Size 9165992 bytes
Variable Size 520093696 bytes
Database Buffers 1845493760 bytes
Redo Buffers 7610368 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 16-JUN-24
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +DATA1/CDB/AUTOBACKUP/2024_06_16/s_1171781905.267.1171781907
channel ORA_AUX_DISK_1: piece handle=+DATA1/CDB/AUTOBACKUP/2024_06_16/s_1171781905.267.1171781907 tag=TAG20240616T065825
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA1/CDB/CONTROLFILE/current.368.1171783271
Finished restore at 16-JUN-24
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 7 to new;
set newname for datafile 11 to new;
set newname for datafile 12 to new;
set newname for datafile 13 to new;
set newname for datafile 14 to new;
set newname for datafile 15 to new;
set newname for datafile 16 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 9, 10, 3, 7, 11, 12, 13, 14, 15, 16;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 16-JUN-24
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +data1
channel ORA_AUX_DISK_1: restoring datafile 00004 to +data1
channel ORA_AUX_DISK_1: restoring datafile 00009 to +data1
channel ORA_AUX_DISK_1: restoring datafile 00010 to +data1
channel ORA_AUX_DISK_1: restoring datafile 00003 to +data1
channel ORA_AUX_DISK_1: restoring datafile 00007 to +data1
channel ORA_AUX_DISK_1: reading from backup piece +DATA1/CDB/BACKUPSET/2024_06_16/nnndf0_fulldbbackup0617_0.322.1171781819
channel ORA_AUX_DISK_1: piece handle=+DATA1/CDB/BACKUPSET/2024_06_16/nnndf0_fulldbbackup0617_0.322.1171781819 tag=FULLDBBACKUP0617
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00012 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00013 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00014 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00015 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00016 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece +DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/BACKUPSET/2024_06_16/nnndf0_fulldbbackup0617_0.324.1171781855
channel ORA_AUX_DISK_1: piece handle=+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/BACKUPSET/2024_06_16/nnndf0_fulldbbackup0617_0.324.1171781855 tag=FULLDBBACKUP0617
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 16-JUN-24
datafile 11 switched to datafile copy
input datafile copy RECID=22 STAMP=1171783346 file name=+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/system.284.1171783321
datafile 12 switched to datafile copy
input datafile copy RECID=23 STAMP=1171783346 file name=+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/sysaux.285.1171783321
datafile 13 switched to datafile copy
input datafile copy RECID=24 STAMP=1171783346 file name=+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undotbs1.293.1171783321
datafile 14 switched to datafile copy
input datafile copy RECID=25 STAMP=1171783346 file name=+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_3.286.1171783321
datafile 15 switched to datafile copy
input datafile copy RECID=26 STAMP=1171783346 file name=+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_4.267.1171783321
datafile 16 switched to datafile copy
input datafile copy RECID=27 STAMP=1171783346 file name=+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.263.1171783321
datafile 1 switched to datafile copy
input datafile copy RECID=28 STAMP=1171783346 file name=+DATA1/CDB/DATAFILE/system.303.1171783277
datafile 4 switched to datafile copy
input datafile copy RECID=29 STAMP=1171783346 file name=+DATA1/CDB/DATAFILE/undotbs1.347.1171783277
datafile 9 switched to datafile copy
input datafile copy RECID=30 STAMP=1171783346 file name=+DATA1/CDB/DATAFILE/undotbs2.345.1171783277
datafile 10 switched to datafile copy
input datafile copy RECID=31 STAMP=1171783346 file name=+DATA1/CDB/DATAFILE/undotbs3.338.1171783277
datafile 3 switched to datafile copy
input datafile copy RECID=32 STAMP=1171783346 file name=+DATA1/CDB/DATAFILE/sysaux.257.1171783277
datafile 7 switched to datafile copy
input datafile copy RECID=33 STAMP=1171783346 file name=+DATA1/CDB/DATAFILE/users.341.1171783277
contents of Memory Script:
{
# set requested point in time
set until time "to_date('16-JUN-2024 07:00:00','DD-MON-YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 9 online";
sql clone "alter database datafile 10 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 7 online";
sql clone 'PDB' "alter database datafile
11 online";
sql clone 'PDB' "alter database datafile
12 online";
sql clone 'PDB' "alter database datafile
13 online";
sql clone 'PDB' "alter database datafile
14 online";
sql clone 'PDB' "alter database datafile
15 online";
sql clone 'PDB' "alter database datafile
16 online";
#recover pdb
recover clone database tablespace "SYSTEM", "UNDOTBS1", "UNDOTBS2", "UNDOTBS3", "SYSAUX", "USERS" pluggable database
'PDB' delete archivelog;
#open in read write mode
sql clone 'alter database open resetlogs';
#unplug dropped pdb into temp file
sql clone "alter pluggable database PDB unplug into ''
/u01/app/oracle/product/19.0.0/db_1/dbs/_rm_pdb_pitr_3_kxhD.xml''";
#create pdb using temp file of recovered pdb
sql "create pluggable database PDB using ''
/u01/app/oracle/product/19.0.0/db_1/dbs/_rm_pdb_pitr_3_kxhD.xml'' nocopy tempfile reuse";
alter pluggable database PDB open;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 10 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 7 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 12 online
sql statement: alter database datafile 13 online
sql statement: alter database datafile 14 online
sql statement: alter database datafile 15 online
sql statement: alter database datafile 16 online
Starting recover at 16-JUN-24
using channel ORA_AUX_DISK_1
Executing: alter database datafile 5, 6, 8 offline
starting media recovery
archived log for thread 3 with sequence 19 is already on disk as file +DATA1/CDB/ARCHIVELOG/2024_06_16/thread_3_seq_19.260.1171781905
archived log for thread 3 with sequence 20 is already on disk as file +DATA1/CDB/ARCHIVELOG/2024_06_16/thread_3_seq_20.332.1171782329
archived log file name=+DATA1/CDB/ARCHIVELOG/2024_06_16/thread_3_seq_19.260.1171781905 thread=3 sequence=19
archived log file name=+DATA1/CDB/ARCHIVELOG/2024_06_16/thread_3_seq_20.332.1171782329 thread=3 sequence=20
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-JUN-24
sql statement: alter database open resetlogs
sql statement: alter pluggable database PDB unplug into ''/u01/app/oracle/product/19.0.0/db_1/dbs/_rm_pdb_pitr_3_kxhD.xml''
sql statement: create pluggable database PDB using ''/u01/app/oracle/product/19.0.0/db_1/dbs/_rm_pdb_pitr_3_kxhD.xml'' nocopy tempfile reuse
Statement processed
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /u01/app/oracle/product/19.0.0/db_1/dbs/_rm_pdb_pitr_3_kxhD.xml deleted
auxiliary instance file +DATA1/CDB/DATAFILE/sysaux.257.1171783277 deleted
auxiliary instance file +DATA1/CDB/CONTROLFILE/current.368.1171783271 deleted
Finished recover at 16-JUN-24
-----------------------------还想再回去，能不能CDB还原呢------------------------
SQL> startup mount force;
ORACLE instance started.
Total System Global Area 2382361320 bytes
Fixed Size 9167592 bytes
Variable Size 922746880 bytes
Database Buffers 1442840576 bytes
Redo Buffers 7606272 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('06/16/2024 06:50:00', 'mm/dd/yyyy hh24:mi:ss');
Flashback complete.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 PDB MOUNTED
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL> alter pluggable database pdb open;
alter pluggable database pdb open
*
ERROR at line 1:
ORA-65019: pluggable database PDB already open
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE YES
SQL> --------------pdb 如果现在在，还原的点也在，两个中间被drop，也不会丢失！！！！
SQL> alter pluggable database pdb close abort;
Pluggable database altered.
SQL> drop pluggable database pdb including datafiles;
Pluggable database dropped.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL> startup mount force;
ORACLE instance started.
Total System Global Area 2382361320 bytes
Fixed Size 9167592 bytes
Variable Size 922746880 bytes
Database Buffers 1442840576 bytes
Redo Buffers 7606272 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('06/16/2024 06:50:00', 'mm/dd/yyyy hh24:mi:ss');
Flashback complete.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 PDB MOUNTED
SQL> alter database open resetlogs;
Database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
SQL> alter pluggable database pdb open;
alter pluggable database pdb open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 11 is offline
SQL> flashback pluggable database pdb to timestamp to_date('16-JUN-2024 06:00:00','DD-MON-YYYY HH24:MI:SS');
flashback pluggable database pdb to timestamp to_date('16-JUN-2024 06:00:00','DD-MON-YYYY HH24:MI:SS')
*
ERROR at line 1:
ORA-00264: no recovery required
SQL> flashback pluggable database pdb to timestamp to_date('16-JUN-2024 06:50:00','DD-MON-YYYY HH24:MI:SS');
flashback pluggable database pdb to timestamp to_date('16-JUN-2024 06:50:00','DD-MON-YYYY HH24:MI:SS')
*
ERROR at line 1:
ORA-00264: no recovery required
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
[oracle@rac3 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jun 16 10:25:26 2024
Version 19.20.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB (DBID=2265125454)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1170 SYSTEM YES +DATA1/CDB/DATAFILE/system.258.1171793673
3 910 SYSAUX NO +DATA1/CDB/DATAFILE/sysaux.313.1171793673
4 615 UNDOTBS1 YES +DATA1/CDB/DATAFILE/undotbs1.314.1171793673
5 510 PDB$SEED:SYSTEM NO +DATA1/CDB/0633F844101D69CBE0636401A8C09D55/DATAFILE/system.385.1171793743
6 480 PDB$SEED:SYSAUX NO +DATA1/CDB/0633F844101D69CBE0636401A8C09D55/DATAFILE/sysaux.386.1171793743
7 5 USERS NO +DATA1/CDB/DATAFILE/users.378.1171793673
8 215 PDB$SEED:UNDOTBS1 NO +DATA1/CDB/0633F844101D69CBE0636401A8C09D55/DATAFILE/undotbs1.387.1171793743
9 250 UNDOTBS2 YES +DATA1/CDB/DATAFILE/undotbs2.315.1171793673
10 75 UNDOTBS3 YES +DATA1/CDB/DATAFILE/undotbs3.377.1171793673
11 0 PDB:SYSTEM NO /u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00011
12 0 PDB:SYSAUX NO /u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00012
13 0 PDB:UNDOTBS1 NO /u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00013
14 0 PDB:UNDO_3 NO /u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014
15 0 PDB:UNDO_4 NO /u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00015
16 0 PDB:USERS NO /u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00016
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 24 TEMP 32767 +DATA/CDB/TEMPFILE/temp.347.1171775247
2 36 PDB$SEED:TEMP 32767 +DATA/CDB/0633F844101D69CBE0636401A8C09D55/TEMPFILE/temp.279.1171775251
RMAN> restore pluggable database pdb ;
Starting restore at 16-JUN-24
recover pluggable database pdb ;
}allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 instance=cdb3 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/16/2024 10:26:23
RMAN-06026: some targets not found - aborting restore
RMAN-06100: no channel to restore a backup or copy of datafile 16
RMAN-06100: no channel to restore a backup or copy of datafile 15
RMAN-06100: no channel to restore a backup or copy of datafile 14
RMAN-06100: no channel to restore a backup or copy of datafile 13
RMAN-06100: no channel to restore a backup or copy of datafile 12
RMAN-06100: no channel to restore a backup or copy of datafile 11
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
120 B A A SBT_TAPE 2024/06/16 06:16:22 1 1 YES FULLDBBACKUP
121 B F A SBT_TAPE 2024/06/16 06:16:55 1 1 YES FULLDBBACKUP
122 B F A SBT_TAPE 2024/06/16 06:17:21 1 1 YES FULLDBBACKUP
123 B F A SBT_TAPE 2024/06/16 06:17:45 1 1 YES FULLDBBACKUP
124 B A A SBT_TAPE 2024/06/16 06:17:48 1 1 YES FULLDBBACKUP
125 B F A SBT_TAPE 2024/06/16 06:17:49 1 1 NO TAG20240616T061749
126 B A A SBT_TAPE 2024/06/16 06:19:26 1 1 NO FULLDBBACKUP
127 B F A SBT_TAPE 2024/06/16 06:19:32 1 1 NO FULLDBBACKUP
128 B F A SBT_TAPE 2024/06/16 06:19:36 1 1 NO FULLDBBACKUP
129 B F A SBT_TAPE 2024/06/16 06:19:39 1 1 NO FULLDBBACKUP
130 B A A SBT_TAPE 2024/06/16 06:19:41 1 1 NO FULLDBBACKUP
131 B F A SBT_TAPE 2024/06/16 06:19:42 1 1 NO TAG20240616T061942
132 B A A DISK 2024/06/16 06:56:59 1 1 YES FULLDBBACKUP0617
133 B F A DISK 2024/06/16 06:57:29 1 1 YES FULLDBBACKUP0617
134 B F A DISK 2024/06/16 06:57:53 1 1 YES FULLDBBACKUP0617
135 B F A DISK 2024/06/16 06:58:21 1 1 YES FULLDBBACKUP0617
136 B A A DISK 2024/06/16 06:58:24 1 1 YES FULLDBBACKUP0617
137 B F A DISK 2024/06/16 06:58:26 1 1 NO TAG20240616T065825
138 B F A DISK 2024/06/16 07:11:33 1 1 NO TAG20240616T071133
139 B F A DISK 2024/06/16 07:13:46 1 1 NO TAG20240616T071346
140 B F A DISK 2024/06/16 07:37:27 1 1 NO TAG20240616T073727
141 B F A DISK 2024/06/16 08:02:40 1 1 NO TAG20240616T080240
6点50前的都在tape呢
RMAN> run{
2> allocate channel ch00 device type 'SBT_TAPE'
3> PARMS="SBT_LIBRARY=oracle.disksbt,
4> ENV=(BACKUP_DIR=/nfs)";
5> set until time "to_date('16-JUN-2024 06:50:00','DD-MON-YYYY HH24:MI:SS')";
6> set newname for pluggable database pdb to '+data1';
7> restore pluggable database pdb ;
8> recover pluggable database pdb ;
9> }
released channel: ORA_DISK_1
allocated channel: ch00
channel ch00: SID=62 instance=cdb3 device type=SBT_TAPE
channel ch00: WARNING: Oracle Test Disk API
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 16-JUN-24
channel ch00: starting datafile backup set restore
channel ch00: specifying datafile(s) to restore from backup set
channel ch00: restoring datafile 00011 to +data1
channel ch00: restoring datafile 00012 to +data1
channel ch00: restoring datafile 00013 to +data1
channel ch00: restoring datafile 00014 to +data1
channel ch00: restoring datafile 00015 to +data1
channel ch00: restoring datafile 00016 to +data1
channel ch00: reading from backup piece 542tfrvm_164_1_1
channel ch00: piece handle=542tfrvm_164_1_1 tag=FULLDBBACKUP
channel ch00: restored backup piece 1
channel ch00: restore complete, elapsed time: 00:00:03
Finished restore at 16-JUN-24
Starting recover at 16-JUN-24
current log archived
starting media recovery
media recovery failed
released channel: ch00
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/16/2024 10:28:04
ORA-38770: FLASHBACK DATABASE failed during recovery.
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 11: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00011'
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00011'
RMAN> report schema;
Report of database schema for database with db_unique_name CDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1170 SYSTEM YES +DATA1/CDB/DATAFILE/system.258.1171793673
3 910 SYSAUX NO +DATA1/CDB/DATAFILE/sysaux.313.1171793673
4 615 UNDOTBS1 YES +DATA1/CDB/DATAFILE/undotbs1.314.1171793673
5 510 PDB$SEED:SYSTEM NO +DATA1/CDB/0633F844101D69CBE0636401A8C09D55/DATAFILE/system.385.1171793743
6 480 PDB$SEED:SYSAUX NO +DATA1/CDB/0633F844101D69CBE0636401A8C09D55/DATAFILE/sysaux.386.1171793743
7 5 USERS NO +DATA1/CDB/DATAFILE/users.378.1171793673
8 215 PDB$SEED:UNDOTBS1 NO +DATA1/CDB/0633F844101D69CBE0636401A8C09D55/DATAFILE/undotbs1.387.1171793743
9 250 UNDOTBS2 YES +DATA1/CDB/DATAFILE/undotbs2.315.1171793673
10 75 UNDOTBS3 YES +DATA1/CDB/DATAFILE/undotbs3.377.1171793673
11 0 PDB:SYSTEM NO /u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00011
12 0 PDB:SYSAUX NO /u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00012
13 0 PDB:UNDOTBS1 NO /u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00013
14 0 PDB:UNDO_3 NO /u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014
15 0 PDB:UNDO_4 NO /u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00015
16 0 PDB:USERS NO /u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00016
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 24 TEMP 32767 +DATA/CDB/TEMPFILE/temp.347.1171775247
2 36 PDB$SEED:TEMP 32767 +DATA/CDB/0633F844101D69CBE0636401A8C09D55/TEMPFILE/temp.279.1171775251
RMAN> switch datafile 11 to copy;
datafile 11 switched to datafile copy "+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/system.381.1171794481"
RMAN> switch datafile 12 to copy;
datafile 12 switched to datafile copy "+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/sysaux.382.1171794481"
RMAN> switch datafile 13 to copy;
datafile 13 switched to datafile copy "+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undotbs1.383.1171794481"
RMAN> switch datafile 14 to copy;
datafile 14 switched to datafile copy "+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_3.384.1171794481"
RMAN> switch datafile 15 to copy;
datafile 15 switched to datafile copy "+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_4.312.1171794481"
RMAN> switch datafile 16 to copy;
datafile 16 switched to datafile copy "+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.389.1171794481"
RMAN> run{
2> allocate channel ch00 device type 'SBT_TAPE'
3> PARMS="SBT_LIBRARY=oracle.disksbt,
4> ENV=(BACKUP_DIR=/nfs)";
5> set until time "to_date('16-JUN-2024 06:50:00','DD-MON-YYYY HH24:MI:SS')";
6> set newname for pluggable database pdb to '+data1';
7> recover pluggable database pdb ;
8> }
released channel: ORA_DISK_1
allocated channel: ch00
channel ch00: SID=62 instance=cdb3 device type=SBT_TAPE
channel ch00: WARNING: Oracle Test Disk API
executing command: SET until clause
executing command: SET NEWNAME
Starting recover at 16-JUN-24
starting media recovery
archived log for thread 3 with sequence 17 is already on disk as file +DATA1/CDB/ARCHIVELOG/2024_06_16/thread_3_seq_17.354.1171779581
archived log for thread 3 with sequence 18 is already on disk as file +DATA1/CDB/ARCHIVELOG/2024_06_16/thread_3_seq_18.346.1171781817
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-JUN-24
released channel: ch00
RMAN> alter pluggable database pdb open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/16/2024 10:32:43
ORA-01245: offline file 16 will be lost if RESETLOGS is done
ORA-01110: data file 16: '+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.389.1171794481'
RMAN> report schema;
Report of database schema for database with db_unique_name CDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1170 SYSTEM YES +DATA1/CDB/DATAFILE/system.258.1171793673
3 910 SYSAUX NO +DATA1/CDB/DATAFILE/sysaux.313.1171793673
4 615 UNDOTBS1 YES +DATA1/CDB/DATAFILE/undotbs1.314.1171793673
5 510 PDB$SEED:SYSTEM NO +DATA1/CDB/0633F844101D69CBE0636401A8C09D55/DATAFILE/system.385.1171793743
6 480 PDB$SEED:SYSAUX NO +DATA1/CDB/0633F844101D69CBE0636401A8C09D55/DATAFILE/sysaux.386.1171793743
7 5 USERS NO +DATA1/CDB/DATAFILE/users.378.1171793673
8 215 PDB$SEED:UNDOTBS1 NO +DATA1/CDB/0633F844101D69CBE0636401A8C09D55/DATAFILE/undotbs1.387.1171793743
9 250 UNDOTBS2 YES +DATA1/CDB/DATAFILE/undotbs2.315.1171793673
10 75 UNDOTBS3 YES +DATA1/CDB/DATAFILE/undotbs3.377.1171793673
11 510 PDB:SYSTEM NO +DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/system.381.1171794481
12 490 PDB:SYSAUX NO +DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/sysaux.382.1171794481
13 215 PDB:UNDOTBS1 NO +DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undotbs1.383.1171794481
14 215 PDB:UNDO_3 NO +DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_3.384.1171794481
15 215 PDB:UNDO_4 NO +DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_4.312.1171794481
16 5 PDB:USERS NO +DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.389.1171794481
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 24 TEMP 32767 +DATA/CDB/TEMPFILE/temp.347.1171775247
2 36 PDB$SEED:TEMP 32767 +DATA/CDB/0633F844101D69CBE0636401A8C09D55/TEMPFILE/temp.279.1171775251
RMAN> exit
Recovery Manager complete.
[oracle@rac3 ~]$ s
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jun 16 10:33:51 2024
Version 19.20.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
SQL> alter session set container=pdb;
SQL> alter database datafile 11 online;
Database altered.
SQL> c/11/12
1* alter database datafile 12 online
SQL> /
Database altered.
SQL> c/12/13
1* alter database datafile 13 online
SQL> /
Database altered.
SQL> c/13/14
1* alter database datafile 14 online
SQL> /
Database altered.
SQL> c/14/15
1* alter database datafile 15 online
SQL> /
Database altered.
SQL> c/15/16
1* alter database datafile 16 online
SQL> /
Database altered.
SQL> exit
SQL> alter pluggable database pdb open;
alter pluggable database pdb open
*
ERROR at line 1:
ORA-01113: file 16 needs media recovery
ORA-01110: data file 16:
'+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.389.1171794481'
SQL> alter pluggable database pdb open resetlogs;
alter pluggable database pdb open resetlogs
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcp_begin_df_resetlogs_5], [16],
[], [], [], [], [], [], [], [], [], []
ORA-01122: database file 16 failed verification check
ORA-01110: data file 16:
'+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.389.1171794481'
ORA-01207: file is more recent than control file - old control file
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
[oracle@rac3 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jun 16 10:35:37 2024
Version 19.20.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB (DBID=2265125454)
RMAN> recover database;
Starting recover at 16-JUN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 instance=cdb3 device type=DISK
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/16/2024 10:35:42
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover
if needed start
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 1 - file is in use or recovery
ORA-01110: data file 1: '+DATA1/CDB/DATAFILE/system.258.1171793673'
RMAN> run{
2> allocate channel ch00 device type 'SBT_TAPE'
3> PARMS="SBT_LIBRARY=oracle.disksbt,
4> ENV=(BACKUP_DIR=/nfs)";
5> set until time "to_date('16-JUN-2024 06:50:00','DD-MON-YYYY HH24:MI:SS')";
6> set newname for pluggable database pdb to '+data1';
7> recover pluggable database pdb ;
8> }
released channel: ORA_DISK_1
allocated channel: ch00
channel ch00: SID=52 instance=cdb3 device type=SBT_TAPE
channel ch00: WARNING: Oracle Test Disk API
executing command: SET until clause
executing command: SET NEWNAME
Starting recover at 16-JUN-24
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-JUN-24
released channel: ch00
RMAN> alter pluggable database pdb open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/16/2024 10:36:18
ORA-65137: Pluggable database PDB is in the middle of Pluggable Database RESETLOGS operation.
RMAN> alter pluggable database pdb open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/16/2024 10:36:29
ORA-00600: internal error code, arguments: [kcp_begin_df_resetlogs_5], [16], [], [], [], [], [], [], [], [], [], []
ORA-01122: database file 16 failed verification check
ORA-01110: data file 16: '+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.389.1171794481'
ORA-01207: file is more recent than control file - old control file
RMAN> run{
2> allocate channel ch00 device type 'SBT_TAPE'
3> PARMS="SBT_LIBRARY=oracle.disksbt,
4> ENV=(BACKUP_DIR=/nfs)";
5> set until time "to_date('16-JUN-2024 07:50:00','DD-MON-YYYY HH24:MI:SS')";
6> set newname for pluggable database pdb to '+data1';
7> recover pluggable database pdb ;
8> }
allocated channel: ch00
channel ch00: SID=52 instance=cdb3 device type=SBT_TAPE
channel ch00: WARNING: Oracle Test Disk API
executing command: SET until clause
executing command: SET NEWNAME
Starting recover at 16-JUN-24
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-JUN-24
released channel: ch00
RMAN> alter pluggable database pdb open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/16/2024 10:37:42
ORA-00600: internal error code, arguments: [kcp_begin_df_resetlogs_5], [16], [], [], [], [], [], [], [], [], [], []
ORA-01122: database file 16 failed verification check
ORA-01110: data file 16: '+DATA1/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.389.1171794481'
ORA-01207: file is more recent than control file - old control file
RMAN> alter pluggable database pdb open read only;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/16/2024 10:38:03
ORA-65137: Pluggable database PDB is in the middle of Pluggable Database RESETLOGS operation.
RMAN> alter pluggable database pdb open RESETLOGS;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/16/2024 10:38:17
ORA-39862: RESETLOGS option only valid after a Pluggable Database incomplete recovery
RMAN> recover pluggable database pdb ;
Starting recover at 16-JUN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 instance=cdb3 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-JUN-24
RMAN> alter pluggable database pdb open RESETLOGS;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/16/2024 10:38:33
ORA-39862: RESETLOGS option only valid after a Pluggable Database incomplete recovery
RMAN> recover pluggable database pdb ;
Starting recover at 16-JUN-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-JUN-24
RMAN> alter pluggable database pdb open RESETLOGS;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/16/2024 10:38:56
ORA-39862: RESETLOGS option only valid after a Pluggable Database incomplete recovery
RMAN>
RMAN>
RMAN> alter pluggable database pdb open RESETLOGS;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/16/2024 10:38:59
ORA-39862: RESETLOGS option only valid after a Pluggable Database incomplete recovery
RMAN> alter pluggable database pdb open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/16/2024 10:39:26
ORA-65137: Pluggable database PDB is in the middle of Pluggable Database RESETLOGS operation.
-----------------------都ORA- 还能recover，神奇。。。。。。。。。。。。。
RMAN> recover pluggable database pdb until time "to_date('16-JUN-2024 07:01:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '+data1';
Starting recover at 16-JUN-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-JUN-24
RMAN> alter pluggable database pdb open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/16/2024 10:39:43
ORA-65137: Pluggable database PDB is in the middle of Pluggable Database RESETLOGS operation.
RMAN> alter pluggable database pdb open RESETLOGS;
Statement processed
RMAN> alter pluggable database pdb close;
recover pluggable database pdb until time "to_date('16-JUN-2024 07:11:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '+data1';Statement processed
RMAN>
Starting recover at 16-JUN-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-JUN-24
RMAN> alter pluggable database pdb open RESETLOGS;
Statement processed
RMAN>
这篇关于pdb restore flashback recover 的三个办法 + CDB 级还原 注意数据库实际时间的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
