ORACLE 19C 使用自己的辅助数据库完成TSPITR基于时间的表空间恢复

郑全

使用自己的辅助数据库完成TSPITR基于时间的表空间恢复

目标数据库：orcl
辅助数据库: aux



1.创建自己的辅助实例aux

1.创建口令文件
   cp $ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapwaux

2.建立辅助监听
  vim listener.ora
  ...
   SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = aux)
      (ORACLE_HOME = /u01/app/oracle/product/19.15.0/db_1)
      (SID_NAME = aux)
    )
  )
  --监听生效
  lsnrctl reload

3.建初始参数

[oracle@dbserver ~]$ cat initaux.ora
*.audit_file_dest='/u01/app/oracle/admin/aux/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/aux/control01.ctl','/u01/app/oracle/oradata/aux/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'    --必须和目标库一样
*.db_unique_name=aux_orcl    --必须和目标库不一样
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=200m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1024m
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/aux/'
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/aux/'
[oracle@dbserver ~]$

4.启动辅助实例
  export ORACLE_SID=aux

  sql>create spfile from pfile='/home/oracle/initaux.ora';
  sql>startup nomount;

5.使用自己的辅助实例恢复到指定时间

rman target sys/sztech_4U@orcl auxiliary sys/sztech_4U@192.168.0.61:1521/aux


12:52:49

RMAN>
run{
allocate channel c1 device type disk;
allocate auxiliary channel a1 device type disk;
recover tablespace tsp_tspitr1,tsp_other until time "to_date('2022-07-17 12:52:49','yyyy-mm-dd hh24:mi:ss')";
}


[oracle@dbserver ~]$ rman target sys/sztech_4U@orcl auxiliary sys/sztech_4U@192.168.0.61:1521/aux

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jul 17 12:54:58 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1635548566)
connected to auxiliary database: ORCL (not mounted)

RMAN> run{
2> allocate channel c1 device type disk;
3> allocate auxiliary channel a1 device type disk;
4> recover tablespace tsp_tspitr1,tsp_other until time "to_date('2022-07-17 12:52:49','yyyy-mm-dd hh24:mi:ss')";
5> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=272 device type=DISK

allocated channel: a1
channel a1: SID=379 device type=DISK

Starting recover at 2022-07-17 12:55:39
current log archived
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
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2022-07-17 12:52:49','yyyy-mm-dd hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';

# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 2022-07-17 12:55:40

channel a1: starting datafile backup set restore
channel a1: restoring control file
channel a1: reading from backup piece /u01/app/oracle/product/19.15.0/db_1/dbs/c-1635548566-20220717-07
channel a1: piece handle=/u01/app/oracle/product/19.15.0/db_1/dbs/c-1635548566-20220717-07 tag=TAG20220717T125243
channel a1: restored backup piece 1
channel a1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/aux/control01.ctl
output file name=/u01/app/oracle/oradata/aux/control02.ctl
Finished restore at 2022-07-17 12:55:42

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2022-07-17 12:52:49','yyyy-mm-dd hh24:mi:ss')";
plsql <<<--
declare
  sqlstatement       varchar2(512);
  pdbname            varchar2(128);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
   pdbname := null; -- pdbname
  sqlstatement := 'alter tablespace '||  'TSP_TSPITR1' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement, 0, pdbname);
exception
  when offline_not_needed then
    null;
end; >>>;
plsql <<<--
declare
  sqlstatement       varchar2(512);
  pdbname            varchar2(128);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
   pdbname := null; -- pdbname
  sqlstatement := 'alter tablespace '||  'TSP_OTHER' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement, 0, pdbname);
exception
  when offline_not_needed then
    null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  1 to
"/u01/app/oracle/oradata/aux/system01.dbf";
set newname for datafile  4 to
"/u01/app/oracle/oradata/aux/undotbs01.dbf";
set newname for datafile  3 to
"/u01/app/oracle/oradata/aux/sysaux01.dbf";
set newname for tempfile  1 to
"/u01/app/oracle/oradata/aux/temp01.dbf";
set newname for datafile  9 to
"/u01/app/oracle/oradata/ORCL/tsp_tspitr1.dbf";
set newname for datafile  8 to
"/u01/app/oracle/oradata/ORCL/tsp_other01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 4, 3, 9, 8;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TSP_TSPITR1 offline immediate

sql statement: alter tablespace TSP_OTHER offline immediate

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 2022-07-17 12:55:47

channel a1: starting datafile backup set restore
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00001 to /u01/app/oracle/oradata/aux/system01.dbf
channel a1: restoring datafile 00004 to /u01/app/oracle/oradata/aux/undotbs01.dbf
channel a1: restoring datafile 00003 to /u01/app/oracle/oradata/aux/sysaux01.dbf
channel a1: restoring datafile 00009 to /u01/app/oracle/oradata/ORCL/tsp_tspitr1.dbf
channel a1: restoring datafile 00008 to /u01/app/oracle/oradata/ORCL/tsp_other01.dbf
channel a1: reading from backup piece /u01/app/oracle/product/19.15.0/db_1/dbs/0v12r80b_31_1_1
channel a1: piece handle=/u01/app/oracle/product/19.15.0/db_1/dbs/0v12r80b_31_1_1 tag=TAG20220717T125227
channel a1: restored backup piece 1
channel a1: restore complete, elapsed time: 00:00:15
Finished restore at 2022-07-17 12:56:03

