oracle dataguard physical standby test(1)

 

//主库配置-oracle软件安装
2012-10-29
安装oracle10205 base on win2k3 x64
db_name=orcl
sid=orcl
字符集=zhs16gbk
sys/abcdefg
oracle_base=C:oracleproduct10.2.0
oracle_home=C:oracleproduct10.2.0db_1
 
//备库配置-oracle软件安装
2012-10-29
安装oracle10205 base on win2k3 x64
 
//主库配置-归档、force logging模式
alter database force logging;
 
———变更成归档模式———————————————————————–
startup mount
alter database archivelog
alter system set log_archive_dest_1='LOCATION=e:arch' scope=spfile
—————————————————————————————————–
 
//创建文本型的初始化参数文件给后续的备库使用(后续启动备库使用)
SQL> create pfile='e:initorcl.ora' from spfile
 
//在主库创建备库的控制文件
 
SQL> alter database create standby controlfile as 'e:controlbak.ctl'
 
//配置主库的监听
主库的监听配置文件如下
# listener.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadminlistener.ora
# Generated by Oracle configuration tools.
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:oracleproduct10.2.0db_1)
      (PROGRAM = extproc)
    )
(SID_DESC =
(GLOBAL_DBNAME= orcl)
(ORACLE_HOME = C:oracleproduct10.2.0db_1)
(SID_NAME = orcl)
)
)
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jack)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
 
———————————————————————————————————————
 
//配置主库的tnsnames.ora,加入对primary和standby的tns解析名字段
 
配置注意:service name都一样。网络名分别为primary和standby
 
//配置主库传输归档日志到备库的参数
SQL> alter system set log_archive_dest_2='service=standby reopen=30 DB_UNIQUE_NAME=orcl' scope=spfile;
 
关闭主库,冷备相关的文件,涉及的文件:数据文件、pwdfile、构造的init文本参数文件。
 
//拷贝完毕所需的文件后,开始启动主库
 
—–开始进入备库的配置——-
//用oradim工具创建备库的orcl实例并设置服务为自动启动的
C:Documents and SettingsAdministrator>oradim -new -sid orcl -startmode m
Instance created.
 
C:Documents and SettingsAdministrator>oradim -edit -sid orcl -startmode a
通常即使设置了startmode为auto,后续最好去services.msc上查看一些,也许它还是"手工"的,以免不必要的麻烦。
 
//创建密码文件
将从主库拷贝过来的pwdfile拷贝至备库的$ORACLE_BASE/database目录下
 
//在备库主机上创建相应的目录:bdump、cdump、udump、pfile、adump、dpdump、oradataorcl
mkdir C:oracleproduct10.2.0adminorclbdump
mkdir C:oracleproduct10.2.0adminorclcdump
mkdir C:oracleproduct10.2.0adminorcladump
mkdir C:oracleproduct10.2.0adminorclpfile
mkdir C:oracleproduct10.2.0adminorcldpdump
mkdir C:oracleproduct10.2.0adminorcludump
mkdir C:oracleproduct10.2.0oradataorcl
 
//在备库创建监听和配置tnsnames.ora
监听的配置文件内容参考
—————————————————————————————————
# listener.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadminlistener.ora
# Generated by Oracle configuration tools.
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:oracleproduct10.2.0db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_NAME = orcl)
      (ORACLE_HOME = C:oracleproduct10.2.0db_1)
      (SID_NAME = orcl)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = peter)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
——————————————————————————————————
 
备库的tnsnames.ora中需要配置对于standby与primary的tns解析,过程与主库的一致,不赘述。
 
//配置备库的初始化参数文件
编辑文本的参数文件,添加如下的内容
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.standby_archive_dest='c:arch'
*.fal_server='PRIMARY'
*.fal_client='STANDBY'
注意:在原initorcl.ora文本文件中包含了主库一些配置信息,例如:归档的位置(log_archive_dest_1)需要注意。
 
//配置备库的controlfile
将在主库中生成的standby控制文件复制三份,放到oradata/orcl目录中。
 
 
//启动备库
 
SQL> create spfile from pfile;
 
File created.
 
SQL> startup noumount
SP2-0714: invalid combination of STARTUP options
SQL> startup nomount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
 
