ORACLE10G 物理DATAGUARD 配置笔记

ORACLE10G DATAGUARD配置笔记
一:Oracle DataGuard环境概述
OS: CentOS5 + ORACLE10G 10.2.0.4.0
IP:192.168.100.208(primary) 192.168.8.201(standby)
ORACLE_SID:jscn
ORACLE_HOME:/oracle/ora10/product

二,主数据库做准备
1.修改primary dba 为归档模式
SQL> alter system set log_archive_dest_1='location=/oraarch/' scope=spfile;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
2.将 primary 数据库置为 FORCE LOGGING 模式
SQL> alter database force logging;
3.检查主机是否有口令文件,如没有需建立
orapwd file='/oracle/ora10/product/dbs/orapwjscn' password=sys
4.为主数据库添加"备用联机日志文件"
SQL> alter database add standby logfile group 4 ('/oracle/ora10/oradata/jscn/redo04.log') size 50m;
SQL> alter database add standby logfile group 5 ('/oracle/ora10/oradata/jscn/redo05.log') size 50m;
SQL> alter database add standby logfile group 6 ('/oracle/ora10/oradata/jscn/redo06.log') size 50m;
SQL> alter database add standby logfile group 7 ('/oracle/ora10/oradata/jscn/redo07.log') size 50m;
5.修改主库参数文件
SQL> create pfile='/home/oracle/init_pr.ora' from spfile;
[oracle@10-208 jscn]$ vim /home/oracle/init_pr.ora
*********************************分割线开始***************************************
jscn.__db_cache_size=515899392
jscn.__java_pool_size=4194304
jscn.__large_pool_size=4194304
jscn.__shared_pool_size=536870912
jscn.__streams_pool_size=0
*.audit_file_dest='/oracle/ora10/admin/jscn/adump'
*.background_dump_dest='/oracle/ora10/admin/jscn/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oracle/ora10/oradata/jscn/control01.ctl','/oracle/ora10/oradata/jscn/control02.ctl','/oracle/ora10/oradata/jscn/control03.ctl'
*.core_dump_dest='/oracle/ora10/admin/jscn/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='jscn'
*.db_recovery_file_dest='/oracle/ora10/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jscnXDB)'
*.job_queue_processes=10
#*.log_archive_dest_1='location=/oraarch/'
*.open_cursors=300
*.pga_aggregate_target=2684354560
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_max_size=1073741824
*.sga_target=1073741824
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/ora10/admin/jscn/udump'

###add below parameter for standy database
*.DB_UNIQUE_NAME='10gpri'
*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)'
*.log_archive_dest_1='location=/oraarch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gpri'
*.log_archive_dest_2='SERVICE=10gstandby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='10gstandby'
*.FAL_CLIENT='10gpri'

*********************************分割线结束***************************************
6.主库用PFILE建立SPFILE
[oracle@host160 pfile]$ sqlplus / as sysdba
SQL> create spfile from pfile='/home/oracle/init_pr.ora';
7.生成数据库备份
[oracle@10-208 ~]$ mkdir /oracle/rmanback/
RMAN> backup database format='/oracle/rmanback/%d_%s.dbf' plus archivelog;  
8.建立备用库的控制文件
SQL> alter database create standby controlfile as '/oracle/rmanback/standby_ctl01.ctl';
9.配置主数据库listener及tnsnames
[oracle@10-208 admin]$ vim listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/ora10/product)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10-208)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
 
[oracle@10-208 admin]$ cat tnsnames.ora
10gpri =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.208)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = 10gpri)
    )
  )

10gstandby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.201)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = 10gstandby)
    )
  )

