Setup Standby Database on One PC

Building the Data Guard

1. Check the primary database name:

SQL> select name from v$database;

NAME

---------

BSL01LAB

2. Enable Forced logging

--check FORCE LOGGING status

SQL> select force_logging from v$database;

FOR

---

NO

--eable FORCE LOGGING

SQL> alter database force logging;

Database altered.

--Verify

SQL> select force_logging from v$database;

FOR

---

YES

3. Create a password file

--To create the password file

D:oracleproduct10.2.0db_1database>orapwd file=PWDbsl01lab.ora password=dow entries=5

--To verify the new password file

D:oracleproduct10.2.0db_1database>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 25 22:24:41 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect sys/dow as sysdba

Connected to an idle instance.

4. Configure a Standby Redo Log

Step 1 Ensure log file sizes are identical on the primary and standby database

SQL> select group#,thread#,bytes/1024/1024,members from v$log;

GROUP# THREAD# BYTES/1024/1024 MEMBERS

---------- ---------- --------------- ----------

1 1 50 1

2 1 50 1

3 1 50 1

Step 2 Determine the appropriate number of standby redo log file groups

The minimal number should be 3+1=4

Step 3 Verify related database parameters and settings

Maxlogfiles

Maxlogmembers

Step 4 Create standby redo log file groups

SQL> alter database add standby logfile ('D:ORACLEPRODUCT10.2.0ORADATABSL01LABredo01d.log') size 50M;

Database altered.

SQL> alter database add standby logfile ('D:ORACLEPRODUCT10.2.0ORADATABSL01LABredo02d.log') size 50M;

Database altered.

SQL> alter database add standby logfile ('D:ORACLEPRODUCT10.2.0ORADATABSL01LABredo03d.log') size 50M;

Database altered.

Step 5 Verify the standby redo log file groups were created

SQL> select group#,thread#,sequence#,archived,status from v$standby_log;

GROUP# THREAD# SEQUENCE# ARC STATUS

---------- ---------- ---------- --- ----------

4 0 0 YES UNASSIGNED

5 0 0 YES UNASSIGNED

6 0 0 YES UNASSIGNED

5. Set Primary Database Initialization Parameters

--Content of pfilebsl01lab.ora

bsl01lab.__db_cache_size=79691776

bsl01lab.__java_pool_size=4194304

bsl01lab.__large_pool_size=4194304

bsl01lab.__shared_pool_size=75497472

bsl01lab.__streams_pool_size=0

*.audit_file_dest='D:oracleproduct10.2.0/admin/bsl01lab/adump'

*.background_dump_dest='D:oracleproduct10.2.0/admin/bsl01lab/bdump'

*.compatible='10.2.0.1.0'

*.control_files='D:oracleproduct10.2.0/oradata/bsl01lab/control01.ctl','D:oracleproduct10.2.0/oradata/bsl01lab/control02.ctl','D:oracleproduct10.2.0/oradata/bsl01lab/control03.ctl'

*.core_dump_dest='D:oracleproduct10.2.0/admin/bsl01lab/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_recovery_file_dest='D:oracleproduct10.2.0/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=bsl01labXDB)'

*.job_queue_processes=10

*.nls_length_semantics='CHAR'

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='D:oracleproduct10.2.0/admin/bsl01lab/udump'

db_name='bsl01lab'

db_unique_name=bsl01lab

log_archive_config='DG_CONFIG=(bsl01lab,bsl01labdg)'

control_files='D:ORACLEPRODUCT10.2.0ORADATABSL01LABCONTROL01.CTL','D:ORACLEPRODUCT10.2.0ORADATABSL01LABCONTROL02.CTL','D:ORACLEPRODUCT10.2.0ORADATABSL01LABCONTROL03.CTL'

log_archive_dest_1=

'LOCATION=D:oracleproduct10.2.0arch

VALID_FOR=(all_logfiles,all_roles)

DB_UNIQUE_NAME=bsl01lab'

log_archive_dest_2=

'SERVICE=bsl01labdg LGWR ASYNC

VALID_FOR=(online_logfiles,primary_role)

DB_UNIQUE_NAME=bsl01labdg'

LOG_ARCHIVE_DEST_STATE_1=enable

LOG_ARCHIVE_DEST_STATE_2=enable

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=bsl01labdg

FAL_CLIENT=bsl01lab

DB_FILE_NAME_CONVERT='C:data_guardbsl01lab','D:oracleproduct10.2.0oradatabsl01lab'

LOG_FILE_NAME_CONVERT=

'C:data_guardbsl01lab','D:oracleproduct10.2.0oradatabsl01lab'

STANDBY_FILE_MANAGEMENT=auto

STANDBY_ARCHIVE_DEST='C:temppriarch'

--create spfile

SQL> create spfile from pfile='d:pfilebsl01lab.ora';

