WINDOWS XP平台下单位物理备用库环境塔建

 

WINDOWS环境下物理备用库实验

 

一、实验环境

操作系统：Microsoft Windows XP [版本 5.1.2600]，单位上实现

数据库　：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

         主库：

ORACLE_SID=primary

   ORACLE_HOME=D:\oracle\product\10.2.0\db_1

从库：

ORACLE_SID=primary

   ORACLE_HOME=D:\oracle\product\10.2.0\db_1

     

二、主库实验步骤

主库归档配置

STARTUP MOUNT

ALTER DATABASE ARCHIVELOG;

ARCHIVE LOG LIST

主库打开强制日志

STARTUP MOUNT

ALTER DATABASE FORCE LOGGING;

SELECT FORCE_LOGGING FROM V$DATABASE;

        主库重建口令文件

　　　　　 C:\>ORAPWD FILE=D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\PWDPRIMARY.ORA

          

　　　　 主库创建备库日志文件

         SQL>ALTER DATABASE ADD STANDBY

LOGFILE(‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\STANDBYREDO.LOG’)

SIZE 150M;

主库创建备库控制文件

             SQL>STARTUP MOUNT

             SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE

AS ‘E:\STANDBY\ORADATA\STANDBY\CONTROL01.CTL’;

　主库参数配置

　 INITPRIMARY.ORA

*.AUDIT_FILE_DEST='D:\ORACLE\PRODUCT\10.2.0/ADMIN/PRIMARY/ADUMP'

*.BACKGROUND_DUMP_DEST='D:\ORACLE\PRODUCT\10.2.0/ADMIN/PRIMARY/BDUMP'

*.COMPATIBLE='10.2.0.1.0'

*.CONTROL_FILES='D:\ORACLE\PRODUCT\10.2.0/ORADATA/PRIMARY/\CONTROL01.CTL',

'D:\ORACLE\PRODUCT\10.2.0/ORADATA/PRIMARY/\CONTROL02.CTL',、'D:\ORACLE\PRODUCT\10.2.0/ORADATA/PRIMARY/\CONTROL03.CTL'

*.CORE_DUMP_DEST='D:\ORACLE\PRODUCT\10.2.0/ADMIN/PRIMARY/CDUMP'

*.DB_BLOCK_SIZE=8192

*.DB_DOMAIN=''

*.DB_FILE_MULTIBLOCK_READ_COUNT=16

*.DISPATCHERS='(PROTOCOL=TCP) (SERVICE=PRIMARYXDB)'

*.JOB_QUEUE_PROCESSES=10

*.NLS_LANGUAGE='SIMPLIFIED CHINESE'

*.NLS_TERRITORY='CHINA'

*.OPEN_CURSORS=300

*.PGA_AGGREGATE_TARGET=96468992

*.PROCESSES=150

*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

*.SGA_TARGET=290455552

*.UNDO_MANAGEMENT='AUTO'

*.UNDO_TABLESPACE='UNDOTBS1'

*.USER_DUMP_DEST='D:\ORACLE\PRODUCT\10.2.0/ADMIN/PRIMARY/UDUMP'

*.DB_NAME='PRIMARY'

*.DB_UNIQUE_NAME=PRIMARY

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)'

*.LOG_ARCHIVE_DEST_1='LOCATION=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'

*.LOG_ARCHIVE_DEST_2='SERVICE=STANDBY LGWR ASYNC 

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'

*.FAL_SERVER=STANDBY

*.FAL_CLIENT=PRIMARY

*.STANDBY_FILE_MANAGEMENT='AUTO'

*.DB_FILE_NAME_CONVERT='E:\STANDBY\ORADATA\STANDBY\',

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\'

*.LOG_FILE_NAME_CONVERT='E:\STANDBY\ORADATA\STANDBY\',、'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

 

        主库创建SPFILE文件

       SQL>CREATE SPFILE FROM PFILE;

主库监听配置　

