oracle学习资料

ORACLE 10G DATAGUARD 配置物理STANDBY1、设置主数据库为force logging 模式cmd> sqlplus / nolog;SQL> conn / as sysdba;SQL>alter 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:\oracle\product\10.2.0\oradata\orac）拷贝到备库上同样目录3.2、创建备库的控制文件在主库上为从库创建控制文件和初始化参数文件,然后拷贝到从库D:\oracle\product\10.2.0\db_1\database目录下。

（记住这个目录，在下面备库的spfile 配置中要用到）。

SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS'd:\backup\STANDBY.CTL';SQL>create pfile from spfile;在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\c ontrol02.ctl','d:\oracle\product\10.2.0\oradata\orac\co ntrol03.ctl'*.core_dump_dest='d:\oracle\product\10.2.0/admin/orac/c dump'*.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=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:\oracle\product\10.2.0/admin/orac/u dump'然后保存,在主库上执行SQL> shutdown immediateSQL>startuppfile='D:\oracle\product\10.2.0\db_1\database\initorac. ora'SQL> create spfile from pfile;SQL> startup3.3 在备库上创建一个Windows服务和密码文件创建服务C:\Documents andSettings\Administrator>oradim -new -sid orac -startmode auto创建密码文件C:\Documents andSettings\Administrator>orapwdfile=D:\oracle\product\10.2.0\db_1\database\PWD orac.ora password=123456 entries=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\o rac\STANDBY.CTL'*.core_dump_dest='d:\oracle\product\10.2.0/admin/orac/c dump'*.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=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:\oracle\product\10.2.0/admin/orac/u dump'-------------------------------------------------------------------------------在备库上创建spfile文件,在备库上执行以下命令：C:\Documents and Settings\Administrator>sqlplus "/@standby as sysdba";SQL> startuppfile='D:\oracle\product\10.2.0\db_1\database\initorac .ora'SQL> CREATE SPFILE FROMPFILE='D:\oracle\product\10.2.0\db_1\database\initorac .ora';SQL>startup5、配置网络，修改listener.ora ,tnsnames.ora5.1 配置先在主从库上分别用netca 命令配置监听和本地服务名，再分别编辑主、从库的listener.ora,tnsnames.ora，参数以下主库的listener.ora 文件如下：################################################## #################################################### # listener.ora Network Configuration File:C:\oracle\product\10.2.0\db_1\network\admin\listener.o ra# Generated by Oracle configuration tools.SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)(PROGRAM = extproc))(SID_DESC =(GLOBAL_DBNAME = ORAC)(ORACLE_HOME =d:\oracle\product\10.2.0\db_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:\oracle\product\10.2.0\db_1\network\admin\tnsnames.o ra# Generated by Oracle configuration tools.ORAC =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.3.34)(PORT = 1521))(CONNECT_DATA =。