新部署系统，基础包19.3 linux
升级到19.7
1、安装opatch
过程，下载新版本的opatch ，cp -r $ORACLE_HOME/OPatch $ORACLE_HOME/OPatchbak
cd $ORACLE_HOME/
unzip /opt/opatchXXXXXXXXX.zip
2、 查看版本
$ORACLE_HOME/opatch version
3、安装 ojvm 补丁
4、安装 database 补丁
5、如果有已存在的数据，执行
cd $ORACLE_HOME/opatch
./datapatch -verbose ----------------更新数据库
其他：参考官方文档readme.html
转载：
https://cloud.tencent.com/developer/news/661665
一、背景
随着Oracle 11g进入扩展支持阶段，Oracle 19C作为12C家族中最终稳定版，已被多数公司熟知及应用于生产。本人所在公司也在尝试对19C进行部署、测试、升级、迁移，于是借此机会将热克隆这个特性做了一番测试。
二、使用热克隆的前提
1、需要12C R2及以上版本
在12C R1中，要克隆PDB，源PDB必须在克隆操作期间处于静止状态，因此它需要源PDB停机，通俗的讲这是种“冷克隆”。
从12C R2及以后的版本中开始支持“热克隆”，即Oracle数据库支持使用联机克隆的功能。当源PDB以读写方式打开的状态下，完全不需要中断源PDB中的操作，无须应用程序停机，就可以进行克隆操作。
2、必须使用local undo
当使用share undo的情况下，需要将share undo转成local undo后才可以使用热克隆。可以在upgrade模式下用alter database local undo on进行转换。
三、工作原理
从下面三张图可以看出不管是本地克隆、远程克隆，还是non-cdb克隆，都是类似rman方式进行备份恢复。热克隆会有以下3个阶段：
第一阶段：当热克隆开始时（t0），对源PDB的数据文件按块进行读取，直到源PDB最后一个块被读取并将其复制到目标PDB时（t1），此时t0-t1时间段内可能对已经复制的一些块进行了更改。那么，在此阶段，目标PDB可能与源PDB在物理上不一致。
第二阶段：将t0-t1之间对源PDB所做的更改传至目标PDB，进行重做应用。在此阶段，目标PDB将成为t1时源PDB的物理副本，但这里即包括了已提交的事务，也包括未提交的事务，因此可能在事务上不一致。
第三阶段：截止至t1时，源PDB中包含所有已提交的事务，所有未提交的事务将进行回滚，目标PDB将是截至t1时源PDB的事务一致的副本。由此可见，实现热克隆的关键是本地撤销，因此热克隆必须使用local undo。
图1 本地PDB克隆
图2 远程PDB克隆
图3 远程non-cdb克隆
四、常见应用场景
1、本地克隆
1）通过seed模板克隆
此方式主要应用于使用seed模板创建一个全新的PDB。
① 查看pdb的状态
SYS@ora19c>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
② 查看seed模板的datafile
SYS@ora19c>select con_id,name from v$datafile where con_id=2;
CON_ID NAME
2 /u01/app/oracle/oradata/ORA19C/pdbseed/system01.dbf
2 /u01/app/oracle/oradata/ORA19C/pdbseed/sysaux01.dbf
2 /u01/app/oracle/oradata/ORA19C/pdbseed/undotbs01.dbf③ 利用seed模板进行新PDB的克隆，无需对源库执行任何操作，指定数据文件转换目录映射
SYS@ora19c>CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb_mgr1 IDENTIFIED BY oracle roles=(dba) file_name_convert=('/u01/app/oracle/oradata/ORA19C/pdbseed','/u01/app/oracle/oradata/ORA19C/pdb1');
Pluggable database created.
④ 打开新的PDB进行验证
SYS@ora19c>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SYS@ora19c>alter pluggable database pdb1 open;
Pluggable database altered.
SYS@ora19c>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SYS@ora19c>select con_id,name from v$datafile where con_id=3;
CON_ID NAME
3 /u01/app/oracle/oradata/ORA19C/pdb1/system01.dbf
3 /u01/app/oracle/oradata/ORA19C/pdb1/sysaux01.dbf
3 /u01/app/oracle/oradata/ORA19C/pdb1/undotbs01.dbf
2）克隆一个已存在的PDB
此方式常用于将已存在的PDB快速的在本地创建镜像，拥有与源PDB完全相同的数据、结构、用户、权限等。
① 将刚创建的PDB1创建一个u1用户并授权，验证克隆是否会克隆用户及权限
SYS@ora19c>alter session set container=pdb1;
Session altered.
SYS@ora19c>create user u1 identified by oracle;
User created.
SYS@ora19c>grant connect,resource to u1;
Grant succeeded.
② 通过已存在的PDB1克隆出PDB2，源库可以在read write模式下直接进行操作
SYS@ora19c>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SYS@ora19c>create pluggable database pdb2 from pdb1 file_name_convert=('pdb1','pdb2');
Pluggable database created.
SYS@ora19c>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED③ 打开新创建的PDB进行验证
SYS@ora19c>alter pluggable database pdb2 open;
Pluggable database altered.
SYS@ora19c>select con_id,name from v$datafile where con_id=4;
CON_ID NAME 4 /u01/app/oracle/oradata/ORA19C/pdb2/system01.dbf
4 /u01/app/oracle/oradata/ORA19C/pdb2/sysaux01.dbf
4 /u01/app/oracle/oradata/ORA19C/pdb2/undotbs01.dbf④ 验证克隆的新库是否存在源库的用户及权限
SYS@ora19c>conn u1/oracle@192.168.8.101/pdb2
Connected.
U1@192.168.8.101/pdb2>select * from session_privs;
SET CONTAINER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE TYPE
CREATE TRIGGER
CREATE PROCEDURE
CREATE SEQUENCE
CREATE CLUSTER
CREATE TABLE
CREATE SESSION
U1@192.168.8.101/pdb2>select * from session_roles;
CONNECT
RESOURCE
SODA_APP
2、远程克隆
1）克隆远程已存在的PDB
此方式常用于将已存在的PDB快速的在异机之间创建镜像，拥有与源PDB完全相同的数据、结构、用户、权限等。
①源库pdb_mgr1用户授create pluggable database权限
SYS@ora19c>alter session set container=pdb1;
Session altered.
SYS@ora19c>grant create pluggable database to pdb_mgr1;
Grant succeeded.
② 目标CDB中创建db link
SYS@ora19c>create public database link lk_pdb1 connect to pdb_mgr1 identified by oracle using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.8.101)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb1)))';
Database link created.
③ 执行远程克隆操作，源库无须进行其它操作，可以在read write下操作
SYS@ora19c>CREATE PLUGGABLE DATABASE pdb1_r FROM pdb1@lk_pdb1 file_name_convert=('pdb1','pdb1_r');
Pluggable database created.
④ 打开新创建的PDB进行验证
SYS@ora19c>alter pluggable database pdb1_r open;
Pluggable database altered.
SYS@ora19c>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO
3 PDB1_R READ WRITE NO
SYS@ora19c>select con_id,name from v$datafile where con_id=3;
CON_ID NAME 3 /u01/app/oracle/oradata/ORA19C/pdb1_r/system01.dbf
3 /u01/app/oracle/oradata/ORA19C/pdb1_r/sysaux01.dbf
3 /u01/app/oracle/oradata/ORA19C/pdb1_r/undotbs01.dbf2）远程克隆Non-CDB
此方式常用于Non-CDB异机迁移CDB生成新的PDB。
① 查看源库的状态
SYS@noncdb>select name,cdb,con_id from v$database;
NAME CDB CON_ID
NONCDB NO 0
② 源库pdb_mgr1用户授create pluggable database权限
SYS@noncdb>grant create pluggable database to system;
Grant succeeded.
③ 目标CDB中创建db link
SYS@ora19c>create public database link lk_noncdb connect to system identified by oracle using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.8.101)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=noncdb)))';
Database link created.
SYS@ora19c>select name,cdb,con_id from v$database@lk_noncdb;
NAME CDB CON_ID
NONCDB NO 0
④ 执行noncdb的远程克隆
SYS@ora19c>CREATE PLUGGABLE DATABASE noncdb_pdb FROM noncdb@lk_noncdb file_name_convert=('/u01/app/oracle/oradata/NONCDB','/u01/app/oracle/oradata/ORA19C/noncdb_pdb');
Pluggable database created.
⑤ 打开新的PDB进行验证
SYS@ora19c>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO
3 PDB1_R READ WRITE NO
5 NONCDB_PDB MOUNTED
SYS@ora19c>alter pluggable database NONCDB_PDB open;
Warning: PDB altered with errors.
⑥ open失败，执行nocdb to pdb的脚本
SYS@ora19c>alter session set container=NONCDB_PDB;
Session altered.
SYS@ora19c>@?/rdbms/admin/noncdb_to_pdb.sql
⑦ 打开新创建的PDB进行验证
SYS@ora19c>alter pluggable database NONCDB_PDB open;
Pluggable database altered.
SYS@ora19c>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO
3 PDB1_R READ WRITE NO
5 NONCDB_PDB READ WRITE NOSYS@ora19c>select con_id,name from v$datafile where con_id=5;
CON_ID NAME 5 /u01/app/oracle/oradata/ORA19C/noncdb_pdb/system01.dbf
5 /u01/app/oracle/oradata/ORA19C/noncdb_pdb/sysaux01.dbf
5 /u01/app/oracle/oradata/ORA19C/noncdb_pdb/undotbs01.dbf
5 /u01/app/oracle/oradata/ORA19C/noncdb_pdb/users01.dbf五、特殊应用场景
1、子集克隆
从12.1.0.2开始，引入了User Tablespaces，简单的说就是可以按表空间（用户创建的）来克隆PDB。比如，当前PDB1中，用户新建了两个表空间ts1,ts2，克隆只需要ts1表空间中的数据，那么我们可以用USER_TABLESPACES子句只克隆PDB1中的ts1表空间，这样大大的缩短了时间和不必要的空间开销。对于拆分数据也很有用，可以把一个库按照表空间拆分。
语法：
USER_TABLESPACES=ALL 默认，所有表空间都克隆；
USER_TABLESPACES=NONE 所有用户创建的表空间都不克隆；
USER_TABLESPACES=(‘ts1’) 指定只克隆ts1；
USER_TABLESPACES=ALL EXCEPT(‘ts1’) 除了ts1之外，其他表空间都克隆。
1）源库创建表空间ts1,ts2
SYS@ora19c>create tablespace ts1 datafile '/u01/app/oracle/oradata/ORA19C/pdb1/ts1.dbf' size 10m;
Tablespace created.
SYS@ora19c>create tablespace ts2 datafile '/u01/app/oracle/oradata/ORA19C/pdb1/ts2.dbf' size 10m;
Tablespace created.
2）进行子集克隆，只克隆ts1表空间
SYS@ora19c>CREATE PLUGGABLE DATABASE pdb1_z FROM pdb1 file_name_convert=('pdb1','pdb1_z') user_tablespaces=('ts1');
Pluggable database created.
3）打开新创建的PDB进行验证
SYS@ora19c>alter pluggable database pdb1_z open;
Pluggable database altered.
SYS@ora19c>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
6 PDB1_Z READ WRITE NO
SYS@ora19c>select con_id,name from v$datafile where con_id=6;
CON_ID NAME 6 /u01/app/oracle/oradata/ORA19C/pdb1_z/system01.dbf
6 /u01/app/oracle/oradata/ORA19C/pdb1_z/sysaux01.dbf
6 /u01/app/oracle/oradata/ORA19C/pdb1_z/undotbs01.dbf
6 /u01/app/oracle/oradata/ORA19C/pdb1_z/ts1.dbf仅元数据的子集克隆，使用no data，创建语法：
create pluggable database pdb_nodata from pdb1 file_name_convert=('pdb1','pdb1_nodata') no data;
2、利用可刷新PDB的功能进行数据迁移
可刷新PDB功能是建立在热克隆的基础之上的。
当生产PDB数据量非常大，需要在很短的窗口时间进行数据迁移，当有了可刷新PDB和热克隆的功能后，一切将变得简单。无需考虑克隆需要花多长时间，因为源数据库无需停机。当目标PDB变得陈旧时，我们可以对其刷新，应用自上次刷新以来积累的所有增量。即使源数据库非常庞大，增量重做通常也将小得多。最后只在需要做割接时将源PDB置为read only后进行一次增量刷新。
刷新PDB须注意以下几点：
源库必须开启归档日志和local undo；
可以手动刷新或者自动定时刷新，但刷新时目标端必须是mounted状态；
在不刷新期间，目标端可以以只读模式打开；
如果需以读写模式打开目标端，则必须将refresh mode设置为none，设置none之后就无法再回退回其它刷新模式；
刷新PDB必须使用dblink，dblink可以指向同一个CDB，也可以指向不同CDB。
1）在目标PDB创建db link
SYS@ora19c>create public database link lk_pdb1 connect to pdb_mgr1 identified by oracle using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.8.101)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb1)))';
Database link created.
2）通过db link创建refresh PDB
SYS@ora19c>CREATE PLUGGABLE DATABASE pdb1_ref FROM pdb1@lk_pdb1 file_name_convert=('pdb1','pdb1_ref') REFRESH MODE EVERY 60 MINUTES;
Pluggable database created.
SYS@ora19c>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO
3 PDB1_R READ WRITE NO
4 PDB1_REF MOUNTED
5 NONCDB_PDB READ WRITE NO3）当PDB处于REFRESH模式时只能有mounted和read only两种状态
SYS@ora19c>alter pluggable database pdb1_ref open;
alter pluggable database pdb1_ref open
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode
SYS@ora19c>alter pluggable database pdb1_ref open read only;
Pluggable database altered.
SYS@ora19c>select pdb_id,pdb_name,refresh_mode from cdb_pdbs;
PDB_ID PDB_NAME REFRES 2 PDB$SEED NONE
4 PDB1_REF AUTO
5 NONCDB_PDB NONE
3 PDB1_R NONE4）PDB只能在mounted状态下使用REFRESH功能
SYS@ora19c>alter pluggable database refresh;
alter pluggable database refresh
*
ERROR at line 1:
ORA-65025: Pluggable database PDB1_REF is not closed on all instances.
Alert log:
PDB1_REF(4):PDB1_REF(4):ERROR:PDB needs to be closed for auto refresh
PDB1_REF(4):Completed: alter pluggable database refresh
5）源PDB创建测试数据
U1@192.168.8.101/pdb1>create table t1 as select * from dba_objects;
Table created.
U1@192.168.8.101/pdb1>select count(*) from t1;
723596）模拟应用侧停应用，将源端PDB置为read only
SYS@ora19c>alter pluggable database pdb1 close immediate;
Pluggable database altered.
SYS@ora19c>alter pluggable database pdb1 open read only;
Pluggable database altered.
7）目标端手动刷新，应用最近的增量，观察目志是否正常
SYS@ora19c>alter pluggable database pdb1_ref refresh;
Pluggable database altered.
Alert log:
2020-02-19T13:23:44.457060+08:00
alter pluggable database pdb1_ref refresh
2020-02-19T13:23:45.940479+08:00
Applying media recovery for pdb-4099 from SCN 2793352 to SCN 2793357
Remote log information: count-1
thr-1, seq-12, logfile-/u01/app/oracle/product/db_1/dbs/archparlog_1_12_4aa635f6_1029786031.arc, los-2752894, nxs-18446744073709551615
PDB1_REF(4):Media Recovery Start
2020-02-19T13:23:45.942469+08:00
PDB1_REF(4):Serial Media Recovery started
PDB1_REF(4):max_pdb is 9
2020-02-19T13:23:45.996021+08:00
PDB1_REF(4):Media Recovery Log /u01/app/oracle/product/db_1/dbs/archparlog_1_12_4aa635f6_1029786031.arc
2020-02-19T13:23:46.257650+08:00
PDB1_REF(4):Incomplete Recovery applied until change 2793357 time 02/19/2020 13:23:09
2020-02-19T13:23:46.264473+08:00
PDB1_REF(4):Media Recovery Complete (ora19c)
Completed: alter pluggable database pdb1_ref refresh
8）目标端PDB关闭刷新模式
SYS@ora19c>ALTER PLUGGABLE DATABASE pdb1_ref REFRESH MODE NONE;
Pluggable database altered
9）拉起目标端PDB
SYS@ora19c>ALTER PLUGGABLE DATABASE pdb1_ref open read write;
Pluggable database altered.
SYS@ora19c>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO
3 PDB1_R MOUNTED
4 PDB1_REF READ WRITE NO
5 NONCDB_PDB MOUNTED10）应用连接新PDB，校验业务
SYS@ora19c>conn u1/oracle@192.168.8.102/pdb1_ref
Connected.
U1@192.168.8.102/pdb1_ref>select count(*) from t1;
72359六、热克隆中常见的错误
错误一：
ORA-65040: operation not allowed from within a pluggable database
解决方法： alter session set container=cdb$root;
错误二：
ORA-17628: Oracle error 1031 returned by remote Oracle server ORA-01031: insufficient privileges
解决方法： 到源库里对用户授create pluggable database权限即可。
错误三：
ORA-19504: failed to create file '/u01/app/oracle/oradata/ORA19C/pdb1' ORA-27038: created file already exists
解决方法： 文件映射路径问题，将“文件夹—文件夹”或“文件—文件”进行一一对应。
错误四：
ORA-65005: missing or invalid file name pattern for file-/u01/app/oracle/oradata/ORA19C/pdb1/system01.dbf
解决方法： 路径错误或注意路径中的大小写。
错误五：
ORA-01578: ORACLE data block corrupted (file # 72, block # 33609) ORA-01110: data file 72: '/u01/app/oracle/oradata/ORA19C/pdb1_ref/system01.dbf' ORA-26040: Data block was loaded using the NOLOGGING option
解决方法： 创建可刷新PDB时，源端未开启归档模式。
七、小结
热克隆的方式目前都已经比较成熟，并且可以灵活使用，适合多种应用场景。既可以应用于快速创建生产环境的完整副本或子集副本，也可以应用于较短停机时间的迁移。业务中断时间短，甚至无需业务中断，操作简单，不易出错，但某些场景下对环境要求较高。
最后一行的值
mapped 表示该进程映射的虚拟地址空间大小，也就是该进程预先分配的虚拟内存大小，即ps出的vsz
writeable/private 表示进程所占用的私有地址空间大小，也就是该进程实际使用的内存大小
shared 表示进程和其他进程共享的内存大小
循环显示进程2318 的设备格式的最后1行，间隔2秒，
[root@orcl:/root]$ while true; do pmap -d 2318 | tail -1; sleep 2; done
mapped: 1805072K writeable/private: 8324K shared: 1558540K
mapped: 1805072K writeable/private: 8324K shared: 1558540K
mapped: 1805072K writeable/private: 8324K shared: 1558540K
mapped: 1805072K writeable/private: 8324K shared: 1558540K
mapped: 1805072K writeable/private: 8324K shared: 1558540K
mapped: 1805072K writeable/private: 8324K shared: 1558540K
mapped: 1805072K writeable/private: 8324K shared: 1558540K
mapped: 1805072K writeable/private: 8324K shared: 1558540K
mapped: 1805072K writeable/private: 8324K shared: 1558540K
mapped: 1805072K writeable/private: 8324K shared: 1558540K
mapped: 1805072K writeable/private: 8324K shared: 1558540K
mapped: 1805072K writeable/private: 8324K shared: 1558540K
mapped: 1805072K writeable/private: 8324K shared: 1558540K
mapped: 1805072K writeable/private: 8324K shared: 1558540K
1、自动安装oracle.zip
2、InstallOracleshell-main (1).zip
1参考：同事上传
2参考：https://github.com/pc-study/InstallOracleshell
[root@dbbackup opt]# sh auto_install_oracle_server.sh
========>-------- seletion------------
========>1.install database
========>2.deinstall database
========>3.configure local yum
========>-----------------------------
========>Your choice:1^C
[root@dbbackup opt]# sh auto_install_oracle_server.sh /data orcl
========>-------- seletion------------
========>1.install database
========>2.deinstall database
========>3.configure local yum
========>-----------------------------
========>Your choice:1
[ok]====>begin install_db
[ok]====>current user is root
[OK]====>the yum configure
[OK]====>the installfile
[OK]====>the config file
========>----charcaterset seletion----
========>1.AMERICAN_AMERICA.ZHS16GBK
========>2.AMERICAN_AMERICA.AL32UTF8
========>-----------------------------
========>Your choice:(default 1):
========>please input sid(less than 8 letters):orcl
========>please input memory target(MB):4096
========>please input ORACLE_BASE(will auto create):/data/oracle
========>please input host IP address:192.168.1.49
[ok]====>cpu
[ok]====>memory
[ok]====>/tmp
[ok]====>swap
[ok]====>ORACLE_BASE
[OK]====>the yum configure
[ok]====>the yum package has been installed
[ok]====>the sysctl.conf has been backup and change
[ok]====>the limits.conf has been backup and change
[ok]====>the login has been backup and change
[ok]====>the profile has been backup and change
[ok]====>the oracle user and oinstall,dba group has been created
[ok]====>the .bash_profile has been backup and change
[ok]====>the dir has been created
[ok]====>the install file has been unzip
[ok]====>the hosts has been backup and change
[ok]====>the db_install.rsp has been backup and change
[ok]====>waiting for software install . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
[ok]====>software has installed
[ok]====>two script has been run by root
[ok]====>netca.rsp has been backup and change
[ok]====>waiting for netca install .
[ok]====>netca has installed
[ok]====>dbca.rsp has been backup and change
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/data/oracle/cfgtoollogs/dbca/orcl/orcl.log" for further details.
[ok]====>dbca has installed
[ok]====>the install process success
DataGuard是甲骨文推出的一种高可用性数据库方案，在Oracle 8i之前被称为Standby Database。从Oracle 9i开始，正式更名为Data Guard。它是在主节点与备用节点间通过日志同步来保证数据的同步，可以实现数据库快速切换与灾难性恢复。Data Guard只是在软件上对数据库进行设置，并不需要额外购买任何组件。用户能够在对主数据库影响很小的情况下，实现主备数据库的同步。而主备机之间的数据差异只限于在线日志部分，因此被不少企业用作数据容灾解决方案。
DataGuard分为"物理standby"和"逻辑standby"
物理standby
主备库之间物理结构，逻辑结构保持一致。通常情况下备用库通过主库传输过来的归档日志做恢复。
基于"块对块"方式。与主数据库相同。物理standby等同于生产库。物理standby能以read only方式打开(或者在flashback数据库下以"读写方式打开")。
物理standby一旦以"读写"方式打开，备用库将不会从主数据库接受重做数据。只有当备库flashback到过去的点，当备用库flashback到"读写"打开时间点前的状态时，DataGuard会自动同步备库与主库。才能恢复standby角色。
逻辑standby
只保证逻辑结构与主库一致。备用库要处于打开状态，通过从主库归档日志文件中提取出来的SQL语句做恢复。
创建时，与物理standby一样，使用主库copy.
逻辑备库通过执行SQL语句来更新。DataGuard转换日志文件中的数据为SQL语句，然后在逻辑standby上执行SQL语句.因为逻辑standby是通过SQL语句来实现数据同步，那么在同步期间其必须保持打开状态。
| 内容 | 主库 | 备库 |
|---|---|---|
| 操作系统 | CENTOS 7.4 | CENTOS 7.4 |
| IP | 192.168.117.129 | 192.168.117.128 |
| db_name | dg | dg |
| db_unique_name | dg1 | dg2 |
| 数据库安装 | 安装数据库软件+创建监听+安装数据库 | 安装数据库软件+创建监听 |
[oracle@dg1 dbs]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.117.129 dg1
192.168.117.128 dg2[oracle@dg1 dbs]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-AUG-2021 10:21:04
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 08-AUG-2021 02:41:32
Uptime 0 days 7 hr. 39 min. 31 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/11.2.0.4/product/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1)(PORT=1521)))
Services Summary...
Service "dg1" has 2 instance(s).
Instance "dg1", status UNKNOWN, has 1 handler(s) for this service...
Instance "dg1", status READY, has 1 handler(s) for this service...
Service "dg1XDB" has 1 instance(s).
Instance "dg1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@dg1 dbs]$ listener.ora
[oracle@dg1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/11.2.0.4/product/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dg1)
(ORACLE_HOME =/u01/app/oracle/11.2.0.4/product)
(SID_NAME = dg1)
)
)
ADR_BASE_LISTENER = /u01/app/oracletnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/11.2.0.4/product/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DG2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =dg2 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg2)
)
)
DG1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg1)
)
)参数文件
[oracle@dg1 dbs]$ cat initdg1.ora
dg1.__db_cache_size=557842432
dg1.__java_pool_size=4194304
dg1.__large_pool_size=8388608
dg1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dg1.__pga_aggregate_target=293601280
dg1.__sga_target=884998144
dg1.__shared_io_pool_size=0
dg1.__shared_pool_size=293601280
dg1.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/dg1/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/dg1/control01.ctl','/u01/app/oracle/recovery_area/dg1/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='dg1'
*.db_recovery_file_dest='/u01/app/oracle/recovery_area'
*.db_recovery_file_dest_size=4385144832
*.DB_UNIQUE_NAME='dg1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg1XDB)'
*.FAL_CLIENT='dg1'
*.FAL_SERVER='dg2'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1'
*.LOG_ARCHIVE_DEST_2='SERVICE=dg2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg2'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=293601280
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=881852416
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'
*.DB_FILE_NAME_CONVERT='dg1','dg2' 节点2
[oracle@dg2 dbs]$ cat initdg2.ora
dg2.__db_cache_size=633339904
dg2.__java_pool_size=4194304
dg2.__large_pool_size=8388608
dg2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dg2.__pga_aggregate_target=293601280
dg2.__sga_target=880803840
dg2.__shared_io_pool_size=0
dg2.__shared_pool_size=222298112
dg2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dg2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/dg2/control01.ctl','/u01/app/oracle/fast_recovery_area/dg2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dg1'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.DB_UNIQUE_NAME='dg2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg2XDB)'
*.FAL_CLIENT='dg2'
*.FAL_SERVER='dg1'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg2'
*.LOG_ARCHIVE_DEST_2='SERVICE=dg1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.log_file_name_convert='</u01/app/oracle/oradata/dg1/>','</u01/app/oracle/oradata/dg2>'
*.open_cursors=300
*.pga_aggregate_target=293601280
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=880803840
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'复制节点1的密码文件到节点2
dg1正常运行，dg2启动到nomount状态
rman target sys/oracle@dg1 auxiliary sys/oracle@dg2复制数据库到dg2
duplicate target database for standby from active database;
duplicate target database for standby from active database nofilenamecheck;（节点1和节点2的目录完全一样时，使用nofilenamecheck参数）备库开始接收应用日志
[oracle@oracle10g-dg2-213-101 ~]$ sqlplus / as sysdba
SQL> alter database recover managed standby database disconnect from session;验证是否同步
备库:
archive log list;
select name,sequence#,applied from v$archived_log a where a.sequence#=(select max(sequence#) from v$archived_log);
col dest_name format A40
SQL> select dest_name,status,error from v$archive_dest where rownum<3;
主库:
archive log list;
SQL> col dest_name format A40
col error format A20
set line 200;
select name,sequence#,applied from v$archived_log a where a.sequence#=(select max(sequence#) from v$archived_log);
SQL> col dest_name format A40
select dest_name,status,error from v$archive_dest where rownum<3;
查看DG的日志信息
SQL> select * from v$dataguard_status;
Open Read Only standby数据库并且开启实时日志应用
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1188511744 bytes
Fixed Size 1364228 bytes
Variable Size 754978556 bytes
Database Buffers 419430400 bytes
Redo Buffers 12738560 bytes
Database mounted.
Database opened.
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 26 CLOSING
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 27 IDLE
7 rows selected.
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 26 CLOSING
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 27 IDLE
MRP0 N/A 27 APPLYING_LOG
8 rows selected. SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
ARCH ARCH 19 CLOSING
ARCH ARCH 20 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 21 CLOSING
MRP0 N/A 22 WAIT_FOR_LOG
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 22 IDLE
9 rows selected.