oracle 19c RAC部署ADG手顺

2021-12-14 04:06:42

hostnamectl set-hostname 19c-adg

1、DG基础环境

ORACLE 主库RAC，scan-IP：111.111.111.155 SID:leodb db_name='leodb' db_unique_name=leodb 主机名：rac19c-01、rac19c-02

ORACLE 备库 IP：111.111.111.150 SID:leodbadg db_name='leodb' db_unique_name=leodbadg 主机名：19c-adg

主库归档目录物理路径：

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 7

Next log sequence to archive 8

Current log sequence 8

+DATA/LEODB/FLASHBACK

主库DATAFILE物理路径

+DATA/LEODB/DATAFILE

主库REDO物理路径

+DATA/LEODB/ONLINELOG

主库字符集

SQL> select * from nls_database_parameters;

PARAMETER VALUE

------------------------------ --------------------

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_CHARACTERSET AL32UTF8

2、修改主库配置文件initLEODB.ora

这里现在数据库里修改相关的参数，与DG的参数就只与几个参数相关，大概就是日志，文件的位置的转换，GAP的处理，其实GAP已经会自动的处理，不过这里我们还是介绍配置FAL_SERVER,FAL_CLIENT参数。在修改完之后重新创建了pfile文件

先创建spfile，修改完后重新生成pfile

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(leodb,leodbadg)';

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=leodb';

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=leodbadg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=leodbadg';

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

SQL> alter system set FAL_SERVER=leodbadg;

SQL> alter system set FAL_CLIENT=leodb;

SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/leodbadg/','+DATA/LEODB/DATAFILE/' scope=spfile;

SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/leodbadg/','+DATA/LEODB/ONLINELOG/' scope=spfile;

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

SQL> create pfile='/home/oracle/initLEODB.ora' from spfile;

DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT参数必须重启数据库生效

下面是备份的主库pfile文件

leodb1.__data_transfer_cache_size=0

leodb2.__data_transfer_cache_size=0

leodb1.__db_cache_size=398458880

leodb2.__db_cache_size=394264576

leodb1.__inmemory_ext_roarea=0

leodb2.__inmemory_ext_roarea=0

leodb1.__inmemory_ext_rwarea=0

leodb2.__inmemory_ext_rwarea=0

leodb1.__java_pool_size=0

leodb2.__java_pool_size=0

leodb1.__large_pool_size=4194304

leodb2.__large_pool_size=4194304

leodb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

leodb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

leodb1.__pga_aggregate_target=264241152

leodb2.__pga_aggregate_target=264241152

leodb1.__sga_target=792723456

leodb2.__sga_target=792723456

leodb1.__shared_io_pool_size=29360128

leodb2.__shared_io_pool_size=29360128

leodb1.__shared_pool_size=343932928

leodb2.__shared_pool_size=348127232

leodb1.__streams_pool_size=0

leodb2.__streams_pool_size=0

leodb1.__unified_pga_pool_size=0

leodb2.__unified_pga_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/leodb/adump'

*.audit_trail='db'

*.cluster_database=TRUE

*.compatible='19.0.0'

*.control_files='+DATA/LEODB/CONTROLFILE/current.261.1056020927'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_file_name_convert='/u01/app/oracle/oradata/leodbadg/','+DATA/LEODB/DATAFILE/'

*.db_name='leodb'

*.db_recovery_file_dest_size=10737418240

*.db_recovery_file_dest='+DATA'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=leodbXDB)'

*.fal_client='LEODB'

*.fal_server='LEODBADG'

family:dw_helper.instance_mode='read-only'

leodb1.instance_number=1

leodb2.instance_number=2

*.local_listener='-oraagent-dummy-'

*.log_archive_config='DG_CONFIG=(leodb,leodbadg)'

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=leodb'

*.log_archive_dest_2='SERVICE=leodbadg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=leodbadg'

*.log_archive_dest_state_1='ENABLE'

*.log_file_name_convert='/u01/app/oracle/oradata/leodbadg/','+DATA/LEODB/ONLINELOG/'

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.pga_aggregate_target=252m

