1、基本配置
主机名:db10g(aix7.1)、p550b(aix7.1)
IP地址:db10g:192.168.29.46 SID:orcl
p550b:192.168.29.159 SID:orcl
2、主库启用FORCE LOGGING
SQL>ALTER DATABASE FORCE LOGGING;
SQL>select FORCE_LOGGING from v$database;
启用归档模式
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
3、关闭数据库,生成数据库备份文件
$tar -cvf oradata.tar oradata
4、启动数据库,生成备份控制文件
SQL> alter database create standby controlfile as '/oradata/stdcotrl.ctl';
5、从主库创建pfile文件
SQL> create pfile from spfile;
在/oracle/db10g/dbs/目录下生成pfile文件:initorcl.ora
6、登陆备库节点,ftp获得数据库文件、备用控制文件及参数文件
oradata.tar、stdcotrl.ctl、initorcl.ora、密码文件orapworcl
解压数据文件
$ tar -xvf oradata.tar
7、主库及备库都添加standby redo log
SQL>alter database add standby logfile group 4 size 50m;
SQL>alter database add standby logfile group 5 size 50m;
SQL>alter database add standby logfile group 6 size 50m;
SQL>alter database add standby logfile group 7 size 50m;
查看日志情况
sql>select * from v$logfile;
8、备库上创建必要的目录
$ mkdir -p /oracle/admin/orcl/adump
$ ls
$ mkdir bdump cdump udump pfile
9、主库pfile文件initorcl.ora增加以下内容
--------------------------------------------------------------
*.db_unique_name='primary'
*.service_names='primary'
*.log_archive_config='dg_config=(primary,standby)'
*.log_archive_dest_1='location=/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'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.fal_server='standby'
*.fal_client='primary'
*.DB_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl'
*.LOG_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl'
*.standby_file_management=auto
-------------------------------------------------------------------
10、主库listener.ora修改
-----------------------------------------------------------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/db10g)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /oracle/db10g)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.29.46)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
--------------------------------------------------------------------------
增加的内容为:
--------------------------------------------------------------------------
++++++ (SID_DESC =
++++++ (GLOBAL_DBNAME = orcl)
++++++ (ORACLE_HOME = /oracle/db10g)
++++++ (SID_NAME = orcl)
++++++ )
--------------------------------------------------------------------------
5、主库修改tnsnames.ora
--------------------------------------------------------------------------
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.29.46)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.29.46)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.29.159)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
--------------------------------------------------------------------------
11、备库修改pfile文件,增加以下内容
--------------------------------------------------------------------------
*.control_files='/oradata/orcl/stdcotrl.ctl'
*.db_unique_name='standby'
*.service_names='standby'
*.log_archive_config='dg_config=(primary,standby)'
*.log_archive_dest_1='location=/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'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.fal_server='primary'
*.fal_client='standby'
*.DB_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl'
*.LOG_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl'
*.standby_file_management=auto
--------------------------------------------------------------------------
12、备库listener.ora文件
------------------------------------------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/db)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /oracle/db)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.29.159)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
-----------------------------------------------------------------
13、备库tnsnames.ora文件
-----------------------------------------------------------------
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.29.159)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.29.46)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.29.159)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
-----------------------------------------------------------------
14、启动备用数据库
SQL> startup nomount pfile='/oracle/db10g/dbs/initorcl.ora';
SQL> alter database mount standby database;
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
SQL> alter database recover managed standby database disconnect from session;
15、进行数据修改测试
测试主库产生的归档日志是否能正常传送到归档日志
在主机上
SQL>archive log list;
SQL>alter system archive log current;
SQL>archive log list;
主库进行日志切换 :
SQL>Alter system switch logfile;
然后分别查看主库和备库的 /archive 目录下是否产生了同样的归档日志文件。
select max(sequence#) from v$archived_log;
select max(sequence#) from v$log_history;
select group#,sequence#,archived,status from v$log;
select name,sequence#,applied from v$archived_log;
select sequence#,applied from v$archived_log;
若不同步,可以在备库 做 如下操作:
SQL>alter database recover managed standby database cancel;
SQL>alter database recover managed standby database disconnect from session;
16、在主库上建表测试
SQL> create table system.t1 as select * from dba_users;
insert into system.t1 select * from dba_users connect by rownum <10000;
SQL> create table system.t2 as select * from dba_users connect by rownum <100000;
create table system.t3 as select * from dba_users connect by rownum <130000;
create table system.t4 as select * from dba_users connect by rownum <140000;
create table system.t5 as select * from dba_users connect by rownum <150000;
SQL> select count(*) from system.t1;
SQL> alter system switch logfile;
计算时间:SQL> select 100000/35 from dual;
在备库上以read only打开数据库,执行查询
SQL> select username from system.t1;
select username from system.t1
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> alter database recover managed standby database cancel;
SQL> ALTER DATABASE OPEN READ ONLY;
SQL> select count(*) from system.t1;
COUNT(*)
----------
100020
备库回到日志传送模式
SQL> alter database recover managed standby database disconnect from session;
SQL> select status from v$instance;
STATUS
------------
MOUNTED
17、日志传送状态监控
1)主库 察看当前日志状况
SQL> select sequence#,status from v$log;
2)备库 察看 RFS(Remote File Service) 接收日志情况和 MRP应用日志情况
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK# FROM V$MANAGED_STANDBY;
3)察看备库是否和主库同步
SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;
4)察看备库已经归档的 redo
SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG;
5)察看备库已经应用的 redo
SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;
18、主库正常切换
1)在主库端检验数据库可切换状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
SWITCHOVER_STATUS:TO STANDBY 表示可以正常切换 .
如果 SWITCHOVER_STATUS 的值为 SESSIONS ACTIVE, 表示当前有会话处于 ACTIVE状态
2)开始主库正常切换
如果 SWITCHOVER_STATUS 的值为 TO STANDBY 则 :
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
如果 SWITCHOVER_STATUS 的值为 SESSIONS ACTIVE 则 :
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
成功运行这个命令后,主库被修改为备库
3)重启先前的主库
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
4)在备库验证可切换状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
1 row selected
5)将目标备库转换为主库
如果 SWITCHOVER_STATUS 的值为 TO STANDBY 则 :
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
如果 SWITCHOVER_STATUS 的值为 SESSIONS ACTIVE 则 :
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
成功运行这个命令后,备库被修改为主库
6)重启目标备库
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
7)将先前主库启动日志传送进程
SQL> alter database recover managed standby database disconnect;
8)总结 : 这样主库的一次正常切换完成 . 切换后的状态 , 原先的主库变为备库 , 原先的备库变为主库 .
添加新评论0 条评论