Data Guard 建立（ASM)

主庫引數檔案內容如下：

test.__db_cache_size=130023424
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__shared_pool_size=75497472
test.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.background_dump_dest='/u01/app/oracle/admin/test/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+DATA/test/controlfile/current.261.733318571','+DATA/test/controlfile/current.260.733318571'
*.core_dump_dest='/u01/app/oracle/admin/test/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=master'
*.open_cursors=300
*.pga_aggregate_target=71303168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=214958080
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/test/udump'
DB_UNIQUE_NAME=master
LOG_ARCHIVE_CONFIG='DG_CONFIG=(master,standby)'
LOG_ARCHIVE_DEST_2='SERVICE=standby_db 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
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=standby_db
FAL_CLIENT=master_db
STANDBY_FILE_MANAGEMENT=AUTO

備庫引數檔案內如下：

test.__db_cache_size=130023424
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__shared_pool_size=75497472
test.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.background_dump_dest='/u01/app/oracle/admin/test/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+DATA/standby/controlfile/backup.273.735399141','+DATA/standby/controlfile/backup.277.735399141'
*.core_dump_dest='/u01/app/oracle/admin/test/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.open_cursors=300
*.pga_aggregate_target=71303168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=214958080
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/test/udump'
DB_UNIQUE_NAME=standby
LOG_ARCHIVE_CONFIG='DG_CONFIG=(master,standby)'
LOG_ARCHIVE_DEST_2='SERVICE=master_db LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=master'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=master_db
FAL_CLIENT=standby_db
STANDBY_FILE_MANAGEMENT=AUTO

主庫LISTENER設定（包括ASM的相關設定)：
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  (SID_DESC =

    (GLOBAL_DBNAME = test)

    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)  

    (SID_NAME = test)

    )
  (SID_DESC =
 
  (SID_NAME = +ASM)

  (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

 )

)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = db_master)(PORT = 1521))
    )
  )

LISTENERASM =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = db_master)(PORT = 1521))
    )
  )
客戶端TNS設定（包括連線ASM的相關設定）

db_# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

MASTER_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )

STANDBY_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )

ASM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = +ASM)
    )
  )

C:\Documents and Settings\Administrator>sqlplus sys/lyf625@asm as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 12月 8 11:41:49 2010

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

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show sga;

Total System Global Area   83886080 bytes
Fixed Size                  1217836 bytes
Variable Size              57502420 bytes
ASM Cache                  25165824 bytes
SQL>（連線到ASM例項）

-> rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 10 18:01:53 2010

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

connected to target database: test (not mounted)