*.processes=300

*.remote_login_passwordfile='exclusive'

*.sga_target=756m

*.standby_file_management='AUTO'

leodb2.thread=2

leodb1.thread=1

leodb1.undo_tablespace='UNDOTBS1'

leodb2.undo_tablespace='UNDOTBS2'

3、备库服务器配置一般和主库存在差异，建议先在备库根据实际环境创建一个数据库（开启归档和闪回，db_name、字符集要与主库一致），然后保留pfile删库，修改备库的配置文件：initLEODBADG.ora

create pfile='/home/oracle/initLEODBADG.ora' from spfile;

下面是新增了dataguard部分的参数后的pfile文件

leodbadg.__data_transfer_cache_size=0

leodbadg.__db_cache_size=2013265920

leodbadg.__inmemory_ext_roarea=0

leodbadg.__inmemory_ext_rwarea=0

leodbadg.__java_pool_size=0

leodbadg.__large_pool_size=16777216

leodbadg.__oracle_base='/u01/app/oracle/'#ORACLE_BASE set from environment

leodbadg.__pga_aggregate_target=922746880

leodbadg.__sga_target=2734686208

leodbadg.__shared_io_pool_size=134217728

leodbadg.__shared_pool_size=553648128

leodbadg.__streams_pool_size=0

leodbadg.__unified_pga_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/leodbadg/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='/u01/app/oracle/oradata/leodbadg/control01.ctl','/u01/app/oracle/fast_recovery_area/leodbadg/control02.ctl'

*.db_block_size=8192

*.db_name='leodb'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=8256m

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=leodbadgXDB)'

*.log_archive_format='%t_%s_%r.dbf'

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.pga_aggregate_target=868m

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=2604m

*.undo_tablespace='UNDOTBS1'

#For data guard

*.db_unique_name='leodbadg'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(leodb,leodbadg)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area/leodbadg/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=leodbadg'

*.LOG_ARCHIVE_DEST_2='SERVICE=leodb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=leodb'

*.FAL_SERVER=leodb

*.FAL_CLIENT=leodbadg

*.DB_FILE_NAME_CONVERT='+DATA/LEODB/DATAFILE/','/u01/app/oracle/oradata/leodbadg/'

*.LOG_FILE_NAME_CONVERT='+DATA/LEODB/ONLINELOG/','/u01/app/oracle/oradata/leodbadg/'

*.STANDBY_FILE_MANAGEMENT=AUTO

创建相关目录

mkdir -p /u01/app/oracle/admin/leodbadg/adump

mkdir -p /u01/app/oracle/oradata/leodbadg/

mkdir -p /u01/app/oracle/fast_recovery_area/leodbadg/archivelog

注意：

1、Linux端配置的pfile文件中，所有windows的路径都要用大写，因为在duplication过程中，windows端都是按照大写路径来传输的！

如果用小写或者大小写混合，则无法识别路径，会有问题！

2、在duplication过程中，虽然是在主库操作，但是datafile和logfile的路径转换却认的是备库的pfile文件中的转换路径！

db_file_name_convert参数

因为rac是用的asm的，standby用的文件系统，目录不一样，需要转换。

所以了解了一下log_file_name_convert、db_file_name_convert参数。

1> 参数的作用：

db_file_name_convert 主数据库和备用数据库的数据文件转换目录对映（如果两数据库的目录结构不一样），

如果有多个对映，逐一指明对映关系。

2> 该参数应该配置在主还是备？

备库，这个参数不能动态配置，需要重启实例，如果在主库配置需要重启主库，对于不能停的数据库是不允许的。

3> 该参数的写法：

db_file_name_convert='+RACDATA/racdb/datafile/','/oradata/standby/'

6、修改主库的listener.ora文件（grid用户执行） ------如果要主备切换演练，则要做。如果不做切换后的新备库无法从新主库接收数据。

1节点添加如下内容：

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = leodb)

(ORACLE_HOME = /u01/app/19.3.0/grid)

(SID_NAME = leodb1)

)

2节点添加如下内容：

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = leodb)

