Oralce10g data guard配置

 Oralce10g data guard配置

 

  具体步骤如下：

    一、主库操作

    1、修改主库属性：

sql> alter database force logging; Database altered. ##查看状态 sql> select FORCE_LOGGING from v$database; FOR --- YES

    2、修改数据库为归档模式：

sql> alter system set log_archive_dest_1='LOCATION=/arch1/' scope=both; system altered. sql> shutdown immediate; Database closed. Database dismounted. oracle instance shut down. sql> startup mount; oracle instance started. Total system Global Area  167772160 bytes Fixed Size                  1218292 bytes Variable Size              79694092 bytes Database Buffers           79691776 bytes Redo Buffers                7168000 bytes Database mounted. sql> alter database archivelog; Database altered. sql> archive log list; Database log mode              Archive Mode Automatic archival             Enabled Archive destination            /arch1/ Oldest online log sequence     1 Next log sequence to archive   2 Current log sequence           2 sql> alter database open; Database altered.

 

3、添加standby logfile（也可以不加）

    为主数据库添加"备用联机日志文件"，这里要保证备日志文件与主库联机日志文件相同大小。

    添加备用日志文件是规则：备用日志最少应该比redo log 多一个。推荐的备重做日志数依赖于主数据库上的线程数。

    （每线程日志文件最大数目 + 1 ） * 线程数

sql> select GROUP#,MEMBERS,BYTES/1024/1024 from v$log;     GROUP#    MEMBERS BYTES/1024/1024 ---------- ---------- ---------------          1          1              50          2          1              50          3          1              50 sql> select GROUP#,MEMBER from v$logfile;      GROUP# MEMBER ---------- ----------------------------------------          3 /oracle/oradata/orcl/redo03.log          2 /oracle/oradata/orcl/redo02.log          1 /oracle/oradata/orcl/redo01.log sql> alter database add standby logfile   2          group 4 ('/oracle/oradata/orclstd_redo04a.log','/oracle/oradata/orcl/std_redo04b.log') size 50m,   3          group 5 ('/oracle/oradata/orcl/std_redo05a.log','/oracle/oradata/orcl/std_redo05b.log') size 50m,   4          group 6 ('/oracle/oradata/orcl/std_redo06a.log','/oracle/oradata/orcl/std_redo06b.log') size 50m,   5          group 7 ('/oracle/oradata/orcl/std_redo07a.log','/oracle/oradata/orcl/std_redo08b.dbf') size 50m; Database altered.

    4、修改主库参数文件：

sql> create pfile='/oracle/orcl.ora' from spfile; File created. orcl.__db_cache_size=79691776 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__shared_pool_size=71303168 orcl.__streams_pool_size=0 *.audit_file_dest='/oracle/admin/orcl/adump' *.background_dump_dest='/oracle/admin/orcl/bdump' *.compatible='10.2.0.1.0' *.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/oradata/orcl/control02.ctl','/oracle/oradata/orcl/control03.ctl' *.core_dump_dest='/oracle/admin/orcl/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='orcl' *.db_recovery_file_dest='/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.job_queue_processes=10 *.DB_UNIQUE_NAME='orclpri'    ##必须 定义每个数据库的唯一标识 *.log_archive_config='DG_CONFIG=(orclpri,orclstandby)'     ###必须 *.log_archive_dest_1='LOCATION=/arch1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' DB_UNIQUE_NAME='orclpri'         ###必须  本地的归档路径 *.LOG_ARCHIVE_DEST_2='SERVICE=orclstandby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstandby'      ###必须(远程 服务器端的归档日志 ) *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=ENABLE *.FAL_SERVER=orclstandby       ### 定义FAL 服务器的 Net服务的名称 *.FAL_CLIENT=orclpri    ### 定义备数据库的 Net服务名     （这两个参数在主库可有可无，但备库必须有。ORACLE 老外工程师说这个必须有^_^）   *.open_cursors=300 *.pga_aggregate_target=16777216 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=167772160 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/oracle/admin/orcl/udump' *.STANDBY_FILE_MANAGEMENT=AUTO        ###设置为AUTO,使得当数据文件添加到主数据库或者从主数据库删除的时候,对应的修改能够在备用数据库中自动执行 oracle oracle

5、用pfile启动，再重新创建spfile.

sql> shutdown immediate; Database closed. Database dismounted. oracle instance shut down. sql> startup pfile='/oracle/orcl.ora' oracle instance started. Total system Global Area  167772160 bytes Fixed Size                  1218292 bytes Variable Size              79694092 bytes Database Buffers           79691776 bytes Redo Buffers                7168000 bytes Database mounted. Database opened. sql> sql> create spfile from pfile='/oracle/orcl.ora'; File created

    6、在主库创建密码文件、以及控制文件。

