【問題解決】單機搭建dataguard的問題

在windows單機上搭建了dataguard，也遇到幾個問題，在楊廷坤版主的幫助下解決了，非常感謝。這裡記錄一下過程
主要是pfile的引數設定，密碼檔案的問題導致主庫不能歸檔到備庫
下面是具體的步驟：
使用oracle 10g
#########################################################################################

windows下單機部署oracle 10g dataguard
1，建立目錄：
E:\oracle\product\10.2.0\oradata\standby
E:\oracle\product\10.2.0\oradata\standby\archive
E:\oracle\product\10.2.0\admin\standby
E:\oracle\product\10.2.0\admin\standby\adump
E:\oracle\product\10.2.0\admin\standby\bdump
E:\oracle\product\10.2.0\admin\standby\cdump
E:\oracle\product\10.2.0\admin\standby\udump
E:\oracle\product\10.2.0\admin\standby\pfile

E:\oracle\product\10.2.0\oradata\test\archive

2,修改LISTNER.ORA

# listener.ora Network Configuration File: E:\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 = E:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )

  (SID_DESC =
      (GLOBAL_NAME = test)
      (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
      (SID_NAME= test)
    )

  (SID_DESC =
      (GLOBAL_NAME = standby)
      (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
      (SID_NAME= standby)
    )
  )

LISTENER10 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = icbcoa-6170d1dd)(PORT = 1521))
    )
  )
 
3，修改tnsnames.ora增加：
standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )
 
 
4，修改主庫pfile：

test.__db_cache_size=482344960
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__shared_pool_size=92274688
test.__streams_pool_size=4194304
*.audit_file_dest='E:\oracle\product\10.2.0/admin/test/adump'
*.audit_trail='true'
*.background_dump_dest='E:\oracle\product\10.2.0/admin/test/bdump'
*.compatible='10.2.0.1.0'
*.control_files='E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\CONTROL01.CTL','E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\CONTROL02.CTL','E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\CONTROL03.CTL'#Restore Controlfile
*.core_dump_dest='E:\oracle\product\10.2.0/admin/test/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.db_recovery_file_dest='E:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=21474836480
*.dispatchers='(protocol=TCP)'
*.job_queue_processes=10
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=197132288
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=592445440
*.shared_servers=2
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\oracle\product\10.2.0/admin/test/udump'

db_unique_name=test
log_archive_dest_1='location=E:\oracle\product\10.2.0\oradata\TEST\archive valid_for=(all_logfiles,all_roles) db_unique_name=test'
log_archive_dest_2='SERVICE=standby arch async valid_for=(online_logfiles,primary_role) db_unique_name=standby'
log_archive_config='dg_config=(test,standby)'
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable
remote_login_passwordfile='EXCLUSIVE'

standby_file_management='auto'
fal_client='test'
fal_server='standby'

db_file_name_convert=('E:\oracle\product\10.2.0\oradata\standby','D:\oracle\product\10.2.0\oradata\test')
log_file_name_convert=('E:\oracle\product\10.2.0\oradata\standby','D:\oracle\product\10.2.0\oradata\test')

5，建立standby的pfile：
*.audit_file_dest='E:\oracle\product\10.2.0/admin/standby/adump'
*.background_dump_dest='E:\oracle\product\10.2.0/admin/standby/bdump'
*.compatible='10.2.0.1.0'
*.control_files='E:\oracle\product\10.2.0\oradata\standby\control01.ctl','E:\oracle\product\10.2.0\oradata\standby\control02.ctl','E:\oracle\product\10.2.0\oradata\standby\control03.ctl'
*.core_dump_dest='E:\oracle\product\10.2.0/admin/standby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.db_recovery_file_dest='E:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=197132288
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=592445440
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\oracle\product\10.2.0/admin/standby/udump'

*.db_unique_name='standby'
#lock_name_space='standby'

log_archive_config='DG_CONFIG=(test,standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=E:\oracle\product\10.2.0\oradata\standby\archive
                    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
                    DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=test arch ASYNC
                    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                    DB_UNIQUE_NAME=test'

*.LOG_ARCHIVE_DEST_STATE_1=enable
*.LOG_ARCHIVE_DEST_STATE_2=enable

#切換備庫為主庫是要應用
*.db_file_name_convert=('E:\oracle\product\10.2.0\oradata\test','E:\oracle\product\10.2.0\oradata\standby')
*.log_file_name_convert=('E:\oracle\product\10.2.0\oradata\test','E:\oracle\product\10.2.0\oradata\standby')
*.standby_archive_dest='E:\oracle\product\10.2.0\oradata\standby\archive'
*.standby_file_management='AUTO'
*.fal_client='standby'
*.fal_server='test'
*.service_names='standby'
*.local_listener='(ADDRESS_LIST= (ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = standby)))'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(test,standby)'

