在同一台机器上搭建物理备用数据库的步骤linux环境 oracle
主库orcl
备库stby
检查侦听是否启动
配置主备数据库的初始化参数文件
sqlplus "/as sysdba"
create pfile=/home/oracle/initprimora from spfile;
cp /home/oracle/initprimora /home/oracle/initstbyora
vi /home/oracle/initprimora
orcl__db_cache_size=
orcl__java_pool_size=
orcl__large_pool_size=
orcl__oracle_base=/oracle#ORACLE_BASE set from environment
orcl__pga_aggregate_target=
orcl__sga_target=
orcl__shared_io_pool_size=
orcl__shared_pool_size=
orcl__streams_pool_size=
*audit_file_dest=/oracle/admin/orcl/adump
*audit_trail=db
*compatible=
*control_files=/oradata/orcl/controlctl/oradata/flash_recovery_area/orcl/controlctl
*db_block_size=
*db_domain=
*db_name=orcl
*db_recovery_file_dest=/oradata/flash_recovery_area
*db_recovery_file_dest_size=
*diagnostic_dest=/oracle
*dispatchers=(PROTOCOL=TCP) (SERVICE=orclXDB)
*memory_target=
*open_cursors=
*processes=
*remote_login_passwordfile=EXCLUSIVE
*undo_tablespace=UNDOTBS
*fal_client=prim
*fal_server=stby
*standby_file_management=auto
*log_archive_dest_=location=/oradata/arch/orcl valid_for=(all_logfilesall_roles) db_unique_name=prim
*log_archive_dest_=service=stby valid_for=(online_logfilesprimary_role) db_unique_name=stby
*DB_UNIQUE_NAME=prim
*log_archive_config=dg_config=(primstby)
编辑备库的参数文件
vi /home/oracle/initstbyora
stby__db_cache_size=
stby__java_pool_size=
stby__large_pool_size=
stby__oracle_base=/oracle#ORACLE_BASE set from environment
stby__pga_aggregate_target=
stby__sga_target=
stby__shared_io_pool_size=
stby__shared_pool_size=
stby__streams_pool_size=
*audit_file_dest=/oracle/admin/stby/adump
*audit_trail=db
*compatible=
*control_files=/oradata/stby/controlctl/oradata/flash_recovery_area/stby/controlctl
*db_block_size=
*db_domain=
*db_name=orcl #< 在同一台机器上搭建dg 要与主库的一样 否则ora
*db_recovery_file_dest=/oradata/flash_recovery_area
*db_recovery_file_dest_size=
*diagnostic_dest=/oracle
*dispatchers=(PROTOCOL=TCP) (SERVICE=stbyXDB)
*memory_target=
*open_cursors=
*processes=
*remote_login_passwordfile=EXCLUSIVE
*undo_tablespace=UNDOTBS
*DB_FILE_NAME_CONVERT=/oradata/orcl/oradata/stby
*LOG_FILE_NAME_CONVERT=/oradata/orcl/oradata/stby
*fal_client=stby
*fal_server=prim
*standby_file_management=auto
*log_archive_dest_=location=/oradata/arch/stby valid_for=(all_logfilesall_roles) db_unique_name=stby
*log_archive_dest_=service=prim valid_for=(online_logfilesprimary_role) db_unique_name=prim
*DB_UNIQUE_NAME=stby
*log_archive_config=dg_config=(primstby)
备份主库
rman target /
backup database format /u/oradata/dbfull%U;
创建备库控制文件
export ORACLE_SID=orcl
sqlplus "/as sysdba"
alter database create standby controlfile as /oradata/stby/stbycontrolctl;
cp /oradata/stby/stbycontrolctl /oradata/stby/controlctl
cp /oradata/stby/stbycontrolctl /oradata/flash_recovery_area/stby/controlctl
处理备库
export ORACLE_SID=stby
orapwd file=/oracle/product//db_/dbs/orapwstby password=oracle entries= ignorecase=y #一定要加ignorecase=y 要不然归档传不到备用库上
sqlplus "/as sysdba"
startup nomount
alter database mount;
rman target /
restore database;
重启主库
export ORACLE_SID=orcl
sqlplus "/as sysdba"
shutdown immediate
startup pfile=/home/oracle/initprimora
配置tnsnamesora(因为在同一台机器上所以就改这一个文件)
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = ))
)
(CONNECT_DATA =
(SID = orcl)
(SERVER = DEDICATED)
)
)
stby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = ))
)
(CONNECT_DATA =
(SID = stby)
(SERVER = DEDICATED)
)
)
将备库置于接收归档日志状态
export ORACLE_SID=stby
sqlplus "/as sysdba"
alter database recover managed standby database disconnect from session;
过一会儿检查是否收到日志
export ORACLE_SID=orcl
sqlplus "/as sysdba"
select max(sequence#) from v$archived_log; 查看归档日志序列号
alter system switch logfile;
alter system switch logfile;
export ORACLE_SID=stby
sqlplus "/as sysdba"
select sequence#applied from v$archived_log order by ; 查看归档日志序列号
主备库角色切换
角色切换
步骤验证主库能否进行角色切换TO STANDBY表示可以进行
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
TO STANDBY
步骤在主库上执行角色切换到从库角色
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
步骤关闭并重新启动之前的主库实例
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
步骤在备库的V$DATABASE视图中查看备库的切换状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
TO_PRIMARY
步骤切换备库到主库角色
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
步骤完成备库到主库的切换
如果备库没有以只读模式打开直接执行以下语句打开到新的主库
SQL> ALTER DATABASE OPEN;
如果备库以只读模式打开先关闭数据然后再重新启动
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
步骤如果有必要重新启动一下新的备库上的重做日志应用服务
SQL> alter database recover managed standby database disconnect from session;
(注可以通过select message from v$dataguard_status;查看当前备库应用重做日志的状态)
步骤开始发送重做数据到备库上
Issue the following statement on the new primary database:
SQL> ALTER SYSTEM SWITCH LOGFILE;
备注
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
如果有缺失的归档日志文件手工考背后在备库上
ALTER DATABASE REGISTER PHYSICAL LOGFILE filespec;
FORCE 关键词终止目标物理备数据库上活动的RFS 进程使得故障转移能不用等待网络连接超时而立即进行
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;