datafile 9 switched to datafile copy
input datafile copy RECID=9 STAMP=1110286564 file name=/u01/app/oracle/oradata/ORCL/tsp_tspitr1.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1110286564 file name=/u01/app/oracle/oradata/ORCL/tsp_other01.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2022-07-17 12:52:49','yyyy-mm-dd 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  3 online";
sql clone "alter database datafile  9 online";
sql clone "alter database datafile  8 online";
# recover and open resetlogs
recover clone database tablespace  "TSP_TSPITR1", "TSP_OTHER", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
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  3 online

sql statement: alter database datafile  9 online

sql statement: alter database datafile  8 online

Starting recover at 2022-07-17 12:56:04

Executing: alter database datafile 2, 5, 7 offline
starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/19.15.0/db_1/dbs/arch1_8_1110278326.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/product/19.15.0/db_1/dbs/arch1_9_1110278326.dbf
archived log file name=/u01/app/oracle/product/19.15.0/db_1/dbs/arch1_8_1110278326.dbf thread=1 sequence=8
archived log file name=/u01/app/oracle/product/19.15.0/db_1/dbs/arch1_9_1110278326.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 2022-07-17 12:56:07

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  TSP_TSPITR1 read only';
sql clone 'alter tablespace  TSP_OTHER read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/product/19.15.0/db_1/dbs''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/product/19.15.0/db_1/dbs''";
}
executing Memory Script

sql statement: alter tablespace  TSP_TSPITR1 read only

sql statement: alter tablespace  TSP_OTHER read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/product/19.15.0/db_1/dbs''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/product/19.15.0/db_1/dbs''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_xCAv_ozie":  
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
   EXPDP> Master table "SYS"."TSPITR_EXP_xCAv_ozie" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_xCAv_ozie is:
   EXPDP>   /u01/app/oracle/product/19.15.0/db_1/dbs/tspitr_xCAv_29678.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TSP_OTHER:
   EXPDP>   /u01/app/oracle/oradata/ORCL/tsp_other01.dbf
   EXPDP> Datafiles required for transportable tablespace TSP_TSPITR1:
   EXPDP>   /u01/app/oracle/oradata/ORCL/tsp_tspitr1.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_xCAv_ozie" successfully completed at Sun Jul 17 12:57:14 2022 elapsed 0 00:00:56
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
# drop target tablespaces before importing them back
sql 'drop tablespace  TSP_TSPITR1 including contents keep datafiles cascade constraints';
sql 'drop tablespace  TSP_OTHER including contents keep datafiles cascade constraints';
}
executing Memory Script

Oracle instance shut down

sql statement: drop tablespace  TSP_TSPITR1 including contents keep datafiles cascade constraints

sql statement: drop tablespace  TSP_OTHER including contents keep datafiles cascade constraints

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_xCAv_fkcp" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_xCAv_fkcp":  
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_xCAv_fkcp" successfully completed at Sun Jul 17 12:58:04 2022 elapsed 0 00:00:27
Import completed


contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  TSP_TSPITR1 read write';
sql 'alter tablespace  TSP_TSPITR1 offline';
sql 'alter tablespace  TSP_OTHER read write';
sql 'alter tablespace  TSP_OTHER offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace  TSP_TSPITR1 read write

sql statement: alter tablespace  TSP_TSPITR1 offline

sql statement: alter tablespace  TSP_OTHER read write

sql statement: alter tablespace  TSP_OTHER offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
auxiliary instance file /u01/app/oracle/oradata/aux/temp01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/aux/redo03.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/redo02.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/redo01.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/sysaux01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/aux/undotbs01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/aux/system01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/aux/control02.ctl deleted
auxiliary instance file /u01/app/oracle/oradata/aux/control01.ctl deleted
auxiliary instance file tspitr_xCAv_29678.dmp deleted
Finished recover at 2022-07-17 12:58:07
released channel: c1

RMAN>



附：
   1.如果辅助实例的DB_NAME不等于目标库的DB_NAME，将报以下错误：

RMAN> run{
2> allocate channel c1 device type disk;
3> allocate auxiliary channel a1 device type disk;
4> recover tablespace tsp_tspitr1,tsp_other until time "to_date('2022-07-17 11:43:18','yyyy-mm-dd hh24:mi:ss')";
5> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=272 device type=DISK

allocated channel: a1
channel a1: SID=379 device type=DISK

Starting recover at 2022-07-17 12:08:01
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
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2022-07-17 11:43:18','yyyy-mm-dd hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';

# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 2022-07-17 12:08:02

channel a1: starting datafile backup set restore
channel a1: restoring control file
channel a1: reading from backup piece /u01/app/oracle/product/19.15.0/db_1/dbs/c-1635548566-20220717-04
channel a1: piece handle=/u01/app/oracle/product/19.15.0/db_1/dbs/c-1635548566-20220717-04 tag=TAG20220717T114155
channel a1: restored backup piece 1
channel a1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/aux/control01.ctl
output file name=/u01/app/oracle/oradata/aux/control02.ctl
Finished restore at 2022-07-17 12:08:03

sql statement: alter database mount clone database
released channel: c1
released channel: a1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/17/2022 12:08:08
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 07/17/2022 12:08:08
RMAN-11003: failure during parse/execution of SQL statement: alter database mount clone database
ORA-01103: database name 'ORCL' in control file is not 'AUX'


   2.如果辅助实例的DB_UNIQUE_NAME没有设置，将报以下错误：

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;
ORA-39511: Start of CRS resource for instance '223' failed with error:[CRS-5702: Resource 'ora.orcl.db' is already running on 'dbserver'
CRS-0223: Resource 'ora.orcl.db' has placement error.
clsr_start_resource:260 status:223
clsrapi_start_db:start_asmdbs status:223

郑全

参考官方文档：https://docs.oracle.com/en/datab ... 4-8249-C5D855D47571
                      21 Performing RMAN Tablespace Point-in-Time Recovery