oracle 10G Data Guard -------终于搞定

刘伟

　　Creating a Physical Standby Database
　　Primary Database:
　　ip:192.168.30.17   sid: hanmi
　　standby database:
　　ip:192.168.10.14   sid:hanmisdb
　　3.1 Preparing the Primary Database for Standby Database Creation
　　3.1.1 Enable Forced Logging

　　SQL>>　　SQL> select force_logging from v$database;
　　3.1.5 Enable Archiving
　　If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:
　　SQL> SHUTDOWN IMMEDIATE;
　　SQL> STARTUP MOUNT;

　　SQL>>
　　SQL>>
　　SQL>>　　SQL> archive log list;
　　3.1.3 Configure a Standby Redo Log
　　A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all
　　atabases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone.
　　Note:

　　>　　primary database so that the primary database can switch over quickly to the standby role without the need for additional DBA intervention. Consider using
　　acle Enterprise Manager to automatically configure standby redo log on both your primary and standby databases.

　　Step 1   Ensure log file>
　　The>　　Step 2   Determine the appropriate number of standby redo log file groups.
　　Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary
　　database.
　　Use the following equation to determine an appropriate number of standby redo log file groups:
　　(maximum number of logfiles for each thread + 1) * maximum number of threads
　　Adding a Standby Redo Log File Group to a Specific Group Number:

