rman异机恢复(set newname for database)https://www.cnblogs.com/hxlasky/p/17405438.html

环境:
OS:centos 7
db:19.3.0.0
主库SID:slnngk
备库SID:slavea

1.异地机器安装相同版本的数据库软件
安装步骤省略,注意只安装软件

2.异地机器创建相应的目录

su - oracle
mkdir -p $ORACLE_BASE/oradata/slavea/
mkdir -p $ORACLE_BASE/admin/slavea/adump
mkdir -p $ORACLE_BASE/admin/slavea/dpdump
mkdir -p $ORACLE_BASE/fast_recovery_area/slavea

3.异地服务器准备参数文件
可以从主库拷贝进行相应修改
SQL>create pfile='/tmp/master_pfile.ora' from spfile;
拷贝到异地机器的dbs目录
scp /tmp/master_pfile.ora oracle@192.168.1.105:/u01/app/oracle/product/19.3.0.0/db_1/dbs/

从库对参数文件重命令
[oracle@slavea dbs]$ cd /u01/app/oracle/product/19.3.0.0/db_1/dbs
[oracle@slavea dbs]$ mv master_pfile.ora sinit.ora

原参数:

[root@19c tmp]# more master_pfile.ora 
slnngk.__data_transfer_cache_size=0
slnngk.__db_cache_size=5553258496
slnngk.__inmemory_ext_roarea=0
slnngk.__inmemory_ext_rwarea=0
slnngk.__java_pool_size=0
slnngk.__large_pool_size=16777216
slnngk.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
slnngk.__pga_aggregate_target=1207959552
slnngk.__sga_target=6442450944
slnngk.__shared_io_pool_size=134217728
slnngk.__shared_pool_size=721420288
slnngk.__streams_pool_size=0
slnngk.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/slnngk/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/SLNNGK/control01.ctl','/u01/app/oracle/fast_recovery_area/SLNNGK/control02.ctl'
*.db_block_size=8192
*.db_name='slnngk'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=slnngkXDB)'
*.enable_goldengate_replication=TRUE
*.log_archive_dest_1='location=/u01/app/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1147m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=6442450944
*.sga_target=6442450944
*.undo_tablespace='UNDOTBS1

异机库修改后参数文件
*.db_name保留与源库一致不需要修改

[oracle@slavea dbs]$ more sinit.ora 
*.audit_file_dest='/u01/app/oracle/admin/slavea/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/slavea/control01.ctl','/u01/app/oracle/fast_recovery_area/slavea/control02.ctl'
*.db_block_size=8192
*.db_name='slnngk'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=slaveaXDB)'
*.enable_goldengate_replication=TRUE
*.log_archive_dest_1='location=/u01/app/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1147m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=6442450944
*.sga_target=6442450944
*.undo_tablespace='UNDOTBS1'

注意配置文件中各参数指定的相应路径需要存在

4.备份源库

mkdir -p /u01/app/rmanbak

run{
allocate channel c1 device type disk;
backup as compressed backupset format '/u01/app/rmanbak/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup as compressed backupset format '/u01/app/rmanbak/archive_log_t%t_s%s_p%p' archivelog all;
backup current controlfile format '/u01/app/rmanbak/ctl.bak' tag 'bak_controlfile';
backup spfile format '/u01/app/rmanbak/spfile_%u_%T.bak';
release channel c1;
}

5.将备份集拷贝到异机

[oracle@19c rmanbak]$scp archive_log_t1136932092_s7_p1 oracle@192.168.1.105:/u01/app/rmanbak/
[oracle@19c rmanbak]$scp ctl.bak oracle@192.168.1.105:/u01/app/rmanbak/
[oracle@19c rmanbak]$scp df_t1136932045_s5_p1 oracle@192.168.1.105:/u01/app/rmanbak/
[oracle@19c rmanbak]$scp spfile_091s8dbr_20230515.bak oracle@192.168.1.105:/u01/app/rmanbak/

6.异地机器启动到nomont状态

oracle@slavea app]$ echo $ORACLE_SID
slavea

SQL> startup nomount pfile=/u01/app/oracle/product/19.3.0.0/db_1/dbs/sinit.ora
ORA-27104: system-defined limits for shared memory was misconfigured

解决办法:
异机的内存太小导致,修改如下参数
*.sga_max_size=4442450944
*.sga_target=4442450944

修改重新启动
SQL> startup nomount pfile=/u01/app/oracle/product/19.3.0.0/db_1/dbs/sinit.ora
ORACLE instance started.

