建立物理備用資料庫

來源:互聯網
上載者:User
建立|資料|資料庫
 

建立物理備用資料庫

在建立物理備用資料庫之前先查看主要資料庫的一些資訊和對主要資料庫做好配置.

將主要資料庫置為FORCE LOGGING模式.在主要資料庫建立之後做如下操作:

SQL>ALTER DATABASE FORCE LOGGING;

確認主要資料庫是歸檔的並定義好本地歸檔.如下:

SQL >ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=e:\oracle\oradata\orcl\archive  MANDATORY' SCOPE=BOTH;

 在主節點a確認主要資料庫的資料檔案的位置和檔案名稱.

   SQL>select name from v$datafile;

NAME

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

E:\ORACLE\ORA92\ORCL YSTEM01.DBF

E:\ORACLE\ORA92\ORCL\UNDOTBS01.DBF

E:\ORACLE\ORA92\ORCL\CWMLITE01.DBF

E:\ORACLE\ORA92\ORCL\DRSYS01.DBF

E:\ORACLE\ORA92\ORCL\EXAMPLE01.DBF

E:\ORACLE\ORA92\ORCL\INDX01.DBF

E:\ORACLE\ORA92\ORCL\ODM01.DBF

E:\ORACLE\ORA92\ORCL\TOOLS01.DBF

E:\ORACLE\ORA92\ORCL\USERS01.DBF

E:\ORACLE\ORA92\ORCL\XDB01.DBF

 做上面查詢得出來的資料檔案的物理備份.將其備份到一個臨時的位置中.

 SQL>SHUTDOWN IMMEDIATE;

 SQL>EXIT

 將E:\ORACLE\ORA92\ORCL整個目錄COPY到a節點的F盤的oracle目錄下.

 在拷貝完之後再啟動資料庫

 SQL>STARTUP;

 SQL>ARCHIVE LOG LIST;

 在主節點a為備用資料庫建立備用控制檔案

SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘f:\oracle tdbycon.ctl’;

建立初始化參數檔案

SQL>CREATE PFILE=’f:\oracle\initstdbyorcl.ora’ FROM SPFILE;

將上面幾步所得到的檔案從主節點a拷貝到備用節點b上.

修改並添加一些參數後如下:

 

*.aq_tm_processes=1

*.background_dump_dest='e:\oracle\admin\orcl\bdump'

*.compatible='9.2.0.0.0'

*.control_files='e:\oracle\ora92 TANDBY TDBYCON.CTL','e:\oracle\ora92 TANDBY TDBYCON02.CTL','e:\oracle\ora92 TANDBY TDBYCON03.CTL'

*.core_dump_dest='e:\oracle\admin TANDBY\cdump'

*.db_block_size=16384

*.db_cache_size=137363456

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

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

*.fast_start_mttr_target=300

*.hash_join_enabled=TRUE

*.instance_name='orcl2'

*.java_pool_size=33554432

*.job_queue_processes=10

*.large_pool_size=27262976

*.log_archive_dest_1='LOCATION=e:\oracle\oradata TANDBY\archive MANDATORY'

*.log_archive_format='log%d_%t_%s.arc'

*.log_archive_start=true

*.open_cursors=300

*.pga_aggregate_target=80000000

*.processes=150

*.query_rewrite_enabled='FALSE'

*.remote_login_passwordfile='EXCLUSIVE'

*.shared_pool_size=45088768

*.sort_area_size=524288

*.sql_trace=FALSE

*.star_transformation_enabled='FALSE'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='e:\oracle\admin TANDBY\udump'

*.workarea_size_policy='AUTO'

*.standby_file_management='AUTO'

*.fal_server='ORCL'

*.fal_client='ORCL2'

*.standby_archive_dest='e:\oracle\oradata tandby tdarch'

*.utl_file_dir='e:\oracle'

*.remote_archive_enable='TRUE'

 

 

在備用資料庫一端建立一個新的執行個體.如下操作:

 

c:\>oradim –new –sid orcl2 –startmode m

 

將拷貝過來的檔案放到e:\oracle\ora92底下,並修改檔案夾名為orcl2

修改e:\oracle\ora92\orcl2下的控制檔案,將其中的control01.ora, control02.ora, control03.ora刪掉,將f:\oracle tdbycon01.ora檔案拷貝到e:\oracle\ora92\orcl2目錄下.並複製和修改其名為stdbycon02.ora, stdbycon03.ora

在e:\oracle\admin下建立orcl2檔案夾,並在其底下建立三個檔案夾,分別叫bdump,cdump,udump

 

在主節點a配置listner.ora和tnsnames.ora , sqlnet.ora配置後檔案內容分別如下:

listener.ora檔案為:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION_LIST =

      (DESCRIPTION =

       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.122)(PORT = 1521))

      )

    )

    (DESCRIPTION_LIST =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

      )

    )

  )

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = e:\oracle\ora92)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = e:/oracle/ora92)

      (SID_NAME = orcl)

    )

  )

tnsnames.ora檔案為:

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.122)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

STANDBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.222)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

 

sqlnet.ora檔案為:

 

SQLNET.AUTHENTICATION_SERVICES= (NTS)

 

NAMES.DIRECTORY_PATH= (HOSTNAME,TNSNAMES, ONAMES)

 

在備用節點b配置listner.ora和tnsnames.ora,sqlnet.ora配置後檔案內容分別如下:

其中配置sqlnet.ora檔案中的參數sqlnet.expire_time是enable死串連偵測

listener.ora檔案為:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

      )

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.222)(PORT = 1521))

      )

    )

  )

 

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl2)

      (ORACLE_HOME = e:\oracle\ora92)

      (SID_NAME = ORCL)

    )

    (SID_DESC =

      (PROGRAM = extproc)

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = e:\oracle\ora92)

    )

  )

 

tnsnames.ora檔案為:

 

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.122)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

STANDBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.222)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

 

sqlnet.ora檔案為:

 

SQLNET.AUTHENTICATION_SERVICES= (NTS)

 

NAMES.DIRECTORY_PATH= (HOSTNAME,TNSNAMES, ONAMES)

 

SQLNET.EXPIRE_TIME=2

 

為備用資料庫建立Server Parameter File

create spfile from pfile;

啟動備用資料庫為MOUNT狀態

SQL>startup nomout

SQL>alter database mount standby database;

初始log apply services

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

在主節點設定遠程歸檔目錄:

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STANDBY’ SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

啟動遠程歸檔:

SQL>ALTER SYSTEM ARCHIVE LOG CURRENT;

確認遠程歸檔成功:

SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

  2  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 

 SEQUENCE# FIRST_TIME         NEXT_TIME

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

         8 11-JUL-02 17:50:45 11-JUL-02 17:50:53

         9 11-JUL-02 17:50:53 11-JUL-02 17:50:58

        10 11-JUL-02 17:50:58 11-JUL-02 17:51:03

 

3 rows selected.


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。