oracle 10G Data Guard -------终于搞定
Creating a Physical Standby DatabasePrimary 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/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=hanmi'
*.LOG_ARCHIVE_DEST_2='SERVICE=hanmisdb arch ASYNCVALID_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.
$ 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 1copythe primary database parameter filepasswd file controlfileto standby database:
scp -r /u01/oracle/oradata 192.168.10.14:/u01/oracle/
standby database:
$ cp ../hanmisdb.ctl control01.ctl
$ cp ../hanmisdb.ctl control02.ctl
$ cp ../hanmisdb.ctl control03.ctl
$ cd /u01/oracle/product/10.2.0/db_1/dbs
$ cp /u01/oracle/oradata/inithanmisdb.ora.
$ 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/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=hanmisdb'
*.LOG_ARCHIVE_DEST_2='SERVICE=hanmi arch ASYNCVALID_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
页:
[1]