三、standby数据库
1.安装软件，不创建数据库
2.复制文件到备机
首先，现在standby数据库上面创建目录
[oracle@IM-8-201 oracle]$ mkdir -p /oracle/ora10/
[oracle@IM-8-201 ora10]$ mkdir -p admin  flash_recovery_area  oradata  oraInventory  product
[oracle@IM-8-201 oracle]$ mkdir -p /oracle/ora10/admin/jscn
[oracle@IM-8-201 jscn]$ cd /oracle/ora10/admin/jscn/
[oracle@IM-8-201 jscn]$ mkdir -p adump  bdump  cdump  dpdump  pfile  scripts  udump
其次，拷贝备份文件到standby数据库
[oracle@10-208 ~]$ cd /oracle/rmanback/
[oracle@10-208 jscn]$ scp *.dbf 192.168.8.201:/oracle/rmanback/  
[oracle@10-208 jscn]$ scp *.ctl 192.168.8.201:/oracle/ora10/oradata/jscn
[oracle@10-208 ~]$ scp /tmp/standby_ctl01.ctl 192.168.8.201:/oracle/ora10/oradata/jscn
[oracle@IM-8-201 jscn]$ mv standby_ctl01.ctl control01.ctl
[oracle@IM-8-201 jscn]$ cp control01.ctl control02.ctl
[oracle@IM-8-201 jscn]$ cp control01.ctl control03.ctl
3.复制并修改备机的参数文件
[oracle@10-208 ~]$ scp init_pr.ora  192.168.8.201:/oracle/ora10/admin/jscn/pfile/init_dg.ora
修改为如下：
[oracle@IM-8-201 pfile]$ vim init_pr.ora
*******************************************分割线*******************************************
jscn.__db_cache_size=515899392
jscn.__java_pool_size=4194304
jscn.__large_pool_size=4194304
jscn.__shared_pool_size=536870912
jscn.__streams_pool_size=0
*.audit_file_dest='/oracle/ora10/admin/jscn/adump'
*.background_dump_dest='/oracle/ora10/admin/jscn/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oracle/ora10/oradata/jscn/control01.ctl','/oracle/ora10/oradata/jscn/control02.ctl','/oracle/ora10/oradata/jscn/control03.ctl'
*.core_dump_dest='/oracle/ora10/admin/jscn/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='jscn'
*.db_recovery_file_dest='/oracle/ora10/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jscnXDB)'
*.job_queue_processes=10
#*.log_archive_dest_1='location=/oraarch/'
*.open_cursors=300
*.pga_aggregate_target=2684354560
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_max_size=1073741824
*.sga_target=1073741824
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/ora10/admin/jscn/udump'

###add below parameter for standy database
*.DB_UNIQUE_NAME='10gstandby'         #############here
*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)'
*.log_archive_dest_1='location=/oraarch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gstandby'
*.log_archive_dest_2='SERVICE=10gpri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gpri'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='10gpri'
*.FAL_CLIENT='10gstandby'
*******************************************分割线*******************************************
4.生成备用库密码文件
[oracle@IM-8-201 dbs]$ orapwd file='/oracle/ora10/product/dbs/orawjscn.ora' password=sys
5.修改备机的listener及tnsnames
[oracle@10-208 admin]$ vim listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/ora10/product)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = IM-8-201)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

[oracle@IM-8-201 admin]$ vim  tnsnames.ora
10gpri =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.208)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = 10gpri)
    )
  )

10gstandby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.201)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = 10gstandby)
    )
  )

6.测试主备之间网络连通
[oracle@10-208 admin]$ lsnrctl start
[oracle@10-208 admin]$ tnsping 10gstandby
[oracle@IM-8-201 admin]$ lsnrctl start
[oracle@IM-8-201 admin]$ tnsping 10gpri
7.利用备用的控制文件,把备用数据库启到mount
SQL> startup nomount pfile='/home/oracle/init_dg.ora';
SQL> alter database mount standby database ;
8.转储数据库
RMAN>connect target /  
connected to target database：TEST(DBID=788075692)  
RMAN> restore database  ;
9.恢复数据库。
SQL>recover managed standby database disconnect from session;
#如果有需要应用的日志并想手工应用，可以运行如下命令  
SQL>recover automatic standby database;
10.打开备库
SQL> startup mount pfile='/oracle/ora10/admin/jscn/pfile/init_dg.ora';
SQL> create spfile from pfile='/oracle/ora10/admin/jscn/pfile/init_dg.ora';
SQL> alter database recover managed standby database disconnect from session;
11.打开主库
SQL> startup
12.测试是否OK
主库：
SQL> alter system switch logfile;
从库：
SQL> select FIRST_TIME,NEXT_TIME, APPLIED,SEQUENCE# from v$archived_log order by SEQUENCE#;
13. 以spfile启动并设为只读
SQL> create spfile from pfile;  
SQL> shutdown immediate
SQL> startup mount  
SQL> alter database recover managed standby database disconnect from session;  
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
14.应用日志
SQL> recover managed standby database;

四、角色切换
1. 把主库上的redo日志全部考到从库上去
2. 查看主库switchover 状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
附： A:switchover_status出现session active/not allowed
当出现session active的时候表示还有活动的session,则运行
Alter database commit to switchover to physical standby with session shutdown;
当出现not allowed时，在官方文档说转换会不成功，但是我测试的时候成功了。
SQL> alter database recover managed standby database disconnect from session;
3. 查看主库switchover 状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database commit to switchover to primary;
附：若不是，用此语句切换：
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown
Database altered.