利用rman备份创建standby备库
来罐百事可乐
于 2021-11-24 14:25:41 发布
阅读量1k
收藏 2
点赞数 2
文章标签: oracle
版权
使用rman来创建standby,过程相当简单,主库无需停机。本次试验演示了利用Rman热备,然后restore来创建备库的过程。
生产库:10.158.5.186
DG备库:10.158.5.188
演示环境
[oracle@dbm apps]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 25 17:48:20 2019
Copyright © 1982, 2013, Oracle. 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
SQL> select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
确认主库处于归档模式
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
主库设置
[oracle@dbm dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 25 19:25:44 2019
Copyright © 1982, 2013, Oracle. 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
SQL> alter system set LOG_ARCHIVE_DEST_1=‘LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db’ sid=’*’;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2=‘SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=dg’ sid=’*’;
System altered.
SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=‘EXCLUSIVE’ scope=spfile sid=’*’;
System altered.
SQL> alter system set log_archive_format=‘log_%t_%r_%s.dbf’ scope=spfile sid=’*’;
System altered.
SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=30 sid=’*’;
System altered.
SQL> alter system set FAL_SERVER=dg sid=’*’;
System altered.
SQL> alter system set FAL_CLIENT=db sid=’*’;
System altered.
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO sid=’*’;
System altered.
SQL> alter system set LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(db,dg)’ sid=’*’;
System altered.
SQL>
3.备库安装数据库软件-配置参数文件-配置网络-生成password file
[oracle@dg admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.158.5.188)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dg)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1)
(SID_NAME = dg)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@dg admin]$ cat tnsnames.ora
DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.158.5.186)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db)
)
)
dg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.158.5.188)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg)
)
)
4.主库rman备份控制文件,备份数据库,日志文件
[root@dbm ~]# su - oracle
[oracle@dbm ~]$
[oracle@dbm ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 24 13:05:06 2021
Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB (DBID=1783742100)
RMAN> backup current controlfile for standby format=’/beifen/sy.bak’;
Starting backup at 2021:11:24 13:05:09
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: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 2021:11:24 13:05:10
channel ORA_DISK_1: finished piece 1 at 2021:11:24 13:05:11
piece handle=/beifen/sy.bak tag=TAG20211124T130509 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021:11:24 13:05:11
RMAN> backup incremental level 0 database format=’/beifen/dbf_full_%s.bak’;
Starting backup at 2021:11:24 13:05:59
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/db/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/db/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/db/users02.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/db/users03.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/db/users04.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/db/users05.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2021:11:24 13:06:00
channel ORA_DISK_1: finished piece 1 at 2021:11:24 13:06:15
piece handle=/beifen/dbf_full_3.bak tag=TAG20211124T130559 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2021:11:24 13:06:16
channel ORA_DISK_1: finished piece 1 at 2021:11:24 13:06:17
piece handle=/beifen/dbf_full_4.bak tag=TAG20211124T130559 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021:11:24 13:06:17
RMAN> backup archivelog all format=’/beifen/sy.log’;
Starting backup at 2021:11:24 13:08:29
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=1019928122
input archived log thread=1 sequence=2 RECID=2 STAMP=1019935563
input archived log thread=1 sequence=3 RECID=3 STAMP=1019935566
input archived log thread=1 sequence=4 RECID=4 STAMP=1089464847
input archived log thread=1 sequence=5 RECID=5 STAMP=1089464909
channel ORA_DISK_1: starting piece 1 at 2021:11:24 13:08:29
channel ORA_DISK_1: finished piece 1 at 2021:11:24 13:08:32
piece handle=/beifen/sy.log tag=TAG20211124T130829 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2021:11:24 13:08:32
将备份文件拷贝到灾备进行恢复
5.灾备进行恢复
启动备库到nomount状态
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 325685248 bytes
Fixed Size 2252944 bytes
Variable Size 213913456 bytes
Database Buffers 104857600 bytes
Redo Buffers 4661248 bytes
恢复控制文件
[oracle@dg ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 24 13:21:13 2021
Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB (not mounted)
RMAN> restore standby controlfile from ‘/sy/sy.bak’;
Starting restore at 2021:11:24 13:21:17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/dg/control01.ctl
Finished restore at 2021:11:24 13:21:19
mount数据库删除原控制文件中的垃圾信息
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> delete force archivelog all;
Starting implicit crosscheck backup at 2021:11:24 13:22:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 2021:11:24 13:22:21
Starting implicit crosscheck copy at 2021:11:24 13:22:21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2021:11:24 13:22:21
searching for all files in the recovery area
cataloging files…
no files cataloged
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 device type=DISK
List of Archived Log Copies for database with db_unique_name DG
Key Thrd Seq S Low Time
2 1 4 A 2019:09:25 19:26:06
Name: /oradata/fast_recovery_area/DG/archivelog/2021_11_24/o1_mf_1_4_jsvm075z_.arc
1 1 5 A 2021:11:24 13:07:27
Name: /oradata/fast_recovery_area/DG/archivelog/2021_11_24/o1_mf_1_5_jsvm075t_.arc
3 1 6 A 2021:11:24 13:08:29
Name: /oradata/fast_recovery_area/DG/archivelog/2021_11_24/o1_mf_1_6_jsvm0cs2_.arc
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/oradata/fast_recovery_area/DG/archivelog/2021_11_24/o1_mf_1_4_jsvm075z_.arc RECID=2 STAMP=1089465735
deleted archived log
archived log file name=/oradata/fast_recovery_area/DG/archivelog/2021_11_24/o1_mf_1_5_jsvm075t_.arc RECID=1 STAMP=1089465735
deleted archived log
archived log file name=/oradata/fast_recovery_area/DG/archivelog/2021_11_24/o1_mf_1_6_jsvm0cs2_.arc RECID=3 STAMP=1089465739
Deleted 3 objects
RMAN> delete force backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
1 1 1 1 EXPIRED DISK /beifen/sy.bak
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/beifen/sy.bak RECID=1 STAMP=1019935992
Deleted 1 objects
将备份目录中的备份文件纳入控制文件
RMAN> catalog start with ‘/sy/’;
searching for all files that match the pattern /sy/
File Name: /sy/sy.log
File Name: /sy/dbf_full_4.bak
File Name: /sy/dbf_full_3.bak
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done
File Name: /sy/sy.log
File Name: /sy/dbf_full_4.bak
File Name: /sy/dbf_full_3.bak
还原数据库,只读打开,实时应用日志
RMAN> run{
2> allocate channel ch1 type disk;
3> allocate channel ch2 type disk;
4> set newname for datafile 1 to ‘/oradata/dg/datafile/system01.dbf’;
5> set newname for datafile 2 to ‘/oradata/dg/datafile/sysaux01.dbf’;
6> set newname for datafile 3 to ‘/oradata/dg/datafile/undotbs01.dbf’;
7> set newname for datafile 4 to ‘/oradata/dg/datafile/users01.dbf’;
8> set newname for datafile 5 to ‘/oradata/dg/datafile/example01.dbf’;
9> set newname for datafile 6 to ‘/oradata/dg/datafile/users02.dbf’;
10> set newname for datafile 7 to ‘/oradata/dg/datafile/users03.dbf’;
11> set newname for datafile 8 to ‘/oradata/dg/datafile/users04.dbf’;
12> set newname for datafile 9 to ‘/oradata/dg/datafile/users05.dbf’;
13> set newname for tempfile 1 to ‘/oradata/dg/tempfile/temp01.dbf’;
14> restore database;
15> switch tempfile all;
16> switch datafile all;
17> release channel ch1;
18> release channel ch2;
19> }
released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=58 device type=DISK
allocated channel: ch2
channel ch2: SID=60 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
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 2021:11:24 14:15:58
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /oradata/dg/datafile/system01.dbf
channel ch1: restoring datafile 00002 to /oradata/dg/datafile/sysaux01.dbf
channel ch1: restoring datafile 00003 to /oradata/dg/datafile/undotbs01.dbf
channel ch1: restoring datafile 00004 to /oradata/dg/datafile/users01.dbf
channel ch1: restoring datafile 00005 to /oradata/dg/datafile/example01.dbf
channel ch1: restoring datafile 00006 to /oradata/dg/datafile/users02.dbf
channel ch1: restoring datafile 00007 to /oradata/dg/datafile/users03.dbf
channel ch1: restoring datafile 00008 to /oradata/dg/datafile/users04.dbf
channel ch1: restoring datafile 00009 to /oradata/dg/datafile/users05.dbf
channel ch1: reading from backup piece /sy/dbf_full_3.bak
channel ch1: piece handle=/sy/dbf_full_3.bak tag=TAG20211124T130559
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
Finished restore at 2021:11:24 14:16:05
renamed tempfile 1 to /oradata/dg/tempfile/temp01.dbf in control file
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=1089468965 file name=/oradata/dg/datafile/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=1089468965 file name=/oradata/dg/datafile/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=1089468965 file name=/oradata/dg/datafile/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=1089468965 file name=/oradata/dg/datafile/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=1089468965 file name=/oradata/dg/datafile/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=1089468965 file name=/oradata/dg/datafile/users02.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=1089468965 file name=/oradata/dg/datafile/users03.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=9 STAMP=1089468965 file name=/oradata/dg/datafile/users04.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=1089468965 file name=/oradata/dg/datafile/users05.dbf
released channel: ch1
released channel: ch2
RMAN>
————————————————
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
原文链接:https://blog.csdn.net/IIOOHHHSSSS/article/details/121511077