Total System Global Area 4445958648 bytes
Fixed Size 8905208 bytes
Variable Size 822083584 bytes
Database Buffers 3607101440 bytes
Redo Buffers 7868416 bytes

7.恢复控制文件

rman target /
RMAN> restore controlfile from '/u01/app/rmanbak/ctl.bak';

Starting restore at 15-MAY-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/slavea/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/slavea/control02.ctl
Finished restore at 15-MAY-23

系统会自动恢复到参照文件参数control_files指定的路径.

8.启动数据库到mount阶段--doing
SQL> connect / as sysdba
Connected.
SQL> alter database mount;

9.注册备份集

[oracle@slavea ~]$ rman target /
RMAN>catalog start with '/u01/app/rmanbak';

查看备份集

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    377.66M    DISK        00:00:44     15-MAY-23      
        BP Key: 5   Status: AVAILABLE  Compressed: YES  Tag: TAG20230515T222725
        Piece Name: /u01/app/rmanbak/df_t1136932045_s5_p1
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 13080326   15-MAY-23              NO    /u01/app/oracle/oradata/SLNNGK/system01.dbf
  2       Full 13080326   15-MAY-23              NO    /u01/app/oracle/oradata/SLNNGK/VER_ias_opss.dbf
  3       Full 13080326   15-MAY-23              NO    /u01/app/oracle/oradata/SLNNGK/sysaux01.dbf
  4       Full 13080326   15-MAY-23              NO    /u01/app/oracle/oradata/SLNNGK/undotbs01.dbf
  5       Full 13080326   15-MAY-23              NO    /u01/app/oracle/oradata/SLNNGK/tps_goldengate01.dbf
  7       Full 13080326   15-MAY-23              NO    /u01/app/oracle/oradata/SLNNGK/users01.dbf
  8       Full 13080326   15-MAY-23              NO    /u01/app/oracle/oradata/SLNNGK/VER_iau.dbf
  9       Full 13080326   15-MAY-23              NO    /u01/app/oracle/oradata/SLNNGK/VER_svctbl.dbf
  10      Full 13080326   15-MAY-23              NO    /u01/app/oracle/oradata/SLNNGK/VER_veridata_user.dbf
  11      Full 13080326   15-MAY-23              NO    /u01/app/oracle/oradata/SLNNGK/VER_wlsservices.dbf

这个时候查看到的数据文件还是源库的路径:/u01/app/oracle/oradata/SLNNGK

10.还原数据文件

run{
allocate channel c1 device type disk;
set newname for database to '/u01/app/oracle/oradata/slavea/%b';
restore database;
switch datafile all;
switch tempfile all;
release channel c1;
}

参数说明:
％b 指定的文件名 ??的目录路径中剥离。例如，如果一个数据文件被命名为/ ORADATA /prod/ financial.dbf，％b可以代表financial.dbf结果。
％f 指定新的名称生成的数据文件的绝对文件号。例如，如果被复制的数据是文件2，然后％F产生的值为2。
％I 指定DBID
％N 指定表空间名称
％U 定的格式如下：data-D-%d_id-%I_TS-%N_FNO-%f

11.恢复数据库
查看备份过来的归档日志

RMAN> list backup of archivelog all;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       1.23G      DISK        00:02:04     15-MAY-23      
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20230515T222812
        Piece Name: /u01/app/rmanbak/archive_log_t1136932092_s7_p1

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    31      6392674    05-APR-23 6642898    05-APR-23
  1    32      6642898    05-APR-23 6891751    05-APR-23
  1    33      6891751    05-APR-23 7135421    05-APR-23
  1    34      7135421    05-APR-23 7384450    05-APR-23
  1    35      7384450    05-APR-23 7633277    05-APR-23
  1    36      7633277    05-APR-23 7881859    05-APR-23
  1    37      7881859    05-APR-23 8131361    05-APR-23
  1    38      8131361    05-APR-23 8380482    05-APR-23
  1    39      8380482    05-APR-23 8630250    05-APR-23
  1    40      8630250    05-APR-23 8874994    06-APR-23
  1    41      8874994    06-APR-23 9124413    06-APR-23
  1    42      9124413    06-APR-23 9373470    06-APR-23
  1    43      9373470    06-APR-23 9622561    06-APR-23
  1    44      9622561    06-APR-23 9871919    06-APR-23
  1    45      9871919    06-APR-23 10121206   06-APR-23
  1    46      10121206   06-APR-23 10371087   06-APR-23
  1    47      10371087   06-APR-23 10616115   06-APR-23
  1    48      10616115   06-APR-23 10865613   06-APR-23
  1    49      10865613   06-APR-23 11115732   06-APR-23
  1    50      11115732   06-APR-23 11364709   06-APR-23
  1    51      11364709   06-APR-23 11614532   06-APR-23
  1    52      11614532   06-APR-23 11864102   06-APR-23
  1    53      11864102   06-APR-23 12112895   06-APR-23
  1    54      12112895   06-APR-23 12356876   06-APR-23
  1    55      12356876   06-APR-23 12661634   06-APR-23
  1    56      12661634   06-APR-23 12796627   06-APR-23
  1    57      12796627   06-APR-23 13019611   12-MAY-23
  1    58      13019611   12-MAY-23 13068553   15-MAY-23
  1    59      13068553   15-MAY-23 13079687   15-MAY-23
  1    60      13079687   15-MAY-23 13079695   15-MAY-23
  1    61      13079695   15-MAY-23 13080453   15-MAY-23
  1    62      13080453   15-MAY-23 13080461   15-MAY-23

