Oracle主备库配置过程

实验环境
操作系统：RHEL AS4
数据库：Version 10.2.0.1.0
一台机器上，装两个数据库，来进行主备切换实验。多台机器道理相同。
整个操作过程为我实验成功后，根据记忆整理，如有遗漏，请指正。
隔壁老王　2012年01月10日

一、安装设置详细过程
正常安装一个数据库，可以用DBCA，也可以手动建库，手动建库数据文件可以小一点儿，实验速度会快一点儿。
数据库名：beijing，初始的主库，备库名：shanghai

1.生成pfile，根据主备库的要求，进行参数修改
SQL> create pfile='/dbfile/initbeijing.ora' from spfile;
#默认带的参数
*.compatible='10.2.0.1.0'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='beijing'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
log_archive_format=%t_%s_%r.arc

#DUMP目录，也是默认带的，需要修改
*.audit_file_dest='/oracle/admin/beijing/adump'
*.background_dump_dest='/oracle/admin/beijing/bdump'
*.user_dump_dest='/oracle/admin/beijing/udump'
*.core_dump_dest='/oracle/admin/beijing/cdump'

#主备切换需要增加的参数
db_unique_name=beijing
log_archive_config='dg_config=(beijing,shanghai)'
control_files='/dbfile/datafile/beijing/control01.ctl',
'/dbfile/datafile/beijing/control02.ctl',
'/dbfile/datafile/beijing/control03.ctl'
log_archive_dest_1=
'location=/dbfile/logfile/beijing/ valid_for=(all_logfiles,all_roles) db_unique_name=beijing'
log_archive_dest_2=
'service=shanghai lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=shanghai'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
standby_file_management=auto

#切换时的一些设置
fal_server=shanghai
fal_client=beijing
db_file_name_convert='shanghai','beijing'
log_file_name_convert='shanghai','beijing'

2.生成spfile，要在数据库关闭状态下进行
SQL> create spfile from pfile='/dbfile/initbeijing.ora';

3.打开到mount状态，启动归档模式
SQL> alter database mount;
SQL> alter database archivelog;

4.创建备库用的控制文件
SQL> alter database create standby controlfile as '/dbfile/beijing.ctl';

5.关闭数据库，准备备库所需的文件，替换控制文件，然后启动主库(beijing)
SQL> shutdown immediate;
拷贝数据文件，相当于冷备，替换控制文件
[oracle@linux ~]$ cp -r /dbfile/datafile/beijing /dbfile/datafile/shanghai
[oracle@linux ~]$ cp /dbfile/beijing.ctl /dbfile/datafile/shanghai/control01.ctl
[oracle@linux ~]$ cp /dbfile/beijing.ctl /dbfile/datafile/shanghai/control02.ctl
[oracle@linux ~]$ cp /dbfile/beijing.ctl /dbfile/datafile/shanghai/control03.ctl
创建归档日志目录
[oracle@linux ~]$ mkdir -p /dbfile/datafile/shanghai
创建dump目录，简单起见，拷贝一下，因为有好几个dump目录。
[oracle@linux ~]$ cp -r $ORACLE_BASE/admin/beijing $ORACLE_BASE/admin/shanghai
启动主库
SQL> startup

6.修改tnsnames.ora，在$ORACLE_HOME/network/admin目录中，如果没有，就新建一个
在文件中增加以下内容，也就是两个库的别名
shanghai =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = shanghai)
    )
  )
beijing =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = beijing)
    )
  )
 
7.启动监听，然后测试一下别名
[oracle@linux ~]$ lsnrctrl start
[oracle@linux ~]$ tnsping beijing
[oracle@linux ~]$ tnsping shanghai

8.创建备库的密码文件，库名为shanghai
[oracle@linux ~]$ orapwd $ORACLE_HOME/dbs/orapwshanghai password=oracle

9.使用主库的数据文件（刚才拷贝了），创建备库的参数文件
修改主库的参数文件initbeijing.ora，保存为initshanghai.ora
*.compatible='10.2.0.1.0'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='beijing'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
log_archive_format=%t_%s_%r.arc

*.audit_file_dest='/oracle/admin/shanghai/adump'
*.background_dump_dest='/oracle/admin/shanghai/bdump'
*.user_dump_dest='/oracle/admin/shanghai/udump'
*.core_dump_dest='/oracle/admin/shanghai/cdump'

db_unique_name=shanghai
log_archive_config='dg_config=(beijing,shanghai)'
control_files='/dbfile/datafile/shanghai/control01.ctl',
'/dbfile/datafile/shanghai/control02.ctl',
'/dbfile/datafile/shanghai/control03.ctl'
log_archive_dest_1=
'location=/dbfile/logfile/shanghai/ valid_for=(all_logfiles,all_roles) db_unique_name=shanghai'
log_archive_dest_2=
'service=beijing lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=beijing'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable

fal_server=beijing
fal_client=shanghai
db_file_name_convert='beijing','shanghai'
log_file_name_convert='beijing','shanghai'
standby_file_management=auto

10.启动备库(shanghai)，使用pfile(刚才修改的)生成spfile
[oracle@linux ~]$ export ORACLE_SID=shanghai
[oracle@linux ~]$ sqlplus / as sysdba
SQL> create spfile from pfile='/dbfile/initshanghai.ora';

11.启动备库到mount状态，然后启动redo应用
SQL> startup mount
SQL> alter database recover managed standby database disconnect from session

