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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8319781/viewspace-704760/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8319781/viewspace-704760/