创建物理备用数据库
在创建物理备用数据库之前先查看主数据库的一些信息和对主数据库做好配置 .
将主数据库置为 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\SYSTEM01.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\stdbycon.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\STANDBY\STDBYCON.CTL','e:\oracle\ora92\STANDBY\STDBYCON02.CTL','e:\oracle\ora92\STANDBY\STDBYCON03.CTL'
*.core_dump_dest='e:\oracle\admin\STANDBY\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\STANDBY\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\STANDBY\udump'
*.workarea_size_policy='AUTO'
*.standby_file_management='AUTO'
*.fal_server='ORCL'
*.fal_client='ORCL2'
*.standby_archive_dest='e:\oracle\oradata\standby\stdarch'
*.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\stdbycon01.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.