oracle 11g r2 rac到单实例的dg
发布时间：2020-12-12 13:19:45 所属栏目：百科 来源：网络整理
导读：oracle 11g r2 rac到单实例的dg 1 主备环境说明 rac环境--primary CentOS release 6.5 (Final) hostname rac1 rac2 ip 10.15.7.11 10.15.7.12 Instance_name bol1 bol2 DB_NAME ? ? bol db_unique_name bol Data、Control File、Redo File ASM SQL*Plus: Rel
|
oracle 11g r2 rac到单实例的dg 1 主备环境说明rac环境--primary 单实例环境--standby SQL*Plus: Release 11.2.0.4.0 Production rac ip详细 [[email?protected] ~]# vim /etc/hosts 文件系统规划 2 主库操作，设置为归档和force?logging--primary SQL> conn /as sysdba Connected. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination +FRA/bol/arch Oldest online log sequence 142 Next log sequence to archive 144 Current log sequence 144 ===select *from v$archived_log order by 1; SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string bol SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string bol SQL> alter database force logging; Database altered. SQL> select force_logging from v$database; FOR --- YES ===修改主库为归档模式 SQL> archive log list SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> archive log list; SQL> select name,open_mode,log_mode,force_logging from gv$database; NAME OPEN_MODE LOG_MODE FOR --------- -------------------- ------------ --- BOL READ WRITE ARCHIVELOG YES SQL> show parameter db_recover NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 5727M ===2 个节点的归档都指向了+FRA 这个磁盘组。 也可以指向其他的磁盘组或者本地的位置，如： 3 主备库添加 standby Redo log 文件?RAC 每个 Redo Thread 都需要创建对应的 Standby Redo Log。 创建原则和单实例一样，包括日志 文件大小相等，日志组数量要多 1 组。 SQL> set lines 120 SQL> col member for a50 SQL> select a.thread#,a.group#,a.bytes/1024/1024,b.member from v$log a,v$logfile b where a.group#=b.group#; THREAD# GROUP# A.BYTES/1024/1024 MEMBER ---------- ---------- ----------------- -------------------------------------------------- 1 1 50 +DATA/bol/onlinelog/group_1.257.990861405 1 1 50 +FRA/bol/onlinelog/group_1.257.990861405 1 2 50 +DATA/bol/onlinelog/group_2.258.990861405 1 2 50 +FRA/bol/onlinelog/group_2.258.990861405 2 3 50 +DATA/bol/onlinelog/group_3.265.990862587 2 3 50 +FRA/bol/onlinelog/group_3.259.990862587 2 4 50 +DATA/bol/onlinelog/group_4.266.990862587 2 4 50 +FRA/bol/onlinelog/group_4.260.990862587 2 4 50 +FRA/bol/onlinelog/group_4.261.991639393 1 5 50 +DATA/bol/onlinelog/group_5.271.991639531 1 5 50 +FRA/bol/onlinelog/group_5.262.991639531 SQL> select thread#,group#,members,bytes/1024/1024 from v$log; THREAD# GROUP# MEMBERS BYTES/1024/1024 ---------- ---------- ---------- --------------- 1 1 2 50 1 2 2 50 2 3 2 50 2 4 3 50 1 5 2 50 --主库添加 standby redo log： alter database add standby logfile thread 1 group 11 (‘+FRA‘) size 50m; alter database add standby logfile thread 1 group 12 (‘+FRA‘) size 50m; alter database add standby logfile thread 1 group 13 (‘+FRA‘) size 50m; alter database add standby logfile thread 1 group 14 (‘+FRA‘) size 50m; alter database add standby logfile thread 1 group 15 (‘+FRA‘) size 50m; alter database add standby logfile thread 1 group 16 (‘+FRA‘) size 50m; alter database add standby logfile thread 1 group 17 (‘+FRA‘) size 50m; alter database add standby logfile thread 2 group 18 (‘+FRA‘) size 50m; alter database add standby logfile thread 2 group 19 (‘+FRA‘) size 50m; alter database add standby logfile thread 2 group 20 (‘+FRA‘) size 50m; alter database add standby logfile thread 2 group 21 (‘+FRA‘) size 50m; alter database add standby logfile thread 2 group 22 (‘+FRA‘) size 50m; alter database add standby logfile thread 2 group 23 (‘+FRA‘) size 50m; SQL> select group#,type,member from v$logfile order by 1,2; GROUP# TYPE MEMBER ---------- ------- -------------------------------------------------- 1 ONLINE +DATA/bol/onlinelog/group_1.257.990861405 1 ONLINE +FRA/bol/onlinelog/group_1.257.990861405 2 ONLINE +DATA/bol/onlinelog/group_2.258.990861405 2 ONLINE +FRA/bol/onlinelog/group_2.258.990861405 3 ONLINE +DATA/bol/onlinelog/group_3.265.990862587 3 ONLINE +FRA/bol/onlinelog/group_3.259.990862587 4 ONLINE +DATA/bol/onlinelog/group_4.266.990862587 4 ONLINE +FRA/bol/onlinelog/group_4.260.990862587 4 ONLINE +FRA/bol/onlinelog/group_4.261.991639393 5 ONLINE +FRA/bol/onlinelog/group_5.262.991639531 5 ONLINE +DATA/bol/onlinelog/group_5.271.991639531 GROUP# TYPE MEMBER ---------- ------- -------------------------------------------------- 11 STANDBY +FRA/bol/onlinelog/group_11.323.1000118283 12 STANDBY +FRA/bol/onlinelog/group_12.322.1000118289 13 STANDBY +FRA/bol/onlinelog/group_13.321.1000118293 14 STANDBY +FRA/bol/onlinelog/group_14.320.1000118303 15 STANDBY +FRA/bol/onlinelog/group_15.319.1000118303 16 STANDBY +FRA/bol/onlinelog/group_16.318.1000118303 17 STANDBY +FRA/bol/onlinelog/group_17.313.1000118387 18 STANDBY +FRA/bol/onlinelog/group_18.312.1000118397 19 STANDBY +FRA/bol/onlinelog/group_19.317.1000118315 20 STANDBY +FRA/bol/onlinelog/group_20.316.1000118315 21 STANDBY +FRA/bol/onlinelog/group_21.315.1000118315 GROUP# TYPE MEMBER ---------- ------- -------------------------------------------------- 22 STANDBY +FRA/bol/onlinelog/group_22.314.1000118317 23 STANDBY +FRA/bol/onlinelog/group_23.311.1000118405 24 rows selected. 4?配置主备库的监听用 net manager 工具，在备库创建一个监听。 也可以手动的修改 listener.ora 文件 --对于 RAC 环境: [[email?protected] ~]$ cd /u01/app/11.2.0/grid/network/admin/ [grid@rac1 admin]$ cat listener.ora LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent [[email?protected] admin]$ vim listener.ora [grid@rac1 admin]$ cat listener.ora LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = bol) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = bol1) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER)) ) ADR_BASE_LISTENER = /u01/app/grid ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent LISTENER_SCAN1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1)) ) ADR_BASE_LISTENER_SCAN1 = /u01/app/grid 节点 2，对应修改即可 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = bol) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = bol2) ) ) --这里写的 Oracle 用户的 ORACLE_HOME [[email?protected] oracle]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/ [oracle@dg1 admin]$ vim listener.ora [oracle@dg1 admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = orcl) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle 配置主备库的 Net Server： tnsnames.ora [[email?protected] admin]$ vim tnsnames.ora [oracle@rac1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. BOL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = scanip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bol) ) ) bol1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.15.7.13)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bol1) ) ) bol2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.15.7.14)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bol2) ) ) orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.15.7.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) [grid@rac1 admin]$ lsnrctl reload [oracle@dg1 admin]$ echo $ORACLE_HOME [oracle@dg1 admin]$ source /home/oracle/.bash_profile [oracle@dg1 admin]$ echo $ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1 [oracle@dg1 admin]$ lsnrctl start --备库 /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora [root@dg1 ~]# cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora bol= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.15.7.11)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.15.7.12)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =bol) ) ) orcl= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.15.7.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =orcl) ) ) --主库ping备库 主库ping备库成功 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 13-FEB-2019 15:56:29 Copyright (c) 1997,2013,Oracle. All rights reserved. Used parameter files:
--备库ping主库 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 13-FEB-2019 11:42:10 Copyright (c) 1997,Oracle. All rights reserved. Used parameter files:
? 5 备库创建相应的目录? [[email?protected] admin]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcl mkdir -p /u01/app/oracle/oradata/orcl/data mkdir -p /u01/app/oracle/oradata/orcl/tempfile [oracle@dg1 admin]$ mkdir -p /u01/app/oracle/admin/orcl/adump [oracle@dg1 admin]$ mkdir -p /u01/app/oracle/admin/orcl/data [oracle@dg1 admin]$ mkdir -p /u01/app/oracle/admin/orcl/redo [oracle@dg1 admin]$ mkdir -p /u01/app/oracle/admin/orcl/tempfile [oracle@dg1 oracle]$ mkdir -p /u01/app/oracle/archive 6?修改RAC参数，并生成pfile与密码文件一起传输到备库复制rac1的密码文件传到备库 db_file_name_convert 和 log_file_name_convert 仅当数据库被 standby 时才会生效，这里配置，是 为切换做准备。 log_file_name_convert 指的是 online redo log。 --alter system set db_unique_name=‘bol‘ scope=spfile sid=‘*‘; alter system set log_archive_config=‘dg_config=(bol,orcl)‘ scope=spfile sid=‘*‘; alter system set log_archive_dest_1=‘location=+FRA valid_for=(all_logfiles,all_roles) db_unique_name=bol‘ scope=spfile sid=‘*‘; alter system set log_archive_dest_2=‘service=orcl valid_for=(online_logfiles,primary_role) db_unique_name=orcl‘ scope=spfile sid=‘*‘; alter system set log_archive_dest_state_1=enable scope=spfile sid=‘*‘; alter system set log_archive_dest_state_2=enable scope=spfile sid=‘*‘; alter system set standby_file_management=‘auto‘ scope=spfile sid=‘*‘; alter system set fal_server=‘orcl‘ scope=spfile sid=‘*‘; alter system set db_file_name_convert=‘+DATA/bol/datafile‘,‘/u01/app/oracle/oradata/orcl/data‘,‘+DATA/bol/tempfile‘,‘/u01/app/oracle/oradata/orcl/tempfile‘ scope=spfile sid=‘*‘; alter system set log_file_name_convert=‘+FRA/bol/onlinelog‘,‘/u01/app/oracle/admin/orcl/redo‘ scope=spfile sid=‘*‘; alter system set log_archive_format=‘%t_%s_%r.arch‘ scope=spfile sid=‘*‘; --备库参数 --在主库创建pfile 文件并scp 到备库修改 SQL> create pfile=‘/u01/app/oracle/product/11.2.0/db_1/dbs/tmp.ora‘ from spfile; File created. [[email?protected] dbs]$ scp tmp.ora oracle@10.15.7.16:/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora [[email?protected] dbs]$ vim initorcl.ora [[email?protected] dbs]$ cat initorcl.ora orcl.__db_cache_size=503316480 orcl.__java_pool_size=16777216 orcl.__large_pool_size=33554432 orcl.__pga_aggregate_target=452984832 orcl.__sga_target=822083584 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=251658240 orcl.__streams_pool_size=0 *.audit_file_dest=‘/u01/app/oracle/admin/orcl/adump‘ *.audit_trail=‘db‘ *.compatible=‘11.2.0.4.0‘ *.control_files=‘/u01/app/oracle/oradata/orcl/control01.ctl‘,‘/u01/app/oracle/fast_recovery_area/orcl/control02.ctl‘ *.db_block_size=8192 *.db_domain=‘‘ *.db_file_name_convert=‘+DATA/bol/datafile‘,‘/u01/app/oracle/oradata/orcl/tempfile‘ *.db_name=‘bol‘ *.service_names=‘orcl‘ *.db_unique_name=‘orcl‘ *.db_recovery_file_dest=‘/u01/app/oracle/fast_recovery_area‘ *.db_recovery_file_dest_size=6005194752 *.diagnostic_dest=‘/u01/app/oracle‘ *.dispatchers=‘(PROTOCOL=TCP) (SERVICE=bolXDB)‘ *.fal_server=‘bol‘ *.log_archive_config=‘dg_config=(bol,orcl)‘ *.log_archive_dest_1=‘location=/u01/app/oracle/archive valid_for=(all_logfiles,all_roles) db_unique_name=orcl‘ *.log_archive_dest_2=‘service=bol valid_for=(online_logfiles,primary_role) db_unique_name=bol‘ *.log_archive_dest_state_1=‘ENABLE‘ *.log_archive_dest_state_2=‘ENABLE‘ *.log_archive_format=‘%t_%s_%r.arch‘ *.log_file_name_convert=‘+FRA/bol/onlinelog‘,‘/u01/app/oracle/admin/orcl/redo‘ *.memory_target=1264582656 *.open_cursors=300 *.processes=350 *.remote_login_passwordfile=‘exclusive‘ *.standby_file_management=‘auto‘ *.undo_tablespace=‘UNDOTBS1‘ 使用 spfile 将备库启动 nomount 状态并启动监听 [[email?protected] dbs]$ sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 13 15:27:54 2019 Copyright (c) 1982,2013,Oracle. All rights reserved. SQL> conn /as sysdba Connected to an idle instance. SQL> create spfile from pfile; File created. SQL> startup nomount ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 822087616 bytes Database Buffers 436207616 bytes Redo Buffers 8818688 bytes [[email?protected] dbs]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-FEB-2019 15:40:44 Copyright (c) 1991,2013,Oracle. All rights reserved. Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 13-FEB-2019 15:40:47 Uptime 0 days 0 hr. 0 min. 10 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl",status UNKNOWN,has 1 handler(s) for this service... The command completed successfully 7?备库进行duplicate[[email?protected] dbs]$ rman target sys/oracle@bol auxiliary sys/oracle@orcl nocatalog Recovery Manager: Release 11.2.0.4.0 - Production on Wed Feb 13 16:18:06 2019 Copyright (c) 1982,2011,Oracle and/or its affiliates. All rights reserved. connected to target database: BOL (DBID=4213574617) using target database control file instead of recovery catalog connected to auxiliary database: BOL (not mounted) RMAN> duplicate target database for standby from active database; Starting Duplicate Db at 13-FEB-19 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=10 device type=DISK --详细日志
[[email?protected] dbs]$ rman target sys/oracle@bol auxiliary sys/oracle@orcl nocatalog Recovery Manager: Release 11.2.0.4.0 - Production on Wed Feb 13 16:18:06 2019 Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. connected to target database: BOL (DBID=4213574617) using target database control file instead of recovery catalog connected to auxiliary database: BOL (not mounted) RMAN> duplicate target database for standby from active database; Starting Duplicate Db at 13-FEB-19 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=10 device type=DISK contents of Memory Script: { backup as copy reuse targetfile ‘/u01/app/oracle/product/11.2.0/db_1/dbs/orapwbol1‘ auxiliary format ‘/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl‘ ; } executing Memory Script Starting backup at 13-FEB-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=26 instance=bol1 device type=DISK Finished backup at 13-FEB-19 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format ‘/u01/app/oracle/oradata/orcl/control01.ctl‘; restore clone controlfile to ‘/u01/app/oracle/fast_recovery_area/orcl/control02.ctl‘ from ‘/u01/app/oracle/oradata/orcl/control01.ctl‘; } executing Memory Script Starting backup at 13-FEB-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_bol1.f tag=TAG20190213T161904 RECID=3 STAMP=1000138745 channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:03 Finished backup at 13-FEB-19 Starting restore at 13-FEB-19 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy Finished restore at 13-FEB-19 contents of Memory Script: { sql clone ‘alter database mount standby database‘; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/oradata/orcl/tempfile/temp.262.990861409"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/orcl/data/system.259.990861405"; set newname for datafile 2 to "/u01/app/oracle/oradata/orcl/data/sysaux.260.990861407"; set newname for datafile 3 to "/u01/app/oracle/oradata/orcl/data/undotbs1.261.990861409"; set newname for datafile 4 to "/u01/app/oracle/oradata/orcl/data/undotbs2.263.990861413"; set newname for datafile 5 to "/u01/app/oracle/oradata/orcl/data/users.264.990861413"; set newname for datafile 6 to "/u01/app/oracle/oradata/orcl/data/test01.dbf"; set newname for datafile 7 to "/u01/app/oracle/oradata/orcl/data/sde_tbs.dbf"; set newname for datafile 9 to "/u01/app/oracle/oradata/orcl/data/example01.dbf"; set newname for datafile 10 to "/u01/app/oracle/oradata/orcl/data/cad01.dbf"; set newname for datafile 11 to "/u01/app/oracle/oradata/orcl/data/sj_data01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/orcl/data/system.259.990861405" datafile 2 auxiliary format "/u01/app/oracle/oradata/orcl/data/sysaux.260.990861407" datafile 3 auxiliary format "/u01/app/oracle/oradata/orcl/data/undotbs1.261.990861409" datafile 4 auxiliary format "/u01/app/oracle/oradata/orcl/data/undotbs2.263.990861413" datafile 5 auxiliary format "/u01/app/oracle/oradata/orcl/data/users.264.990861413" datafile 6 auxiliary format "/u01/app/oracle/oradata/orcl/data/test01.dbf" datafile 7 auxiliary format "/u01/app/oracle/oradata/orcl/data/sde_tbs.dbf" datafile 9 auxiliary format "/u01/app/oracle/oradata/orcl/data/example01.dbf" datafile 10 auxiliary format "/u01/app/oracle/oradata/orcl/data/cad01.dbf" datafile 11 auxiliary format "/u01/app/oracle/oradata/orcl/data/sj_data01.dbf" ; sql ‘alter system archive log current‘; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/orcl/tempfile/temp.262.990861409 in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 13-FEB-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=+DATA/bol/datafile/undotbs1.261.990861409 output file name=/u01/app/oracle/oradata/orcl/data/undotbs1.261.990861409 tag=TAG20190213T161917 channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:45 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=+DATA/bol/datafile/sysaux.260.990861407 output file name=/u01/app/oracle/oradata/orcl/data/sysaux.260.990861407 tag=TAG20190213T161917 channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=+DATA/bol/datafile/system.259.990861405 output file name=/u01/app/oracle/oradata/orcl/data/system.259.990861405 tag=TAG20190213T161917 channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=+DATA/bol/datafile/users.264.990861413 output file name=/u01/app/oracle/oradata/orcl/data/users.264.990861413 tag=TAG20190213T161917 channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=+DATA/bol/datafile/sde_tbs.dbf output file name=/u01/app/oracle/oradata/orcl/data/sde_tbs.dbf tag=TAG20190213T161917 channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+DATA/bol/datafile/undotbs2.263.990861413 output file name=/u01/app/oracle/oradata/orcl/data/undotbs2.263.990861413 tag=TAG20190213T161917 channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00010 name=+DATA/bol/datafile/cad01.dbf output file name=/u01/app/oracle/oradata/orcl/data/cad01.dbf tag=TAG20190213T161917 channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:26 channel ORA_DISK_1: starting datafile copy input datafile file number=00011 name=+DATA/bol/datafile/sj_data01.dbf output file name=/u01/app/oracle/oradata/orcl/data/sj_data01.dbf tag=TAG20190213T161917 channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00009 name=+DATA/bol/datafile/example01.dbf output file name=/u01/app/oracle/oradata/orcl/data/example01.dbf tag=TAG20190213T161917 channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=+DATA/bol/datafile/test01.dbf output file name=/u01/app/oracle/oradata/orcl/data/test01.dbf tag=TAG20190213T161917 channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:07 Finished backup at 13-FEB-19 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=3 STAMP=1000139021 file name=/u01/app/oracle/oradata/orcl/data/system.259.990861405 datafile 2 switched to datafile copy input datafile copy RECID=4 STAMP=1000139021 file name=/u01/app/oracle/oradata/orcl/data/sysaux.260.990861407 datafile 3 switched to datafile copy input datafile copy RECID=5 STAMP=1000139021 file name=/u01/app/oracle/oradata/orcl/data/undotbs1.261.990861409 datafile 4 switched to datafile copy input datafile copy RECID=6 STAMP=1000139021 file name=/u01/app/oracle/oradata/orcl/data/undotbs2.263.990861413 datafile 5 switched to datafile copy input datafile copy RECID=7 STAMP=1000139021 file name=/u01/app/oracle/oradata/orcl/data/users.264.990861413 datafile 6 switched to datafile copy input datafile copy RECID=8 STAMP=1000139021 file name=/u01/app/oracle/oradata/orcl/data/test01.dbf datafile 7 switched to datafile copy input datafile copy RECID=9 STAMP=1000139021 file name=/u01/app/oracle/oradata/orcl/data/sde_tbs.dbf datafile 9 switched to datafile copy input datafile copy RECID=10 STAMP=1000139021 file name=/u01/app/oracle/oradata/orcl/data/example01.dbf datafile 10 switched to datafile copy input datafile copy RECID=11 STAMP=1000139021 file name=/u01/app/oracle/oradata/orcl/data/cad01.dbf datafile 11 switched to datafile copy input datafile copy RECID=12 STAMP=1000139021 file name=/u01/app/oracle/oradata/orcl/data/sj_data01.dbf Finished Duplicate Db at 13-FEB-19View Code 8?启动备库并应用MRPSQL> select DBID,NAME,OPEN_MODE,DATABASE_ROLE from v$database; DBID NAME OPEN_MODE DATABASE_ROLE ---------- --------- -------------------- ---------------- 4213574617 BOL MOUNTED PHYSICAL STANDBY SQL> select process,status,sequence#,delay_mins from v$managed_standby; PROCESS STATUS SEQUENCE# DELAY_MINS --------- ------------ ---------- ---------- ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 SQL> show parameter convert; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string +DATA/bol/datafile,/u01/app/o racle/oradata/orcl/data,+DATA /bol/tempfile,/u01/app/oracle /oradata/orcl/tempfile log_file_name_convert string +FRA/bol/onlinelog,/u01/app/o racle/admin/orcl/redo SQL> alter database open; ---打开db报错 alter database open * ERROR at line 1: ORA-10458: standby database requires recovery ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: ‘/u01/app/oracle/oradata/orcl/data/system.259.990861405‘ [[email?protected] admin]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/ [[email?protected] trace]$ tail -n 200 alert_orcl.log ---查看错误日志 alter database open AUDIT_TRAIL initialization parameter is changed to OS,as DB is NOT compatible for database opened with read-only access Signalling error 1152 for datafile 1! Beginning Standby Crash Recovery. Serial Media Recovery started Managed Standby Recovery starting Real Time Apply Media Recovery Waiting for thread 1 sequence 150 Wed Feb 13 16:26:33 2019 Standby crash recovery need archive log for thread 1 sequence 150 to continue. Please verify that primary database is transporting redo logs to the standby database. Wait timeout: thread 1 sequence 150 Standby Crash Recovery aborted due to error 16016. Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2176.trc: ORA-16016: archived log for thread 1 sequence# 150 unavailable Recovery interrupted! Completed Standby Crash Recovery. Signalling error 1152 for datafile 1! Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2176.trc: ORA-10458: standby database requires recovery ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: ‘/u01/app/oracle/oradata/orcl/data/system.259.990861405‘ ORA-10458 signalled during: alter database open... -- DDE: Problem Key ‘ORA 1110‘ was flood controlled (0x1) (no incident) ORA-01110: data file 1: ‘/u01/app/oracle/oradata/orcl/data/system.259.990861405‘ 处理方法 在主库找到对应的日志文件并cp到备库 select * from v$archived_log where sequence#=150; ASMCMD> cp +FRA/bol/arch/150_1_990861401.log /u01/app/oracle copying +FRA/bol/arch/150_1_990861401.log -> /u01/app/oracle/150_1_990861401.log [[email?protected] oracle]$ scp 150_1_990861401.log oracle@10.15.7.16:/u01/app/oracle/archive/. 在备库上执行 SQL> alter database register physical logfile ‘/u01/app/oracle/archive/150_1_990861401.log‘; Database altered. 再尝试open数据库，正常打开 SQL> alter database open; Database altered. [[email?protected] trace]$ tail -f -n 200 alert_orcl.log Wed Feb 13 16:58:43 2019 alter database open Signalling error 1152 for datafile 1! Beginning Standby Crash Recovery. Serial Media Recovery started Managed Standby Recovery starting Real Time Apply Media Recovery Log /u01/app/oracle/archive/150_1_990861401.log Incomplete Recovery applied until change 6049418 time 02/13/2019 16:23:33 Completed Standby Crash Recovery. Wed Feb 13 16:58:44 2019 SMON: enabling cache recovery Dictionary check beginning Wed Feb 13 16:58:44 2019 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_2159.trc: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: ‘/u01/app/oracle/oradata/orcl/tempfile/temp.262.990861409‘ ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_2159.trc: ORA-01186: file 201 failed verification tests ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: ‘/u01/app/oracle/oradata/orcl/tempfile/temp.262.990861409‘ File 201 not verified due to error ORA-01157 Dictionary check complete Re-creating tempfile /u01/app/oracle/oradata/orcl/tempfile/temp.262.990861409 Database Characterset is AL32UTF8 No Resource Manager plan active ********************************************************** WARNING: Files may exists in db_recovery_file_dest that are not known to the database. Use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files. If files cannot be cataloged,then manually delete them using OS command. One of the following events caused this: 1. A backup controlfile was restored. 2. A standby controlfile was restored. 3. The controlfile was re-created. 4. db_recovery_file_dest had previously been enabled and then disabled. ********************************************************** replication_dependency_tracking turned off (no async multimaster replication found) Physical standby database opened for read only access. Completed: alter database open SQL> select DBID,DATABASE_ROLE from v$database; DBID NAME OPEN_MODE DATABASE_ROLE ---------- --------- -------------------- ---------------- 4213574617 BOL READ ONLY PHYSICAL STANDBY SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select DBID,DATABASE_ROLE from v$database; DBID NAME OPEN_MODE DATABASE_ROLE ---------- --------- -------------------- ---------------- 4213574617 BOL READ ONLY WITH APPLY PHYSICAL STANDBY 9?验证同步主库conn sde用户 备库查看 SQL> show parameter db_unique NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string orcl SQL> conn sde/sde Connected. SQL> select count(*) from dg_test1; ---没有同步 select count(*) from dg_test1 * ERROR at line 1: ORA-00942: table or view does not exist SQL> select sequence#,standby_dest,archived,applied,status from v$archived_log; SEQUENCE# STA ARC APPLIED S ---------- --- --- --------- - 150 NO YES YES A SQL> select process,delay_mins from v$managed_standby; PROCESS STATUS SEQUENCE# DELAY_MINS --------- ------------ ---------- ---------- ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 MRP0 WAIT_FOR_LOG 151 0 --主库日志 ALTER SYSTEM ARCHIVE LOG Wed Feb 13 16:23:40 2019 Thread 1 advanced to log sequence 151 (LGWR switch) Current log# 1 seq# 151 mem# 0: +DATA/bol/onlinelog/group_1.257.990861405 Current log# 1 seq# 151 mem# 1: +FRA/bol/onlinelog/group_1.257.990861405 Wed Feb 13 16:23:40 2019 Archived Log entry 212 added for thread 1 sequence 150 ID 0xfb25b6d9 dest 1: Wed Feb 13 16:46:13 2019 Thread 1 advanced to log sequence 152 (LGWR switch) Current log# 2 seq# 152 mem# 0: +DATA/bol/onlinelog/group_2.258.990861405 Current log# 2 seq# 152 mem# 1: +FRA/bol/onlinelog/group_2.258.990861405 Wed Feb 13 16:46:13 2019 Archived Log entry 213 added for thread 1 sequence 151 ID 0xfb25b6d9 dest 1: --备库日志 alter database recover managed standby database disconnect from session Attempt to start background Managed Standby Recovery process (orcl) Wed Feb 13 17:00:53 2019 MRP0 started with pid=20,OS id=2436 MRP0: Background Managed Standby Recovery process started (orcl) started logmerger process Wed Feb 13 17:00:58 2019 Managed Standby Recovery not using Real Time Apply Parallel Media Recovery started with 2 slaves Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Log /u01/app/oracle/archive/150_1_990861401.log Media Recovery Waiting for thread 1 sequence 151 Completed: alter database recover managed standby database disconnect from session ==启用real-time apply，从而实现real-time query： SQL> alter database recover managed standby database cancel; Database altered. SQL> conn / as sysdba Connected. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY ====== ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT Attempt to start background Managed Standby Recovery process (orcl) Wed Feb 13 17:18:04 2019 MRP0 started with pid=20,OS id=2497 MRP0: Background Managed Standby Recovery process started (orcl) started logmerger process Wed Feb 13 17:18:09 2019 Managed Standby Recovery starting Real Time Apply Parallel Media Recovery started with 2 slaves Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Waiting for thread 1 sequence 151 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT ==== SQL> set sqlprompt "primary>" primary>SELECT PROCESS,THREAD#,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY; PROCESS THREAD# SEQUENCE# STATUS --------- ---------- ---------- ------------ ARCH 1 185 CLOSING ARCH 1 186 CLOSING ARCH 0 0 CONNECTED ARCH 1 187 CLOSING SQL> set sqlprompt "standby>" --没有同步，于是重启rac的实例，重启后，dg自动同步---万能重启大法（因为前面修改了spfile的一些参数，必须重启才能生效） [[email?protected] ~]$ srvctl stop instance -o immediate -d bol -i bol1 [[email?protected] ~]$ srvctl status database -d bol Instance bol1 is not running on node rac1 Instance bol2 is not running on node rac2 [[email?protected] ~]$ srvctl start instance -d bol -i bol1 standby>select * from yhq22; ID NAME ---------- -------------------- 1 yhq11 SQL> set sqlprompt "primary>" primary>SELECT PROCESS,STATUS FROM V$MANAGED_STANDBY; PROCESS THREAD# SEQUENCE# STATUS --------- ---------- ---------- ------------ ARCH 1 189 CLOSING ARCH 1 189 CLOSING ARCH 1 187 CLOSING ARCH 1 186 CLOSING LNS 1 190 WRITING ---主库日志 ARC0: STARTING ARCH PROCESSES COMPLETE Completed: ALTER DATABASE OPEN /* db agent *//* {1:9052:2690} */ ARC0: Standby redo logfile selected for thread 1 sequence 188 for destination LOG_ARCHIVE_DEST_2 Wed Feb 13 17:49:31 2019 Starting background process CJQ0 Wed Feb 13 17:49:31 2019 CJQ0 started with pid=47,OS id=27107 --- standby>select DBID,DATABASE_ROLE,force_logging from v$database; DBID NAME OPEN_MODE DATABASE_ROLE LOG_MODE FOR ---------- --------- -------------------- ---------------- ------------ --- 4213574617 BOL READ ONLY WITH APPLY PHYSICAL STANDBY ARCHIVELOG YES === primary> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 193 standby> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 193 primary>select process,delay_mins from v$managed_standby; PROCESS STATUS SEQUENCE# DELAY_MINS --------- ------------ ---------- ---------- ARCH CLOSING 191 0 ARCH CLOSING 192 0 ARCH CLOSING 187 0 ARCH CLOSING 193 0 LNS WRITING 194 0 standby>select process,delay_mins from v$managed_standby; PROCESS STATUS SEQUENCE# DELAY_MINS --------- ------------ ---------- ---------- ARCH CLOSING 193 0 ARCH CLOSING 192 0 ARCH CONNECTED 0 0 ARCH CLOSING 191 0 MRP0 APPLYING_LOG 194 0 RFS IDLE 194 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 primary>select * from v$archive_gap; no rows selected standby>select * from v$archive_gap; no rows selected select sequence#,status from v$archived_log order by 1; select group#,bytes,status from v$standby_log; 至此，同步完成 --查看主备参数 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‘); --primary db_file_name_convert +DATA/bol/datafile,/u01/app/oracle/oradata/orcl/data,+DATA/bol/tempfile,/u01/app/oracle/oradata/orcl/tempfile log_file_name_convert +FRA/bol/onlinelog,/u01/app/oracle/admin/orcl/redo log_archive_dest_1 location=+FRA/BOL/ARCH log_archive_dest_2 service=orcl valid_for=(online_logfiles,primary_role) db_unique_name=orcl log_archive_dest_state_1 ENABLE log_archive_dest_state_2 ENABLE fal_server orcl log_archive_config dg_config=(bol,orcl) log_archive_format %s_%t_%r.log log_archive_max_processes 4 standby_file_management auto remote_login_passwordfile EXCLUSIVE db_name bol db_unique_name bol --standby db_file_name_convert +DATA/bol/datafile,/u01/app/oracle/admin/orcl/redo log_archive_dest_1 location=/u01/app/oracle/archive valid_for=(all_logfiles,all_roles) db_unique_name=orcl log_archive_dest_2 service=bol valid_for=(online_logfiles,primary_role) db_unique_name=bol log_archive_dest_state_1 ENABLE log_archive_dest_state_2 ENABLE fal_server bol log_archive_config dg_config=(bol,orcl) log_archive_format %t_%s_%r.arch log_archive_max_processes 4 standby_file_management auto remote_login_passwordfile EXCLUSIVE db_name bol db_unique_name orcl 10 错误及相关解决方法?--1?--主库ping备库，发现主库ping不同备库 [[email?protected] ~]$ tnsping orcl TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 13-FEB-2019 11:40:14 Copyright (c) 1997,Oracle. All rights reserved. Used parameter files: TNS-03505: Failed to resolve name --修改主备库的?tnsnames.ora，并reload --修改备库的tnsnames.ora [[email?protected] admin]$ cat tnsnames.ora bol= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.15.7.11)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.15.7.12)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =bol) ) ) orcl= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.15.7.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =orcl) ) ) --2 ORA-00845: MEMORY_TARGET not supported on this system SQL> startup nomount ---修改/dev/shm 简单来说就是 MEMORY_MAX_TARGET 的设置不能超过 /dev/shm 的大小 [[email?protected] ~]# cat /etc/fstab | grep tmpfs
--LOG_ARCHIVE_DEST_1 参数重复，去掉一个即可，完整的pfile可以参照上面，已经是修改过的， --3 rman的时候 DBGSQL: TARGET> begin :fhdbi := dbms_rcvcat.getDbid; end; 1）如果使用的是非catalog，在rman 连接时，加上nocatalog关键字，如 2） 在执行duplicate的时候，如果源库和目标库目录相同，那么在duplicate 时，需要加上nofilenamecheck，如下 参考：https://blog.csdn.net/tianlesoftware/article/details/6232292 --4 db不能打开到opne 状态? SQL> alter database open; 查看错误日志并进行解决 --alert.log Please verify that primary database is transporting redo logs to the standby database. 在主库找到对应的日志文件并cp到备库 Database altered. --5 主备库不同步，日志也并没有报错 最后完成，发现主库的操作米有同步过去 SQL> set sqlprompt "primary>" PROCESS THREAD# SEQUENCE# STATUS 由于前面修改了主库的spfile的一些参数，要重启db才能生效 [[email?protected] ~]$ srvctl stop instance -o immediate -d bol -i bol1 重启过后，主备dg自动同步 SQL> set sqlprompt "primary>" PROCESS THREAD# SEQUENCE# STATUS 相关的查询 standby>select DBID,status from v$standby_log; ? 日常运维管理 检查主备库是否存在GAP ? --文档参考 --https://www.cndba.cn/leo1990/article/1939 --https://blog.csdn.net/u014257861/article/details/80626257 --https://blog.csdn.net/tianlesoftware/article/details/6232292 --https://blog.csdn.net/imliuqun123/article/details/76292638 --https://www.cnblogs.com/dc-chen/p/9025872.html （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
推荐文章
站长推荐
热点阅读