1、
配置主库和备库信息
主库:
操作系统:oracle liunx 5.6
主机名:SZSCPDB
ip地址:192.168.20.10
oracle_sid:szsc
db_unqiue_name:szscpdb
监听名、端口:listener、1521
service_name:szscpdb
备库:
操作系统:oracle liunx 5.6
主机名:SZSCSTB
ip地址:192.168.20.11
oracle_sid:szsc
db_unqiue_name:szscstb
监听名、端口:listener、1521
service_name:szscstb
2、
启动主库的监听:
lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 16-MAY-2013 03:42:01
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SZSCPDB)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 15-MAY-2013 21:02:28
Uptime 0 days 6 hr. 39 min. 33 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/products/11.2.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/SZSCPDB/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SZSCPDB)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "szscXDB" has 1 instance(s).
Instance "szsc", status READY, has 1 handler(s) for this service...
Service "szscpdb" has 2 instance(s).
Instance "szsc", status UNKNOWN, has 1 handler(s) for this service...
Instance "szsc", status READY, has 1 handler(s) for this service...
The command completed successfully
3、
查看数据库是否归档:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /RECO/arch/szsc/
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
4、
更改主库为force logging
SQL> alter database force logging;
Database altered.
5、
创建主库的密码文件
cd /u01/app/oracle/products/11.2.0/dbs/
orapwd file=$ORACLE_HOME/dbs/orapwszsc password=oracle entries=30;
6、
修改主库监听及tns文件
# listener.ora Network Configuration File: /u01/app/oracle/products/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = szscpdb)
(ORACLE_HOME = /u01/app/oracle/products/11.2.0)
(SID_NAME = szsc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SZSCPDB)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/products/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SZSCPDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SZSCPDB)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = szscpdb)
)
)
SZSCSTB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SZSCSTB)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = szscstb)
)
)
7、
修改主库的参数文件
*.audit_file_dest='/u01/app/oracle/admin/szscpdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/szscpdb/control01.ctl','/u01/app/oracle/oradata/szscpdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='szscpdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=szscXDB)'
*.log_archive_config='dg_config=(szscpdb,szscstb)'
*.log_archive_dest_1='LOCATION=/RECO/arch/szsc/ LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=szscpdb'
*.log_archive_dest_2='SERVICE=szscstb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=szscstb'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/szscstb/','/u01/app/oracle/oradata/szscpdb/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/szscstb/','/u01/app/oracle/oradata/szscpdb/'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.standby_file_management='AUTO'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
fal_server=szscstb
fal_client=szscpdb
db_unique_name=szscpdb
8、
传输参数文件,密码文件,tns文件到备库
cd /u01/app/oracle/products/11.2.0/dbs
scp initszsc.ora orapwszsc SZSCSTB:$ORACLE_HOME/dbs
scp orapwszsc SZSCSTB:/u01/app/oracle/products/11.2.0/dbs
cd /u01/app/oracle/products/11.2.0/network/admin/
scp tnsnames.ora SZSCSTB:$ORACLE_HOME/network/admin
9、
修改备库的参数文件
*.audit_file_dest='/u01/app/oracle/admin/szscstb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/szscstb/control01.ctl','/u01/app/oracle/oradata/szscstb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='szscpdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=szscXDB)'
*.log_archive_config='dg_config=(szscpdb,szscstb)'
*.log_archive_dest_1='LOCATION=/RECO/arch/szsc/ LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=szscstb'
*.log_archive_dest_2='SERVICE=szscpdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=szscpdb'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/szscpdb/','/u01/app/oracle/oradata/szscstb/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/szscpdb/','/u01/app/oracle/oradata/szscstb/'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.standby_file_management='AUTO'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
fal_server=szscpdb
fal_client=szscstb
db_unique_name=szscstb
10、
建立参数文件相关的目录
audit_file_dest
mkdir -p /u01/app/oracle/admin/szscstb/adump/
control_files
Mkdir -p '/u01/app/oracle/oradata/szscstb
11、
做一个主库的全备
mkdir -p /u01/BACKUP/
chown -R oracle:oinstall /u01/BACKUP/
su - oracle
rman target /
RMAN>backup device type disk format '/u01/BACKUP/%U' database plus archivelog;
12、
传输备份到备库
scp u01/backup/* SZSCSTB:/u01/BACKUP/
13、
备库启动到nomount
su - oracle
sqlplus / as sysdba
SQL> startup nomount;
14、
主库 duplicate target database for standby;
在主库上运行
su - oracle
rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Mar 8 02:41:18 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SZSCPDB (DBID=850224774)
RMAN> connect auxiliary sys/oracle@SZSCSTB
connected to auxiliary database: SZSCPDB (not mounted)
RMAN> duplicate target database for standby;
Starting Duplicate Db at 08-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script.:
{
restore clone standby controlfile;
}
executing Memory Script
Starting restore at 08-MAR-13
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /BACKUP/0lo3vm4i_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/BACKUP/0lo3vm4i_1_1 tag=TAG20130308T022752
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/szscstb/control01.ctl
output file name=/u01/app/oracle/oradata/szscstb/control02.ctl
Finished restore at 08-MAR-13
contents of Memory Script.:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script.:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/szscstb/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/szscstb/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/szscstb/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/szscstb/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/szscstb/users01.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/szscstb/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 08-MAR-13
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to //u01/app/oracle/oradata/szscstb/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/szscstb/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/szscstb/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/szscstb/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/BACKUP/0ko3vm18_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/BACKUP/0ko3vm18_1_1 tag=TAG20130308T022752
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 08-MAR-13
contents of Memory Script.:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=809491977 file name=/u01/app/oracle/oradata/szscstb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=809491977 file name=/u01/app/oracle/oradata/szscstb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=809491977 file name=/u01/app/oracle/oradata/szscstb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=809491977 file name=/u01/app/oracle/oradata/szscstb/users01.dbf
Finished Duplicate Db at 08-MAR-13
15、
执行备库恢复模式
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
szsc MOUNTED
SQL> alter database recover managed standby database disconnect from session;
Database altered.
16、
查看日志同步情况
SQL> SELECT SEQUENCE#, REGISTRAR, FIRST_TIME, NEXT_TIME, APPLIED
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
17、
创建STANDBY LOGFILE
主库:
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscpdb/sredo01.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscpdb/sredo02.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscpdb/sredo03.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscpdb/sredo04.log' size 512M;
备库:
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscstb/sredo01.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscstb/sredo02.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscstb/sredo03.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscstb/sredo04.log' size 512M;
当做完上面的添加standby logfile的时候就可以执行下面的(备库上执行)
alter database recover managed standby database disconnect from session;
现在DG就已经配置完成了,可以查看两边的数据库情况。
switchover:
1、查询主库的switchover状态
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
注意:当是to standby或session active的时候可以切换
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY ;
2、关闭szscpdb也就是旧的主库,启动到mount状态
SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT;
3、在备库上执行
alter database recover managed standby database disconnect from session;
然后在执行
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
当查询的值为TO PRIMARY的时候可以切换
4、在原来的备库上执行(szscstb)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [WITH SESSION SHUTDOWN];
注意:WITH SESSION SHUTDOWN子句可以省略,当之前查询的结果为TO PRIMARYAD的时候
5、打开新的主库也就是原来的备库(szscstb)
alter database open;
select switchover_status from v$database;
这时候出现的结果应该是to standby
6、现在你在原来的主库(szscpdb)也就是现在的备库上查询
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
注意:出现这种情况就代表你切换成功了,就是原来的主库变成了物理备库,原来的备库变成了主库
7、现在验证切换成功,看看你新的主库的日志是不是能传到备库上
insert into t values(2);
commit;
alter system switch logfile; (注意监测两边的alert日志)
现在:
在新的主库(原来的备库)上看归档日志序列
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /RECO/arch/szsc/
Oldest online log sequence 56
Next log sequence to archive 58
Current log sequence 58
在新的备库上(原来的主库上)看
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /RECO/arch/szsc/
Oldest online log sequence 56
Next log sequence to archive 0
Current log sequence 58
注意:现在已经证明了新主库上的日志已经可以传到新的备库上了
8、在新的备库上应用你传过来的日志
SQL> alter database recover managed standby database disconnect from session;
Database altered.
或者
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
9、查看表数据
SQL> select * from t;
ID
----------
1
2
成功完成了主备库的switchover
10、查看主备库的角色信息
在新主库上:
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
在新的备库上:
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
如果备库还有没有应用的日志,那么就执行(这句话如果执行了就不用在执行)
alter database recover managed standby database disconnect from session;
查看备库的alert日志文件,会应用你的归档,如果发现它停在某个归档上,那么就是缺失这个归档,
这时候你需要将你主库的归档路径下的归档日志传到备库上,备库会自动应用的
当你的归档全部应用完成之后,就可以执行
alter database recover managed standby database disconnect from session;
或者执行:
alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database cancel;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29107230/viewspace-769012/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29107230/viewspace-769012/