主库搭建
1、主库启动 force logging
检查主机是否启动 force logging
select force_logging from v$database;
启动 force logging
alter database force logging;
2、启用归档模式
查看归档模式
archive log list
这里显示为未归档模式
关闭数据库,启动到mount,在mount下启动到归档模式
shutdown immediate
startup mount
alter database archivelog;
切换数据库至open状态,并查看归档模式
alter database open;
archive log list
3、在主库添加standby redo logfile
查看主库的 redo 相关信息
需要添加 4(3+1) 个standby logfile
alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo04.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo05.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo06.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo07.log' size 200M;
4、配置监听
配置 listener.ora 文件
vim listener.ora
– 加入下面的内容,注意ORACLE_HOME的配置
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = standby)
(ORACLE_HOME = /u01/app/oracle/12.2.0.1/db_1)
(GLOBAL_DBNAME=standby)
)
)
配置 tnsnames.ora
vim tnsnames.ora
– 加入如下内容,注意 HOST 参数
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = shx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
5、在主库创建 pfile 文件并修改 pfile 内容
将数据库关闭
shutdown immediate
创建 pfile 文件
create pfile from spfile
修改 pfile 内容,在 pfile 添加如下内容(已经存在的参数无需添加):
db_name='orcl'
db_unique_name='orcl'
log_archive_config='dg_config=(orcl,standby)'
log_archive_dest_1='location=/u01/archivelog valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
log_archive_dest_2='service=standby valid_for=(online_logfiles,primary_role) db_unique_name=standby'
log_archive_dest_3='location=/u01/archivelog1 valid_for=(standby_logfiles,standby_role) db_unique_name=orcl'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_dest_state_3=enable
standby_file_management='auto'
fal_server='standby'
fal_client='orcl'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/orcl/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/orcl/'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
创建归档目录
mkdir -p /u01/archivelog
mkdir -p /u01/archivelog1
用新参数重启数据库
create spfile from pfile;
startup
备库搭建
1、创建备库的实例
配置备库的参数文件
[oracle@shx dbs]$ cp initorcl.ora initstandby.ora
vim initstandby.ora
– 将文件中所有的 orcl 字符串替换为 standby
修改如下内容,再重新生成 spfile
db_name='orcl'
db_unique_name='standby'
log_archive_config='dg_config=(orcl,standby)'
log_archive_dest_1='location=/u01/archivelog2 valid_for=(standby_logfiles,standby_role) db_unique_name=standby'
log_archive_dest_2='service=orcl valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
log_archive_dest_3='location=/u01/archivelog3 valid_for=(online_logfiles,primary_role) db_unique_name=standby'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_dest_state_3=enable
fal_server='orcl'
fal_client='standby'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/standby/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/standby/'
创建文件目录
mkdir -p /u01/archivelog2
mkdir -p /u01/archivelog3
mkdir -p /u01/app/oracle/admin/standby/adump
设置 ORACLE_SID
export ORACLE_SID=standby
利用 pfile 生成 spfile
create spfile from pfile;
2、将备库启动到 nomount
startup nomount
3、为备库生成口令文件
[oracle@shx ~]$ cd $ORACLE_HOME/dbs
[oracle@shx dbs]$ cp orapworcl orapwstandby
4、使用 Active duplicate 复制备库
rman target sys/a@orcl auxiliary sys/a$standby
connected to target database: ORCL (DBID=1545127484)
connected to auxiliary database: ORCL (not mounted)
备份数据库
RMAN>duplicate target database for standby from active database dorecover;
如果报如下错误
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/03/2019 12:13:19
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name /u01/archivelog/1_5_1017346750.dbf conflicts with a file used by the target database
那么执行
RMAN>duplicate target database for standby nofilenamecheck from active database dorecover;
5、打开备库并启动 apply
打开备库
alter database open;
启动 real-time apply
recover managed standby database using current logfile disconnect from session;
12c 版本可以使用下面的写法
recover managed standby database disconnect;
测验
为了方便起见,为主库设置别名 orcl>
set sqlprompt orcl>
为备库设置别名 standby>
set sqlprompt standby>
在主库添加示例用户 scott
@?/rdbms/admin/utlsampl.sql
那么备库上也能够登录 scott
也可以在主库建表删表,增删数据,那么备库相应的也会发生数据改变。