RMAN> run {
2> set controlfile autobackup format for device type disk to '+DATA/test/controlfile/%F';
3> restore controlfile from '/u01/app/control.bak';
4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

Starting restore at 10-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA/standby/controlfile/backup.256.734724225
output filename=+DATA/standby/controlfile/backup.257.734724225
Finished restore at 10-NOV-10

RMAN> exit

Recovery Manager complete.
db_Standby-> exit
exit

SQL> alter database mount;

Database altered.
db_Standby-> rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 10 18:04:29 2010

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

connected to target database: TEST (DBID=2031465002, not open)

RMAN> restore database;

Starting restore at 10-NOV-10
Starting implicit crosscheck backup at 10-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=120 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 10-NOV-10

Starting implicit crosscheck copy at 10-NOV-10
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 10-NOV-10

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/test/datafile/system.256.733318485
restoring datafile 00002 to +DATA/test/datafile/undotbs1.258.733318485
restoring datafile 00003 to +DATA/test/datafile/sysaux.257.733318485
restoring datafile 00004 to +DATA/test/datafile/users.259.733318485
restoring datafile 00005 to +DATA/test/datafile/example.269.733318631
restoring datafile 00006 to +DATA/test/datafile/ceshi.271.733405923
restoring datafile 00007 to +DATA/test/datafile/ceshi.272.733406097
restoring datafile 00008 to +DATA/test/datafile/ceshi.273.733406115
channel ORA_DISK_1: reading from backup piece /u01/app/0jlsq5cr_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/0jlsq5cr_1_1 tag=TAG20101112T075819
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at 10-NOV-10

RMAN> exit

Recovery Manager complete.
db_Standby-> exit
exit

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

Database altered.

Data Gurad 主庫和備庫的切換：

主庫全部操作如下（切換後變為備庫）：

select switchover_status from  v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL>select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------- -------------------- -------------------- --------------------
READ WRITE MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO STANDBY

SQL> SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS NOT NULL;

  COUNT(*)
----------
         1
SQL>  alter   database  commit to switchover to physical standby;

Database altered.
SQL> shutdown
immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> SP2-0042: unknown command "immediate" - rest of line ignored.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  218103808 bytes
Fixed Size                  1218604 bytes
Variable Size              83888084 bytes
Database Buffers          130023424 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            89

SQL> alter database open read only;

Database altered.

SQL> select * from leviton;

NAME
----------
HELLO
leviton
Good
Bye

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

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select * from leviton;（驗證切換後，在主庫上新增的兩條記錄，在備庫有沒有生成？關於新增的兩條記錄請參看下面在主庫上的操作）

NAME
----------
HELLO
leviton
Good
Bye
ok
standby

6 rows selected.

備庫全部操作如下(切換後變為主庫）：

select switchover_status from  v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------- -------------------- -------------------- --------------------
MOUNTED    MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  NOT ALLOWED

SQL> SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS NOT NULL;

  COUNT(*)
----------
         2

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database open;

Database altered.

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------- -------------------- -------------------- --------------------
READ WRITE MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  SESSIONS ACTIVE

SQL> insert into leviton values('ok');(在切換後的主庫上插入兩條新紀錄）

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            89

SQL> insert into leviton values('standby');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> commit;

Commit complete.

SQL> !

10g物理standby主備switchover方式切換詳述

以下給大家展現一下10g物理standby主備之間通過switchover方式進行切換的詳細步驟，供參考。

1、主庫檢查是否為“TO STANDBY”狀態，若不是，需要重新啟動一下主庫（主庫ora10g操作）
sys@ora10g> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

sys@ora10g> startup force ;
ORACLE instance started.

Total System Global Area  104857600 bytes
Fixed Size                  1266056 bytes
Variable Size              83889784 bytes
Database Buffers           16777216 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.
sys@ora10g> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

2.將primary轉換為standby角色（主庫ora10g操作）
sys@ora10g> alter database commit to switchover to physical standby;

Database altered.

3.原主庫重啟動到mount狀態（原主庫ora10g操作）
NotConnected@> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
NotConnected@>
NotConnected@> startup mount;
ORACLE instance started.

Total System Global Area  104857600 bytes
Fixed Size                  1266056 bytes
Variable Size              88084088 bytes
Database Buffers           12582912 bytes
Redo Buffers                2924544 bytes
Database mounted.

4.檢查原備庫是否為“TO PRIMARY”狀態，如果為“SWITCHOVER PENDING”狀態，需要先進行一下恢復再切換 （待切換備庫ora10gdg操作）
ora10g@linux5 /home/oracle$ export ORACLE_SID=ora10gdg
ora10gdg@linux5 /home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 28 08:26:53 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options

NotConnected@> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SWITCHOVER PENDING

NotConnected@> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required

NotConnected@> alter database recover managed standby database disconnect from session;

Database altered.

NotConnected@> alter database recover managed standby database cancel;

Database altered.

NotConnected@> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

5.原備庫轉換角色到primary （待切換備庫ora10gdg操作）
NotConnected@> alter database commit to switchover to primary;

Database altered.

6.主備切換完成，open新的primary資料庫  （待切換備庫ora10gdg操作）
NotConnected@> alter database open;

Database altered.

7.最後驗證階段
1).新的primary庫在sec使用者建立一個新表test_new
sec@ora10g> create table test_new (a int);

Table created.

sec@ora10g> insert into test_new values ( 100 );

1 row created.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select * from test_new;

         A
----------
       100

sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> alter system switch logfile;

System altered.

sys@ora10g> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            69

2).檢視新standby庫，驗證切換是否成功
ora10gdg@linux5 /home/oracle$ export ORACLE_SID=ora10g
ora10g@linux5 /home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 28 08:55:09 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options

NotConnected@> alter database recover managed standby database disconnect from session;

Database altered.

NotConnected@> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            69

NotConnected@> alter database recover managed standby database cancel;

Database altered.

NotConnected@> alter database open read only;

Database altered.

NotConnected@> conn sec/sec
Connected.
sec@ora10g> select * from test_new;

         A
----------
       100

OK！到此主備之間的switchover方式切換成功。

secooler
09.03.27

-- The End --

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