oracle實驗記錄 (oracle 10G dataguard(1)手工搭建)

搭建物理DG

建立物理datagurad的前提條件

archivelog, 開啟 強制日誌alter database force logging  意思 primary無論什麼情況都做redo,通過select force_logging from v$database可以看到當前資料庫強制日誌模式的狀態

手動方式

SQL> alter database force logging;

資料庫已更改。
SQL> select force_logging  from v$database;

FOR
---
YES
SQL> archive log list
資料庫日誌模式            存檔模式
自動存檔             啟用
存檔終點            USE_DB_RECOVERY_FILE_DEST
最早的聯機日誌序列     1
下一個存檔日誌序列   3
當前日誌序列           3

 

SQL> select 'host copy '||name||' d:\osbackup' from v$tablespace;

'HOSTCOPY'||NAME||'D:\OSBACKUP'
----------------------------------------------------
host copy SYSTEM d:\osbackup
host copy UNDOTBS1 d:\osbackup
host copy SYSAUX d:\osbackup
host copy USERS d:\osbackup
host copy TEMP d:\osbackup
host copy EXAMPLE d:\osbackup

已選擇6行。

SQL> host mkdir e:\standby

SQL> host mkdir e:\standby\adump

SQL> host mkdir e:\standby\bdump

SQL> host mkdir e:\standby\udump

SQL> host mkdir e:\standby\cdump

SQL> host mkdir e:\standby\archive

SQL> host mkdir e:\standby\datafile

 

進行熱備份
SQL> alter database begin backup
  2  ;

資料庫已更改。

SQL> @ d:\standbyscript\backup.txt
已複製         1 個檔案。

已複製         1 個檔案。

已複製         1 個檔案。

已複製         1 個檔案。

已複製         1 個檔案。

SQL>
SQL> alter database end backup;

資料庫已更改。

SQL> alter system switch logfile;

系統已更改。

SQL> alter system checkpoint;

系統已更改。

建立standby controlfile
SQL> alter database create standby controlfile as 'e:\standby\datafile\control01
.ctl';

資料庫已更改。

SQL> alter database create standby controlfile as 'e:\standby\datafile\control02
.ctl';

資料庫已更改。

SQL> alter database create standby controlfile as 'e:\standby\datafile\control03
.ctl';

資料庫已更改。

 

修改primary target pfile
xh.__db_cache_size=201326592
xh.__java_pool_size=4194304
xh.__large_pool_size=4194304
xh.__shared_pool_size=71303168
xh.__streams_pool_size=0
*.audit_file_dest='g:\oracle\product\10.2.0/admin/xh/adump'
*.background_dump_dest='g:\oracle\product\10.2.0/admin/xh/bdump'
*.compatible='10.2.0.1.0'
*.control_files='g:\oracle\product\10.2.0\oradata\xh\control01.ctl','g:\oracle\product\10.2.0\oradata\xh\control02.ctl','g:\oracle\product\10.2.0\oradata\xh\control03.ctl'
*.core_dump_dest='g:\oracle\product\10.2.0/admin/xh/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='xh'
*.db_recovery_file_dest='g:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='xh'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=xhXDB)'
*.fal_client='xh'
*.fal_server='standby1'
*.job_queue_processes=10
*.log_archive_dest_1='location=G:\archivelog valid_for=(all_logfiles,all_roles) db_unique_name=xh'
*.log_archive_dest_2='service=standby1 valid_for=(online_logfiles,primary_role) db_unique_name=standby1'
*.open_cursors=300
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=287309824
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='g:\oracle\product\10.2.0/admin/xh/udump'
*.log_archive_config='dg_config=(xh,standby1)'

SQL> create spfile from pfile;

檔案已建立。
SQL> shutdown immediate;
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> create spfile from pfile;

檔案已建立。

SQL> startup
ORACLE 例程已經啟動。

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              79692480 bytes
Database Buffers          201326592 bytes
Redo Buffers                7139328 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL>

 

建立standby  pfile

standby1.__db_cache_size=201326592
standby1.__java_pool_size=4194304
standby1.__large_pool_size=4194304
standby1.__shared_pool_size=71303168
standby1.__streams_pool_size=0
*.audit_file_dest='e:\standby/adump'
*.background_dump_dest='e:\standby/bdump'
*.compatible='10.2.0.1.0'
*.control_files='e:\standby\datafile\control01.ctl'
*.core_dump_dest='e:\standby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='xh'
*.db_recovery_file_dest='e:\standby\archive'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=xhXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=287309824
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='e:\standby/udump'
*.fal_client='standby1'
*.fal_server='xh'
*.db_unique_name='standby1'
*.log_archive_dest_1='location=e:\standby\archive valid_for=(all_logfiles,all_roles) db_unique_name=standby1'
*.log_archive_dest_2='service=xh valid_for=(online_logfiles,primary_role) db_unique_name=xh'
*.standby_file_management='auto'
*.service_names=standby1
*.instance_name=standby1
*.standby_archive_dest='e:\standby\archive'
*.db_file_name_convert='g:\oracle\product\10.2.0\oradata\xh','e:\standby\datafile'
*.log_file_name_convert='g:\oracle\product\10.2.0\oradata\xh','e:\standby\datafile'
*.log_archive_config='dg_config=(xh,standby1)'

 

C:\>oradim -new -sid standby1 -intpwd a831115
例項已建立。

C:\>set oracle_sid=standby1

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 11月 3 10:30:13 2009

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