RMAN> 

恢复到62号归档日志

run{
set until sequence 63 thread 1;
recover database;
}

RMAN> run{
2> set until sequence 63 thread 1;
3> recover database;
4> }

executing command: SET until clause

Starting recover at 15-MAY-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=61
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=62
channel ORA_DISK_1: reading from backup piece /u01/app/rmanbak/archive_log_t1136932092_s7_p1
channel ORA_DISK_1: piece handle=/u01/app/rmanbak/archive_log_t1136932092_s7_p1 tag=TAG20230515T222812
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:45
archived log file name=/u01/app/archivelog/1_61_1128656321.dbf thread=1 sequence=61
archived log file name=/u01/app/archivelog/1_62_1128656321.dbf thread=1 sequence=62
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-MAY-23

11.打开数据库

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'/u01/app/oracle/oradata/SLNNGK/redo01.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1

查看当前v$log日志
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SLNNGK/redo03.log
/u01/app/oracle/oradata/SLNNGK/redo02.log
/u01/app/oracle/oradata/SLNNGK/redo01.log

修改路径

alter database rename file '/u01/app/oracle/oradata/SLNNGK/redo01.log' to '/u01/app/oracle/oradata/slavea/redo01.log';
alter database rename file '/u01/app/oracle/oradata/SLNNGK/redo02.log' to '/u01/app/oracle/oradata/slavea/redo02.log';
alter database rename file '/u01/app/oracle/oradata/SLNNGK/redo03.log' to '/u01/app/oracle/oradata/slavea/redo03.log';

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/slavea/redo03.log'

查看日志状态
SQL> select group#,bytes/1024/1024||'M',status from v$log;

    GROUP# BYTES/1024/1024||'M'                      STATUS
---------- ----------------------------------------- ----------------
         1 200M                                      CLEARING
         3 200M                                      CLEARING_CURRENT
         2 200M                                      CLEARING

修复:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;


再次打开:
SQL> alter database open resetlogs;

Database altered.

12.临时表空间处理

若在恢复的时候没有加上switch tempfile all

run{
allocate channel c1 device type disk;
set newname for database to '/u01/app/oracle/oradata/slavea/%b';
restore database;
switch datafile all;
switch tempfile all;
release channel c1;
}

那么需要对临时表空间做如下处理

SQL> column name format a64;
SQL> column bytes format 9999999999;
SQL> column ts# format 99;
SQL> select name,bytes,ts# from v$tempfile;

NAME                                                                   BYTES TS#
---------------------------------------------------------------- ----------- ---
/u01/app/oracle/oradata/SLNNGK/temp01.dbf                                  0   3
/u01/app/oracle/oradata/SLNNGK/VER_iastemp.dbf                             0   8
/u01/app/oracle/oradata/SLNNGK/VER_veridata_temp.dbf                       0  11


SQL>alter database tempfile '/u01/app/oracle/oradata/SLNNGK/temp01.dbf' drop;
SQL>alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/slavea/temp01.dbf' size 200m autoextend on;


SQL>alter database tempfile '/u01/app/oracle/oradata/SLNNGK/VER_iastemp.dbf' drop;
SQL>alter tablespace VER_IAS_TEMP add tempfile '/u01/app/oracle/oradata/slavea/VER_iastemp.dbf' size 200M autoextend on;


SQL>alter database tempfile '/u01/app/oracle/oradata/SLNNGK/VER_veridata_temp.dbf' drop;
SQL>alter tablespace VER_VERIDATA_TEMP add tempfile '/u01/app/oracle/oradata/slavea/VER_veridata_temp.dbf' size 200M autoextend on;

分类