oracle学习资料.doc

OSDBSIDIP主库windows2003ORACLE 10GORCL01备库windows2003ORACLE 10G无02ORACLE 10G DATAGUARD 配置物理STANDBY 1、设置主数据库为force logging 模式cmd sqlplus / nolog;SQL conn / as sysdba;SQLalter database force logging;2、设置主数据库为归档模式SQL archive log listSQL shutdown immediateSQL startup mountSQL alter database archivelog;SQL archive log list3、数据文件拷贝数据文件的拷贝可以分为两种模式：rman 和文件的直接拷贝，由于我们的系统可以停机，所以会采用第二种方式。注意不用拷贝RED 文件，备用机在启动的时候会自动的创建RED 文件。3.1、拷贝主库的数据文件到备库中SQL SELECT NAME FROM v$datafile;SQL select * from v$tempfile;把主库的以上数据文件（D:oracleproduct10.2.0oradataorac）拷贝到备库上同样目录3.2、创建备库的控制文件在主库上为从库创建控制文件和初始化参数文件,然后拷贝到从库D:oracleproduct10.2.0db_1database目录下。（记住这个目录，在下面备库的spfile 配置中要用到）。SQLALTER DATABASE CREATE STANDBY CONTROLFILE AS d:backupSTANDBY.CTL;SQLcreate pfile from spfile;在D:oracleproduct10.2.0db_1database目录下找到pfile（initorac.ora）;修改其参数orac._db_cache_size=335544320orac._java_pool_size=4194304orac._large_pool_size=4194304orac._shared_pool_size=251658240orac._streams_pool_size=0*.audit_file_dest=d:oracleproduct10.2.0/admin/orac/adump*.background_dump_dest=d:oracleproduct10.2.0/admin/orac/bdump*.compatible=.0*.control_files=d:oracleproduct10.2.0oradataoraccontrol01.ctl,d:oracleproduct10.2.0oradataoraccontrol02.ctl,d:oracleproduct10.2.0oradataoraccontrol03.ctl*.core_dump_dest=d:oracleproduct10.2.0/admin/orac/cdump*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=orac*.db_recovery_file_dest=d:oracleproduct10.2.0/flash_recovery_area*.db_recovery_file_dest_size=2147483648*.DB_UNIQUE_NAME=primary*.FAL_CLIENT=primary*.FAL_SERVER=standby*.log_archive_config=DG_CONFIG=(primary,standby)*.log_archive_dest_1=location=C:archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary*.log_archive_dest_2=SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.log_archive_format=%T%S%r.ARC*.open_cursors=300*.pga_aggregate_target=201326592*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=603979776*.STANDBY_FILE_MANAGEMENT=AUTO*.undo_management=AUTO*.undo_tablespace=UNDOTBS1*.user_dump_dest=d:oracleproduct10.2.0/admin/orac/udump然后保存,在主库上执行SQL shutdown immediateSQLstartup pfile=D:oracleproduct10.2.0db_1databaseinitorac.oraSQL create spfile from pfile;SQL startup3.3 在备库上创建一个Windows服务和密码文件创建服务C:Documents and SettingsAdministratororadim -new -sidorac -startmode auto创建密码文件C:Documents and SettingsAdministratororapwd file=D:oracleproduct10.2.0db_1databasePWDorac.ora password=123456 entries=5;3.4 备库创建admin目录将主库的d:oracleproduct10.2.0adminorac 目录及文件夹(adump,bdump,cdump,dpdump,pfile,udump)拷贝到备库的相同目录。3.5 拷贝控制文件和参数文件备库的控制文件（D:backupstandby。ctl）已经创建， 只需要从主库拷贝到备库的（D:oracleproduct10.2.0oradataorac）下此时备库已经存在 数据文件和控制文件。3.6 拷贝初始化参数文件到备库将主库下的初始化参数文件（D:oracleproduct10.2.0db_1databaseinitorac.ora）拷贝到备库D:oracleproduct10.2.0db_1databaseinitorac.ora下4、修改备库pfile文件修改备库的D:oracleproduct10.2.0db_1databaseinitorac.ora文件如下-standby.ora-orac._db_cache_size=335544320orac._java_pool_size=4194304orac._large_pool_size=4194304orac._shared_pool_size=251658240orac._streams_pool_size=0*.audit_file_dest=d:oracleproduct10.2.0/admin/orac/adump*.background_dump_dest=d:oracleproduct10.2.0/admin/orac/bdump*.compatible=.0*.control_files=d:oracleproduct10.2.0oradataoracSTANDBY.CTL*.core_dump_dest=d:oracleproduct10.2.0/admin/orac/cdump*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=orac*.db_recovery_file_dest=d:oracleproduct10.2.0/flash_recovery_area*.db_recovery_file_dest_size=2147483648*.DB_UNIQUE_NAME=standby*.FAL_CLIENT=standby*.FAL_SERVER=primary*.log_archive_config=DG_CONFIG=(primary,standby)*.log_archive_dest_1=location=C:archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby*.log_archive_dest_2=SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.log_archive_format=%T%S%r.ARC*.open_cursors=300*.pga_aggregate_target=201326592*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=603979776*.STANDBY_FILE_MANAGEMENT=AUTO*.undo_management=AUTO*.undo_tablespace=UNDOTBS1*.user_dump_dest=d:oracleproduct10.2.0/admin/orac/udump-在备库上创建spfile文件,在备库上执行以下命令：C:Documents and SettingsAdministratorsqlplus /standby as sysdba;SQL startup pfile=D:oracleproduct10.2.0db_1databaseinitorac.oraSQL CREATE SPFILE FROM PFILE=D:oracleproduct10.2.0db_1databaseinitorac.ora;SQLstartup 5、配置网络，修改listener.ora ,tnsnames.ora5.1 配置先在主从库上分别用netca 命令配置监听和本地服务名，再分别编辑主、从库的listener.ora,tnsnames.ora，参数以下主库的listener.ora 文件如下：# listener.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadminlistener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = d:oracleproduct10.2.0db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = ORAC) (ORACLE_HOME = d:oracleproduct10.2.0db_1) (SID_NAME = ORAC) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = acct-zhangpei)(PORT = 1521) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0) ) )#主库的tnsnames.ora 文件如下：# tnsnames.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadmintnsnames.ora# Generated by Oracle configuration tools.ORAC = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 4)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orac) ) )primary = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 4)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orac) ) )standby = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 7)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orac) ) )#从库的listeners.ora 如下# listener.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadminlistener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = d:oracleproduct10.2.0db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = ORAC) (ORACLE_HOME = d:oracleproduct10.2.0db_1) (SID_NAME = ORAC) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sss-f115w6wz6ge)(PORT = 1521) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0) ) )#从库的tnsnames.ora 文件如下：# tnsnames.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadmintnsnames.ora# Generated by Oracle configuration tools.PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 4)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = ORAC) ) )STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 7)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORAC) ) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )#在上面配置完成之后，需要在主、备机上重启监听服务CMD Lsnrctl stopCMD lsnrctl start可以使用lsnrctl status 命令查看监听器状态5.2 测试网络配置如果配置成功，在主机上测试SQL conn sys/123456standby as sysdba;如果配置成功，在备库上测试SQL conn sys/123456primary as sysdba; 也能得到相似结果则说明网络配置成功。6、启动主备服务6.1 主库:SQL STARTUP MOUNT;SQL ALTER DATABASE ARCHIVELOG;SQL ALTER DATABASE OPEN;6.2 备库:SQL STARTUP MOUNT;SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;到次为止，最关键的时刻来了，我们要测试看是否配置成功，如果成功的话在主机上的归档就能顺利传到6.3 测试归档1. 测试主库产生的归档日志是否能正常传送到归档日志在主机上SQLarchive log list;SQLalter system archive log current;SQLarchive log list;主库进行日志切换:SQLAlter system switch logfile;然后分别查看主库和备库的D:arch 目录下是否产生了同样的归档日志文件。select max(sequence#) from v$archived_log;select max(sequence#) from v$log_history;select group#,sequence#,archived,status from v$log;select name,sequence#,applied from v$archived_log;select sequence#,applied from v$archived_log;若不同步，1. 看log 日志， archive 是否有丢失2. 可以在备库做如下操作：alter database recover managed standby database cancel;alter database recover managed standby database disconnect from session;7、日常维护7.1、正确打开主库和备库主库:SQL STARTUP MOUNT;SQL ALTER DATABASE ARCHIVELOG;SQL ALTER DATABASE OPEN;备库:SQL STARTUP MOUNT;SQLALTER DATABASE RECOVER MANAGED STANDBY DATABASEDISCONNECT FROM SESSION;7.2、正确关闭顺序备库:SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;SQLSHUTDOWN IMMEDIATE;主库SQLSHUTDOWN IMMEDIATE;7.3、备库Read-Only模式打开当前主库正常OPEN 状态、备库处于日志传送状态.1 在备库停止日志传送SQL recover managed standby database cancel;2 备库Read-only 模式打开SQL alter database open read only;3 备库回到日志传送模式SQL recover managed standby database disconnect from session;SQL select status from v$instance;7.4、日志传送状态监控1 主库察看当前日志状况SQL select sequence#,status from v$log;2 备库察看RFS(Remote File Service)接收日志情况和MRP 应用日志同步主库情况SQL SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKSFROM V$MANAGED_STANDBY;3 察看备库是否和主库同步SQL SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#,APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;4 察看备库已经归档的redoSQL SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG;5 察看备库已经应用的redoSQL SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#FROM V$LOG_HISTORY;6 察看备库接收,应用redo 数据过程.SQL SELECT MESSAGE FROM V$DATAGUARD_STATUS;7.5 备库归档目录维护1 找到备库归档目录SQL show parameter log_archive_dest_12 维护策略每周2,4,7 删除已经应用的归档文件具体参见附录二8、主库正常切换8.1人工干预主库正常切换1 在主库端检验数据库可切换状态SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS:TO STANDBY 表示可以正常切换.如果SWITCHOVER_STATUS 的值为SESSIONS ACTIVE,表示当前有会话处于ACTIVE状态2 开始主库正常切换如果SWITCHOVER_STATUS 的值为TO STANDBY 则:SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;如果SWITCHOVER_STATUS 的值为SESSIONS ACTIVE 则:SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSIONSHUTDOWN;成功运行这个命令后，主库被修改为备库3 重启先前的主库SQL SHUTDOWN IMMEDIATE;SQL STARTUP MOUNT;4 在备库验证可切换状态SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS-TO_PRIMARY1 row selected5 将目标备库转换为主库如果SWI