1.数据库环境介绍
1)主库(源数据库)：
Oracle 11G R2
IP：192.168.4.105
HOSTNAME：oadb
SID:oa
ORACLE_BASE：/u01/app/oracle
ORACLE_HOME：/u01/app/oracle/product/11.2.0/dbhome_1
2)备库(目标数据库)：
Oracle 11g R2
IP：192.168.4.106
HOSTNAME：oadbstandby
SID:oa
ORACLE_BASE：/u01/app/oracle
ORACLE_HOME：/u01/app/oracle/product/11.2.0/dbhome_1
2.配置db_unique_name
1)配置主库db_unique_name
SQL> show parameter db_unique_name;
NAME TYPE VALUE
db_unique_name string oa
注：主库db_unique_name这里不作修改，保持生产环境不受影响
2)配置备库db_unique_name
SQL> show parameter db_unique_name;
NAME TYPE VALUE
db_unique_name string oa
SQL> alter system set db_unique_name=standbydb scope=spfile;
System altered
注：配置db_unique_name，需要重启数据库才能生效
3.主库配置
1)配置网络服务名
[oracle@oadb ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@oadb admin]$ vi tnsnames.ora
OA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.105)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oa)
)
)
STANDBYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.106)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standbydb)
)
)
2)配置监听，添加静态注册
[root@oadb admin]# pwd
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[root@oadb admin]# vi listener.ora
listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = oa)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = oa)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = oadb)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
3)将主库修改为forcelogging模式
SQL> select name,open_mode,log_mode,force_logging from gv$database;
NAME OPEN_MODE LOG_MODE FOR
OA READ WRITE ARCHIVELOG NO
SQL> alter database force logging;
Database altered.
SQL> select name,open_mode,log_mode,force_logging from gv$database;
NAME OPEN_MODE LOG_MODE FOR
OA READ WRITE ARCHIVELOG YES
4)设置参数
a)设置所有启用的数据库名
SQL> show parameter log_archive_config;
NAME TYPE VALUE
log_archive_config string
SQL> alter system set log_archive_config='dg_config=(oa,standbydb)';
System altered.
SQL> show parameter log_archive_config;
NAME TYPE VALUE
log_archive_config string dg_config=(oa,standbydb)
注：dg_config为主备库的db_unique_name
b)开启归档模式
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes
Variable Size 310381648 bytes
Database Buffers 92274688 bytes
Redo Buffers 4284416 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
c)设置归档日志的路径(本地和网络)
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/OA/archivelog/ optional';
System altered.
SQL> alter system set log_archive_dest_2='service=standbydb lgwr sync affirm valid_for=(online_logfile,primary_role) db_unique_name=standbydb';
System altered.
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
log_archive_dest1 string LOCATION=/u01/app/oracle/fast
recovery_area/OA/archivelog/ o
ptional
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
NAME TYPE VALUE
log_archive_dest_18 string
log_archive_dest_19 string
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
log_archive_dest_2 string service=standbydb lgwr sync af
firm valid_for=(online_logfile
,primary_role) db_unique_name=
standbydb
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
NAME TYPE VALUE
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
注：service值为备用数据库的TNSNAMES描述符
d)设置备库文件自动管理,可以在主库新建数据文件的时候自动新建备库
SQL> show parameter standby_file_management;
NAME TYPE VALUE
standby_file_management string MANUAL
SQL> alter system set standby_file_management='AUTO';
System altered.
SQL> show parameter standby_file_management;
NAME TYPE VALUE
standby_file_management string AUTO
e)设置FAL_SERVER为备库Oracle Net service name,
SQL> show parameter fal_server;
NAME TYPE VALUE
fal_server string
SQL> alter system set fal_server='standbydb';
System altered.
SQL> show parameter fal_server;
NAME TYPE VALUE
fal_server string standbydb
f)创建standby redolog
SQL> alter database add standby logfile group 11 ('/u01/app/oracle/oradata/oa/standby11.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 12 ('/u01/app/oracle/oradata/oa/standby12.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 13 ('/u01/app/oracle/oradata/oa/standby13.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 14 ('/u01/app/oracle/oradata/oa/standby14.log') size 50m;
Database altered.
注1：由于备库后续会使用主库RMAN还原，这里无需在备库存创建，等备库使用RMAN还原后，再创建standby redolog
注2：建立standby如要注意以下几点：
a)standby log files的大小和redo log files一样。 查询redo log files文件大小(默认50M,3个)：select group#,bytes/1024/1024 as M from v$log;
b)一般而言， standbyredo 日志文件组数要比 primary 数据库的 online redo 日志文件组数至少多一个。 有一个推荐的公式可以做参考：(每线程的日志组数+1)最大线程数 假设现在节点是1个，则=(3+1)1=4
如果是双节点 则=(3+1)*2=8
c)不建议组号group#紧挨着redo，因为后续redo有可能调整
c)不建议组号group#紧挨着redo，因为后续redo有可能调整
4.备库配置
1)配置网络服务名
[oracle@oadbstandby ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@oadbstandby admin]$ vi tnsnames.ora
OA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.105)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oa)
)
)
STANDBYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.106)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standbydb)
)
)
2)配置监听，添加静态注册
[root@oadbstandby admin]# pwd
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[root@oadbstandby admin]# vi listener.ora
listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = oa)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = oa)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = oadbstandby)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
3)将备库修改为forcelogging模式
SQL> select name,open_mode,log_mode,force_logging from gv$database;
NAME OPEN_MODE LOG_MODE FOR
OA READ WRITE ARCHIVELOG NO
SQL> alter database force logging;
Database altered.
SQL> select name,open_mode,log_mode,force_logging from gv$database;
NAME OPEN_MODE LOG_MODE FOR
OA READ WRITE ARCHIVELOG YES
4)设置参数
a)开启归档模式
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes
Variable Size 310381648 bytes
Database Buffers 92274688 bytes
Redo Buffers 4284416 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
b)设置所有的启用的数据库名
SQL> show parameter log_archive_config;
NAME TYPE VALUE
log_archive_config string
SQL> alter system set log_archive_config='dg_config=(oa,standbydb)';
System altered.
SQL> show parameter log_archive_config;
NAME TYPE VALUE
log_archive_config string dg_config=(oa,standbydb)
注：dg_config为主备库的db_unique_name
c)设置归档日志的路径(本地和网络)
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/OA/archivelog/ optional';
System altered.
SQL> alter system set log_archive_dest_2='service=oa lgwr sync affirm valid_for=(online_logfile,primary_role) db_unique_name=oa';
System altered.
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
log_archive_dest1 string LOCATION=/u01/app/oracle/fast
recovery_area/OA/archivelog/ o
ptional
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
NAME TYPE VALUE
log_archive_dest_18 string
log_archive_dest_19 string
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
log_archive_dest_2 string service=oa lgwr sync affirm va
lid_for=(online_logfile,primar
y_role) db_unique_name=oa
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
NAME TYPE VALUE
log_archive_dest_28 string
log_archive_dest_29 string
注： 目的，当备库转换为主库的时候把重做日志写到新备库
service值为备用数据库的TNSNAMES描述符
d)设置备库文件自动管理,可以在主库新建数据文件的时候自动新建备库
SQL> show parameter standby_file_management;
NAME TYPE VALUE
standby_file_management string MANUAL
SQL> alter system set standby_file_management='AUTO';
System altered.
SQL> show parameter standby_file_management;
NAME TYPE VALUE
standby_file_management string AUTO
e)设置FAL_SERVER为备库Oracle Net service name,
SQL> show parameter fal_server;
NAME TYPE VALUE
fal_server string
SQL> alter system set fal_server='oa';
System altered.
SQL> show parameter fal_server;
NAME TYPE VALUE
fal_server string oa
5.拷贝主库密码文件到备库
注：密码文件一般都在$ORACLE_HOME/dbs/下，名称为orapw实例名；
注意每个实例的实例名不一样,所以复制到对应的实例下时一定需要注意命名问题
6.使用RMAN备份主库
备份脚本：
rman target / log=/backup/rmanbackup/2018-05-11/rman_oa2018-05-11.log <
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
change archivelog all crosscheck;
crosscheck backup;
delete noprompt expired backup;
delete noprompt archivelog until time '(sysdate-10)';
backup database format '/backup/rmanbackup/2018-05-11/db%U%I%T' TAG='standbydb';
sql 'alter system archive log current';
backup archivelog all not backed up format '/backup/rmanbackup/2018-05-11/arch%U%I%T' tag='standbyarch';
backup current controlfile format='/backup/rmanbackup/2018-05-11/ctl%U%I%T' tag='standbyctl';
backup spfile format='/backup/rmanbackup/2018-05-11/spfile%U%I%T' tag='standby_spfile';
release channel c1;
release channel c2;
}
EOF
7.备库还原(使用主库RMAN备份文件还原)
1)授权从主库拷贝到备库的RMAN备份文件
[root@oadbstandby rmanbackup]# chown -R oracle:oinstall 2018-05-11
2)登录到备库，启动备库到nomount状态
[root@oadbstandby /]# su - oracle
[oracle@oadbstandby ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 11 10:56:14 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: OA (DBID=3393907898)
RMAN> startup nomount force;
Oracle instance started
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes
Variable Size 314575952 bytes
Database Buffers 88080384 bytes
Redo Buffers 4284416 bytes
RMAN>
3)将备库dbid设置和主库dbid相同
RMAN> set dbid 3393850737;
executing command: SET DBID
4)还原控制文件
RMAN> restore standby controlfile from '/backup/rmanbackup/2018-05-11/ctl_0at2k9ev_1_1_3393850737_20180511';
Starting restore at 11-MAY-18
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:03
output file name=/u01/app/oracle/oradata/oa/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/oa/control02.ctl
Finished restore at 11-MAY-18
5)启动数据库到mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
6)注册从源数据库拷贝过来的备份集到rman中
RMAN> catalog start with '/backup/rmanbackup/2018-05-11/';
Starting implicit crosscheck backup at 11-MAY-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 11-MAY-18
Starting implicit crosscheck copy at 11-MAY-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 11-MAY-18
searching for all files in the recovery area
cataloging files...
cataloging done
=======================
List of Cataloged Files
File Name: /u01/app/oracle/fast_recovery_area/STANDBYDB/archivelog/2018_05_11/o1_mf_1_8fh9zs7mh.arc
File Name: /u01/app/oracle/fast_recovery_area/STANDBYDB/archivelog/2018_05_11/o1_mf_1_6fh9znlgg.arc
File Name: /u01/app/oracle/fast_recovery_area/STANDBYDB/archivelog/2018_05_11/o1_mf_1_7fh9zpxw5.arc
searching for all files that match the pattern /backup/rmanbackup/2018-05-11/
List of Files Unknown to the Database
File Name: /backup/rmanbackup/2018-05-11/spfile_0bt2k9f1_1_1_3393850737_20180511
File Name: /backup/rmanbackup/2018-05-11/rman_oa_2018-05-11.log
File Name: /backup/rmanbackup/2018-05-11/ctl_0at2k9ev_1_1_3393850737_20180511
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
=======================
List of Cataloged Files
File Name: /backup/rmanbackup/2018-05-11/spfile_0bt2k9f1_1_1_3393850737_20180511
File Name: /backup/rmanbackup/2018-05-11/ctl_0at2k9ev_1_1_3393850737_20180511
=======================================
List of Files Which Where Not Cataloged
File Name: /backup/rmanbackup/2018-05-11/rman_oa_2018-05-11.log
RMAN-07517: Reason: The file header is corrupted
7)还原数据文件
RMAN> run {
set newname for datafile 1 to '/u01/app/oracle/oradata/oa/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/oa/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/oa/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/oa/users01.dbf';
restore database;
switch datafile all;
}6> 7> 8>
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 11-MAY-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/oa/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/oa/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /backup/rmanbackup/2018-05-11/db_04t2k9d9_1_1_3393850737_20180511
channel ORA_DISK_1: piece handle=/backup/rmanbackup/2018-05-11/db_04t2k9d9_1_1_3393850737_20180511 tag=STANDBY_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/oa/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/oa/users01.dbf
channel ORA_DISK_1: reading from backup piece /backup/rmanbackup/2018-05-11/db_03t2k9d9_1_1_3393850737_20180511
channel ORA_DISK_1: piece handle=/backup/rmanbackup/2018-05-11/db_03t2k9d9_1_1_3393850737_20180511 tag=STANDBY_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 11-MAY-18
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=975841382 file name=/u01/app/oracle/oradata/oa/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=975841382 file name=/u01/app/oracle/oradata/oa/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=975841382 file name=/u01/app/oracle/oradata/oa/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=975841382 file name=/u01/app/oracle/oradata/oa/users01.dbf
8)恢复数据库
RMAN> recover database;
Starting recover at 11-MAY-18
using channel ORA_DISK_1
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=8
channel ORA_DISK_1: reading from backup piece /backup/rmanbackup/2018-05-11/arch_08t2k9es_1_1_3393850737_20180511
channel ORA_DISK_1: piece handle=/backup/rmanbackup/2018-05-11/arch_08t2k9es_1_1_3393850737_20180511 tag=STANDBY_ARCH
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/OA/archivelog/1_8_975777331.dbf thread=1 sequence=8
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_DISK_1: reading from backup piece /backup/rmanbackup/2018-05-11/arch_09t2k9eu_1_1_3393850737_20180511
channel ORA_DISK_1: piece handle=/backup/rmanbackup/2018-05-11/arch_09t2k9eu_1_1_3393850737_20180511 tag=STANDBY_ARCH
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/OA/archivelog/1_9_975777331.dbf thread=1 sequence=9
unable to find archived log
archived log thread=1 sequence=10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/11/2018 11:03:46
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1001269
8.创建standby redolog
SQL> alter database drop standby logfile group 11;
Database altered.
SQL> alter database drop standby logfile group 12;
Database altered.
SQL> alter database drop standby logfile group 13;
Database altered.
SQL> alter database drop standby logfile group 14;
Database altered.
SQL> alter database add standby logfile group 11 ('/data/oradata/standby11.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 12 ('/data/oradata/standby12.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 13 ('/data/oradata/standby13.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 14 ('/data/oradata/standby14.log') size 50m;
9.以只读方式打开standbydb
SQL> alter database open read only;
Database altered.
10.standbydb启动redo apply
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
8419
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