STANDBY_FILE_MANAGEMENT=AUTO
instance_name=standby

standby_archive_dest='E:\oracle\product\10.2.0\oradata\standby'
background_dump_dest='E:\oracle\product\10.2.0\admin\standby\bdump'
user_dump_dest='E:\oracle\product\10.2.0\admin\standby\udump'

6，關閉主庫，拷貝資料檔案到
E:\oracle\product\10.2.0\oradata\standby

7，開啟主庫為備庫建立控制檔案：
alter database create standby controlfile as 'E:\oracle\product\10.2.0\oradata\standby\control01.ctl';
將此檔案複製兩份，分別命名為control02.ctl control03.ctl

8,建立stanby例項，建立password檔案

oradim -new -sid standby
orapwd file=E:\oracle\product\10.2.0\oradata\standby\pwdstandby.ora password=sys entries=10

orapwd file=E:\oracle\product\10.2.0\db_1\dbs\pwdtest.ora password=sys entries=10

orapwd file=E:\oracle\product\10.2.0\oradata\standby\pwdstandby.ora password=sys entries=10

後來發現建立的密碼檔案有問題，後面講述。

9,mount standby
set oracle_sid=standby
sqlplus / as sysdba

SQL> startup force nomount pfile='E:\oracle\product\10.2.0\admin\standby\pfil
nitstandby.ora';
ORACLE 例程已經啟動。

Total System Global Area  595591168 bytes
Fixed Size                  1250332 bytes
Variable Size             163580900 bytes
Database Buffers          423624704 bytes
Redo Buffers                7135232 bytes
SQL> create spfile from pfile='E:\oracle\product\10.2.0\admin\standby\pfile\initstandby.ora';

檔案已建立。

SQL> alter database mount standby database;

資料庫已更改。

SQL>

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

資料庫已更改。

SQL>

 

10,測試歸檔是否成功
連線主庫：
SQL> alter system switch logfile;

系統已更改。

SQL> alter system switch logfile;

系統已更改。

連線備庫：
SQL>  select DATABASE_ROLE from v$database;

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

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

未選定行

這裡檢視主庫和備庫的alert發現了一些問題：
Redo shipping client performing standby login
OCIServerAttach failed -1
.. Detailed OCI error val is 12514 and errmsg is 'ORA-12514: TNS: 監聽程式當前無法識別連線描述符中請求的服務
'
*** 2010-06-15 13:29:46.281 60680 kcrr.c
Error 12514 received logging on to the standby
Error 12514 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'standby'
Error 12514 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'standby'
ORA-12514: TNS: 監聽程式當前無法識別連線描述符中請求的服務
*** 2010-06-15 13:29:46.281 60680 kcrr.c
PING[ARC0]: Heartbeat failed to connect to standby 'standby'. Error is 12514.
*** 2010-06-15 13:29:46.281 58942 kcrr.c
kcrrfail: dest:2 err:12514 force:0 blast:1

上面是tns配置有誤，實際上沒有問題，需要重啟一下lsnrctl

Redo shipping client performing standby login
OCISessionBegin failed -1
.. Detailed OCI error val is 1031 and errmsg is 'ORA-01031: insufficient privileges
'
*** 2010-06-15 13:34:46.609 60680 kcrr.c
Error 1031 received logging on to the standby
Error 1031 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'standby'
Error 1031 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'standby'
ORA-01031: 許可權不足
*** 2010-06-15 13:34:46.609 60680 kcrr.c
PING[ARC0]: Heartbeat failed to connect to standby 'standby'. Error is 1031.
*** 2010-06-15 13:34:46.609 58942 kcrr.c
kcrrfail: dest:2 err:1031 force:0 blast:1

上面是由於密碼檔案的問題導致，最後將test的密碼檔案複製，重新命名為pwdstandby.ora重啟主庫和備庫後成功
如果pfile裡面的配置有誤，則有可能發生一些其他錯誤。

 

錯誤問題都解決以後，在備庫查詢可知，從備份點到現在的都歸檔了：
SQL> select sequence#, applied from v$archived_log;

SEQUENCE# APP
---------- ---
        77 YES
        78 YES
        79 YES
        80 YES
        81 YES
        82 YES
        83 YES
        84 YES
        85 YES
        86 YES
        87 YES

SEQUENCE# APP
---------- ---
        88 YES
        89 YES
        90 YES
        91 YES
        92 YES
        93 YES
        94 YES
        95 YES
        96 YES
        97 YES

已選擇21行。

SQL>

 

 

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