标签(空格分隔): Oracle 系列
一:配置介绍
使用Oracle 19c 的安装包安装两台 主机:
系统介绍:
CentOS7.9x64
cat /etc/hosts
--------
172.130.216.176 [主库]
172.130.216.177 [备库]
--------
配置条件:
主库 安装 好 Oracle 19c软件 与 建好DBCA的pdb的 (skedupdb)---> 数据库SID [skedudb]
备库 安装好 数据库软件[只安装数据库软件] ----》 备库预设 数据库SID 【skedudg】
二: dataguard 配置步骤
2.1 配置监听:
准备备库监听配置
cd $ORACLE_HOME/network/admin/
vim listener.ora
----
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = flyfish02)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=skedudg)
(SID_NAME=skedudg)
(ORACLE_HOME=/oracle/app/oracle/product/19c/db_1)
)
)
----
lsnrctl start
lsnrctl status
三:配置Oracle 19c DG 的过程
1. 主库配置oracledg01:
主库配置
1. 打开强制日志模式
alter database force logging;
2. 设置 归档目录
打开数据库的归档:
archive log list;
show parameter recover
alter system set db_recovery_file_dest_size=1800g; [设置归档参数大小]
alter system set db_recovery_file_dest='/archive';
关闭数据库
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
alter system switch logfile;
查看是不是有归档日志 产生
cd /archive/SKEDUDB/archivelog/2022_12_02/
03.创建主库的redolog 日志
sqlplus / as sysdba
select * from v$log; [查看文件有几组]
select* from v$logfile;
默认有3组 配置至少要在加 4组
alter database add standby logfile group 4 '/oradata/SKEDUDB/stredo04.log' size 200m;
alter database add standby logfile group 5 '/oradata/SKEDUDB/stredo05.log' size 200m;
alter database add standby logfile group 6 '/oradata/SKEDUDB/stredo06.log' size 200m;
alter database add standby logfile group 7 '/oradata/SKEDUDB/stredo07.log' size 200m;
主备库监听tnsnames.ora:[主备机器全部配置]
vi /oracle/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
----
SKEDUDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledg01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = skedudb)
)
)
SKEDUDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledg02)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = skedudg)
)
)
-----
scp tnsnames.ora oracle@oracledg02:/oracle/app/oracle/product/19c/db_1/network/admin/
tnsping skedudb
tnsping skedudg
准备数据库密码文件
cd $ORACLE_HOME/dbs/
备份原有密码文件:
mv orapwskedudb orapwskedudb.bak
主库:
orapwd file=/oracle/app/oracle/product/19c/db_1/dbs/orapwskedudb password=sk123.com
登录测试:
sqlplus "sys/sk123.com@skedudb as sysdba"
同步密码文件到备库:
cd $ORACLE_HOME/dbs
scp orapwskedudb oracle@oracledg02:/oracle/app/oracle/product/19c/db_1/dbs/
oracledg02 主机从新命名:
mv orapwitpuxdb orapwskedudg
登录
sqlplus "sys/sk123.com@skedudg as sysdba"
主库生成pfile文件:
主库:
create pfile='/oradata/pfile.ora' from spfile;
主库参数(重启生效):
alter system set db_unique_name='skedudb' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(skedudb,skedudg)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=skedudb' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=skedudg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=skedudg' scope=both;
alter system set fal_client='skedudb' scope=both;
alter system set FAL_SERVER='skedudg' scope=both;
alter system set DB_FILE_NAME_CONVERT='SKEDUDG','SKEDUDB' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='SKEDUDG','SKEDUDB' scope=spfile;
alter system set standby_file_management=AUTO scope=both;
重启数据库生效
shutdown immediate;
start up ;
主库同步:
-------------------
cd /oradata/
scp pfile.ora oracle@oracledg02:/opt/oracle/
-----------------------
备库参数:
vim pfile.ora
---------------------------------------------------------------
*.audit_file_dest='/oracle/app/oracle/admin/skedudg/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/oradata/SKEDUDG/control01.ctl','/oradata/SKEDUDG/control02.ctl'
*.db_block_size=8192
*.db_name='skedudb'
*.db_recovery_file_dest_size=107374182400
*.db_recovery_file_dest='/archive'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=skedudgXDB)'
*.enable_pluggable_database=true
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=780m
*.processes=640
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2340m
*.standby_file_management='MANUAL'
*.undo_tablespace='UNDOTBS1'
-----
新增:
---------------------------------------------------------
db_unique_name='skedudg'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(skedudg,skedudb)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=skedudg'
LOG_ARCHIVE_DEST_2='SERVICE=skedudb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=skedudb'
fal_client='skedudg'
FAL_SERVER='skedudb'
DB_FILE_NAME_CONVERT='SKEDUDB','SKEDUDG'
LOG_FILE_NAME_CONVERT='SKEDUDB','SKEDUDG'
standby_file_management=AUTO
备库创建目录:
mkdir -p /oracle/app/oracle/admin/skedudg/adump
mkdir -p /oradata/SKEDUDG/
sqlplus "sys/sk123.com@skedudg as sysdba"
startup pfile='/oradata/pfile.ora' nomount;
create spfile from pfile='/oradata/pfile.ora';
shutdown immediate;
startup nomount;
创建dataguard数据库
rman target sys/sk123.com@skedudb auxiliary sys/sk123.com@skedudg
duplicate target database for standby from active database;
打开备库的pdb
sqlplus / as sysdba
shutdown immediate;
startup
alter pluggable database skedupdb open;
alter session set container=SKEDUPDB;
检查测试:
检查dataguard状态:
select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
启动dataguard数据同步:
sqlplus "/as sysdba"
alter database recover managed standby database disconnect from session;
关闭数据库同步
关闭dataguard数据同步:
alter database recover managed standby database cancel;
备库启动adg模式的备库:
alter database open;
alter database recover managed standby database disconnect from session;
主库测试:
alter system switch logfile;
/
主备库检查dataguard状态:
SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;
主库:
SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;
备库:
SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;
主库日志:
cd /oracle/app/oracle/diag/rdbms/skedudb/skedudb/trace [主库日志]
tail -300f alert_skedudb.log
备库日志:
cd /oracle/app/oracle/diag/rdbms/skedudg/skedudg/trace
dataguard数据同步测试
主库:
alter session set container=SKEDUPDB;
create tablespace skedu66 datafile '/oradata/SKEDUDB/skedu66.dbf' size 10m autoextend off;
create user skedu66 identified by skedu66 default tablespace skedu66;
grant dba to skedu66;
conn skedu66/skedu66@localhost:1521/SKEDUPDB
create table skedu66.skpux01(c1 varchar2(10),c2 number);
insert into skpux01 values('skpux01','1');
insert into skpux01 values('skpux02','2');
insert into skpux01 values('skpux03','3');
insert into skpux01 values('skpux04','4');
commit;
select * from skedu66.skpux01;
alter system switch logfile;
备库检查:
sqlplus / as sysdba
show pdbs
alter session set container=SKEDUPDB;
conn skedu66/skedu66@localhost:1521/SKEDUPDB;
select * from skedu66.skpux01;
标签:主库,oracle19c,skedudb,skedudg,database,alter,dataguard,oracle,pdb From: https://blog.51cto.com/flyfish225/5916418