[oracle@node2 oracle]$ orapwd file='/oracle/product/10.2.0/db_1/dbs/orapworcl.ora' password=oracle entries=10 sql> ALTER DATABASE CREATE STANDBY CONTROLFILE AS  '/oracle/oradata/orcl/standby.ctl'; Database altered.

    7、TNS信息如下：

主库 ORCL =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = tcp)(HOST = 172.16.37.239)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = orcl)     )   ) orclpri =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = tcp)(HOST = 172.16.37.239)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = orcl)     )   ) orclstandby =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = tcp)(HOST = 172.16.37.211)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = orcl)     )   ) EXTPROC_CONNECTION_DATA =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))     )     (CONNECT_DATA =       (SID = PLSExtProc)       (PRESENTATION = RO)     )   ) 监听信息如下 SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (SERVICE_NAME=orclpri)       (ORACLE_HOME = /oracle/product/10.2.0/db_1)         (SID_NAME=ORCL)     )   ) LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = tcp)(HOST = localhost.localdomain)(PORT = 1521))       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))     )   )

    8、把数据库scp到备库相应的目录（包括密码文件、standby controlfile）

  二备机操作

    1、备份的参数文件内容

orcl.__db_cache_size=79691776 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__shared_pool_size=71303168 orcl.__streams_pool_size=0 *.audit_file_dest='/oracle/admin/orcl/adump' *.background_dump_dest='/oracle/admin/orcl/bdump' *.compatible='10.2.0.1.0' *.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/oradata/orcl/control02.ctl','/oracle/oradata/orcl/control03.ctl' *.core_dump_dest='/oracle/admin/orcl/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='orcl' *.db_recovery_file_dest='/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.job_queue_processes=10 *.DB_UNIQUE_NAME='orclstandby'    ##必须 定义每个数据库的唯一标识 *.log_archive_config='DG_CONFIG=(orclpri,orclstandby)'     ###必须 *.log_archive_dest_1='LOCATION=/arch1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' DB_UNIQUE_NAME='orclstandby'         ###必须  本地的归档路径 *.LOG_ARCHIVE_DEST_2='SERVICE=orclpri arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclpri'      ###必须(远程服务器端的归档日志) *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=ENABLE *.FAL_SERVER=orclstandby       ### 定义FAL服务器的Oracle Net服务的名称 *.FAL_CLIENT=orclpri   ### 定义备数据库的Oracle Net服务名     （这两个参数在主库可有可无，但备库必须有。ORACLE 老外工程师说这个必须有^_^）   *.open_cursors=300 *.pga_aggregate_target=16777216 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=167772160 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/oracle/admin/orcl/udump' *.STANDBY_FILE_MANAGEMENT=AUTO

    2、修改上面参数文件里的

*.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/oradata/orcl/control02.ctl','/oracle/oradata/orcl/control03.ctl' 为： *.control_files='/oracle/oradata/orcl/standby.ctl' ##在主机上生成的那个控制文件，也可以多放几份

3、TNS信息如下：

备库 ORCL =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = tcp)(HOST = 172.16.37.239)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = orcl)     )   ) orclpri =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = tcp)(HOST = 172.16.37.239)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = orcl)     )   ) orclstandby =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = tcp)(HOST = 172.16.37.211)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = orcl)     )   ) EXTPROC_CONNECTION_DATA =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))     )     (CONNECT_DATA =       (SID = PLSExtProc)       (PRESENTATION = RO)     )   ) 监听信息如下 SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (SERVICE_NAME=orclstandby)       (ORACLE_HOME = /oracle/product/10.2.0/db_1)         (SID_NAME=ORCL)     )   ) LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = tcp)(HOST = localhost.localdomain)(PORT = 1521))       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))     )   )

    4、用创建的参数文件启动数据库到nomount

sql> startup pfile='/oracle/orclstandby.ora' nomount; oracle instance started. Total system Global Area  167772160 bytes Fixed Size                  1218316 bytes Variable Size              62916852 bytes Database Buffers          100663296 bytes Redo Buffers                2973696 bytes sql> ALTER DATABASE MOUNT STANDBY DATABASE; Database altered.

5、修改备库处于应用归档状态

sql> alter database recover managed standby database disconnect from session; Database altered.

    如果主库从不过来归档，可以通过在主库侧手工修改参数如下：

ALTER system SET log_archive_dest_state_2='DEFER' SCOPE=MEMORY; ALTER system SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY;

    7、测试

    通过在主库执行alter system switch logfile；切换日志可以观察到备库会自动应用通过主库传过来的日志。

    三、切换测试

    1、在主库端

select switchover_stats from v$database;

    如果是to standby  表可以正常切换。

    直接执行

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

    否则执行：

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; shutdown immediate; startup nomount; alter database mount standby database;

    如果是to_primary  表可以正常切换。

    2、在备库

    在备库

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

    执行：

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

    否则执行：

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; shutdown immediate; startup;

    然后观察主备库日志，如果正常的话会看到备库会自动应用日志。