ORACLE 11G 配置dataguard物理备库

    本实验主要是测试在同一台 LINUX机器上配置dataguard的物理备库。这次实验的环境是主库在ASM, 备库准备创建在OS的文件系统上。
    下面是主库 和 备库的相关名称

parameter name	db_name	ORACLE_SID	db_unique_name	service_names
Primary database	orclpr	orclpr	orclpr	orclpr, orcl
Standby database	orclpr	orcldg	orcldg	orcldg

1、准备主数据库用于创建备用数据库
       1）查看主库是否已经开启归档模式
            SQL> archive log list;
            如果没有开启归档模式，则执行下面的语句
            SQL> shutdown immediate;
            SQL> startup mount;
            SQL> alter database archivelog;
            SQL> alter database open;
        2）查看数据库是否已经启用强日志模式
            SQL> select force_logging from v$database;        
            如果返回的是 “no”， 则执行下面的语句
            SQL> alter database force logging;

2、修改主库参数文件
        1）主库通过spfile 创建 pfile 文件
            SQL> create pfile from spfile;
        2）修改主库的参数pfile 文件（在$ORACLE_HOME/dbs/initorclpr.ora）

orclpr.__db_cache_size=423624704 orclpr.__java_pool_size=4194304 orclpr.__large_pool_size=4194304 orclpr.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orclpr.__pga_aggregate_target=209715200 orclpr.__sga_target=633339904 orclpr.__shared_io_pool_size=0 orclpr.__shared_pool_size=192937984 orclpr.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orclpr/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='+FRA/ORCLPR/control01.ctl','+FRA/ORCLPR/control02.ctl' *.db_block_size=8192 *.db_create_file_dest='+FRA' *.db_domain='' *.db_name='orclpr' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclprXDB)' *.open_cursors=300 *.pga_aggregate_target=209715200 *.processes=150 *.service_names='orclpr','orcl' *.sga_target=631242752 *.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=orclpr LOG_ARCHIVE_CONFIG='DG_CONFIG=(orclpr,orcldg)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area/ORCLPR/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclpr' LOG_ARCHIVE_DEST_2='SERVICE=orcldg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=orcldg DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcldg','+FRA/orclpr/datafile' LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcldg','+FRA/orclpr/onlinelog' STANDBY_FILE_MANAGEMENT=AUTO

3、创建主库主库的密码文件
        移除 $ORACLE_HOME/dbs 目录下面的orapworclpr文件（[oracle@LINUX10 dbs]$ rm -f orapworclpr），再重新创建密码文件
        [oracle@LINUX10 dbs]$ orapwd file=/u01/app/oracle/product/11.2.3/oracle/dbs/orapworclpr password='oracle' entries=20;
4、为备库创建控制文件

1. SQL> shutdow immediate;
   
2. SQL> startup mount;
   
3. SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/orcldg/control01.ctl';
   
4. SQL> alter database open;

5、文件拷贝
        由于这里是主库和备库都是公共使用的"$ORACLE_HOME/dbs" 目录，所以只需要在该目录进行参数文件和密码文件的拷贝即可
        1）拷贝参数文件
        [oracle@LINUX10 dbs]$ cp initorclpr.ora initorcldg.ora
        2）拷贝密码文件
        [oracle@LINUX10 dbs]$ cp orapworclpr orapworcldg
        3）由于控制文件拷贝
        [oracle@LINUX10 dbs]$ cp /u01/app/oracle/oradata/orcldg/control01.ctl /u01/app/oracle/oradata/orcldg/control02.ctl

6、修改备库的参数文件  initorcldg.ora

orclpr.__db_cache_size=423624704 orclpr.__java_pool_size=4194304 orclpr.__large_pool_size=4194304 orclpr.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orclpr.__pga_aggregate_target=209715200 orclpr.__sga_target=633339904 orclpr.__shared_io_pool_size=0 orclpr.__shared_pool_size=192937984 orclpr.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orclpr/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/orcldg/control01.ctl','/u01/app/oracle/oradata/orcldg/control02.ctl' *.db_block_size=8192 *.db_create_file_dest='/u01/app/oracle/oradata/orcldg' *.db_domain='' *.db_name='orclpr' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclprXDB)' *.open_cursors=300 *.pga_aggregate_target=209715200 *.processes=150 *.service_names='orcldg' *.sga_target=631242752 *.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=orcldg LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcldg,orclpr)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg' LOG_ARCHIVE_DEST_2='SERVICE=orclpr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclpr' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=orclpr DB_FILE_NAME_CONVERT='+FRA/orclpr/datafile','/u01/app/oracle/oradata/orcldg' LOG_FILE_NAME_CONVERT='+FRA/orclpr/onlinelog','/u01/app/oracle/oradata/orcldg' STANDBY_FILE_MANAGEMENT=AUTO

7、启动备库到mount状态

1. [oracle@LINUX10 archivelog]$ export ORACLE_SID=orcldg
   
2. [oracle@LINUX10 archivelog]$ sqlplus / as sysdba
   
3. 
   
4. SQL*Plus: Release 11.2.0.3.0 Production on Sun May 31 00:50:30 2015
   
5. 
   
6. Copyright (c) 1982, 2011, Oracle. All rights reserved.
   
7. 
   
