oracle 10g data guard,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> ALTER DATABASE FORCE LOGGING;

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> alter system set log_archive_dest_1='location=/u02/arch' scope=both;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

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:

Although the standby redo log is only used when the database is running in the standby role, Oracle recommends that you create a standby redo log on the

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 sizes are identical on the primary and standby databases.

The size of the current standby redo log files must exactly match the size of the current primary database online redo log files.

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> alter database add standby logfile group 4 ('/u01/oracle/oradata/hanmi/log4a.rdo','/u01/oracle/oradata/hanmi/log4b.rdo') size 50m;

SQL> alter database add standby logfile group 5 ('/u01/oracle/oradata/hanmi/log5a.rdo','/u01/oracle/oradata/hanmi/log5b.rdo') size 50m;

SQL> alter database add standby logfile group 6 ('/u01/oracle/oradata/hanmi/log6a.rdo','/u01/oracle/oradata/hanmi/log6b.rdo') size 50m;

SQL> alter database add standby logfile group 7 ('/u01/oracle/oradata/hanmi/log7a.rdo','/u01/oracle/oradata/hanmi/log7b.rdo') size 50m;

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 idle standby database, use the SQL CREATE statement to create a server parameter file for the standby database from the text initialization

parameter file

SQL> create pfile='/u01/oracle/oradata/inithanmisdb.ora' from spfile;

3.2.2 Create a Control File for the Standby Database

SQL> alter  database create standby controlfile as '/u01/oracle/oradata/hanmisdb.ctl';

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> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

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> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Open the database for read-only access:

SQL> ALTER DATABASE OPEN;

在Read-Only 状态不能收到主库的数据，主与从库不同步，从新让从库同步主库用以下方法：

To change the standby database from being open for read-only access to performing Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

测试部分：

Step 1 Identify the existing archived redo log files (node2):

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> ALTER SYSTEM ARCHIVE LOG CURRENT;

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> ALTER SYSTEM SWITCH LOGFILE;

node2:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> alter database open;

SQL> select * from comtv;

重新同步：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;