rman异机恢复(set newname for database)
环境:
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;
