主库不需要停机
环境的ip
信息
[root@racbj01 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
###public
10.107.174.164 racbj01
10.107.174.165 racbj02
###vip
10.107.174.167 racbj01vip
10.107.174.168 racbj02vip
###private
10.107.176.164 racbj01priv
10.107.176.165 racbj02priv
###scanip
10.107.174.166 racbjscan
备库安装好Oracle
软件
[root@173adg dbs]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.107.174.173 173adg
10.107.174.166 racbjscan
RAC
两个节点写好Oracle
用户下的tnsnames.ora
注意备库hosts
文件写自己的ip
和rac
的scanip
的解析,tns
文件也可以加一个rac
其中一个节点的IP
,然后rman
连接的时候使用其中的一个节点
pritns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racbjscan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
stdtns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.107.174.173)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdg) ##unique_name
)
)
准备备库监听文件
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=10.107.174.173)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=racdg)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=racdb))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM=extproc)))
lsnrctl start
备库tns
文件
pritns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racbjscan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
stdtns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.107.174.173)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdg)
)
)
把主库的密码文件传到备库而且要改名字,rac-dg
这个环境要注意密码文件的问题,三个节点的密码文件都要一致,rac1
也要传给rac2
改名叫orapwracdb2
,要不然rac2
的alert就会报ORA-16191
[oracle@racbj01 dbs]$ orapwd file=orapwracdb1 password=oracle force=y
[oracle@racbj01 dbs]$ scp orapwracdb1 10.107.174.173:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwracdb
[oracle@racbj01 dbs]$ scp orapwracdb1 racbj02:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwracdb2
主备都执行(测试):
sqlplus sys/oracle@pritns as sysdba
sqlplus sys/oracle@stdtns as sysdba
配置Primary
的参数
alter database force logging;
--添加standby logfile方便自动传到standby,一般是比redo多一组
alter database standby logfile
group 4 ('/u01/app/oracle/oradata/orcl/styredo04.log') size 100m,
group 5 ('/u01/app/oracle/oradata/orcl/styredo05.log') size 100m,
group 6 ('/u01/app/oracle/oradata/orcl/styredo06.log') size 100m,
group 7 ('/u01/app/oracle/oradata/orcl/styredo07.log') size 100m;
alter system set log_archive_config='DG_CONFIG=(racdb,racdg)' scope=both sid='*'; ##这里DG_CONFIG填的都是数据库的db_unique_name
alter system set log_archive_dest_2='SERVICE=stdtns LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdg' scope=both sid='*';
alter system set log_archive_dest_state_2=enable scope=both sid='*';
alter system set log_archive_max_processes=8 scope=both sid='*';
##这个和log_file_name_convert是作为备库时的是参数,重启生效,这两参数改了之后有可能到导致rac中一个节点shutdown,startup报错
ORA-01105: mount is incompatible with mounts by other instances
ORA-01677: standby file name convert parameters differ from other instance
解决办法参考:https://blog.csdn.net/aaron8219/article/details/10027293
alter system set db_file_name_convert='/u01/app/oracle/oradata','+DATA/racdb/datafile','/u01/app/oracle/tempfile','+DATA/racdb/tempfile' scope=spfile sid='*';
alter system set log_file_name_convert='/u01/app/oracle/oradata','+DATA/racdb/onlinelog','/u01/app/oracle/redo','+ARC/racdb/onlinelog' scope=spfile sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set fal_server='stdtns' scope=both sid='*';
alter system set fal_client='pritns' scope=both sid='*';
--检查是否生效
set linesize 500 pages 0
col value for a90
col name for a50
select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2','remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert', 'log_file_name_convert', 'standby_file_management');
主库创建pfile
,备库创建pfile
中涉及到的目录比如audit_dump
create pfile from spfile
备库
mkdir -p /u01/app/oracle/admin/racdb/adump /u01/app/oracle/oradata /u01/app/oracle/redo /u01/app/oracle/archivelog /u01/app/oracle/tempfile
Standby
准备参数文件
db_name='racdb'
db_unique_name='racdg'
--要使用pfile启动,使用spfile启动会报错
RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause
startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initracdb.ora';
rman target sys/oracle@pritns auxiliary sys/oracle@stdtns
run{
allocate channel ch00 type disk;
allocate channel ch01 type disk;
allocate channel ch02 type disk;
allocate channel ch03 type disk;
duplicate target database
for standby nofilenamecheck
from active database
spfile
SET CLUSTER_DATABASE='FALSE' ##因为主库是RAC,备库是单实例,在读主库的控制文件的也想要复制到RAC结构的备库,所以这里指明备库不是RAC
set 'db_unique_name' = 'racdg'
set log_file_name_convert ='+DATA/racdb/onlinelog','/u01/app/oracle/oradata','+ARC/racdb/onlinelog','/u01/app/oracle/redo'##加上另一个路径(有几个不同的路径就写几个映射)
set db_file_name_convert = '+DATA/racdb/datafile','/u01/app/oracle/oradata','+DATA/racdb/tempfile','/u01/app/oracle/tempfile' ##加上临时文件路径(有几个不同的路径就写几个映射)
set control_files='/u01/app/oracle/oradata/control01.ctl'
set log_archive_dest_1 ='location=/u01/app/oracle/archivelog'
set log_archive_dest_2='service=pritns LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=racdb' ##service写tns中的名字
set fal_client='stdtns'##tns中的名字
set fal_server='pritns'##tns中的名字
;
release channel ch00;
release channel ch01;
release channel ch02;
release channel ch03;
}
Standby
进入恢复状态(非实时)
alter database recover managed standby database disconnect from session;
确认standby logfile
状态
select GROUP#,THREAD#,SEQUENCE#,USED,ARCHIVED,STATUS from v$standby_log;
GROUP# THREAD# SEQUENCE# USED ARCHIVED STATUS
---------- ---------- ---------- ---------- --------- ------------------------------
11 1 0 0 YES UNASSIGNED
12 1 0 0 YES UNASSIGNED
13 1 0 0 YES UNASSIGNED
14 2 18521 217088 YES ACTIVE
15 2 0 0 NO UNASSIGNED
16 2 0 0 YES UNASSIGNED
进入只读状态
alter database recover managed standby database cancel;
alter database open;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
alter database recover managed standby database disconnect from session;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
select process,client_process,thread#,sequence#,status from v$managed_standby;
PROCESS CLIENT_PROCESS THREAD# SEQUENCE# STATUS
--------------------------- ------------------------ ---------- ---------- ------------------------------------
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 2 18519 CLOSING
ARCH ARCH 1 49629 CLOSING
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
RFS UNKNOWN 0 0 IDLE
RFS ARCH 0 0 IDLE
RFS UNKNOWN 0 0 IDLE
RFS LGWR 2 18521 IDLE
RFS UNKNOWN 0 0 IDLE
RFS UNKNOWN 0 0 IDLE
RFS LGWR 1 49630 IDLE
RFS UNKNOWN 0 0 IDLE
MRP0 N/A 2 18521 APPLYING_LOG
17 rows selected.
备库
show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_create_file_dest string +DATA
设置为空,要不然主库添加数据文件备库会报错,db_create_file_dest
和db_file_name_convert
有冲突
alter system set db_create_file_dest = '' scope=both;
System altered.
参考https://blog.csdn.net/cuji4856/article/details/100157657
遇到的坑:ORA-19595
:这个问题有可能就是因为spfile
是在ASM
上的原因
解决办法create pfile from spfil
e然后create spfile=... from pfile=...
还要记得把备库参数文件中所有带有ASM的都替换掉