已有的环境准备
机器一：IP: 10.0.10.85 已有普通的数据库gsweb，准备做为主库
机器二：IP: 10.0.10.55 准备新增一个备库
/** 预处理：将机器二上的原来的一个数据库的数据文件目录和ADMIN目录改名为gsweb2 保留了机器二的口令文件。
否则需要从主库机器上复制一个口令文件 */
--3.1.1 Enable Forced Logging 在主库上启用force logging模式
--Place the primary database in FORCE LOGGING mode ALTER DATABASE FORCE LOGGING;
--3.1.2 Enable Archiving and Define a Local Archiving Destination
--Ensure that the primary database is in ARCHIVELOG mode, that automatic archiving is enabled, and that you have defined a local archiving destination.
--Set the local archive destination using the following SQL statement:
-- 在主库上检查归档模式 SELECT DBID, NAME, LOG_MODE, FORCE_LOGGING FROM V$DATABASE;
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=d:oracleora92oradataarchive';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_START = TRUE SCOPE = SPFILE;
SHUTDOWN IMMEDIATE STARTUP MOUNT ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN; SELECT DBID, NAME, LOG_MODE, FORCE_LOGGING FROM V$DATABASE;

-- 3.2.1 Identify the Primary Database Datafiles
-- 在主库上确定数据文件及日志文件位置 文档上说只要数据文件
sql> SELECT NAME FROM V$DATAFILE UNION ALL SELECT NAME FROM V$TEMPFILE UNION ALL SELECT MEMBER FROM V$LOGFILE;

-- 3.2.2 Make a Copy of the Primary Database
-- 将主库上的数据文件复制到备库上
Step 1 Shut down the primary database.
sql> SHUTDOWN IMMEDIATE;
Step 2 Copy the datafiles to a temporary location.
-- 我是通过10.0.10.85d$oracleora92oradatagsweb2进行拷贝
-- unix 下使用 cp /disk1/oracle/oradata/payroll/system01.dbf /disk1/oracle/oradata/payroll/standby/system01.dbf
Step 3 Restart the primary database.
-- 启动数据库后建立从库所需的PFILE和CONTROLFILE：
sql> STARTUP;

-- 3.2.3 Create a Control File for the Standby Database
-- 在主库上创建控制文件给备库使用
sql> alter database create standby controlfile as 'd:gsweb2.ctl';

-- 3.2.4 Prepare the Initialization Parameter File to be Copied to the Standby Database
-- 在主库上创建初始化参数文件给备库使用
sql> create pfile='d:initgsweb2.ora' from spfile;

-- 3.2.5 Copy Files from the Primary System to the Standby System
--Backup datafiles created in Section 3.2.2
--Standby control file created in Section 3.2.3
--Initialization parameter file created in Section 3.2.4
-- 将上面的控制文件、初始化文件 和密码文件拷到备库的指定位置：
-- 因为备库上原来有口令文件，所以我没有拷，(在创建spfile startup nomount之前使用这个口令文件)

-- 3.2.6 Set Initialization Parameters on a Physical Standby Database
-- 在备库上修改初始化参数文件
-- 添加以下内容
...
standby_archive_dest='d:oracleoradataarchive' -- 这里指备库上的archive目录 db_file_name_convert=('d:oracleoradatagsweb','d:oracleoradatagsweb2') --前面上主库的目录，后面是备库的目录 log_file_name_convert=('d:oracleoradatagsweb','d:oracleoradatagsweb2') --同上 standby_file_management=auto remote_archive_enable=true instance_name=gsweb2 -- 此处要注意修改实例名
...

-- 修改以下内容 备库有gsweb2目录
*.background_dump_dest='D:oracleadmingsweb2bdump'
*.control_files='D:oracleoradatagsweb2GSWEB2.CTL'
*.core_dump_dest='D:oracleadmingsweb2cdump'
*.log_archive_dest_1='LOCATION=d:oracleoradataarchive' -- 这里指备库上的archive目录
*.user_dump_dest='D:oracleadmingsweb2udump' -- 文档上备注说如果在同一台机器上安装，则要增加以下参数
# The following parameter is required only if the primary and standby databases # are located on the same system.
lock_name_space=gsweb2

-- 3.2.7 Create a Windows Service 在备库上增加一个服务
WINNT> oradim -NEW -SID gsweb2 -STARTMODE manual

-- 3.2.8 Configure Listeners for the Primary and Standby Databases
-- 在两个库上配置Listeners 在主库上修改以下内容
-- 主库的listener.ora 将127.0.0.1改为10.0.10.85
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.85)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) )
-- 主库的tnsnames.ora 增加
GSWEB1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.85)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = gsweb) ) ) GSWEB2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.55)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = gsweb) ) )
-- 从库上的listener.ora
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.55)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = gsweb) (ORACLE_HOME = D:oracleora92) (SID_NAME = gsweb2) ) )
-- 从库上的tnsnames.ora 与主库一致 非必要，但好看
GSWEB1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.85)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = gsweb) ) ) GSWEB2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.55)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = gsweb) ) )
-- 两台机器上重启lsnrctl
% lsnrctl stop
% lsnrctl start

--3.2.9 Enable Dead Connection Detection on the Standby System
--在备库上启用expire_time 参数
--Enable dead connection detection by setting the SQLNET.EXPIRE_TIME parameter to 2 in the SQLNET.ORA parameter file on the standby system. For example: SQLNET.EXPIRE_TIME=2

--3.2.10 Create Oracle Net Service Names 在上面已经做过，修改tnsnames.ora
--3.2.11 Create a Server Parameter File for the Standby Database
--On an idle standby database, use the SQL CREATE statement to create a server parameter file for the standby database from the text initialization parameter file that was edited in Section 3.2.6. For example:
-- 在备库上登录后，创建spfile 如果不能登录 需要口令文件
c:> set ORACLE_SID=gsweb2
c:> sqlplus /nolog
SQL> conn / as sysdba
SQL> CREATE SPFILE FROM PFILE='initpayroll2.ora';

--3.2.12 Start the Physical Standby Database
SQL> startup nomount
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

--3.2.13 Initiate Log Apply Services --On the standby database, start log apply services as shown in the following example:
-- 在备库上启用日志应用服务
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

-- 3.2.14 Enable Archiving to the Physical Standby Database
-- 在主库上启用“归档到备库”
SQL> alter system set log_archive_dest_2='SERVICE=gsweb2' scope=both;
SQL> alter system set log_archive_dest_state_2=enable scope=both;

--Step 4 Start remote archiving.
SQL> alter system archive log current;

-- 3.3 Verifying the Physical Standby Database
-- 检验日志能否同步
-- Step 1 Identify the existing archived redo logs.
-- 在备库上查询当前的日志号
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME 2 FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------------ ------------------
10 11-JUL-02 17:50:58 11-JUL-02 17:51:03
-- Step 2 Archiving the current log.
-- 在主库上切换归档日志
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
-- Step 3 Verify that the new archived redo log was received.
-- 在备库上检查日志号是否增加
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------------ ------------------
10 11-JUL-02 17:50:58 11-JUL-02 17:51:03
11 11-JUL-02 17:51:03 11-JUL-02 18:34:11
The logs are now available for log apply services to apply redo data to the standby database.
Step 4 Verify that the new archived redo log was applied. On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log was applied.
-- 在备库检验日志是否已应用
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG 2 ORDER BY SEQUENCE#;
SEQUENCE# APP
--------- ---
10 YES 11 YES
-- 已应用 结束
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------