分类 ORACLE 下的文章

1、process不是越大越好。
一是process分配越大,占用的sga内存也会越多。平均150k一个会话数。
二是process越大,pga中保持空闲会话的内存也越多,linux平均一个会话5M,aix8M。

2、实验过程:
process=150
sys@ORCL 16:01:46> select * from v$sgainfo;

NAME BYTES RES
-------------------------------------------------------------------------------- ---------- ---
Fixed SGA Size 2253624 No
Redo Buffers 7393280 No
Buffer Cache Size 1174405120 Yes
Shared Pool Size 352321536 Yes
Large Pool Size 33554432 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 1586708480 No
Startup overhead in Shared Pool 91557192 No 87MB
Free SGA Memory Available 0

process=1500
sys@ORCL 24-AUG-21> select * from v$sgainfo;

NAME BYTES RES
-------------------------------------------------------------------------------- ---------- ---
Fixed SGA Size 2253624 No
Redo Buffers 7393280 No
Buffer Cache Size 1191182336 Yes
Shared Pool Size 335544320 Yes
Large Pool Size 33554432 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 1586708480 No
Startup overhead in Shared Pool 303860664 No 289MB
Free SGA Memory Available 0

sys@ORCL 15:58:38> select sum(pga_used_mem)/1024 total_used, sum(pga_used_mem)/count(1)/1024 used_avg, sum(pga_alloc_mem)/1024 total_alloc, sum(pga_alloc_mem)/count(1)/1024 alloc_avg from v$process;

TOTAL_USED USED_AVG TOTAL_ALLOC ALLOC_AVG
---------- ---------- ----------- ----------
128314.369 4424.63342 150193.664 5179.09186
平均每个会话分配内存5179.09186 KB
平均每个会话使用内存4424.63342 KB

环境

新部署系统,基础包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;

PRIVILEGE

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;

ROLE

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.dbf

2)远程克隆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 NO

SYS@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 NO

3)当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          NONE

4)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;

COUNT(*)

 72359

6)模拟应用侧停应用,将源端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                     MOUNTED

10)应用连接新PDB,校验业务

SYS@ora19c>conn u1/oracle@192.168.8.102/pdb1_ref
Connected.

U1@192.168.8.102/pdb1_ref>select count(*) from t1;

COUNT(*)

 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时,源端未开启归档模式。

七、小结
热克隆的方式目前都已经比较成熟,并且可以灵活使用,适合多种应用场景。既可以应用于快速创建生产环境的完整副本或子集副本,也可以应用于较短停机时间的迁移。业务中断时间短,甚至无需业务中断,操作简单,不易出错,但某些场景下对环境要求较高。

概述

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语句来实现数据同步,那么在同步期间其必须保持打开状态。

DataGuard环境规划

内容主库备库
操作系统CENTOS 7.4CENTOS 7.4
IP192.168.117.129192.168.117.128
db_namedgdg
db_unique_namedg1dg2
数据库安装安装数据库软件+创建监听+安装数据库安装数据库软件+创建监听
  1. IP地址

[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
  1. 监听
[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]$ 
  1. 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/oracle
  1. tnsnames.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)
    )
  )
  1. 参数文件
[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.