https://blog.csdn.net/weixin_52188141/article/details/118788172

rman异机备份恢复之backup_log.txt

rman异机备份恢复.txt

前提：

CONFIGURE CONTROLFILE AUTOBACKUP off;

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

当 CONFIGURE CONTROLFILE AUTOBACKUP OFF;时，仅备份1号数据文件(system)和全备数据库时，会自动备份控

制文件和参数文件。

而当控制文件自动备份关闭时，备份脚本仅【包含备份全库及备份归档(不包含备份控制文件时)】，数据库备份顺序是

备份数据库->自动备份控制文件和参数文件->备份归档。

--备份脚本

run

{

backup as compressed backupset full format '/opt/oracle/oraback/snrcs/snrcs_full_%d_%T_%s.bak' database;

sql 'alter system archive log current';

backup archivelog all format '/opt/oracle/oraback/snrcs/snrcs_arch_%d_%T_%s.bak';

delete noprompt obsolete;

delete noprompt archivelog until time 'sysdate-15';

}

============= 在异地机器上恢复如下 =============

1.在目标库上,还原spfile文件

export ORACLE_SID=snrcs

rman target /

startup nomount;

注意：这里会报错，主要是没有静态参数文件，可以先忽略

2.恢复spfile

rman target /

restore spfile to '/opt/oracle/product/11.2.0/db_1/dbs/spfilesnrcs.ora' from '/home/oracle/tmp

/snrcs_full_SNRCS_20220818_1578.bak';

3.利用spfile生成pfile，然后编辑pfile,修改参数pfile

export ORACLE_SID=snrcs

sqlplus / as sysdba

show parameter spfile

create pfile from spfile;

--编辑pfile

*.archive_lag_target=600

*.audit_file_dest='/opt/oracle/admin/snrcs/adump'

#*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/data1/snrcs/control01.ctl','/data1/snrcs/control02.ctl'

*.db_block_size=8192

*.db_cache_size=10240M

*.java_pool_size=512M

*.large_pool_size=2048M

*.pga_aggregate_target=10240M

*.shared_pool_size=3072M

*.db_domain=''

*.db_files=4096

*.db_name='snrcs'

*.db_recovery_file_dest='/opt/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=214748364800

*.diagnostic_dest='/opt/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=snrcsXDB)'

*.job_queue_processes=30

*.license_max_sessions=400

*.lock_sga=true

*.log_archive_dest_1='LOCATION=/data1/snrcs/archive'

*.log_archive_format='snrcs_%t_%s_%r.arc'

*.nls_date_format='YYYY-MM-DD HH24:MI:SS'

*.open_cursors=300

*.parallel_max_servers=0

*.processes=2000

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.utl_file_dir='/home/oracle/tmp'

mkdir -p /opt/oracle/admin/snrcs/adump

mkdir -p /data1/snrcs

mkdir -p /data1/snrcs/archive

--spfile文件查看

strings spfilesnrcs.ora

4.修改后的pfile，重启到nomount并再重新创建spfile

sqlplus / as sysdba

shutdown immediate;

startup nomount pfile='/opt/oracle/product/11.2.0/db_1/dbs/initsnrcs.ora';

create spfile from pfile;

shutdown immediate;

startup nomount

show parameter spfile

5.还原控制文件

rman target /

restore controlfile from '/home/oracle/tmp/snrcs_full_SNRCS_20220818_1578.bak';

alter database mount;

--检查备份，删除过期备份并重新注册备份

crosscheck backup;

delete noprompt expired backup;

catalog start with '/home/oracle/tmp/';

--检查备份信息

list backup;

list backup of database summary;

list backup of controlfile;

list backup of archivelog all;

6.根据备份信息，restore和recover 数据库

--查询数据文件号

report schema;

--如果数据文件路径改变需要重新定义文件路径

set newname for datafile 1 to 'xxxxxxxxxxxxxxxxxxx';

--执行

run {      

set newname for datafile 1  to '/data1/snrcs/system01.dbf';

set newname for datafile 2  to '/data1/snrcs/sysaux01.dbf';

set newname for datafile 3  to '/data1/snrcs/undotbs01.dbf';

set newname for datafile 4  to '/data1/snrcs/users01.dbf';

set newname for datafile 5  to '/data1/snrcs/undotbs02.dbf';

set newname for datafile 6  to '/data1/snrcs/undotbs03.dbf';

set newname for datafile 7  to '/data1/snrcs/ETL_USER01.dbf';

set newname for datafile 8  to '/data1/snrcs/snail_rcs01.dbf';

set newname for datafile 9  to '/data1/snrcs/snail_rcs02.dbf';

set newname for datafile 10 to '/data1/snrcs/snail_rcs03.dbf';

set newname for datafile 11 to '/data1/snrcs/snail_rcs04.dbf';

set newname for datafile 12 to '/data1/snrcs/snail_rcs05.dbf';

set newname for datafile 13 to '/data1/snrcs/snail_rcs06.dbf';

set newname for datafile 14 to '/data1/snrcs/snail_rcs07.dbf';

set newname for datafile 15 to '/data1/snrcs/snail_rcs08.dbf';

set newname for datafile 16 to '/data1/snrcs/snail_rcs09.dbf';

set newname for datafile 17 to '/data1/snrcs/snail_rcs10.dbf';

set newname for datafile 18 to '/data1/snrcs/snail_rcs11.dbf';

set newname for datafile 19 to '/data1/snrcs/snail_rcs12.dbf';

set newname for datafile 20 to '/data1/snrcs/snail_rcs13.dbf';

set newname for datafile 21 to '/data1/snrcs/sysaux02.dbf';

restore database;

switch datafile all;

recover database;

}

7.重建temp临时文件和redo日志文件

--查询

sqlplus / as sysdba

select member from gv$logfile

union all

select name from gv$tempfile;

--执行

rman target /

run{                                                                                                              

 sql "alter database rename file ''/data1/oradata/snrcs/redo01.log'' to ''/data1/snrcs/redo01.log'' ";

 sql "alter database rename file ''/data1/oradata/snrcs/redo02.log'' to ''/data1/snrcs/redo02.log'' ";

 sql "alter database rename file ''/data1/oradata/snrcs/redo03.log'' to ''/data1/snrcs/redo03.log'' ";

 sql "alter database rename file ''/data1/oradata/snrcs/redo04.log'' to ''/data1/snrcs/redo04.log'' ";

 sql "alter database rename file ''/data1/oradata/snrcs/redo05.log'' to ''/data1/snrcs/redo05.log'' ";  

 }

run{                                                                                                              

 sql "alter database rename file ''/data1/oradata/snrcs/temp01.dbf'' to ''/data1/snrcs/temp01.dbf'' ";

 sql "alter database rename file ''/data1/oradata/snrcs/temp02.dbf'' to ''/data1/snrcs/temp02.dbf'' ";

 sql "alter database rename file ''/data1/oradata/snrcs/temp03.dbf'' to ''/data1/snrcs/temp03.dbf'' ";

 }

8.恢复完成,最后

alter database open resetlogs;