Total System Global Area  293601280 bytes
Fixed Size                  2077040 bytes
Variable Size             100667024 bytes
Database Buffers          184549376 bytes
Redo Buffers                6307840 bytes
SQL> select status from v$instance;
 
STATUS
————————
STARTED
 
SQL> alter database mount standby database;
 
Database altered.
 
SQL> alter database recover managed standby database disconnect from session;
 
Database altered.
 
//进行一些基本的测试工作
在主、备库之间测试一下互相的ping、tnsping,查看是否连通
 
测试生产主库的归档日志传输情况
在主库上执行,如下命令,然后在主库的归档路径与备库的归档路径上是否有相应的归档日志文件
SQL> alter system switch logfile;
 
System altered.
 
主库归档路径:log_archive_dest_1
备库的归档路径:standby_archive_dest
在主库的e:arch目录和备库的c:arch目录中都能产生相应的归档日志文件
 
————–主备切换测试——————
在主库的scott用户下创建一个测试表(test),里面是dba_users中的内容
 
//主库中增加如下的参数
alter system set standby_file_management=auto scope=spfile
alter system set remote_archive_enable=true scope=spfile
alter system set standby_archive_dest='e:standby_archive_dest' scope=spfile
alter system set fal_server='standby' scope=spfile
alter system set fal_client='primary' scope=spfile
 
//查看switchover状态
在没有切换主库到备库之前的switchover状态
SQL> select switchover_status from v$database;
 
SWITCHOVER_STATUS
——————–
SESSIONS ACTIVE
 
session active说明有session连接在上面。
 
开始切换主库到备库
SQL> alter database commit to switchover to physical standby;
 
Database altered.
 
//启动到mount和应用日志状态
SQL> shutdown immediate
ORA-01507: database not mounted
 
 
ORACLE instance shut down.
SQL> startup nomount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
 
Total System Global Area  293601280 bytes
Fixed Size                  2077040 bytes
Variable Size             100667024 bytes
Database Buffers          184549376 bytes
Redo Buffers                6307840 bytes
SQL> alter database mount standby database;
 
Database altered.
 
SQL> alter database recover managed standby database disconnect from session;
 
Database altered.
 
 
————–备库操作—————-
 
//在备库中增加如下的参数
SQL> select status from v$instance;
 
STATUS
————
MOUNTED
 
SQL> alter system set log_archive_dest_1='location=c:arch_after' scope=spfile;
 
System altered.
 
alter system set log_archive_dest_2='service=primary valid_for=(online_logfiles,primary_role) db_unique_name=orcl' scope=spfile
SQL> alter system set log_archive_dest_2='service=primary valid_for=(online_logfiles,primary_role) db_unique_name=orcl' scope=spfile;
 
System altered.
 
//停止应用日志,这个操作在主库上执行
alter database recover managed standby database cancel
 
//在备库上执行切换到主库
SQL> alter database commit to switchover to primary;
 
SQL> shutdown immediate
ORA-01109: database not open
 
 
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
 
Total System Global Area  293601280 bytes
Fixed Size                  2077040 bytes
Variable Size             100667024 bytes
Database Buffers          184549376 bytes
Redo Buffers                6307840 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;
 
System altered.
 
SQL> archive log list;
Database log mode               Archive Mode
Automatic archival                  Enabled
Archive destination                 c:arch_after
Oldest online log sequence     9
Next log sequence to archive   10
Current log sequence           10
SQL>
 
//切换之后查看switchover_status(在原来的备库上查询,切换后的主库)
SQL> select switchover_status from v$database;
 
SWITCHOVER_STATUS
——————–
TO STANDBY
 
//在oracle online doc上,这个字段的解释如下
TO STANDBY – This is a primary database and is allowed to switch over to a standby database
 
//切换之后,查看当前主库的角色和状态。
SQL> select database_role,protection_mode from v$database;
 
DATABASE_ROLE    PROTECTION_MODE
—————- ——————–
PRIMARY          MAXIMUM PERFORMANCE
 
//查看切换之后,备注中scott用户下是否有test这个对象,其中的内容是否存在,同时与主库一致。
 
–EOF–