rman异机恢复(11G单机)
环境:
OS:Centos6.9
DB:11.2.0.4
主库SID:slnngka
备库SID:slnngkb
1.异地机器安装相同版本的数据库软件
安装步骤省略,注意只安装软件和启动监听器(没有监听任何服务器)
确保监听器已经启动
[oracle@slnngkb rmanbak]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-NOV-2023 15:10:25
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=slnngkb)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 27-NOV-2023 09:25:57
Uptime 0 days 5 hr. 44 min. 28 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/slnngkb/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=slnngkb)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
2.异地机器创建相应的目录
su - oracle
mkdir -p $ORACLE_BASE/oradata/slnngkb/
mkdir -p $ORACLE_BASE/admin/slnngkb/adump
mkdir -p $ORACLE_BASE/admin/slnngkb/dpdump
mkdir -p $ORACLE_BASE/fast_recovery_area/slnngkb
3.异地服务器准备参数文件
可以从主库拷贝进行相应修改
SQL>create pfile='/tmp/master_pfile.ora' from spfile;
拷贝到异地机器的dbs目录
scp /tmp/master_pfile.ora oracle@192.168.56.103:/u01/app/oracle/product/11.2.0.4/db_1/dbs/
从库对参数文件重命令
[oracle@slavea dbs]$ cd /u01/app/oracle/product/11.2.0.4/db_1/dbs/
[oracle@slavea dbs]$ mv master_pfile.ora initslnngkb.ora
进行修改,最后的参数如下:
*.audit_file_dest='/u01/app/oracle/admin/slnngkb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/slnngkb/control01.ctl','/u01/app/oracle/fast_recovery_area/slnngkb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='slnngka'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=slnngkbXDB)'
*.enable_goldengate_replication=TRUE
*.log_archive_dest_1='location=/u01/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=slnngkb'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=1070596096
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3213885440
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
注意:
*.db_name保留与源库一致不需要修改,否则在启动的时候报如下错误:
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01103: database name 'SLNNGKA' in control file is not 'SLNNGKB'
4.原库备份
run{
allocate channel c1 device type disk;
backup as compressed backupset format '/u01/rmanbak/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup as compressed backupset archivelog all delete input format '/u01/rmanbak/archive_log_t%t_s%s_p%p';
backup current controlfile format '/u01/rmanbak/ctl_%u.bak' tag 'bak_controlfile';
backup spfile format '/u01/rmanbak/spfile_%u_%T.bak';
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
delete noprompt obsolete;
release channel c1;
}
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/archivelog/1_129_1153413040.dbf thread=1 sequence=129
有DG的环境,归档日志还不能删除
解决办法1(不需要重启):
好像该方法不管用
alter system set "_deferred_log_dest_is_valid" = FALSE scope=both;
解决办法2:
alter system set log_archive_dest_2='' scope=both;
5.将备份集拷贝到异机
scp archive_log_t1154017805_s41_p1 oracle@192.168.56.103:/u01/rmanbak/
scp ctl_1a2chqhh.bak oracle@192.168.56.103:/u01/rmanbak/
scp df_t1154017711_s38_p1 oracle@192.168.56.103:/u01/rmanbak/
scp spfile_1b2chqhj_20231127.bak oracle@192.168.56.103:/u01/rmanbak/
6.异地机器启动到nomont状态
[oracle@slnngkb dbs]$ sqlplus /nolog
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0.4/db_1/dbs/initslnngkb.ora
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 721423760 bytes
Database Buffers 2466250752 bytes
Redo Buffers 16904192 bytes
7.恢复控制文件(在nomount状态下恢复)
[oracle@slnngkb dbs]$ rman target /
RMAN> restore controlfile from '/u01/rmanbak/ctl_1a2chqhh.bak';
Starting restore at 27-NOV-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 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/slnngkb/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/slnngkb/control02.ctl
Finished restore at 27-NOV-23
自动会根据启动参数的配置参数*.control_files复制到指定的位置.
8.启动数据库到mount阶段
SQL> connect / as sysdba
Connected.
SQL> alter database mount;
Database altered.
9.注册备份集
[oracle@slnngkb dbs]$ rman target /
RMAN>catalog start with '/u01/rmanbak';
查看备份集:
RMAN> crosscheck backupset;
RMAN> delete expired backupset;
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Full 311.94M DISK 00:00:50 27-NOV-23
BP Key: 14 Status: AVAILABLE Compressed: YES Tag: TAG20231127T145444
Piece Name: /u01/rmanbak/df_t1154012084_s14_p1
List of Datafiles in backup set 14
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 3655725 27-NOV-23 /u01/app/oracle/oradata/slnngka/system01.dbf
2 Full 3655725 27-NOV-23 /u01/app/oracle/oradata/slnngka/sysaux01.dbf
3 Full 3655725 27-NOV-23 /u01/app/oracle/oradata/slnngka/undotbs01.dbf
4 Full 3655725 27-NOV-23 /u01/app/oracle/oradata/slnngka/users01.dbf
5 Full 3655725 27-NOV-23 /u01/app/oracle/oradata/slnngka/gguser.dbf
6 Full 3655725 27-NOV-23 /u01/app/oracle/oradata/slnngka/tps_goldengate01.dbf
10.还原数据文件
run{
allocate channel c1 device type disk;
set newname for database to '/u01/app/oracle/oradata/slnngkb/%b';
restore database;
switch datafile all;
switch tempfile all;
release channel c1;
}
参数说明:
％b 指定的文件名从目录路径中剥离,只获取数据文件名称.
11.恢复数据库
查看备份过来的归档日志
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
16 346.29M DISK 00:00:47 27-NOV-23
BP Key: 16 Status: AVAILABLE Compressed: YES Tag: TAG20231127T145542
Piece Name: /u01/rmanbak/archive_log_t1154012143_s16_p1
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
18 231.09M DISK 00:00:38 27-NOV-23
BP Key: 18 Status: AVAILABLE Compressed: YES Tag: TAG20231127T145542
Piece Name: /u01/rmanbak/archive_log_t1154012244_s18_p1
List of Archived Logs in backup set 18
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 98 3205872 23-NOV-23 3205990 23-NOV-23
1 99 3205990 23-NOV-23 3206048 23-NOV-23
1 100 3206048 23-NOV-23 3206105 23-NOV-23
1 101 3206105 23-NOV-23 3206164 23-NOV-23
1 102 3206164 23-NOV-23 3206316 23-NOV-23
1 103 3206316 23-NOV-23 3206892 23-NOV-23
1 104 3206892 23-NOV-23 3207306 23-NOV-23
1 105 3207306 23-NOV-23 3309631 23-NOV-23
1 106 3309631 23-NOV-23 3309722 23-NOV-23
1 107 3309722 23-NOV-23 3309782 23-NOV-23
1 108 3309782 23-NOV-23 3309840 23-NOV-23
1 109 3309840 23-NOV-23 3309899 23-NOV-23
1 110 3309899 23-NOV-23 3310046 23-NOV-23
1 111 3310046 23-NOV-23 3330987 27-NOV-23
1 112 3330987 27-NOV-23 3331257 27-NOV-23
1 113 3331257 27-NOV-23 3433179 27-NOV-23
1 114 3433179 27-NOV-23 3433268 27-NOV-23
1 115 3433268 27-NOV-23 3433328 27-NOV-23
1 116 3433328 27-NOV-23 3433389 27-NOV-23
1 117 3433389 27-NOV-23 3433449 27-NOV-23
1 118 3433449 27-NOV-23 3433603 27-NOV-23
1 119 3433603 27-NOV-23 3436106 27-NOV-23
1 120 3436106 27-NOV-23 3436489 27-NOV-23
1 121 3436489 27-NOV-23 3536868 27-NOV-23
1 122 3536868 27-NOV-23 3536943 27-NOV-23
1 123 3536943 27-NOV-23 3536991 27-NOV-23
1 124 3536991 27-NOV-23 3537046 27-NOV-23
1 125 3537046 27-NOV-23 3537100 27-NOV-23
1 126 3537100 27-NOV-23 3537171 27-NOV-23
1 127 3537171 27-NOV-23 3537631 27-NOV-23
1 128 3537631 27-NOV-23 3538990 27-NOV-23
1 129 3538990 27-NOV-23 3554627 27-NOV-23
1 130 3554627 27-NOV-23 3593037 27-NOV-23
1 131 3593037 27-NOV-23 3633058 27-NOV-23
1 132 3633058 27-NOV-23 3655797 27-NOV-23
1 133 3655797 27-NOV-23 3655805 27-NOV-23
12.恢复到指定的归档日志
我们这里恢复到133的归档日志,语法需要指定到134(133+1)
run{
set until sequence 134 thread 1;
recover database;
}
13.以read only方式打开数据库进行数据验证
SQL> alter database open read only;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> select count(1) from hxl.tb_test;
COUNT(1)
----------
2600000
但是不能使用expdp导出
oracle@slnngkb slnngkb]$ expdp system/oracle tables=hxl.tb_test dumpfile=tb_test.dmp directory=DATA_PUMP_DIR
Export: Release 11.2.0.4.0 - Production on Tue Nov 28 10:20:53 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-16000: database open for read-only access
若这里恢复不是自己预期的,可以继续使用规定日志进行恢复(注意必须是往后恢复)
shutdown immediate
startup mount
run{
set until sequence 135 thread 1;
recover database;
}
再次往前恢复会报如下错误:
RMAN> run{
2> set until sequence 135 thread 1;
3> recover database;
4> }
executing command: SET until clause
Starting recover at 28-NOV-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/28/2023 09:20:47
RMAN-06556: datafile 1 must be restored from backup older than SCN 3662435
14.打开数据库
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/slnngka/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/slnngka/redo03.log
/u01/app/oracle/oradata/slnngka/redo02.log
/u01/app/oracle/oradata/slnngka/redo01.log
/u01/app/oracle/oradata/slnngka/stdbyredo04.log
/u01/app/oracle/oradata/slnngka/stdbyredo05.log
/u01/app/oracle/oradata/slnngka/stdbyredo06.log
6 rows selected.
修改路径
alter database rename file '/u01/app/oracle/oradata/slnngka/redo01.log' to '/u01/app/oracle/oradata/slnngkb/redo01.log';
alter database rename file '/u01/app/oracle/oradata/slnngka/redo02.log' to '/u01/app/oracle/oradata/slnngkb/redo02.log';
alter database rename file '/u01/app/oracle/oradata/slnngka/redo03.log' to '/u01/app/oracle/oradata/slnngkb/redo03.log';
standby log调整,若这里不调整,open数据库的时候可以进行删除
alter database rename file '/u01/app/oracle/oradata/slnngka/stdbyredo04.log' to '/u01/app/oracle/oradata/slnngkb/stdbyredo04.log';
alter database rename file '/u01/app/oracle/oradata/slnngka/stdbyredo05.log' to '/u01/app/oracle/oradata/slnngkb/stdbyredo05.log';
alter database rename file '/u01/app/oracle/oradata/slnngka/stdbyredo06.log' to '/u01/app/oracle/oradata/slnngkb/stdbyredo06.log';
删除standby log的方法(open database的情况下)
select GROUP# from v$standby_log;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 2 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/slnngkb/redo02.log'
查看日志状态
SQL> select group#,bytes/1024/1024||'M',status from v$log;
GROUP# BYTES/1024/1024||'M' STATUS
---------- ----------------------------------------- ----------------
1 50M CLEARING
3 50M CLEARING
2 50M CLEARING_CURRENT
修复:
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.
15.创建spfile
create spfile from pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initslnngkb.ora';
