oracle学习资料

OSDBSIDIP主库windows2003ORACLE10GORCL192.168.0.101备库windows2003ORACLE10G无192.168.0.102ORACLE10GDATAGUARD配置物理STANDBY1、设置主数据库为forcelogging模式cmdsqlplus/nolog;SQLconn/assysdba;SQLalterdatabaseforcelogging;2、设置主数据库为归档模式SQLarchiveloglistSQLshutdownimmediateSQLstartupmountSQLalterdatabasearchivelog;SQLarchiveloglist3、数据文件拷贝数据文件的拷贝可以分为两种模式：rman和文件的直接拷贝，由于我们的系统可以停机，所以会采用第二种方式。注意不用拷贝RED文件，备用机在启动的时候会自动的创建RED文件。3.1、拷贝主库的数据文件到备库中SQLSELECTNAMEFROMv$datafile;SQLselect*fromv$tempfile;把主库的以上数据文件（D:\oracle\product\10.2.0\oradata\orac）拷贝到备库上同样目录3.2、创建备库的控制文件在主库上为从库创建控制文件和初始化参数文件,然后拷贝到从库D:\oracle\product\10.2.0\db_1\database目录下。（记住这个目录，在下面备库的spfile配置中要用到）。SQLALTERDATABASECREATESTANDBYCONTROLFILEAS'd:\backup\STANDBY.CTL';SQLcreatepfilefromspfile;在D:\oracle\product\10.2.0\db_1\database目录下找到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:\oracle\product\10.2.0/admin/orac/adump'*.background_dump_dest='d:\oracle\product\10.2.0/admin/orac/bdump'*.compatible='10.2.0.1.0'*.control_files='d:\oracle\product\10.2.0\oradata\orac\control01.ctl','d:\oracle\product\10.2.0\oradata\orac\control02.ctl','d:\oracle\product\10.2.0\oradata\orac\control03.ctl'*.core_dump_dest='d:\oracle\product\10.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:\oracle\product\10.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=standbyLGWRASYNCVALID_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:\oracle\product\10.2.0/admin/orac/udump'然后保存,在主库上执行SQLshutdownimmediateSQLstartuppfile='D:\oracle\product\10.2.0\db_1\database\initorac.ora'SQLcreatespfilefrompfile;SQLstartup3.3在备库上创建一个Windows服务和密码文件创建服务C:\DocumentsandSettings\Administratororadim-new-sidorac-startmodeauto创建密码文件C:\DocumentsandSettings\Administratororapwdfile=D:\oracle\product\10.2.0\db_1\database\PWDorac.orapassword=123456entries=5;3.4备库创建admin目录将主库的d:\oracle\product\10.2.0\admin\orac目录及文件夹(adump,bdump,cdump,dpdump,pfile,udump)拷贝到备库的相同目录。3.5拷贝控制文件和参数文件备库的控制文件（D:\backup\standby。ctl）已经创建，只需要从主库拷贝到备库的（D:\oracle\product\10.2.0\oradata\orac）下此时备库已经存在数据文件和控制文件。3.6拷贝初始化参数文件到备库将主库下的初始化参数文件（D:\oracle\product\10.2.0\db_1\database\initorac.ora）拷贝到备库D:\oracle\product\10.2.0\db_1\database\initorac.ora下4、修改备库pfile文件修改备库的D:\oracle\product\10.2.0\db_1\database\initorac.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:\oracle\product\10.2.0/admin/orac/adump'*.background_dump_dest='d:\oracle\product\10.2.0/admin/orac/bdump'*.compatible='10.2.0.1.0'*.control_files='d:\oracle\product\10.2.0\oradata\orac\STANDBY.CTL'*.core_dump_dest='d:\oracle\product\10.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:\oracle\product\10.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=primaryLGWRASYNCVALID_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:\oracle\product\10.2.0/admin/orac/udump'-------------------------------------------------------------------------------在备库上创建spfile文件,在备库上执行以下命令：C:\DocumentsandSettings\Administratorsqlplus/@standbyassysdba;SQLstartuppfile='D:\oracle\product\10.2.0\db_1\database\initorac.ora'SQLCREATESPFILEFROMPFILE='D:\oracle\product\10.2.0\db_1\database\initorac.ora';SQLstartup5、配置网络，修改listener.ora,tnsnames.ora5.1配置先在主从库上分别用netca命令配置监听和本地服务名，再分别编辑主、从库的listener.ora,tnsnames.ora，参数以下主库的listener.ora文件如下：###############################################################################################