1、在主机上创建新数据库实例mt01,密码mt01mt01
2、修改.bash_profile文件中的SID为正确上面新建实例的名称
修改 /etc/hosts 文件中的地址与机器名对应关系
3、修改主机为归档模式
--获取实例名
SQL> select instance_name from v$instance;
--设置归档文件默认路径
SQL> alter system set log_archive_dest_1='location=/oracle/arch' scope=spfile;
--设置归档文件名格式
SQL> alter system set log_archive_format='MT01_%t_%s_%r.arc' scope=spfile;
--重启数据库使其处于归档模式
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
--强制数据库归档当前日志
SQL> alter system archive log current;
--查看归档目录是否成功生成归档文件
SQL> !ls -l /oracle/arch
-rw-r----- 1 oracle oinstall 858112 Oct 9 16:15 db10g_1_22_814630003.arc
4、设置主机数据库参数
SQL> alter system set standby_archive_dest='/oracle/arch' scope=spfile;
SQL> alter system set standby_file_management=auto scope=spfile;
SQL> alter system set log_archive_dest_2='service=standby_db' scope=spfile;
SQL> alter system set fal_server='primary_db' scope=spfile;
SQL> alter system set fal_client='standby_db' scope=spfile;
5、配置主机数据库 $ORACLE_HOME/network/admin/tnsnames.ora 文件为以下内容
primary_db =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mt01)
)
)
standby_db =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mt01)
)
)
6、主机RMAN生成数据库文件和归档文件的备份(RMAN备份文件不要和日志文件在同一个目录)
$ rman target /
RMAN>run {
allocate channel c1 type disk;
allocate channel c2 type disk;
crosscheck backup;
crosscheck archivelog all;
delete noprompt backup;
delete noprompt archivelog all;
backup filesperset 2 database format '/oracle/arch/full_%d_%T_%s_%p';
sql 'alter system archive log current';
backup archivelog all format '/oracle/arch/arc_%d_%T_%s_%p' delete input;
}
7、在主机端产生standby controlfile备份
SQL> alter database create standby controlfile as '/oracle/arch/ctl_std';
8、在主机端生成standby pfile文件(pfile就是 $ORACLE_HOME/dbs/ 下的 initmt01.ora 文件)
SQL> create pfile='/oracle/arch/pfile' from spfile;
SQL> create spfile from pfile='/oracle/arch/pfile';
9、把主机端密码文件也备份到 /oracle/arch/ 目录下
$ cp $ORACLE_HOME/dbs/orapwmt01 /oracle/arch/
10、拷贝主机文件到standby主机
$scp /oracle/arch/* 192.168.56.104:/oracle/arch
11、配置standby主机数据库 $ORACLE_HOME/network/admin/tnsnames.ora 文件为以下内容
primary_db =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mt01)
)
)
standby_db =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mt01)
)
)
12、覆盖standby主机pfile文件 initmt01.ora,修改参数文件
$ cp /oracle/arch/pfile /oracle/app/oracle/product/10.2/db_1/dbs/initmt01.ora
$ vi /oracle/app/oracle/product/10.2/db_1/dbs/initmt01.ora
12.1、创建参数文件中的对应路径下的adump bdump cdump udump目录
12.2、修改参数 log_archive_dest_2 的值为空
SQL> alter system set log_archive_dest_2='' ;
13、覆盖standby主机密码文件
$ cp /oracle/arch/orapwmt01 /oracle/app/oracle/product/10.2/db_1/dbs/orapwmt01
14、恢复standby主机控制文件(重启数据库到nomount状态)
SQL> shutdown immediate
SQL> startup nomount
$ rman target /
RMAN> restore controlfile from '/oracle/arch/ctl_std';
15、恢复standby主机数据库和归档(在mount状态下)
RMAN> alter database mount;
RMAN> run {allocate channel c1 type disk; allocate channel c2 type disk; restore database;}
RMAN> run {allocate channel c1 type disk; allocate channel c2 type disk; recover database;}
16、standby数据库的启动过程
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
17、主库备库切换
17.1、检查主库是否支持switchover操作
SQL> select switchover_status from v$database;
如果switchover_status 为TO STANDBY 则执行
SQL> alter database commit to switchover to physical standby;
若为SESSIONS ACTIVE表示当前会话处于ACTIVE,则执行
SQL> alter database commit to switchover to physical standby with session shutdown;
修改参数 log_archive_dest_2 的值为空
SQL> alter system set log_archive_dest_2='' scope=both;
17.2、重启主库到备库状态
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
查看数据库是否切换成功
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- ---------- ---------------- ------------------------------
MT01 READ WRITE PHYSICAL STANDBY mt01
17.3、备库检查状态,并转换为主库
SQL> select switchover_status from v$database;
如果 switchover_status 为 TO PRIMARY 则执行
SQL> alter database commit to switchover to primary;
若为SESSIONS ACTIVE表示当前会话处于ACTIVE,则执行
SQL> alter database commit to switchover to primary with session shutdown;
修改参数 log_archive_dest_2 的值为 service=standby_db
SQL> alter system set log_archive_dest_2='service=standby_db';
启动数据库到open状态
SQL> alter database open;
查看数据库是否切换成功
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- ---------- ---------------- ------------------------------
MT01 READ WRITE PRIMARY mt01