Oracle DataGuard中的一些坑
知识0:SQL> 中alter set 设置变量值时注意用引号 '' , 否则会自动设置为大写。
知识1:Oracle数据库DB_NAME、SERVICE_NAME、SID、INSTANCE_NAME等区别
DB_NAME:
①是数据库名,长度不能超过8个字符,记录在datafile、redolog和control file中
②在DataGuard环境中DB_NAME相同而DB_UNIQUE_NAME不同
③在RAC环境中,各个节点的DB_NAME 都相同,但是INSTANCE_NAME不同
④DB_NAME还在动态注册监听的时候起作用,无论是否定义了SERVICE_NAME,PMON进程都会使用DB_NAME动态注册监听
DB_UNIQUE_NAME:
①在DataGuard中,主备库拥有相同的DB_NAME,为了区别,就必须有不同的DB_UNIQUE_NAME
②DB_UNIQUE_NAME在DG中会影响动态注册的SERVICE_NAME,即如果采用的是动态注册,则注册的SERVICE_NAME为DB_UNIQUE_NAME,但是实例还是INSTANCE_NAME,即SID
INSTANCE_NAME:
①数据库实例的名称,INSTANCE_NAME默认值是SID,一般情况下和数据库名称(DB_NAME)相同,也可不同
②initSID.ora 和orapwSID 文件要与INSTANCE_NAME保持一致
③INSTANCE_NAME会影响进程的名称
SID(SID_NAME)://待修正
①是操作系统中的环境变量,和ORACLE_HOME,ORACLE_BASE用法相同
②在操作系统中要想得到实例名,就必须使用ORACLE_SID。且ORACLE_SID必须与INSTANCE_NAME的值一致
SID即INSTANCE_NAME是用来唯一标示实例的
SERVICE_NAME(s):
SERVICE_NAME参数直接对应数据库。
①数据库和客户端相连是使用的服务名
②在DataGuard中,如果采用动态注册,建议在主备库使用相同的service_names
③在DataGuard中,如果采用静态注册,建议在主备库上的listener中输入相同的服务名(service_name)
④如果采监听采用了静态注册,那么SERVICE_NAME就等于Listener.ora文件中的GLOBAL_DATABASE_NAME的值
GLOBAL_DATABASE_NAME:
①GLOBAL_DATABASE_NAME 是listener配置的对外网络连接名称,可以是任意值
②在客户端配置监听的tnsnames.ora 文件中的service_name与这个GLOBAL_DBNAME 保持一致就可以了
③配置静态监听注册时,需要输入SID和GLOBAL_NAME
建议1:
$ tnsping <target_db>
$ tnsping <auxiliary_db>
多tnsping,验证两个数据库的连通性。
建议2:
SQL>shutdown 时,先退出RMAN连接。
bug1、lsnrctl reload 与 lsnrctl>stop lsnrctl>start并不一样(待确认)。
bug2、ORACLE error from auxiliary database: ORA-17629: Cannot connect to the remote database server
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/03/2019 10:16:24
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12533: TNS:illegal ADDRESS parameters
ORA-17629: Cannot connect to the remote database server
解决方:1:
如果用grid账户安装了GI,那grid目录下的sqlnet.ora、listen.ora和tnsname.ora是真正的配置文件。
方法是:将oracle用户下的tnsnames.ora的内容拷贝到grid用户下的tnsnames.ora文件中。
[root@ol73 admin]# cp tnsnames.ora /u01/12.1.0/grid/network/admin/
[root@ol73 admin]# chown -R grid:oinstall tnsnames.ora
解决方案2:可能是lsnrctl status的状态不对
lsnrctl reload / lsnrctl>start
bug3、“invalid username/password; logon denied”错误,也可能是数据库处于关闭状态。
如:
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01017: invalid username/password; logon denied
解决方法:
SQL>startup nomount(open)
bug4: MEMORY_TARGET not supported on this system
SQL> startup open;
ORA-00845: MEMORY_TARGET not supported on this system
解决方案:原因是/dev/shm 必须大于 MEMORY_TARGET。
$ df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 1.8G 0 1.8G 0% /dev
tmpfs 1.9G 635M 1.2G 35% /dev/shm
tmpfs 1.9G 9.2M 1.8G 1% /run
tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup
/dev/mapper/ol-root 10G 3.9G 6.2G 39% /
/dev/sda1 397M 199M 198M 51% /boot
/dev/mapper/ol-home 3.0G 58M 3.0G 2% /home
/dev/mapper/ol-u01 30G 18G 13G 58% /u01
/dev/mapper/ol-tmp 3.0G 59M 3.0G 2% /tmp
vmhgfs-fuse 159G 133G 27G 84% /mnt/hgfs
tmpfs 370M 0 370M 0% /run/user/54322
tmpfs 370M 16K 370M 1% /run/user/42
tmpfs 370M 0 370M 0% /run/user/54321
tmpfs 370M 0 370M 0% /run/user/0
$su
[root@ol73 dbs]# mount -o size=2560m /dev/shm
//*****************以下尚未测试**********
这个方案是临时方案,
#永久增加tempfs的大小,直接修改/etc/fstab
vi /etc/fstab
tmpfs /dev/shm tmpfs defaults,size=2048M 0 0
修改之后remount
mount -o remount /dev/shm
*************************************************//
bug5: failed to create file "/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf"
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/cdb1/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
dbms_backup_restore.restoreCancel() failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/03/2019 10:43:37
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-19660: some files in the backup set could not be verified
ORA-19661: datafile 5 could not be verified
ORA-19849: error while reading backup piece from service cdb1
ORA-19504: failed to create file "/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
解决方法:
在备机上创建目录 pdbseed。
------------以下是搞定的流程显示-----------------------------------------------------------------------------------------------------
[oracle@ol73 pdb1]$ rman target sys/Tian2018@cdb1 auxiliary sys/Tian2018@cdb2 nocatalog
Recovery Manager: Release 12.1.0.2.0 - Production on Fri May 3 11:36:14 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (DBID=980180079)
using target database control file instead of recovery catalog
connected to auxiliary database: CDB1 (not mounted)
RMAN> duplicate target database for standby nofilenamecheck from active database;
Starting Duplicate Db at 03-MAY-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=22 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.1.0/db_1/dbs/orapwcdb1' auxiliary format
'/u01/app/oracle/product/12.1.0/db_1/dbs/orapwcdb1' ;
}
executing Memory Script
Starting backup at 03-MAY-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
Finished backup at 03-MAY-19
contents of Memory Script:
{
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/cdb1/controlfile/o1_mf_gdm6xmg4_.ctl'', ''/u01/app/oracle/fast_recovery_area/cdb1/controlfile/o1_mf_gdm6xmp1_.ctl'' comment=
''Set by RMAN'' scope=spfile";
restore clone from service 'cdb1' standby controlfile;
}
executing Memory Script
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/cdb1/controlfile/o1_mf_gdm6xmg4_.ctl'', ''/u01/app/oracle/fast_recovery_area/cdb1/controlfile/o1_mf_gdm6xmp1_.ctl'' comment= ''Set by RMAN'' scope=spfile
Starting restore at 03-MAY-19
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/cdb1/controlfile/o1_mf_gdm6xmg4_.ctl
output file name=/u01/app/oracle/fast_recovery_area/cdb1/controlfile/o1_mf_gdm6xmp1_.ctl
Finished restore at 03-MAY-19
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
Using previous duplicated file /u01/app/oracle/oradata/cdb1/system01.dbf for datafile 1 with checkpoint SCN of 2716331
Using previous duplicated file /u01/app/oracle/oradata/cdb1/sysaux01.dbf for datafile 3 with checkpoint SCN of 2716354
Using previous duplicated file /u01/app/oracle/oradata/cdb1/undotbs01.dbf for datafile 4 with checkpoint SCN of 2716372
Using previous duplicated file /u01/app/oracle/oradata/cdb1/users01.dbf for datafile 6 with checkpoint SCN of 2724419
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/cdb1/temp01.dbf";
set newname for tempfile 2 to
"/u01/app/oracle/oradata/cdb1/pdbseed/pdbseed_temp012019-04-24_08-58-04-PM.dbf";
set newname for tempfile 3 to
"/u01/app/oracle/oradata/cdb1/pdb1/pdb1_temp012019-04-24_09-08-24-PM.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/cdb1/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/cdb1/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/cdb1/undotbs01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf";
set newname for datafile 6 to
"/u01/app/oracle/oradata/cdb1/users01.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf";
set newname for datafile 8 to
"/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf";
set newname for datafile 9 to
"/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf";
set newname for datafile 10 to
"/u01/app/oracle/oradata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf";
set newname for datafile 11 to
"/u01/app/oracle/oradata/cdb1/pdb1/example01.dbf";
restore
from service 'cdb1' clone datafile
5, 7, 8, 9, 10, 11 ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/cdb1/temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/cdb1/pdbseed/pdbseed_temp012019-04-24_08-58-04-PM.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/cdb1/pdb1/pdb1_temp012019-04-24_09-08-24-PM.dbf 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 restore at 03-MAY-19
using channel ORA_AUX_DISK_1
skipping datafile 5; already restored to SCN 1809309
skipping datafile 7; already restored to SCN 1809309
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/cdb1/pdb1/example01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 03-MAY-19
sql statement: alter system archive log current
contents of Memory Script:
{
restore clone force from service 'cdb1'
archivelog from scn 2716331;
catalog clone datafilecopy "/u01/app/oracle/oradata/cdb1/system01.dbf",
"/u01/app/oracle/oradata/cdb1/sysaux01.dbf",
"/u01/app/oracle/oradata/cdb1/undotbs01.dbf",
"/u01/app/oracle/oradata/cdb1/users01.dbf";
switch clone datafile 1 to datafilecopy
"/u01/app/oracle/oradata/cdb1/system01.dbf";
switch clone datafile 3 to datafilecopy
"/u01/app/oracle/oradata/cdb1/sysaux01.dbf";
switch clone datafile 4 to datafilecopy
"/u01/app/oracle/oradata/cdb1/undotbs01.dbf";
switch clone datafile 6 to datafilecopy
"/u01/app/oracle/oradata/cdb1/users01.dbf";
switch clone datafile all;
}
executing Memory Script
Starting restore at 03-MAY-19
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=45
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=46
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=47
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=48
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=49
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=50
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 03-MAY-19
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/cdb1/system01.dbf RECID=8 STAMP=1007293014
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/cdb1/sysaux01.dbf RECID=9 STAMP=1007293014
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/cdb1/undotbs01.dbf RECID=10 STAMP=1007293014
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/cdb1/users01.dbf RECID=11 STAMP=1007293014
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=1007293014 file name=/u01/app/oracle/oradata/cdb1/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=1007293014 file name=/u01/app/oracle/oradata/cdb1/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=1007293014 file name=/u01/app/oracle/oradata/cdb1/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=11 STAMP=1007293014 file name=/u01/app/oracle/oradata/cdb1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=1007293015 file name=/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=13 STAMP=1007293015 file name=/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=14 STAMP=1007293015 file name=/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=15 STAMP=1007293015 file name=/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=16 STAMP=1007293015 file name=/u01/app/oracle/oradata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=17 STAMP=1007293015 file name=/u01/app/oracle/oradata/cdb1/pdb1/example01.dbf
Finished Duplicate Db at 03-MAY-19