primary环境信息
IP : 192.168.152.10
ORACLE_SID=PROD
数据库版本 10.2.0.1
操作系统 Red Hat Enterprise Linux Server release 5.9 (Tikanga) 32位
standby环境信息
IP : 192.168.152.20
ORACLE_SID=OCM3
数据库版本 10.2.0.1
操作系统 Red Hat Enterprise Linux Server release 5.9 (Tikanga) 32位
一、primary database 配置
1)检查数据库是否启用forced logging模式
SYS@PROD>select name,LOG_MODE,OPEN_MODE,FORCE_LOGGING from v$database;
NAME LOG_MODE OPEN_MODE FOR
--------- ------------ ---------- ---
PROD ARCHIVELOG READ WRITE NO
启动forced logging模式
SYS@PROD>startup mount
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 188743904 bytes
Database Buffers 331350016 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS@PROD>alter database force logging;
Database altered.
SYS@PROD>alter database open;
Database altered.
SYS@PROD>select name,LOG_MODE,OPEN_MODE,FORCE_LOGGING from v$database;
NAME LOG_MODE OPEN_MODE FOR
--------- ------------ ---------- ---
PROD ARCHIVELOG READ WRITE YES
2)创建密码文件。检查发现密码文件orapw 文件存在。
[oracle@ocm1 ~]$ cd $ORACLE_HOME/dbs
[oracle@ocm1 dbs]$ ls
hc_PROD.dat initdw.ora init.ora initPROD.ora lkPROD orapwPROD snapcf_PROD.f spfilePROD.ora
这里为了试验,将其删除重建
[oracle@ocm1 dbs]$ rm orapwPROD
[oracle@ocm1 dbs]$ orapwd file=orapwPROD password=oracle entries=30 force=y
3)配置standby redo log
standby redo log是最大保护模式、最大可用模式和日志传输模式所要求使用的。
standby redo log 文件大小要与online redo log文件大小一致
standby redo log groups 要比 online redo log groups 多1个
检查maxlogfiles和maxlogmembers参数值。确认添加的standby redo 不会超过这个值。
create standby redo log groups
检查online redo log信息。
THREAD# GROUP# SEQUENCE# ARC STATUS FILE_SIZEMB MEMBER
---------- ---------- ---------- --- ---------------- ----------- --------------------------------------------------
1 1 7 YES INACTIVE 100 /u01/app/oracle/oradata/PROD/disk1/redo01.log
1 1 7 YES INACTIVE 100 /u01/app/oracle/oradata/PROD/disk1/redo04.log
1 2 8 YES INACTIVE 100 /u01/app/oracle/oradata/PROD/disk1/redo05.log
1 2 8 YES INACTIVE 100 /u01/app/oracle/oradata/PROD/disk1/redo02.log
1 3 9 NO CURRENT 100 /u01/app/oracle/oradata/PROD/disk1/redo06.log
1 3 9 NO CURRENT 100 /u01/app/oracle/oradata/PROD/disk1/redo03.log
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/PROD/disk1/standbylog4a.log','/u01/app/oracle/oradata/PROD/disk1/standbylog4b.log') size 100M;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/PROD/disk1/standbylog5a.log','/u01/app/oracle/oradata/PROD/disk1/standbylog5b.log') size 100M;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/PROD/disk1/standbylog6a.log','/u01/app/oracle/oradata/PROD/disk1/standbylog6b.log') size 100M;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/PROD/disk1/standbylog7a.log','/u01/app/oracle/oradata/PROD/disk1/standbylog7b.log') size 100M;
确认standby redo log已经被创建
SYS@PROD>select group#,thread#,sequence#,bytes/1024/1024 sizeMB,archived,status from v$standby_log;
GROUP# THREAD# SEQUENCE# SIZEMB ARC STATUS
---------- ---------- ---------- ---------- --- ----------
4 0 0 100 YES UNASSIGNED
5 0 0 100 YES UNASSIGNED
6 0 0 100 YES UNASSIGNED
7 0 0 100 YES UNASSIGNED
4)设置primary database参数文件
配置tnsnames.ora参数文件
PROD=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=PROD)
)
)
OCM3=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm2)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=OCM3)
)
)
主库角色参数
#DB_NAME=chicago
DB_UNIQUE_NAME=PROD
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,OCM3)'
#CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archlog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD'
LOG_ARCHIVE_DEST_2='SERVICE=OCM3 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OCM3'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
备库角色参数
FAL_SERVER=OCM3
FAL_CLIENT=PROD
DB_FILE_NAME_CONVERT='OCM3','PROD'
LOG_FILE_NAME_CONVERT='OCM3','PROD'
STANDBY_FILE_MANAGEMENT=AUTO
创建pfile文件,将上述参数在pfile上修改
SYS@PROD>create pfile='/home/oracle/pfile_20140304.ora' from spfile;
File created.
然后使用pfile重启数据库
SYS@PROD>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD>create spfile from pfile='/home/oracle/pfile_20140304.ora' ;
File created.
SYS@PROD>startup
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 192938208 bytes
Database Buffers 327155712 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
5)检查数据库是否启动在归档状态
SYS@PROD>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archlog
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
6)启动primary database 的监听
[oracle@ocm1 ~]$ lsnrctl start
二、创建physical standby database
1)对primary database进行备份,这里的备份可以采用冷备或者rman备份。
关闭数据库,对数据文件进行冷备
[oracle@ocm1 PROD]$ tar -zcvf primary_datafile.tar.gz disk1/
2)create a controlfile for the standby database
SYS@PROD>startup mount
SYS@PROD>alter database create standby controlfile as '/home/oracle/standby_controlfile.ctl';
SYS@PROD>alter database open;
3)创建standby database参数文件
SYS@PROD>create pfile='/home/oracle/standby_pfile.ora' from spfile;
需要修改的参数
*.control_files='/u01/app/oracle/oradata/OCM3/disk1/control01.ctl','/u01/app/oracle/oradata/OCM3/disk1/control02.ctl','/u01/app/oracle/oradata/OCM3/disk1/control03.ctl'#Restore Controlfile
*.DB_FILE_NAME_CONVERT='PROD','OCM3'
*.DB_UNIQUE_NAME='OCM3'
*.FAL_CLIENT='OCM3'
*.FAL_SERVER='PROD'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archlog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=OCM3'
*.LOG_ARCHIVE_DEST_2='SERVICE=PROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD'
*.LOG_FILE_NAME_CONVERT='PROD','OCM3'
4)从primary 系统将数据文件、控制文件、参数文件和密码文件拷贝到standby 系统
[oracle@ocm1 ~]$ scp standby_controlfile.ctl oracle@ocm2:~
[oracle@ocm1 ~]$ scp standby_pfile.ora oracle@ocm2:~
[oracle@ocm1 PROD]$ scp primary_datafile.tar.gz oracle@ocm2:/u01/app/oracle/oradata
[oracle@ocm1 dbs]$ scp orapwPROD oracle@ocm2:/u01/app/oracle/product/10.2.0/db_1/dbs
5)配置standby database环境
5.1)创建密码文件,我们这边的密码文件是从primary直接拷贝过来的,因此直接改名即可
[oracle@ocm2 dbs]$ mv orapwPROD orapwOCM3
5.2)创建相关路径
mkdir -p /u01/app/oracle/admin/OCM3/adump
mkdir -p /u01/app/oracle/admin/OCM3/bdump
mkdir -p /u01/app/oracle/admin/OCM3/cdump
mkdir -p /u01/app/oracle/admin/OCM3/udump
5.3)解压数据文件
[oracle@ocm2 ~]$ cd /u01/app/oracle/oradata
[oracle@ocm2 ~]$ tar -zxvf primary_datafile.tar.gz -C /u01/app/oracle/oradata/OCM3
5.4)生成备库控制文件
[oracle@ocm2 disk1]$ cd /u01/app/oracle/oradata/OCM3/disk1
[oracle@ocm2 disk1]$ rm *.ctl
[oracle@ocm2 ~]$ cp standby_controlfile.ctl /u01/app/oracle/oradata/OCM3/disk1/control01.ctl
[oracle@ocm2 ~]$ cp standby_controlfile.ctl /u01/app/oracle/oradata/OCM3/disk1/control02.ctl
[oracle@ocm2 ~]$ cp standby_controlfile.ctl /u01/app/oracle/oradata/OCM3/disk1/control03.ctl
5.5)创建参数文件
SYS@OCM3>create spfile from pfile='/home/oracle/standby_pfile.ora';
6)配置standby 监听
[oracle@ocm2 ~]$ cd $ORACLE_HOME/network/admin
[oracle@ocm2 admin]$ lsnrctl start
这时,最好来primary database的监听一起检查一下。
7)配置tnsnames.ora文件,这个primary和standby保持一致即可
PROD=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=PROD)
)
)
OCM3=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm2)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=OCM3)
)
)
8)启动physical standby database
SYS@OCM3>startup mount
SYS@OCM3>alter database recover managed standby database disconnect from session;
三、DG测试
在standby database:
SYS@OCM3>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- --------- --------- ---
1 13-JAN-14 13-JAN-14 NO
2 13-JAN-14 20-JAN-14 NO
3 20-JAN-14 22-JAN-14 NO
4 22-JAN-14 24-JAN-14 NO
5 24-JAN-14 28-JAN-14 NO
6 28-JAN-14 03-MAR-14 NO
7 03-MAR-14 03-MAR-14 NO
8 03-MAR-14 03-MAR-14 NO
9 03-MAR-14 04-MAR-14 YES
10 04-MAR-14 04-MAR-14 YES
10 rows selected.
在primary database:
SYS@PROD>alter system switch logfile;
System altered.
在standby database:
SYS@OCM3>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- --------- --------- ---
1 13-JAN-14 13-JAN-14 NO
2 13-JAN-14 20-JAN-14 NO
3 20-JAN-14 22-JAN-14 NO
4 22-JAN-14 24-JAN-14 NO
5 24-JAN-14 28-JAN-14 NO
6 28-JAN-14 03-MAR-14 NO
7 03-MAR-14 03-MAR-14 NO
8 03-MAR-14 03-MAR-14 NO
9 03-MAR-14 04-MAR-14 YES
10 04-MAR-14 04-MAR-14 YES
11 04-MAR-14 04-MAR-14 YES
11 rows selected.
从alert log 中
RFS[13]: Assigned to RFS process 17159
RFS[13]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Re-archiving standby log 4 thread 1 sequence 11
Primary database is in MAXIMUM PERFORMANCE mode
RFS[13]: Successfully opened standby log 5: '/u01/app/oracle/oradata/OCM3/disk1/standbylog5a.log'
Tue Mar 4 15:52:42 2014
Media Recovery Log /u01/app/oracle/archlog/1_11_836758036.arc
Media Recovery Waiting for thread 1 sequence 12 (in transit)
在primary database:
SYS@PROD>select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
在standby database:
SYS@OCM3>select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11590946/viewspace-1100877/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11590946/viewspace-1100877/