LISTENER.ORA

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSEXTPROC)

      (ORACLE_HOME = D:\ORACLE\PRODUCT\10.2.0\DB_1)

      (PROGRAM = EXTPROC)

    )

 (SID_DESC =

   (GLOBAL_DBNAME =ok)

   (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

   (SID_NAME=primary)

  )

 (SID_DESC =

   (GLOBAL_DBNAME =ok2)

   (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

   (SID_NAME=standby)

  )   

)

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = TCP)(HOST = PC-201006151039)(PORT = 1521))

    )

  )

       启动监听服务

        C:\>LSNRCTL STOP

        C:\>LSNRCTL START

       

网络服务配置

TNSNAMES.ORA

PRIMARY=

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = PC-201006151039)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =OK)

    )

)

STANDBY=

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = PC-201006151039)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =OK2)

    )

)

 

测试服务

        C:\>TNSPING PRIMARY

        C:\>TNSPING STANDBY

 

  主库其它操作

关闭主库，拷贝主库数据文件和备库日志文件至备库

SHUTDOWN IMMEDIATE

COPY .\PRIMARY\SYSTEM01.DBF .\STANDBY\ORADATA\SYSTEM01.DBF

COPY .\PRIMARY\SYSAUX01.DBF .\STANDBY\ORADATA\SYSAUX01.DBF

COPY .\PRIMARY\TEMP01.DBF .\STANDBY\ORADATA\TEMP01.DBF

COPY .\PRIMARY\UNDOTBS01.DBF .\STANDBY\ORADATA\UNDOTBS01.DBF

COPY .\PRIMARY\STANDBYREDO.LOG .\STANDBY\ORADATA\STANDBYREDO.LOG

      

通过主库生成的备库控制文件生成另外两个备库控制文件

       COPY E:\STANDBY\ORADATA\STANDBY\CONTROL01.CTL

E:\STANDBY\ORADATA\STANDBY\CONTROL02.CTL

COPY E:\STANDBY\ORADATA\STANDBY\CONTROL02.CTL

E:\STANDBY\ORADATA\STANDBY\CONTROL03.CTL

 

三、备库实验步骤

物理备库配置

 C:\>ORADIM –NEW –SID STANDBY

   C:\>SET ORACLE_SID=STANDBY

   C:\>SQLPLUS / AS SYSDBA

备库口令文件配置

COPY D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\PWDPRIMARY.ORA

               D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\PWDSTANDBY.ORA

备库参数配置

INITSTANDBY.ORA

  STANDBY.__DB_CACHE_SIZE=192937984

STANDBY.__JAVA_POOL_SIZE=4194304

STANDBY.__LARGE_POOL_SIZE=4194304

STANDBY.__SHARED_POOL_SIZE=83886080

STANDBY.__STREAMS_POOL_SIZE=0

*.AUDIT_FILE_DEST='E:\STANDBY\ADMIN\STANDBY\ADMIN/ADUMP'

*.BACKGROUND_DUMP_DEST='E:\STANDBY\ADMIN\STANDBY\ADMIN/BDUMP'

*.COMPATIBLE='10.2.0.1.0'

*.CONTROL_FILES='E:\STANDBY\ORADATA\STANDBY\CONTROL01.CTL',

'E:\STANDBY\ORADATA\STANDBY\CONTROL02.CTL',

'E:\STANDBY\ORADATA\STANDBY\CONTROL03.CTL'

*.CORE_DUMP_DEST='E:\STANDBY\ADMIN\STANDBY\ADMIN/CDUMP'

*.DB_BLOCK_SIZE=8192

*.DB_DOMAIN=''

*.DB_FILE_MULTIBLOCK_READ_COUNT=16

*.DISPATCHERS='(PROTOCOL=TCP) (SERVICE=STANDBYXDB)'

*.JOB_QUEUE_PROCESSES=10

*.NLS_LANGUAGE='SIMPLIFIED CHINESE'

*.NLS_TERRITORY='CHINA'

*.OPEN_CURSORS=300

*.PGA_AGGREGATE_TARGET=96468992

*.PROCESSES=150

*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

*.SGA_TARGET=290455552

