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/orcl/std_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服务器的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 ###设置为AUTO,使得当数据文件添加到主数据库或者从主数据库删除的时候,对应的修改能够在备用数据库中自动执行
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 网管网bitsCN_com
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 网管网bitsCN.com
*.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_status 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;

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