File created.

6. Enable the primary DB Archiving

SQL> startup mount;

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1247900 bytes

Variable Size 83887460 bytes

Database Buffers 79691776 bytes

Redo Buffers 2945024 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

7. Create a Backup Copy of the Standby Database

--Shutdown DB

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

--Copy data files from “D:oracleproduct10.2.0oradatabsl01lab” to “C:data guardbsl01lab”

8. Create a Control File for the Standby Database

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1247900 bytes

Variable Size 83887460 bytes

Database Buffers 79691776 bytes

Redo Buffers 2945024 bytes

Database mounted.

SQL> alter database create standby controlfile as 'd:bsl01labdg.ctl';

Database altered.

SQL> alter database open;

Database altered.

9. Create the parameter file for Standby Database

Contents of the parameter file:

bsl01lab.__db_cache_size=79691776

bsl01lab.__java_pool_size=4194304

bsl01lab.__large_pool_size=4194304

bsl01lab.__shared_pool_size=75497472

bsl01lab.__streams_pool_size=0

*.audit_file_dest='C:data_guarddump'

*.background_dump_dest='C:data_guarddump'

*.compatible='10.2.0.1.0'

*.core_dump_dest='C:data_guarddump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_recovery_file_dest='C:data_guarddump'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=bsl01labXDB)'

*.job_queue_processes=10

*.nls_length_semantics='CHAR'

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='C:data_guarddump'

db_name='bsl01lab'

db_unique_name=bsl01labdg

log_archive_config='DG_CONFIG=(bsl01lab,bsl01labdg)'

control_files='C:data_guardbsl01labcontrol01.ctl','C:data_guardbsl01labcontrol02.ctl','C:data_guardbsl01labcontrol03.ctl'

log_archive_dest_1=

'LOCATION=C:data_guardarch

VALID_FOR=(all_logfiles,all_roles)

DB_UNIQUE_NAME=bsl01labdg'

log_archive_dest_2=

'SERVICE=bsl01lab LGWR ASYNC

VALID_FOR=(online_logfiles,primary_role)

DB_UNIQUE_NAME=bsl01lab'

LOG_ARCHIVE_DEST_STATE_1=enable

LOG_ARCHIVE_DEST_STATE_2=enable

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=bsl01lab

FAL_CLIENT=bsl01labdg

DB_FILE_NAME_CONVERT='D:oracleproduct10.2.0oradatabsl01lab','C:data_guardbsl01lab'

LOG_FILE_NAME_CONVERT=

'D:oracleproduct10.2.0oradatabsl01lab','C:data_guardbsl01lab'

STANDBY_FILE_MANAGEMENT=auto

STANDBY_ARCHIVE_DEST='C:tempstdarch'

10. Setup the Environment to Support the Standby Database

Step 1 Create a windows-based service

D:oracleproduct10.2.0db_1>oradim -new -sid bsl01labdg -intpwd dow -startmode manual

Instance created.

Step 2 create a password file

D:oracleproduct10.2.0db_1>set oracle_sid=bsl01labdg

C:data guarddatabase>orapwd file=PWDbsl01labdg.ora password=dow entries=5

Step 3 configure listeners for the primary and standby database

Step 4 create Oracle Net Service names

Step 5 create a server parameter file for the standby database

D:oracleproduct10.2.0db_1database>set oracle_sid=bsl01labdg

D:oracleproduct10.2.0db_1database>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 26 03:11:04 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect sys/dow as sysdba

Connected to an idle instance.

SQL> create spfile from pfile='d:pfilebsl01labdg.ora';

File created.

--Verify the new spfile

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string D:ORACLEPRODUCT10.2.0DB_1DATABASESPFILEBSL01LABDG.ORA

11. Mount the standby DB

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1247900 bytes

Variable Size 62915940 bytes

Database Buffers 100663296 bytes

Redo Buffers 2945024 bytes

Database mounted.

12. Start Redo Apply

SQL> alter database recover managed standby database disconnect from session;

Database altered.

13. Verify the status of primary and standby database

--bsl01lab window

D:oracleproduct10.2.0db_1database>set oracle_sid=bsl01lab

D:oracleproduct10.2.0db_1database>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 26 03:24:41 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect / as sysdba

Connected.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

bsl01lab OPEN

--bsl01labdg window

D:oracleproduct10.2.0db_1database>set oracle_sid=bsl01labdg

D:oracleproduct10.2.0db_1database>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 26 03:34:15 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect / as sysdba

Connected.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

bsl01labdg MOUNTED

Both instance status are OK

14. Verify archived log transport and applying service

--bsl01lab window

SQL> select name,sequence# from v$archived_log order by 2;

NAME SEQUENCE#

-------------------------------------------------- ----------

............