(ORACLE_HOME = /u01/app/19.3.0/grid)

(SID_NAME = leodb2)

)

重启监听

srvctl stop listener -n rac19c-01

srvctl stop listener -n rac19c-02

srvctl start listener -n rac19c-01

srvctl start listener -n rac19c-02

7、修改主库的tnsnames.ora文件

增加DG备库连接串（RAC环境所有节点都要配置，oracle用户进入ORACLE_HOME/network/admin）

LEODBADG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 19c-adg)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = leodbadg)

)

8、修改备库的listener.ora文件

# listener.ora Network Configuration File: /u01/app/oracle//product/19.3.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 19c-adg)(PORT = 1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = leodbadg)

(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)

(SID_NAME = leodbadg)

)

--SID_LIST_LISTENER部分为新增内容，GLOBAL_DBNAME不能少，少的了话后面主库rman连接过来会报错，本次就发生了这个问题，补上这一行就好了。

--重启监听

9、修改备库的tnsnames.ora文件

LEODBADG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 19c-adg)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = leodbadg)

)

LISTENER_IPDBADG =

(ADDRESS = (PROTOCOL = TCP)(HOST = 19c-adg)(PORT = 1521))

LEODB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac19c-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = leodb)

)

LEODB1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac19c-01)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = leodb)

(INSTANCE_NAME = leodb1)

)

LEODB2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac19c-02)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = leodb)

(INSTANCE_NAME = leodb2)

)

orapwd file=orapwLEODBADG password=oracle（为防止密码问题导致无法访问，最好直接把主库的orapw文件拷贝过来然后更名orapwSID就可以了）

12c rac密码文件默认存放在asm上，不再是$ORACLE_HOME/dbs目录下

ASMCMD> pwcopy +DATA/LEODB/PASSWORD/pwdleodb.256.1056020773 /home/grid/orapwLEODBADG

copying +DATA/LEODB/PASSWORD/pwdleodb.256.1056020773 -> /home/grid/orapwLEODBADG

然后拷贝到备机$ORACLE_HOME/dbs目录下

主库和备库都启动监听：lsnrctl start

10、设置服务器为归档模式

alter database archivelog

设置主数据库为日志强制写状态

alter database force logging;

查看状态日志强制写状态为YES

select log_mode,force_logging from v$database;

LOG_MODE FORCE_LOGGING

------------ ---------------------------------------

ARCHIVELOG YES

11、查看主库数据库的日志组个数与大小，因为我们创建standby日志组的个数是每个节点日志组个数+1再与thread的积，size不能小于原日志文件的大小。

SQL> select group#,THREAD#,bytes/1024/1024 from v$log;

GROUP# THREAD# BYTES/1024/1024

---------- ---------- ---------------

1 1 200

2 1 200

3 2 200

4 2 200

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

+DATA/LEODB/ONLINELOG/group_2.263.1056020931

+DATA/LEODB/ONLINELOG/group_1.262.1056020931

+DATA/LEODB/ONLINELOG/group_3.266.1056021723

+DATA/LEODB/ONLINELOG/group_4.267.1056021725

12、创建standby日志组，位置与原日志组相同的路径。创建完成后查询是否成功创建6个standby 日志组

SQL> alter database add standby logfile '+DATA/LEODB/ONLINELOG/standby01.log' size 200m;

alter database add standby logfile '+DATA/LEODB/ONLINELOG/standby01.log' size 200m;

alter database add standby logfile '+DATA/LEODB/ONLINELOG/standby02.log' size 200m;

alter database add standby logfile '+DATA/LEODB/ONLINELOG/standby03.log' size 200m;

alter database add standby logfile '+DATA/LEODB/ONLINELOG/standby04.log' size 200m;

alter database add standby logfile '+DATA/LEODB/ONLINELOG/standby05.log' size 200m;

alter database add standby logfile '+DATA/LEODB/ONLINELOG/standby06.log' size 200m;

col member for a60

select group#,status,type,member from v$logfile;

13、使用之前创建的initLEODBADG.ora文件启动备库到nomount状态

