|
具体步骤如下 一主库操作 修改主库属性 SQL> alter database force logging; Database altered ##查看状态 SQL> select FORCE_LOGGING from v$database; FOR
YES 修改数据库为归档模式 SQL> alter system set log_archive_dest_=LOCATION=/arch/ 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 bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes Database mounted SQL> alter database archivelog; Database altered SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /arch/ Oldest online log sequence Next log sequence to archive Current log sequence SQL> alter database open; Database altered 添加standby logfile（也可以不加） 为主数据库添加备用联机日志文件这里要保证备日志文件与主库联机日志文件相同大小 添加备用日志文件是规则备用日志最少应该比redo log 多一个推荐的备重做日志数依赖于主数据库上的线程数 （每线程日志文件最大数目 + ） * 线程数 SQL> select GROUP#MEMBERSBYTES// from v$log; GROUP# MEMBERS BYTES// SQL> select GROUP#MEMBER from v$logfile; GROUP# MEMBER /oracle/oradata/orcl/redolog /oracle/oradata/orcl/redolog /oracle/oradata/orcl/redolog SQL> alter database add standby logfile group (/oracle/oradata/orclstd_redoalog/oracle/oradata/orcl/std_redoblog) size m group (/oracle/oradata/orcl/std_redoalog/oracle/oradata/orcl/std_redoblog) size m group (/oracle/oradata/orcl/std_redoalog/oracle/oradata/orcl/std_redoblog) size m group (/oracle/oradata/orcl/std_redoalog/oracle/oradata/orcl/std_redobdbf) size m; Database altered 修改主库参数文件 SQL> create pfile=/oracle/orclora from spfile; File created orcl__db_cache_size= orcl__java_pool_size= orcl__large_pool_size= orcl__shared_pool_size= orcl__streams_pool_size= *audit_file_dest=/oracle/admin/orcl/adump *background_dump_dest=/oracle/admin/orcl/bdump patible= ntrol_files=/oracle/oradata/orcl/controlctl/oracle/oradata/orcl/controlctl/oracle/oradata/orcl/controlctl re_dump_dest=/oracle/admin/orcl/cdump *db_block_size= *db_domain= *db_file_multiblock_read_count= *db_name=orcl *db_recovery_file_dest=/oracle/flash_recovery_area *db_recovery_file_dest_size= *dispatchers=(PROTOCOL=TCP) (SERVICE=orclXDB) *job_queue_processes= *DB_UNIQUE_NAME=orclpri ##必须 定义每个数据库的唯一标识 *log_archive_config=DG_CONFIG=(orclpriorclstandby) ###必须 *log_archive_dest_=LOCATION=/arch/ VALID_FOR=(ALL_LOGFILESALL_ROLES) DB_UNIQUE_NAME=orclpri ###必须 本地的归档路径 *LOG_ARCHIVE_DEST_=SERVICE=orclstandby arch ASYNC VALID_FOR=(ONLINE_LOGFILESPRIMARY_ROLE) DB_UNIQUE_NAME=orclstandby ###必须(远程服务器端的归档日志 ) *LOG_ARCHIVE_DEST_STATE_=ENABLE *LOG_ARCHIVE_DEST_STATE_=ENABLE *FAL_SERVER=orclstandby ### 定义FAL服务器的Oracle Net服务的名称 *FAL_CLIENT=orclpri ### 定义备数据库的Oracle Net服务名 （这两个参数在主库可有可无但备库必须有ORACLE 老外工程师说这个必须有^_^） *open_cursors= *pga_aggregate_target= *processes= *remote_login_passwordfile=EXCLUSIVE a_target= *undo_management=AUTO *undo_tablespace=UNDOTBS *user_dump_dest=/oracle/admin/orcl/udump *STANDBY_FILE_MANAGEMENT=AUTO ###设置为AUTO使得当数据文件添加到主数据库或者从主数据库删除的时候对应的修改能够在备用数据库中自动执行 用pfile启动再重新创建spfile SQL> shutdown immediate; Database closed Database dismounted ORACLE instance shut down SQL> startup pfile=/oracle/orclora ORACLE instance started Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes Database mounted Database opened SQL> SQL> create spfile from pfile=/oracle/orclora; File created 在主库创建密码文件以及控制文件 [oracle@node oracle]$ orapwd file=/oracle/product//db_/dbs/orapworclora password=oracle entries= SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS /oracle/oradata/orcl/standbyctl; Database altered TNS信息如下 主库 ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) orclpri = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) orclstandby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) 监听信息如下 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SERVICE_NAME=orclpri) (ORACLE_HOME = /oracle/product//db_) (SID_NAME=ORCL) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhostlocaldomain)(PORT = )) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) ) 把数据库scp到备库相应的目录（包括密码文件standby controlfile） 二备机操作 备份的参数文件内容 orcl__db_cache_size= orcl__java_pool_size= orcl__large_pool_size= orcl__shared_pool_size= orcl__streams_pool_size= *audit_file_dest=/oracle/admin/orcl/adump *background_dump_dest=/oracle/admin/orcl/bdump patible= ntrol_files=/oracle/oradata/orcl/controlctl/oracle/oradata/orcl/controlctl/oracle/oradata/orcl/controlctl re_dump_dest=/oracle/admin/orcl/cdump *db_block_size= *db_domain= *db_file_multiblock_read_count= *db_name=orcl *db_recovery_file_dest=/oracle/flash_recovery_area *db_recovery_file_dest_size= *dispatchers=(PROTOCOL=TCP) (SERVICE=orclXDB) *job_queue_processes= *DB_UNIQUE_NAME=orclstandby ##必须 定义每个数据库的唯一标识 *log_archive_config=DG_CONFIG=(orclpriorclstandby) ###必须 *log_archive_dest_=LOCATION=/arch/ VALID_FOR=(ALL_LOGFILESALL_ROLES) DB_UNIQUE_NAME=orclstandby ###必须 本地的归档路径 *LOG_ARCHIVE_DEST_=SERVICE=orclpri arch ASYNC VALID_FOR=(ONLINE_LOGFILESPRIMARY_ROLE) DB_UNIQUE_NAME=orclpri ###必须(远程服务器端的归档日志) *LOG_ARCHIVE_DEST_STATE_=ENABLE *LOG_ARCHIVE_DEST_STATE_=ENABLE *FAL_SERVER=orclstandby ### 定义FAL服务器的Oracle Net服务的名称 *FAL_CLIENT=orclpri ### 定义备数据库的Oracle Net服务名 （这两个参数在主库可有可无但备库必须有ORACLE 老外工程师说这个必须有^_^） *open_cursors= *pga_aggregate_target= *processes= *remote_login_passwordfile=EXCLUSIVE a_target= *undo_management=AUTO *undo_tablespace=UNDOTBS *user_dump_dest=/oracle/admin/orcl/udump *STANDBY_FILE_MANAGEMENT=AUTO 修改上面参数文件里的 ntrol_files=/oracle/oradata/orcl/controlctl/oracle/oradata/orcl/controlctl/oracle/oradata/orcl/controlctl 为 ntrol_files=/oracle/oradata/orcl/standbyctl ##在主机上生成的那个控制文件也可以多放几份 TNS信息如下 备库 ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) orclpri = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) orclstandby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) 监听信息如下 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SERVICE_NAME=orclstandby) (ORACLE_HOME = /oracle/product//db_) (SID_NAME=ORCL) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhostlocaldomain)(PORT = )) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) ) 用创建的参数文件启动数据库到nomount SQL> startup pfile=/oracle/orclstandbyora nomount; ORACLE instance started Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes SQL> ALTER DATABASE MOUNT STANDBY DATABASE; Database altered 修改备库处于应用归档状态 SQL> alter database recover managed standby database disconnect from session; Database altered 如果主库从不过来归档可以通过在主库侧手工修改参数如下 ALTER SYSTEM SET log_archive_dest_state_=DEFER SCOPE=MEMORY; ALTER SYSTEM SET log_archive_dest_state_=ENABLE SCOPE=MEMORY; 测试 通过在主库执行alter system switch logfile切换日志可以观察到备库会自动应用通过主库传过来的日志 三切换测试 在主库端 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 表可以正常切换 在备库 在备库 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; 然后观察主备库日志如果正常的话会看到备库会自动应用日志 |