*.UNDO_MANAGEMENT='AUTO'

*.UNDO_TABLESPACE='UNDOTBS1'

*.USER_DUMP_DEST='E:\STANDBY\ADMIN\STANDBY\ADMIN/UDUMP'

*.DB_NAME='PRIMARY'

*.DB_UNIQUE_NAME=STANDBY

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)'

*.LOG_ARCHIVE_DEST_1='LOCATION=E:\STANDBY\ORADATA\ARCHIVE VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=STANDBY'

*.LOG_ARCHIVE_DEST_2='SERVICE=PRIMARY LGWR ASYNC 

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'

*.FAL_SERVER=PRIMARY

*.FAL_CLIENT=STANDBY

*.STANDBY_FILE_MANAGEMENT='AUTO'

*.DB_FILE_NAME_CONVERT='D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\',

'E:\STANDBY\ORADATA\STANDBY\'

*.LOG_FILE_NAME_CONVERT='D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMARY\',

'E:\STANDBY\ORADATA\STANDBY\'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

 

备库监听配置

LISTENER.ORA

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSEXTPROC)

      (ORACLE_HOME = D:\ORACLE\PRODUCT\10.2.0\DB_1)

      (PROGRAM = EXTPROC)

    )

 (SID_DESC =

   (GLOBAL_DBNAME =OK)

   (ORACLE_HOME = D:\ORACLE\PRODUCT\10.2.0\DB_1)

   (SID_NAME=PRIMARY)

  )

 (SID_DESC =

   (GLOBAL_DBNAME =OK2)

   (ORACLE_HOME = D:\ORACLE\PRODUCT\10.2.0\DB_1)

   (SID_NAME=STANDBY)

  )   

)

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = TCP)(HOST = PC-201006151039)(PORT = 1521))

    )

  )

 

备库网络配置

TNSNAMES.ORA

PRIMARY=

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = PC-201006151039)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =OK)

    )

)

STANDBY=

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = PC-201006151039)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =OK2)

    )

)

启动加载备用库

 C:\>ORADIM –NEW –SID STANDBY

   C:\>SET ORACLE_SID=STANDBY

   C:\>SQLPLUS / AS SYSDBA

SQL>STARTUP MOUNT

PFILE= E:\STANDBY\ADMIN\STANDBY\ADMIN\PFILE\INITSTANDBY.ORA

 

四、常用操作命令

备库:

      备库上MOUNT 状态执行,起动MPR后台进程(MANAGED RECOVERY PROCESS),

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

 

      通过执行以上命令，由于后台恢复进程MRP，当前会话会变成挂起状态

为了避免数据库挂起，你能够执行DISCONNECT选项

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

      备库上查看日志应用情况

SELECT NAME,APPLIED,ARCHIVED FROM V$ARCHIVED_LOG;

主库:

   主库上启用归档模式

ALTER DATABASE ARCHIVELOG;

主库上启用强制日志

ALTER DATABASE FORCE LOGGING;

       主库上切换日志

ALTER SYSTEM SWITCH LOGFILE;

在主库上创建备库备库控件文件

ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'C:\STANDCONTROL.CTL' REUSE;

在主库上检查备库备库日志情况

SELECT STATUS ,ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

 

 

五、物理库数据同步

主库上做业务

  CREATE TABLE SCOTT.T1(I INT);

  /

  BEGIN

   FOR I IN 1..100 LOOP

    INSERT INTO SCOTT.T1(I) VALUES(I);

   END LOOP;

  END;

  /

  COMMIT;

 

  ALTER SYSTEM SWITCH LOGFILE;

  ALTER SYSTEM SWITCH LOGFILE;

  ALTER SYSTEM SWITCH LOGFILE;

 

备库上启动后台进程应用日志: 

  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

备库上查看日志应用情况       

SELECT NAME,APPLIED,ARCHIVED FROM V$ARCHIVED_LOG;

 

打开备库查看变化

  ALTER DATABASE OPEN;

  DESC SCOTT.T1

      SELECT COUNT(0) FROM SCOTT.T1;