8. Connected to an idle instance.
   
9. 
   
10. SQL> create spfile from pfile;
    
11. SQL> startup nomount;
    
12. SQL> alter database mount

8、配置监听 和 tnsname
    1）配置监听
        我这里主库和备库使用的是同一个监听， 配置完成后，重新启动监听

1. [oracle@LINUX10 admin]$ vi listener.ora
   
2. 
   
3. # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.3/oracle/network/admin/listener.ora
   
4. # Generated by Oracle configuration tools.
   
5. 
   
6. SID_LIST_LISTENER =
   
7.   (SID_LIST =
   
8.     (SID_DESC =
   
9.       (GLOBAL_DBNAME = orclpr)
   
10.       (ORACLE_HOME = /u01/app/oracle/product/11.2.3/oracle)
    
11.       (SID_NAME = orclpr)
    
12.     )
    
13.     (SID_DESC =
    
14.       (GLOBAL_DBNAME = orcldg)
    
15.       (ORACLE_HOME = /u01/app/oracle/product/11.2.3/oracle)
    
16.       (SID_NAME = orcldg)
    
17.     )
    
18.   )
    
19. 
    
20. LISTENER =
    
21.   (DESCRIPTION =
    
22.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
    
23.   )
    
24. 
    
25. ADR_BASE_LISTENER = /u01/app/oracle

    2）配置tnsnames.ora文件    

1. [oracle@LINUX10 admin]$ vi tnsnames.ora
   
2. 
   
3. # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.3/oracle/network/admin/tnsnames.ora
   
4. # Generated by Oracle configuration tools.
   
5. 
   
6. ORCLPR =
   
7.   (DESCRIPTION =
   
8.     (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX10)(PORT = 1521))
   
9.     (CONNECT_DATA =
   
10.       (SERVER = DEDICATED)
    
11.       (SERVICE_NAME = orclpr)
    
12.     )
    
13.   )
    
14. 
    
15. ORCL =
    
16.   (DESCRIPTION =
    
17.     (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX10)(PORT = 1521))
    
18.     (CONNECT_DATA =
    
19.       (SERVER = DEDICATED)
    
20.       (SERVICE_NAME = orcl)
    
21.     )
    
22.   )
    
23. 
    
24. ORCLDG =
    
25.   (DESCRIPTION =
    
26.     (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX10)(PORT = 1521))
    
27.     (CONNECT_DATA =
    
28.       (SERVER = DEDICATED)
    
29.       (SERVICE_NAME = orcldg)
    
30.     )
    
31.   )

9、在主库添加备库日志组
    注意备库的standby日志组数，必须比主库的在线日志数多一个。
    用下面的sql检查主库的在线日志文件的组数standby logfile的组数比下面的结果多一组
    select distinct group# from v$logfile where type='ONLINE';

    使用下面的sql语句检查在线日志文件的大小，standby logfile 应该等于下面的结果
    select max(bytes)/1024/1024 from v$log;
    

1. SQL> alter database add standby logfile group 20('+FRA') size 50M;
   
2. SQL> alter database add standby logfile group 21('+FRA') size 50M;
   
3. SQL> alter database add standby logfile group 22('+FRA') size 50M;
   
4. SQL> alter database add standby logfile group 23('+FRA') size 50M;

10、重启备库到 nomount 状态

11、创建主库的spfile，并重启主库    

1. SQL> shutdown immediate
   
2. SQL> create spfile from pfile;
   
3. SQL> startup

12、在主库上通过rman进行复制备库(注意在这一步之前必须退出备库的所有连接,否则会报错)    

1. [oracle@LINUX10 admin]$ rman target sys/oracle@orclpr auxiliary sys/oracle@orcldg
   
2. 
   
3. Recovery Manager: Release 11.2.0.3.0 - Production on Sun May 31 01:23:32 2015
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
   
6. 
   
7. connected to target database: ORCLPR (DBID=542685180)
   
8. connected to auxiliary database: ORCLPR (not mounted)
   
9. 
   
10. RMAN> duplicate target database for standby nofilenamecheck from active database

13、备库打开应用日志

1. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
   
2. -- 或者
   
3. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
   
4. 
   
5. --备库以只读方式打开
   
6. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
   
7. SQL> ALTER DATABASE OPEN read only;
   
8. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

主备切换的相关语句

1. --前提是备库的参数文件中的 log_archive_dest_n进行了相关的设置，且db_file_name_convert，log_file_name_convert，fal_server 参数也进行了相关的设置。
   
2. 
   
3. --查询主备是日志是否有缺口(主备都执行)
   
4. select to_char(scn_to_timestamp(current_scn),\'yyyy-mm-dd hh24:mi:ss\') from v$database;
   
5. select * from v$archive_gap;
   
6. 
   
7. --在当前的主库上
   
8. SQL> alter database commit to switchover to physical standby with session shutdown;
   
9. SQL> shutdown immediate;
   
10. SQL> startup mount;
    
11. SQL> alter database recover managed standby database USING CURRENT LOGFILE disconnect from session；
    
12. 
    
13. --在备库上
    
14. SQL> alter database commit to switchover to primary;
    
15. SQL> shutdown immediate;
    
16. SQL> startup

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30150152/viewspace-1689999/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30150152/viewspace-1689999/