创建物理备用数据库

 

创建物理备用数据库

在创建物理备用数据库之前先查看主数据库的一些信息和对主数据库做好配置.

将主数据库置为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.