　　SQL>>
　　SQL>>
　　SQL>>
　　SQL>>　　Step 5   Verify the standby redo log file groups were created.
　　SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
　　3.1.4 Set Primary Database Initialization Parameters
　　SQL> create pfile from spfile;
　　vi inihanmi.ora
　　hanmi.__db_cache_size=889192448
　　hanmi.__java_pool_size=16777216
　　hanmi.__large_pool_size=16777216
　　hanmi.__shared_pool_size=285212672
　　hanmi.__streams_pool_size=0
　　*.audit_file_dest='/u01/oracle/admin/hanmi/adump'
　　*.background_dump_dest='/u01/oracle/admin/hanmi/bdump'
　　*.compatible='10.2.0.1.0'
　　*.control_files='/u01/oracle/oradata/hanmi/control01.ctl','/u01/oracle/oradata/hanmi/control02.ctl','/u01/oracle/oradata/hanmi/control03.ctl'
　　*.core_dump_dest='/u01/oracle/admin/hanmi/cdump'
　　*.db_block_size=8192
　　*.db_domain=''
　　*.db_file_multiblock_read_count=16
　　*.db_name='hanmi'
　　*.db_recovery_file_dest='/u01/oracle/flash_recovery_area'
　　*.db_recovery_file_dest_size=2147483648
　　*.dispatchers='(PROTOCOL=TCP) (SERVICE=hanmiXDB)'
　　*.job_queue_processes=10
　　*.log_archive_dest_1='location=/u02/arch'
　　*.open_cursors=300
　　*.pga_aggregate_target=402653184
　　*.processes=150
　　*.remote_login_passwordfile='EXCLUSIVE'
　　*.sga_target=1210056704
　　*.undo_management='AUTO'
　　*.undo_tablespace='UNDOTBS1'
　　*.user_dump_dest='/u01/oracle/admin/hanmi/udump'
　　#add for data guard
　　*.DB_UNIQUE_NAME='hanmi'
　　*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(hanmi,hanmisdb)'　　　## "hanmi与hanmisdb"　这两个名字与 tnsnames.ora 中 "net service name" 相同）
　　*.LOG_ARCHIVE_DEST_1='LOCATION=/u02/arch  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=hanmi'
　　*.LOG_ARCHIVE_DEST_2='SERVICE=hanmisdb arch ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=hanmisdb'
　　*.STANDBY_FILE_MANAGEMENT='AUTO'
　　*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
　　*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
　　*.FAL_CLIENT='hanmi'
　　*.FAL_SERVER='hanmisdb'
　　SQL> create spfile from pfile;
　　3.2.4 Copy Files from the Primary System to the Standby System
　　Step 2   Create a password file.
　　cp /u01/oracle/product/10.2.0/db_1/dbs/orapwhanmi  /u01/oracle/oradata/orapwhanmisdb  (/u01/oracle/oradata repare for standby system )
　　Step 3   Configure listeners for the primary and standby databases.
　　% lsnrctl stop
　　% lsnrctl start
　　Step 4   Create Oracle Net service names.
　　On both the primary and standby systems, use Oracle Net Manager to create a network service name for the primary and standby databases that will be used
　　by redo transport services.
　　[oracle@oracle admin]$ cat tnsnames.ora
　　# tnsnames.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
　　# Generated by Oracle configuration tools.
　　HANMI =
　　(DESCRIPTION =
　　(ADDRESS_LIST =
　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.17)(PORT = 1521))
　　)
　　(CONNECT_DATA =
　　(SERVICE_NAME = hanmi)
　　)
　　)
　　HANMISDB =
　　(DESCRIPTION =
　　(ADDRESS_LIST =
　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.14)(PORT = 1521))
　　)
　　(CONNECT_DATA =
　　(SERVICE_NAME = hanmisdb)
　　)
　　)
　　EXTPROC_CONNECTION_DATA =
　　(DESCRIPTION =
　　(ADDRESS_LIST =
　　(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
　　)
　　(CONNECT_DATA =
　　(SID = PLSExtProc)
　　(PRESENTATION = RO)
　　)
　　)
　　Step 5   Create a server parameter file for the standby database.

　　On an>　　parameter file
　　SQL> create pfile='/u01/oracle/oradata/inithanmisdb.ora' from spfile;
　　3.2.2 Create a Control File for the Standby Database

　　SQL>>　　3.2.3 Prepare an Initialization Parameter File for the Standby Database
　　Step 1  copy  the primary database parameter file  passwd file controlfile  to standby database:
　　scp -r /u01/oracle/oradata 192.168.10.14:/u01/oracle/
　　standby database:
　　[oracle@ora2 hanmi]$ cp ../hanmisdb.ctl control01.ctl
　　[oracle@ora2 hanmi]$ cp ../hanmisdb.ctl control02.ctl
　　[oracle@ora2 hanmi]$ cp ../hanmisdb.ctl control03.ctl
　　[oracle@ora2 dbs]$ cd /u01/oracle/product/10.2.0/db_1/dbs
　　[oracle@ora2 dbs]$ cp /u01/oracle/oradata/inithanmisdb.ora  .
　　[oracle@ora2 dbs]$ cp /u01/oracle/oradata/orapwhanmisdb .
　　Step 2   Set initialization parameters on the physical standby database.
　　cat inithanmisdb.ora
　　hanmi.__java_pool_size=16777216
　　hanmi.__large_pool_size=16777216
　　hanmi.__shared_pool_size=285212672
　　hanmi.__streams_pool_size=0
　　*.audit_file_dest='/u01/oracle/admin/hanmi/adump'
　　*.background_dump_dest='/u01/oracle/admin/hanmi/bdump'
　　*.compatible='10.2.0.1.0'
　　*.control_files='/u01/oracle/oradata/hanmi/control01.ctl','/u01/oracle/oradata/hanmi/control02.ctl','/u01/oracle/oradata/hanmi/control03.ctl'
　　*.core_dump_dest='/u01/oracle/admin/hanmi/cdump'
　　*.db_block_size=8192
　　*.db_domain=''
　　*.db_file_multiblock_read_count=16
　　*.db_name='hanmi'
　　*.db_recovery_file_dest='/u01/oracle/flash_recovery_area'
　　*.db_recovery_file_dest_size=2147483648
　　*.DB_UNIQUE_NAME='hanmisdb'
　　*.dispatchers='(PROTOCOL=TCP) (SERVICE=hanmiXDB)'
　　*.FAL_CLIENT='hanmisdb'
　　*.FAL_SERVER='hanmi'
　　*.job_queue_processes=10
　　*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(hanmi,hanmisdb)'
　　*.LOG_ARCHIVE_DEST_1='LOCATION=/u02/arch  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=hanmisdb'
　　*.LOG_ARCHIVE_DEST_2='SERVICE=hanmi arch ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=hanmi'
　　*.STANDBY_FILE_MANAGEMENT=AUTO
　　*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
　　*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
　　*.open_cursors=300
　　*.pga_aggregate_target=402653184
　　*.processes=150
　　*.remote_login_passwordfile='EXCLUSIVE'
　　*.sga_target=1210056704
　　*.undo_management='AUTO'
　　*.undo_tablespace='UNDOTBS1'
　　*.user_dump_dest='/u01/oracle/admin/hanmi/udump'
　　3.2.6 Start the Physical Standby Database
　　Perform the following steps to start the physical standby database and Redo Apply.
　　Step 1   Start the physical standby database.
　　On the standby database, issue the following SQL statement to start and mount the database:
　　SQL> STARTUP MOUNT;
　　Step 2   Start Redo Apply.
　　On the standby database, issue the following command to start Redo Apply:

　　SQL>>　　3.2.7 Verify the Physical Standby Database Is Performing Properly
　　SQL> select FIRST_TIME,NEXT_TIME, APPLIED,SEQUENCE# from v$archived_log order by SEQUENCE#;
　　8.2.2 Opening a Physical Standby Database for Read-Only Access
　　To open a standby database for read-only access when it is currently performing Redo Apply or real-time apply:
　　Cancel Redo Apply or real-time apply:

　　SQL>>　　Open the database for read-only access:

　　SQL>>　　在Read-Only 状态不能收到主库的数据，主与从库不同步，从新让从库同步主库用以下方法：
　　To change the standby database from being open for read-only access to performing Redo Apply:

　　SQL>>　　测试部分：

　　Step 1>　　SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME   FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
　　Step 2 Force a log switch to archive the current online redo log file.(node1)

　　SQL>>　　SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
　　测试:在主库上建一个表，看是否在从库上会自动建同样的表：
　　node1:
　　SQL> create table comtv(id integer, name char(10));
　　SQL> insert into comtv values(101,'zxw');
　　SQL> insert into comtv values(102,'lyq');
　　SQL> commit;

　　SQL>>　　node2:

　　SQL>>
　　SQL>>　　SQL> select * from comtv;
　　重新同步：

　　SQL>>　　参考：http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm
　　http://jolly10.itpub.net/post/7268/414626
　　http://bbs.chinaunix.net/viewthread.php?tid=998901&extra=&page=1
　　http://comtv.blog.51cto.com/17037/406197