創建物理備用數據庫
2024-07-21 02:06:24
供稿：網友
創建物理備用數據庫
在創建物理備用數據庫之前先查看主數據庫的一些信息和對主數據庫做好配置.
將主數據庫置為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.
本文來源于網頁設計愛好者web開發社區http://www.html.org.cn收集整理，歡迎訪問。