SELECT ROUND(SUM(BYTES)/1024/1024/1024,2)||'GB' FROM DBA_DATA_FILES;

源环境datafile大小：1.87GB

duplicate开始

在主库上通过rman进行复制备库(注意在这一步之前必须退出备库的所有连接,否则会报错)

rman target sys/oracle auxiliary sys/oracle@LEODBADG

configure device type disk parallelism 10;

rman>duplicate target database for standby nofilenamecheck from active database;

---也可使用下面语句（主库开3个通道，备库开3个通道）：

run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate AUXILIARY channel c4 type disk;

allocate AUXILIARY channel c5 type disk;

allocate AUXILIARY channel c6 type disk;

duplicate target database for standby nofilenamecheck from active database;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

release channel c5;

release channel c6;

}

备库查询

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

把备库启动到open only下面。并recover

SQL> alter database open read only;

为了方便实时查询，恢复自动恢复状态。

在备库上启动数据库到恢复管理模式，并开始准备从主库接受归档日志的传输。

SQL> alter database recover managed standby database using current logfile disconnect from session;

13．主库执行：

select process,status from v$managed_standby;

查看进程，看有没有LNS进程

SQL> select process,status from v$managed_standby;

PROCESS STATUS

------------------ ------------------------

DGRD ALLOCATED

ARCH CLOSING

DGRD ALLOCATED

ARCH CLOSING

LNS OPENING

DGRD ALLOCATED

LNS WRITING

9 rows selected.

验证standby能否接收日志传输

在备库端查看其角色是否已经是physical standby

select DATABASE_ROLE,open_mode from v$database;

DATABASE_ROLE OPEN_MODE

---------------- --------------------

PHYSICAL STANDBY READ ONLY WITH APPLY

在主库查看其角色

select DATABASE_ROLE,open_mode from gv$database;

DATABASE_ROLE OPEN_MODE

---------------- --------------------

PRIMARY READ WRITE

在备库查看data guard为哪种日志接受方式

select process,client_process,sequence#,status from v$managed_standby;

检查当前备库的模式

select protection_mode from v$database;

PROTECTION_MODE

--------------------

MAXIMUM PERFORMANCE

主备库检查当前最大sequence

select max(sequence#) from v$archived_log;

在备库查看日志的队列情况

select thread#,sequence#,creator,applied,first_time,next_time from v$archived_log order by sequence#;

THREAD# SEQUENCE# CREATOR APPLIED FIRST_TIM NEXT_TIME

---------- ---------- ------- --------- --------- ---------

1 28 ARCH YES 21-MAY-21 21-MAY-21

1 29 ARCH YES 21-MAY-21 21-MAY-21

2 44 ARCH YES 21-MAY-21 21-MAY-21

其中applied字段应该为YES，如果为NO就使用偶redo没有些过来，需要关注了。

在主库进行强制归档

ALTER SYSTEM ARCHIVE LOG CURRENT;

alter system switch logfile;

检查下两边的日志同步情况

select thread#,sequence#,creator,applied,first_time,next_time from v$archived_log where applied='YES' order by sequence#;

这里注意，在备库创建之前的redo归档是不会写过来的。

查看DG是否正常工作

select dest_id,error,status from v$archive_dest where dest_id=2;

SQL> select dest_id,error,status from v$archive_dest where dest_id=2;

DEST_ID ERROR STATUS

---------- ----------------------------------------------------------------- ------------------

2 VALID

error值为空则正常

主库操作

--创建用户

create user dgtest identified by oracle;

grant dba to dgtest;

--创建表

create table dgtest (

id number(9) not null primary key,

classname varchar2(40) not null

);

insert into dgtest values(28,'class one');

commit;

刚才在sys用户下也创建了这个表，可以一起查看

为区分，分别插入两条数据:

sys用户：

insert into dgtest values(27,'sys one');

dgtest用户：

insert into dgtest values(29,'detest one');

在备库执行查询：

select * from sys.dgtest;

select * from dgtest.dgtest;

drop tablespace dfeip including contents and datafiles;

drop user dgtest cascade

码农公寓

相关文章