热门标签
当前位置: article > 正文
oracle12c存在pdb情况下的dataguard简单搭建_pdb dataguard
作者：Li_阴宅 | 2024-08-13 20:27:36
赞
踩
pdb dataguard
-- Oracle 12c Data Guard
-- 12c DG 特点及搭建注意点
- 特点;
Oracle 12c 的DataGuard 是在CDB级别实现的.但在有pdb的情况下与12c之前的版本有些区别.
在Oracle 12c的架构里，online redo log 和控制文件是保存在CDB中的，PDB中只有自己的数据文件，
所以我们这里加standby redo log，也是在CDB中加。
- 注意点;
备库在 rman restore数据文件时,需要注意创建pdb(还有pdbseed数据库)的目录.
-- 规划
- cat /etc/hosts
192.168.128.228 wh -- primary
192.168.128.229 sh -- standby
用主库上的PDB：POC_PDB 做我们的主库。
- 实例名：
DG Role --- DB_UNIQUE_NAME --- Oracle Net Service Name
Primary poc_sh poc_sh
standby poc_wh poc_wh
- 归档路径;
/u01/fra -- FRA
-- 环境
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 POC_PDB READ WRITE NO
SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;
SYS_CONTEXT('USERENV','CON_NAME')
----------------------------------------------------------------------------------------------------
CDB$ROOT
--- DG 搭建 主库端
- 主库启动FORCE LOGGING
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
POC_PDB READ WRITE
SQL> alter database force logging;
SQL> select force_logging from v$database;
-- 归档直接放在FRA, pcb到open状态
SQL> show parameter recovery
SQL> alter system set db_recovery_file_dest_size=1G;
SQL> alter system set db_recovery_file_dest='/u01/fra';
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/fra
db_recovery_file_dest_size big integer 1G
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
POC_PDB MOUNTED
SQL> alter pluggable database POC_PDB open;
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
POC_PDB READ WRITE
-- 启动归档模式
SQL> show con_name --确认在cdb下.
CON_NAME
------------------------------
CDB$ROOT
SQL> archive log list;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
-- 在主库添加 standby redo logfile
查看 Primary 库的 REDO 相关信息：
SQL> show con_name
select group#, members, bytes/1024/1024 m from v$log;
select member from v$logfile;
alter database add standby logfile thread 1
group 10 ('/u01/oradata/poc/sbredo10.log')size 50M,
group 11 ('/u01/oradata/poc/sbredo11.log')size 50M,
group 12 ('/u01/oradata/poc/sbredo12.log')size 50M,
group 13 ('/u01/oradata/poc/sbredo13.log')size 50M;
SQL> select GROUP#,THREAD#,BYTES/1024/1024 from v$standby_log;
GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
10 1 50
11 1 50
12 1 50
13 1 50
-- 主备库配置静态监听
- 使用netmgr工具生成,或手工编辑
[oracle@wh ~]$ vi /u01/app/oracle/product/12.1/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = POC)
(ORACLE_HOME = /u01/app/oracle/product/12.1/db_1)
(SID_NAME = POC) --注意; 是cdb
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wh)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
lsnrctl reload
lsnrctl status (看到1个 UNKNOW状态的Service )
[oracle@wh ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-FEB-2017 09:56:07
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wh)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 06-FEB-2017 14:52:45
Uptime 0 days 19 hr. 3 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/wh/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wh)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "POC" has 1 instance(s).
Instance "POC", status UNKNOWN, has 1 handler(s) for this service... -- 静态监听
Service "pocXDB" has 1 instance(s).
Instance "poc", status READY, has 1 handler(s) for this service...
Service "poc_pdb" has 1 instance(s).
Instance "poc", status READY, has 1 handler(s) for this service...
Service "poc_wh" has 1 instance(s).
Instance "poc", status READY, has 1 handler(s) for this service...
The command completed successfully
-- 分别在主备库配置tnsnames.ora
$ORACLE_HOME/network/admin/tnsnames.ora
-添加如下;
poc_wh =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wh)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = POC) -- 与listener.ora中的 unknow状态的静态监听一致.
)
)
poc_sh =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sh)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = POC)
)
)
- 测试
sqlplus sys/oracle@poc_sh as sysdba
sqlplus sys/oracle@poc_wh as sysdba
-- 在备库创建必要的目录
su - oracle
mkdir -p /u01/fra
mkdir -p /u01/oradata/poc/poc_pdb
mkdir -p /u01/oradata/poc/pdbseed
mkdir -p /u01/app/oracle/admin/poc/adump
-- 主库修改参数
alter system set db_unique_name='poc_wh' scope=spfile;
alter system set log_archive_config='dg_config=(poc_wh,poc_sh)';
alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=poc_wh' scope=spfile;
alter system set log_archive_dest_2='service=poc_sh reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=poc_sh';
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set standby_file_management='auto';
-
alter system set DB_FILE_NAME_CONVERT='/u01/oradata/poc','/u01/oradata/poc' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/u01/oradata/poc','/u01/oradata/poc' scope=spfile;
- 重启数据库,使得新参数生效
SQL>
shutdown immediate
startup
- 创建pfile
SQL>
create pfile='/home/oracle/pfile' from spfile;
-- 9 主库口令文件到备库
-- 10 主库参数文件到备库,并修改
-- 11 备库使用pfile创建spfile并启动到nomount状态.
SQL>
startup nomount pfile='/home/oracle/pfile';
create spfile from pfile='/home/oracle/pfile';
shutdown immediate
startup nomount;
show parameter spfile;
-- 12 创建备库
- Primary端,创建备份
rman target /
run{
ALLOCATE CHANNEL c1 TYPE disk;
BACKUP AS COMPRESSED BACKUPSET FULL DATABASE FILESPERSET 10 FORMAT '/u01/rman_bak/dbfull_%M%D_%U.bus';
RELEASE CHANNEL c1;
}
[oracle@wh ~]$ rman target /
run{
ALLOCATE CHANNEL c1 TYPE disk;
BACKUP AS COMPRESSED BACKUPSET FULL DATABASE FILESPERSET 10 FORMAT '/u01/rman_bak/dbfull_%M%D_%U.bus';
RELEASE CHANNEL c1;
}
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Feb 6 15:16:55 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: POC (DBID=1572033017)
RMAN> 2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=367 device type=DISK
Starting backup at 06-FEB-17
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/oradata/poc/undotbs01.dbf
input datafile file number=00003 name=/u01/oradata/poc/sysaux01.dbf
input datafile file number=00001 name=/u01/oradata/poc/system01.dbf
input datafile file number=00006 name=/u01/oradata/poc/users01.dbf
channel c1: starting piece 1 at 06-FEB-17
channel c1: finished piece 1 at 06-FEB-17
piece handle=/u01/rman_bak/dbfull_0206_02rrtgn9_1_1.bus tag=TAG20170206T151657 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:55
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/oradata/poc/poc_pdb/sysaux01.dbf
input datafile file number=00007 name=/u01/oradata/poc/poc_pdb/system01.dbf
input datafile file number=00009 name=/u01/oradata/poc/poc_pdb/poc_pdb_users01.dbf
channel c1: starting piece 1 at 06-FEB-17
channel c1: finished piece 1 at 06-FEB-17
piece handle=/u01/rman_bak/dbfull_0206_03rrtgp1_1_1.bus tag=TAG20170206T151657 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:25
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/oradata/poc/pdbseed/sysaux01.dbf
input datafile file number=00002 name=/u01/oradata/poc/pdbseed/system01.dbf
channel c1: starting piece 1 at 06-FEB-17
channel c1: finished piece 1 at 06-FEB-17
piece handle=/u01/rman_bak/dbfull_0206_04rrtgpq_1_1.bus tag=TAG20170206T151657 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:25
Finished backup at 06-FEB-17
Starting Control File and SPFILE Autobackup at 06-FEB-17
piece handle=/u01/fra/POC_WH/autobackup/2017_02_06/o1_mf_s_935248723_d9j8tmgt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-FEB-17
released channel: c1
RMAN> exit
Recovery Manager complete.
[oracle@wh ~]$
- Primary,创建standby控制文件
SQL> alter database create standby controlfile as '/u01/rman_bak/control01.ctl';
- 把rman备份，crontfile 从主库传到备库
- 启动standby数据库到mount状态
$ mv /u01/rman_bak/control01.ctl /u01/oradata/poc/control01.ctl (将从Primary传来的带有rman备份信息的control，复制到pfile中的位置)
$ cp /u01/oradata/poc/control01.ctl /u01/oradata/poc/control02.ctl
sqlplus / as sysdba
startup mount
- 使用RMAN restore数据文件
RMAN target /
RMAN> restore database; （由于需要把datafile 恢复到1T，非常缓慢）
RMAN> restore database;
Starting restore at 07-FEB-17
using channel ORA_DISK_1
datafile 1 is already restored to file /u01/oradata/poc/system01.dbf
datafile 3 is already restored to file /u01/oradata/poc/sysaux01.dbf
datafile 5 is already restored to file /u01/oradata/poc/undotbs01.dbf
datafile 6 is already restored to file /u01/oradata/poc/users01.dbf
skipping datafile 7; already restored to file /u01/oradata/poc/poc_pdb/system01.dbf
skipping datafile 8; already restored to file /u01/oradata/poc/poc_pdb/sysaux01.dbf
skipping datafile 9; already restored to file /u01/oradata/poc/poc_pdb/poc_pdb_users01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/poc/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/poc/pdbseed/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman_bak/dbfull_0206_04rrtgpq_1_1.bus
channel ORA_DISK_1: piece handle=/u01/rman_bak/dbfull_0206_04rrtgpq_1_1.bus tag=TAG20170206T151657
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 07-FEB-17
RMAN>
-- 13 启动备库并启动apply
- restore后备库是mount状态;
SQL> select open_mode from v$database; -- mount状态
OPEN_MODE
--------------------
MOUNTED
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 POC_PDB MOUNTED
SQL> alter database open; -- cdb(poc) 到open状态
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO -- 变为 read only
3 POC_PDB MOUNTED
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY -- cdb 由 mount状态 变为 read only状态
SQL> alter pluggable database poc_pdb open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 POC_PDB READ ONLY NO -- 变为 read only
- 主备库状态
SQL> select log_mode,open_mode ,database_role from v$database;
LOG_MODE OPEN_MODE DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG READ WRITE PRIMARY
- 备库启动real-time apply
SQL> alter database recover managed standby database using current logfile disconnect from session;
2017-02-07 15:15:27.419000 +08:00
Archived Log entry 6 added for thread 1 sequence 202 ID 0x5db35af9 dest 1:
2017-02-07 15:44:50.598000 +08:00
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (poc)
Starting background process MRP0
MRP0 started with pid=43, OS id=11310
MRP0: Background Managed Standby Recovery process started (poc)
2017-02-07 15:44:55.641000 +08:00
Started logmerger process
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 1 /u01/oradata/poc/redo01.log
Clearing online log 1 of thread 1 sequence number 196
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
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/poc_sh/poc/trace/poc_m000_11322.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
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/poc_sh/poc/trace/poc_m000_11322.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 1 complete
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 2 /u01/oradata/poc/redo02.log
Clearing online log 2 of thread 1 sequence number 197
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2017-02-07 15:44:56.623000 +08:00
Completed: alter database recover managed standby database using current logfile disconnect from session
Clearing online redo logfile 2 complete
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 3 /u01/oradata/poc/redo03.log
Clearing online log 3 of thread 1 sequence number 195
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 3 complete
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_197_d9lv0gpq_.arc
2017-02-07 15:44:59.192000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_198_d9lv0gpt_.arc
2017-02-07 15:45:02.667000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_199_d9lv0gpm_.arc
Resize operation completed for file# 3, old size 1075200K, new size 1085440K
2017-02-07 15:45:04.810000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_200_d9lv0vwl_.arc
2017-02-07 15:45:06.447000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_201_d9lv0wj9_.arc
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_202_d9lx0h6c_.arc
2017-02-07 15:45:08.310000 +08:00
Media Recovery Waiting for thread 1 sequence 203 (in transit)
Recovery of Online Redo Log: Thread 1 Group 13 Seq 203 Reading mem 0
Mem# 0: /u01/oradata/poc/sbredo13.log
SQL> select open_mode from v$database;
- 主库;
SQL> alter system switch logfile;
备库;
2017-02-07 15:47:23.977000 +08:00
RFS[1]: Selected log 11 for thread 1 sequence 205 dbid 1572033017 branch 934278201
Archived Log entry 8 added for thread 1 sequence 204 ID 0x5db35af9 dest 1:
Media Recovery Waiting for thread 1 sequence 205 (in transit)
Recovery of Online Redo Log: Thread 1 Group 11 Seq 205 Reading mem 0
Mem# 0: /u01/oradata/poc/sbredo11.log
-- 14 验证DG同步
- 主库;
SQL>
SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL; -- 查看当前处在哪个容器中.
alter session set Container=poc_pdb; -- 进入pdb
create table cndba as select * from dba_users;
SQL> alter system switch logfile; --提示 ora-65040 log在cdb级别,pdb不能切换
alter system switch logfile
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
alter session set Container=CDB$ROOT; -- 切换到cdb
alter system switch logfile;
-
-- 问题;
1. 在备库rman restore database完成后, alert日志报错如下;
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
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/poc_sh/poc/trace/poc_lgwr_11161.trc:
在dg同步开启后,主库切换几次日志,备库就会自动创建出 redo,standby redo
restore 完成后,备库目录;
[root@sh ~]# ll /u01/oradata/poc/
total 3492180
-rw-r----- 1 oracle oinstall 17973248 Feb 7 15:00 control01.ctl
-rw-r----- 1 oracle oinstall 17973248 Feb 7 15:00 control02.ctl
drwxr-xr-x 2 oracle oinstall 4096 Feb 7 14:54 pdbseed
drwxr-xr-x 2 oracle oinstall 4096 Feb 7 14:57 poc_pdb
-rw-r----- 1 oracle oinstall 1101012992 Feb 7 14:54 sysaux01.dbf
-rw-r----- 1 oracle oinstall 849354752 Feb 7 14:54 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Feb 7 14:54 temp01.dbf
-rw-r----- 1 oracle oinstall 1583357952 Feb 7 14:54 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Feb 7 14:54 users01.dbf
dg同步,并切换数个日志后,
[root@sh ~]# ll /u01/oradata/poc/
total 3860848
-rw-r----- 1 oracle oinstall 17973248 Feb 7 15:48 control01.ctl
-rw-r----- 1 oracle oinstall 17973248 Feb 7 15:48 control02.ctl
drwxr-xr-x 2 oracle oinstall 4096 Feb 7 14:54 pdbseed
drwxr-xr-x 2 oracle oinstall 4096 Feb 7 14:57 poc_pdb
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:44 redo01.log -- redo
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:44 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:44 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:48 sbredo10.log -- standby redo
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:48 sbredo11.log
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:15 sbredo12.log
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:47 sbredo13.log
-rw-r----- 1 oracle oinstall 1111498752 Feb 7 15:48 sysaux01.dbf
-rw-r----- 1 oracle oinstall 849354752 Feb 7 15:48 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Feb 7 14:54 temp01.dbf
-rw-r----- 1 oracle oinstall 1583357952 Feb 7 15:48 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Feb 7 15:48 users01.dbf
在备库开启恢复后,会开始 clear redo, 注意alert日志中的 "Clearing online redo logfile 3 complete".
2017-02-07 15:44:50.598000 +08:00
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (poc)
Starting background process MRP0
MRP0 started with pid=43, OS id=11310
MRP0: Background Managed Standby Recovery process started (poc)
2017-02-07 15:44:55.641000 +08:00
Started logmerger process
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 1 /u01/oradata/poc/redo01.log
Clearing online log 1 of thread 1 sequence number 196
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
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/poc_sh/poc/trace/poc_m000_11322.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
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/poc_sh/poc/trace/poc_m000_11322.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 1 complete
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 2 /u01/oradata/poc/redo02.log
Clearing online log 2 of thread 1 sequence number 197
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2017-02-07 15:44:56.623000 +08:00
Completed: alter database recover managed standby database using current logfile disconnect from session
Clearing online redo logfile 2 complete
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 3 /u01/oradata/poc/redo03.log
Clearing online log 3 of thread 1 sequence number 195
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 3 complete
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_197_d9lv0gpq_.arc
2017-02-07 15:44:59.192000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_198_d9lv0gpt_.arc
2017-02-07 15:45:02.667000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_199_d9lv0gpm_.arc
Resize operation completed for file# 3, old size 1075200K, new size 1085440K
2017-02-07 15:45:04.810000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_200_d9lv0vwl_.arc
2017-02-07 15:45:06.447000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_201_d9lv0wj9_.arc
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_202_d9lx0h6c_.arc
2017-02-07 15:45:08.310000 +08:00
Media Recovery Waiting for thread 1 sequence 203 (in transit)
Recovery of Online Redo Log: Thread 1 Group 13 Seq 203 Reading mem 0
Mem# 0: /u01/oradata/poc/sbredo13.log
-- 12c DG 特点及搭建注意点
- 特点;
Oracle 12c 的DataGuard 是在CDB级别实现的.但在有pdb的情况下与12c之前的版本有些区别.
在Oracle 12c的架构里，online redo log 和控制文件是保存在CDB中的，PDB中只有自己的数据文件，
所以我们这里加standby redo log，也是在CDB中加。
- 注意点;
备库在 rman restore数据文件时,需要注意创建pdb(还有pdbseed数据库)的目录.
-- 规划
- cat /etc/hosts
192.168.128.228 wh -- primary
192.168.128.229 sh -- standby
用主库上的PDB：POC_PDB 做我们的主库。
- 实例名：
DG Role --- DB_UNIQUE_NAME --- Oracle Net Service Name
Primary poc_sh poc_sh
standby poc_wh poc_wh
- 归档路径;
/u01/fra -- FRA
-- 环境
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 POC_PDB READ WRITE NO
SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;
SYS_CONTEXT('USERENV','CON_NAME')
----------------------------------------------------------------------------------------------------
CDB$ROOT
--- DG 搭建 主库端
- 主库启动FORCE LOGGING
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
POC_PDB READ WRITE
SQL> alter database force logging;
SQL> select force_logging from v$database;
-- 归档直接放在FRA, pcb到open状态
SQL> show parameter recovery
SQL> alter system set db_recovery_file_dest_size=1G;
SQL> alter system set db_recovery_file_dest='/u01/fra';
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/fra
db_recovery_file_dest_size big integer 1G
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
POC_PDB MOUNTED
SQL> alter pluggable database POC_PDB open;
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
POC_PDB READ WRITE
-- 启动归档模式
SQL> show con_name --确认在cdb下.
CON_NAME
------------------------------
CDB$ROOT
SQL> archive log list;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
-- 在主库添加 standby redo logfile
查看 Primary 库的 REDO 相关信息：
SQL> show con_name
select group#, members, bytes/1024/1024 m from v$log;
select member from v$logfile;
alter database add standby logfile thread 1
group 10 ('/u01/oradata/poc/sbredo10.log')size 50M,
group 11 ('/u01/oradata/poc/sbredo11.log')size 50M,
group 12 ('/u01/oradata/poc/sbredo12.log')size 50M,
group 13 ('/u01/oradata/poc/sbredo13.log')size 50M;
SQL> select GROUP#,THREAD#,BYTES/1024/1024 from v$standby_log;
GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
10 1 50
11 1 50
12 1 50
13 1 50
-- 主备库配置静态监听
- 使用netmgr工具生成,或手工编辑
[oracle@wh ~]$ vi /u01/app/oracle/product/12.1/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = POC)
(ORACLE_HOME = /u01/app/oracle/product/12.1/db_1)
(SID_NAME = POC) --注意; 是cdb
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wh)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
lsnrctl reload
lsnrctl status (看到1个 UNKNOW状态的Service )
[oracle@wh ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-FEB-2017 09:56:07
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wh)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 06-FEB-2017 14:52:45
Uptime 0 days 19 hr. 3 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/wh/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wh)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "POC" has 1 instance(s).
Instance "POC", status UNKNOWN, has 1 handler(s) for this service... -- 静态监听
Service "pocXDB" has 1 instance(s).
Instance "poc", status READY, has 1 handler(s) for this service...
Service "poc_pdb" has 1 instance(s).
Instance "poc", status READY, has 1 handler(s) for this service...
Service "poc_wh" has 1 instance(s).
Instance "poc", status READY, has 1 handler(s) for this service...
The command completed successfully
-- 分别在主备库配置tnsnames.ora
$ORACLE_HOME/network/admin/tnsnames.ora
-添加如下;
poc_wh =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wh)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = POC) -- 与listener.ora中的 unknow状态的静态监听一致.
)
)
poc_sh =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sh)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = POC)
)
)
- 测试
sqlplus sys/oracle@poc_sh as sysdba
sqlplus sys/oracle@poc_wh as sysdba
-- 在备库创建必要的目录
su - oracle
mkdir -p /u01/fra
mkdir -p /u01/oradata/poc/poc_pdb
mkdir -p /u01/oradata/poc/pdbseed
mkdir -p /u01/app/oracle/admin/poc/adump
-- 主库修改参数
alter system set db_unique_name='poc_wh' scope=spfile;
alter system set log_archive_config='dg_config=(poc_wh,poc_sh)';
alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=poc_wh' scope=spfile;
alter system set log_archive_dest_2='service=poc_sh reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=poc_sh';
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set standby_file_management='auto';
-
alter system set DB_FILE_NAME_CONVERT='/u01/oradata/poc','/u01/oradata/poc' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/u01/oradata/poc','/u01/oradata/poc' scope=spfile;
- 重启数据库,使得新参数生效
SQL>
shutdown immediate
startup
- 创建pfile
SQL>
create pfile='/home/oracle/pfile' from spfile;
-- 9 主库口令文件到备库
-- 10 主库参数文件到备库,并修改
-- 11 备库使用pfile创建spfile并启动到nomount状态.
SQL>
startup nomount pfile='/home/oracle/pfile';
create spfile from pfile='/home/oracle/pfile';
shutdown immediate
startup nomount;
show parameter spfile;
-- 12 创建备库
- Primary端,创建备份
rman target /
run{
ALLOCATE CHANNEL c1 TYPE disk;
BACKUP AS COMPRESSED BACKUPSET FULL DATABASE FILESPERSET 10 FORMAT '/u01/rman_bak/dbfull_%M%D_%U.bus';
RELEASE CHANNEL c1;
}
[oracle@wh ~]$ rman target /
run{
ALLOCATE CHANNEL c1 TYPE disk;
BACKUP AS COMPRESSED BACKUPSET FULL DATABASE FILESPERSET 10 FORMAT '/u01/rman_bak/dbfull_%M%D_%U.bus';
RELEASE CHANNEL c1;
}
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Feb 6 15:16:55 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: POC (DBID=1572033017)
RMAN> 2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=367 device type=DISK
Starting backup at 06-FEB-17
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/oradata/poc/undotbs01.dbf
input datafile file number=00003 name=/u01/oradata/poc/sysaux01.dbf
input datafile file number=00001 name=/u01/oradata/poc/system01.dbf
input datafile file number=00006 name=/u01/oradata/poc/users01.dbf
channel c1: starting piece 1 at 06-FEB-17
channel c1: finished piece 1 at 06-FEB-17
piece handle=/u01/rman_bak/dbfull_0206_02rrtgn9_1_1.bus tag=TAG20170206T151657 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:55
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/oradata/poc/poc_pdb/sysaux01.dbf
input datafile file number=00007 name=/u01/oradata/poc/poc_pdb/system01.dbf
input datafile file number=00009 name=/u01/oradata/poc/poc_pdb/poc_pdb_users01.dbf
channel c1: starting piece 1 at 06-FEB-17
channel c1: finished piece 1 at 06-FEB-17
piece handle=/u01/rman_bak/dbfull_0206_03rrtgp1_1_1.bus tag=TAG20170206T151657 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:25
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/oradata/poc/pdbseed/sysaux01.dbf
input datafile file number=00002 name=/u01/oradata/poc/pdbseed/system01.dbf
channel c1: starting piece 1 at 06-FEB-17
channel c1: finished piece 1 at 06-FEB-17
piece handle=/u01/rman_bak/dbfull_0206_04rrtgpq_1_1.bus tag=TAG20170206T151657 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:25
Finished backup at 06-FEB-17
Starting Control File and SPFILE Autobackup at 06-FEB-17
piece handle=/u01/fra/POC_WH/autobackup/2017_02_06/o1_mf_s_935248723_d9j8tmgt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-FEB-17
released channel: c1
RMAN> exit
Recovery Manager complete.
[oracle@wh ~]$
- Primary,创建standby控制文件
SQL> alter database create standby controlfile as '/u01/rman_bak/control01.ctl';
- 把rman备份，crontfile 从主库传到备库
- 启动standby数据库到mount状态
$ mv /u01/rman_bak/control01.ctl /u01/oradata/poc/control01.ctl (将从Primary传来的带有rman备份信息的control，复制到pfile中的位置)
$ cp /u01/oradata/poc/control01.ctl /u01/oradata/poc/control02.ctl
sqlplus / as sysdba
startup mount
- 使用RMAN restore数据文件
RMAN target /
RMAN> restore database; （由于需要把datafile 恢复到1T，非常缓慢）
RMAN> restore database;
Starting restore at 07-FEB-17
using channel ORA_DISK_1
datafile 1 is already restored to file /u01/oradata/poc/system01.dbf
datafile 3 is already restored to file /u01/oradata/poc/sysaux01.dbf
datafile 5 is already restored to file /u01/oradata/poc/undotbs01.dbf
datafile 6 is already restored to file /u01/oradata/poc/users01.dbf
skipping datafile 7; already restored to file /u01/oradata/poc/poc_pdb/system01.dbf
skipping datafile 8; already restored to file /u01/oradata/poc/poc_pdb/sysaux01.dbf
skipping datafile 9; already restored to file /u01/oradata/poc/poc_pdb/poc_pdb_users01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/poc/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/poc/pdbseed/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman_bak/dbfull_0206_04rrtgpq_1_1.bus
channel ORA_DISK_1: piece handle=/u01/rman_bak/dbfull_0206_04rrtgpq_1_1.bus tag=TAG20170206T151657
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 07-FEB-17
RMAN>
-- 13 启动备库并启动apply
- restore后备库是mount状态;
SQL> select open_mode from v$database; -- mount状态
OPEN_MODE
--------------------
MOUNTED
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 POC_PDB MOUNTED
SQL> alter database open; -- cdb(poc) 到open状态
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO -- 变为 read only
3 POC_PDB MOUNTED
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY -- cdb 由 mount状态 变为 read only状态
SQL> alter pluggable database poc_pdb open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 POC_PDB READ ONLY NO -- 变为 read only
- 主备库状态
SQL> select log_mode,open_mode ,database_role from v$database;
LOG_MODE OPEN_MODE DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG READ WRITE PRIMARY
- 备库启动real-time apply
SQL> alter database recover managed standby database using current logfile disconnect from session;
2017-02-07 15:15:27.419000 +08:00
Archived Log entry 6 added for thread 1 sequence 202 ID 0x5db35af9 dest 1:
2017-02-07 15:44:50.598000 +08:00
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (poc)
Starting background process MRP0
MRP0 started with pid=43, OS id=11310
MRP0: Background Managed Standby Recovery process started (poc)
2017-02-07 15:44:55.641000 +08:00
Started logmerger process
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 1 /u01/oradata/poc/redo01.log
Clearing online log 1 of thread 1 sequence number 196
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
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/poc_sh/poc/trace/poc_m000_11322.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
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/poc_sh/poc/trace/poc_m000_11322.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 1 complete
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 2 /u01/oradata/poc/redo02.log
Clearing online log 2 of thread 1 sequence number 197
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2017-02-07 15:44:56.623000 +08:00
Completed: alter database recover managed standby database using current logfile disconnect from session
Clearing online redo logfile 2 complete
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 3 /u01/oradata/poc/redo03.log
Clearing online log 3 of thread 1 sequence number 195
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 3 complete
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_197_d9lv0gpq_.arc
2017-02-07 15:44:59.192000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_198_d9lv0gpt_.arc
2017-02-07 15:45:02.667000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_199_d9lv0gpm_.arc
Resize operation completed for file# 3, old size 1075200K, new size 1085440K
2017-02-07 15:45:04.810000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_200_d9lv0vwl_.arc
2017-02-07 15:45:06.447000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_201_d9lv0wj9_.arc
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_202_d9lx0h6c_.arc
2017-02-07 15:45:08.310000 +08:00
Media Recovery Waiting for thread 1 sequence 203 (in transit)
Recovery of Online Redo Log: Thread 1 Group 13 Seq 203 Reading mem 0
Mem# 0: /u01/oradata/poc/sbredo13.log
SQL> select open_mode from v$database;
- 主库;
SQL> alter system switch logfile;
备库;
2017-02-07 15:47:23.977000 +08:00
RFS[1]: Selected log 11 for thread 1 sequence 205 dbid 1572033017 branch 934278201
Archived Log entry 8 added for thread 1 sequence 204 ID 0x5db35af9 dest 1:
Media Recovery Waiting for thread 1 sequence 205 (in transit)
Recovery of Online Redo Log: Thread 1 Group 11 Seq 205 Reading mem 0
Mem# 0: /u01/oradata/poc/sbredo11.log
-- 14 验证DG同步
- 主库;
SQL>
SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL; -- 查看当前处在哪个容器中.
alter session set Container=poc_pdb; -- 进入pdb
create table cndba as select * from dba_users;
SQL> alter system switch logfile; --提示 ora-65040 log在cdb级别,pdb不能切换
alter system switch logfile
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
alter session set Container=CDB$ROOT; -- 切换到cdb
alter system switch logfile;
-
-- 问题;
1. 在备库rman restore database完成后, alert日志报错如下;
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
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/poc_sh/poc/trace/poc_lgwr_11161.trc:
在dg同步开启后,主库切换几次日志,备库就会自动创建出 redo,standby redo
restore 完成后,备库目录;
[root@sh ~]# ll /u01/oradata/poc/
total 3492180
-rw-r----- 1 oracle oinstall 17973248 Feb 7 15:00 control01.ctl
-rw-r----- 1 oracle oinstall 17973248 Feb 7 15:00 control02.ctl
drwxr-xr-x 2 oracle oinstall 4096 Feb 7 14:54 pdbseed
drwxr-xr-x 2 oracle oinstall 4096 Feb 7 14:57 poc_pdb
-rw-r----- 1 oracle oinstall 1101012992 Feb 7 14:54 sysaux01.dbf
-rw-r----- 1 oracle oinstall 849354752 Feb 7 14:54 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Feb 7 14:54 temp01.dbf
-rw-r----- 1 oracle oinstall 1583357952 Feb 7 14:54 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Feb 7 14:54 users01.dbf
dg同步,并切换数个日志后,
[root@sh ~]# ll /u01/oradata/poc/
total 3860848
-rw-r----- 1 oracle oinstall 17973248 Feb 7 15:48 control01.ctl
-rw-r----- 1 oracle oinstall 17973248 Feb 7 15:48 control02.ctl
drwxr-xr-x 2 oracle oinstall 4096 Feb 7 14:54 pdbseed
drwxr-xr-x 2 oracle oinstall 4096 Feb 7 14:57 poc_pdb
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:44 redo01.log -- redo
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:44 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:44 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:48 sbredo10.log -- standby redo
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:48 sbredo11.log
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:15 sbredo12.log
-rw-r----- 1 oracle oinstall 52429312 Feb 7 15:47 sbredo13.log
-rw-r----- 1 oracle oinstall 1111498752 Feb 7 15:48 sysaux01.dbf
-rw-r----- 1 oracle oinstall 849354752 Feb 7 15:48 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Feb 7 14:54 temp01.dbf
-rw-r----- 1 oracle oinstall 1583357952 Feb 7 15:48 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Feb 7 15:48 users01.dbf
在备库开启恢复后,会开始 clear redo, 注意alert日志中的 "Clearing online redo logfile 3 complete".
2017-02-07 15:44:50.598000 +08:00
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (poc)
Starting background process MRP0
MRP0 started with pid=43, OS id=11310
MRP0: Background Managed Standby Recovery process started (poc)
2017-02-07 15:44:55.641000 +08:00
Started logmerger process
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 1 /u01/oradata/poc/redo01.log
Clearing online log 1 of thread 1 sequence number 196
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/poc/redo01.log'
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/poc_sh/poc/trace/poc_m000_11322.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
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/poc_sh/poc/trace/poc_m000_11322.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 1 complete
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 2 /u01/oradata/poc/redo02.log
Clearing online log 2 of thread 1 sequence number 197
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/poc/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2017-02-07 15:44:56.623000 +08:00
Completed: alter database recover managed standby database using current logfile disconnect from session
Clearing online redo logfile 2 complete
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 3 /u01/oradata/poc/redo03.log
Clearing online log 3 of thread 1 sequence number 195
Errors in file /u01/app/oracle/diag/rdbms/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
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/poc_sh/poc/trace/poc_mrp0_11310.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/poc/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 3 complete
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_197_d9lv0gpq_.arc
2017-02-07 15:44:59.192000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_198_d9lv0gpt_.arc
2017-02-07 15:45:02.667000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_199_d9lv0gpm_.arc
Resize operation completed for file# 3, old size 1075200K, new size 1085440K
2017-02-07 15:45:04.810000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_200_d9lv0vwl_.arc
2017-02-07 15:45:06.447000 +08:00
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_201_d9lv0wj9_.arc
Media Recovery Log /u01/fra/POC_SH/archivelog/2017_02_07/o1_mf_1_202_d9lx0h6c_.arc
2017-02-07 15:45:08.310000 +08:00
Media Recovery Waiting for thread 1 sequence 203 (in transit)
Recovery of Online Redo Log: Thread 1 Group 13 Seq 203 Reading mem 0
Mem# 0: /u01/oradata/poc/sbredo13.log
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Li_阴宅/article/detail/976412
推荐阅读
相关标签