12.检查日志，看设置是否成功
主库切换日志
SQL> alter system switch logfile;
备库查看有无新收到的日志
SQL> select sequence#,first_time,next_time from v$archived_log order by 1;

SEQUENCE# FIRST_TIME          NEXT_TIME
---------- ------------------- -------------------
        16 2012-01-10 08:51:41 2012-01-10 08:51:45
        16 2012-01-10 08:51:41 2012-01-10 08:51:45
        17 2012-01-10 08:51:45 2012-01-10 08:51:48
        17 2012-01-10 08:51:45 2012-01-10 08:51:48
        18 2012-01-10 08:51:48 2012-01-10 08:52:26
        18 2012-01-10 08:51:48 2012-01-10 08:52:26
        19 2012-01-10 08:52:26 2012-01-10 08:53:57
        19 2012-01-10 08:52:26 2012-01-10 08:53:57
        20 2012-01-10 08:53:57 2012-01-10 08:54:06
        20 2012-01-10 08:53:57 2012-01-10 08:54:06
也可以到/dbfile/logfile/shanghai目录中，看一下有无归档日志文件

二、主备切换

1.查看主库(beijing)的状态，是TO STANDBY状态，可切换成备库。
SQL> select db_unique_name,switchover_status,database_role from v$database;

DB_UNIQUE_NAME       SWITCHOVER_STATUS                        DATABASE_ROLE
-------------------- ---------------------------------------- --------------------------------
beijing              TO STANDBY                               PRIMARY

2.执行命令，切换成备库
SQL> alter database commit to switchover to physical standby;

Database altered.

3.查看一下备库(shanghai)，可以切换成主库。
SQL> select db_unique_name,switchover_status,database_role from v$database;

DB_UNIQUE_NAME       SWITCHOVER_STATUS                        DATABASE_ROLE
-------------------- ---------------------------------------- --------------------------------
shanghai             TO PRIMARY                               PHYSICAL STANDBY
(在主库切换之前，SWITCHOVER_STATUS是NOT ALLOWED)

4.执行命令，切换成主库，打开库，就可以执行正常操作了。
SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database open;

Database altered.

5.查看一下数据文件，没有显示，重新登录一下。发现数据文件按参数设置的方式发生的变化。
SQL> select name from v$datafile;

SQL> conn / as sysdba
Connected.
SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------------------------
/dbfile/datafile/shanghai/system01.dbf
/dbfile/datafile/shanghai/undotbs01.dbf
/dbfile/datafile/shanghai/sysaux01.dbf
/dbfile/datafile/shanghai/users01.dbf

关闭原来的主库(beijing)，启动Redo应用
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218968 bytes
Variable Size              88082024 bytes
Database Buffers          188743680 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> select db_unique_name,switchover_status,database_role from v$database;

DB_UNIQUE_NAME       SWITCHOVER_STATUS                        DATABASE_ROLE
-------------------- ---------------------------------------- --------------------------------
beijing              TO PRIMARY                               PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;

Database altered.

三、通过日志检查切换结果

现在的备库(beijing)上看一下归档日志
SQL> select sequence#,first_time,next_time from v$archived_log order by 1;

SEQUENCE# FIRST_TIME          NEXT_TIME
---------- ------------------- -------------------
        21 2012-01-10 08:54:06 2012-01-10 09:49:30
        21 2012-01-10 08:54:06 2012-01-10 09:49:30
        22 2012-01-10 09:49:30 2012-01-10 09:49:32
        22 2012-01-10 09:49:30 2012-01-10 09:49:32
        23 2012-01-10 09:49:32 2012-01-10 09:49:47
        23 2012-01-10 09:49:32 2012-01-10 09:49:47(此时还是主库，有两个同序号的日志)
        24 2012-01-10 09:49:47 2012-01-10 09:52:43

现在的主库(shanghai)切换一下日志       
SQL> alter system switch logfile;

System altered.

现在的备库(beijing)上看一下归档日志，发现同样新增了一条日志，序号为25
SQL> select sequence#,first_time,next_time from v$archived_log order by 1;

SEQUENCE# FIRST_TIME          NEXT_TIME
---------- ------------------- -------------------
        21 2012-01-10 08:54:06 2012-01-10 09:49:30
        21 2012-01-10 08:54:06 2012-01-10 09:49:30
        22 2012-01-10 09:49:30 2012-01-10 09:49:32
        22 2012-01-10 09:49:30 2012-01-10 09:49:32
        23 2012-01-10 09:49:32 2012-01-10 09:49:47
        23 2012-01-10 09:49:32 2012-01-10 09:49:47
        24 2012-01-10 09:49:47 2012-01-10 09:52:43
        25 2012-01-10 09:52:43 2012-01-10 09:59:20
       
看一下现在的备库(beijing)的状态，SWITCHOVER_STATUS是NOT ALLOWED，因为主库正常运转中。
SQL> select db_unique_name,switchover_status,database_role from v$database;

DB_UNIQUE_NAME       SWITCHOVER_STATUS                        DATABASE_ROLE
-------------------- ---------------------------------------- --------------------------------
beijing              NOT ALLOWED                              PHYSICAL STANDBY

四、数据验证

1.在主库scott.dept表中插入些数据。
SQL> select * from scott.dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
SQL> insert into scott.dept values (55,'wallimn','beijing');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.     

2.备库查询一下
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from scott.dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        55 wallimn                      beijing 
       
3.备库恢复REDO应用
SQL> alter database recover managed standby database disconnect from session;

Database altered.

-END-