已連線到空閒例程。
SQL> startup nomount pfile='e:\standby\datafile\initstandby1.ora'
ORACLE 例程已經啟動。

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              79692480 bytes
Database Buffers          201326592 bytes
Redo Buffers                7139328 bytes
SQL> create spfile from  pfile='e:\standby\datafile\initstandby1.ora'
  2  ;

檔案已建立。
SQL> shutdown immediate;
ORA-01507: ??????

ORACLE 例程已經關閉。
SQL> startup nomount
ORACLE 例程已經啟動。

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              79692480 bytes
Database Buffers          201326592 bytes
Redo Buffers                7139328 bytes
～～～～～～～～～～～～～～～～～～～～～～～～～～～將備份資料檔案 考過來
SQL> startup mount
ORACLE 例程已經啟動。

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              79692480 bytes
Database Buffers          201326592 bytes
Redo Buffers                7139328 bytes
資料庫裝載完畢。

 

ORACLE NET

primary target
C:\>hostname
PC-200901221248

 
listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = g:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )

 (SID_DESC =
      (SID_NAME = xh)
      (ORACLE_HOME = g:\oracle\product\10.2.0\db_1)
      (GLOBAL_DBNAME = xh)
    )
 )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PC-200901221248)(PORT = 1521))
    )
  )

 

LSTANDBY =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PC-200901221248)(PORT = 1522))
    )
  )

SID_LIST_LSTANDBY=

 (SID_DESC =
      (SID_NAME = standby1)
      (ORACLE_HOME = g:\oracle\product\10.2.0\db_1)
      (GLOBAL_DBNAME = standby1)
    )
)
配置完後 要重啟lsnrctl stop
lsnrctl start

lsnrctl status(檢視狀態)

 

 

 

tnsname.ora
XH =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PC-200901221248)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xh)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

STANDBY1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PC-200901221248)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STANDBY1)
    )
  )

 

SQL> host tnsping standby1

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-11月-
2009 11:13:38

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

已使用的引數檔案:
g:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

已使用 TNSNAMES 介面卡來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = PC-20090
1221248)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STAN
DBY1)))
OK (30 毫秒)

SQL> host tnsping xh(standby操作)

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-11月-
2009 11:11:10

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

已使用的引數檔案:
g:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

已使用 TNSNAMES 介面卡來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = PC-20090
1221248)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xh))
)
OK (30 毫秒)

C:\>sqlplus system/a831115@standby1

SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 11月 8 12:54:48 2009

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> select open_mode,db_unique_name from v$database;

OPEN_MODE  DB_UNIQUE_NAME
---------- ------------------------------
READ ONLY  standby1

SQL> conn system/a831115@xh
已連線。
SQL> select open_mode,db_unique_name from v$database;

OPEN_MODE  DB_UNIQUE_NAME
---------- ------------------------------
READ WRITE xh

 

standby
SQL> select name,applied from v$archived_log;

未選定行

target
SQL> alter system switch logfile;

系統已更改。

standby
SQL> select count(*)  from v$archived_log;

  COUNT(*)
----------
         7

 

target
SQL> select * from t1;

         A
----------
         1
         2

SQL> insert into t1 values(3);

已建立 1 行。

SQL> commit;

提交完成。

SQL> alter system switch logfile;

系統已更改。

standby

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

資料庫已更改。

SQL> select applied from v$archived_log;

APP
---
NO
NO
NO
NO
NO
YES
YES
YES
YES
YES

已選擇10行。

SQL> select applied from v$archived_log;

APP
---
NO
NO
NO
NO
NO
YES~~~~~~~~~~~~~~~~~~應用日誌
YES
YES
YES
YES

已選擇10行。

 

switch over
SQL> alter database recover managed standby database cancel;

資料庫已更改。
SQL> alter database open read only;

資料庫已更改。

SQL> select * from t1;

         A
----------
         1
         2
         3

SQL> 

SQL>  select DB_UNIQUE_NAME ,DATABASE_ROLE,OPEN_MODE from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
standby1                       PHYSICAL STANDBY READ ONLY

SQL>  select DB_UNIQUE_NAME ,DATABASE_ROLE,OPEN_MODE from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
xh                             PRIMARY          READ WRITE

 

SQL> select database_role,switchover_status from v$database
  2  ;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          SESSIONS ACTIVE

SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-01093: ALTER DATABASE CLOSE 僅允許在沒有連線會話時使用

提交完成。

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          SESSIONS ACTIVE~~~~~~~~~~~加with session shutdown

SQL> alter database commit to switchover to physical standby with session shutdo
wn;

資料庫已更改。

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY SWITCHOVER PENDING~~~~~~~~~~pending(或SWITCHOVER LATENT)狀態 要先恢復下

SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
第 1 行出現錯誤:
ORA-16139: 需要介質恢復

alter database recover managed standby database disconnect from session;*************
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY

SQL> alter database commit to switchover to primary;

資料庫已更改。

。
SQL> startup
ORACLE 例程已經啟動。

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              79692480 bytes
Database Buffers          201326592 bytes
Redo Buffers                7139328 bytes
資料庫裝載完畢。
SQL>  select DB_UNIQUE_NAME ,DATABASE_ROLE,OPEN_MODE from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
standby1                       PRIMARY          READ WRITE

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY

SQL>  select DB_UNIQUE_NAME ,DATABASE_ROLE,OPEN_MODE from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
xh                             PHYSICAL STANDBY MOUNTED

交換回來************

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