D:ORACLEPRODUCT10.2.0ARCH1_80_595473116.ARC 80

bsl01labdg 80

D:ORACLEPRODUCT10.2.0ARCH1_81_595473116.ARC 81

bsl01labdg 81

D:ORACLEPRODUCT10.2.0ARCH1_82_595473116.ARC 82

bsl01labdg 82

--bsl01labdg

SQL> select name,sequence# from v$archived_log order by 2;

NAME SEQUENCE#

-------------------------------------------------- ----------

…………

C:DATA_GUARDARCH1_80_595473116.ARC 80

C:DATA_GUARDARCH1_81_595473116.ARC 81

C:DATA_GUARDARCH1_82_595473116.ARC 82

--On the primary DB, switch log file by force

--bsl01lab window

SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:05.21

SQL> /

System altered.

Elapsed: 00:00:01.14

SQL> /

System altered.

Elapsed: 00:00:06.01

--Check the archived log again

--bsl01lab window

SQL> select name,sequence# from v$archived_log order by 2;

NAME SEQUENCE#

-------------------------------------------------- ----------

…………

D:ORACLEPRODUCT10.2.0ARCH1_84_595473116.ARC 84

bsl01labdg 84

D:ORACLEPRODUCT10.2.0ARCH1_85_595473116.ARC 85

NAME SEQUENCE#

-------------------------------------------------- ----------

bsl01labdg 85

--bsl01labdg window

SQL> select name,sequence# from v$archived_log order by 2;

NAME SEQUENCE#

-------------------------------------------------- ----------

…………

C:DATA_GUARDARCH1_84_595473116.ARC 84

C:DATA_GUARDARCH1_85_595473116.ARC 85

--To check whether the new archived logs have been applied

--bsl01labdg window

SQL> select sequence#,applied

2 from v$archived_log

3 order by sequence#;

SEQUENCE# APP

---------- ---

80 YES

81 YES

82 YES

83 YES

84 YES

85 YES

35 rows selected.

The archived logs are applied successfully on the standby database

Switch-over Testing

1. Verify it is possible to perform a switchover.

--bsl01lab window

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

--------------------

SESSIONS ACTIVE

SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes integer 10

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION

2 WHERE TYPE = 'USER'

3 AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);

SID PROCESS PROGRAM

---------- ------------ ----------------------------------------------------------------

86 1404:3172 emagent.exe

89 1234 OMS

90 1234OMS

93 1404:1768 emagent.exe

95 1234 OMS

98 1988 ORACLE.EXE (J000)

6 rows selected.

--To stop ‘emagent’ and ‘OMS’, open another window, and in OS run

S:>set oracle_sid=bsl01lab

S:>emctl stop agent

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0

Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.

This will stop the Oracle Enterprise Manager 10g Database Control process. Conti

nue [y/n] :y

The OracleDBConsolebsl01lab service is stopping............

The OracleDBConsolebsl01lab service was stopped successfully.

--Common Processes That Prevent Switchover

Type of Process	Process Description	Corrective Action
CJQ0	Job Queue Scheduler Process	Change the JOB_QUEUE_PROCESSES dynamic parameter to the value 0. The change will take effect immediately without having to restart the instance.
QMN0	Advanced Queue Time Manager	Change the AQ_TM_PROCESSES dynamic parameter to the value 0. The change will take effect immediately without having to restart the instance.
DBSNMP	Oracle Enterprise Manager Management Agent	Issue the emctl stop agent command from the operating system prompt.

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION

2 WHERE TYPE = 'USER'

3 AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);

no rows selected

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

--------------------

TO STANDBY

Now it is ready to switch to standby

2. Initiate the switchover on the primary database.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Database altered.

Elapsed: 00:02:57.73

3. Shut down and restart the former primary instance.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1247900 bytes

Variable Size 88081764 bytes

Database Buffers 75497472 bytes

Redo Buffers 2945024 bytes

Database mounted.

4. Verify the switchover status on the standby database

--bsl01labdg window

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

--------------------

TO PRIMARY

5. Switch the target physical standby database role to the primary role

--bsl01labdg window

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

6. Finish the transition of the standby database to the primary role.

--bsl01labdg window

SQL> alter database open;

Database altered.

Elapsed: 00:00:33.56

7. Verify the status of primary and standby database

--bsl01labdg now is the primary database

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

bsl01labdg OPEN

--bsl01lab now is the standby database

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

bsl01lab MOUNTED

--start archived log applying on bsl01lab

SQL> alter database recover managed standby database disconnect from session;

Database altered.

--bsl01labdg window

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

--bsl01lab window

SQL> select sequence#,applied from v$archived_log order by 1;

SEQUENCE# APP

--------- ---

…………

90 YES

91 YES

92 YES

successfully switched-over!

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242164/，如需轉載，請註明出處，否則將追究法律責任。