dataguard11g_for_enc

2024-04-05 16:28:12

su -
vi /etc/hosts 2个主机分别增加若为ASM的dataguard 用户asmsnmp的密码为oracle
192.168.92.100 source
192.168.92.101 target

su - oracle
stty erase ^H
. oraenv ->orcl
--asm文件系统则可以使用FRA闪回区
--mkdir -p /u01/app/oracle/fra

sqlplus / as sysdba
--开启强制日志模式
alter database force logging;
SELECT force_logging FROM v$database;

--配置standby redo log 要求数为（redo log 数量+1）*thread，此处为（3+1）*1 =4 组standby redo
/*
ALTER DATABASE ADD STANDBY LOGFILE '+OCP_DATA' size 52428800;
ALTER DATABASE ADD STANDBY LOGFILE '+OCP_DATA' size 52428800;
ALTER DATABASE ADD STANDBY LOGFILE '+OCP_DATA' size 52428800;
ALTER DATABASE ADD STANDBY LOGFILE '+OCP_DATA' size 52428800;
*/
--若为文件系统
--standby redo log的文件大小与primary 数据库online redo log 文件大小相同
--配置standby logfile实时应用日志以及主库切换为备库时能够实时应用日志
--Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数
--每一日志组为了安全起见，可以包含多个成员文件
ALTER DATABASE ADD STANDBY LOGFILE group 4 '/u01/app/oracle/oradata/orcl/standby04.log' size 100M;
ALTER DATABASE ADD STANDBY LOGFILE group 5 '/u01/app/oracle/oradata/orcl/standby05.log' size 100M;
ALTER DATABASE ADD STANDBY LOGFILE group 6 '/u01/app/oracle/oradata/orcl/standby06.log' size 100M;
ALTER DATABASE ADD STANDBY LOGFILE group 7 '/u01/app/oracle/oradata/orcl/standby07.log' size 100M;

--配置归档地址及log_archive_config 参数 dest 1 为本地归档 dest 2 归档至备库
alter system set log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl';
alter system set log_archive_dest_2 = 'service=stby async valid_for=(online_logfiles,primary_role) db_unique_name=stby';
alter system set log_archive_config = 'dg_config=(orcl,stby)';
--asm文件系统则默认使用FRA闪回区目录，不需要设置闪回区目录，只需要设置闪回区目录大小
--alter system set db_recovery_file_dest='+OCP_FRA' scope =spfile;
--alter system set db_recovery_file_dest='/u01/app/oracle/fra' scope =spfile;
alter system set db_recovery_file_dest_size=10g;
--普通文件系统若设置主备库为不同的存储目录
alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stby' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stby' scope=spfile;

/*
--主备库更改为同步模式在最大性能模式下可同步数据 tested
--主库
alter system set log_archive_dest_2='service=stby sync valid_for=(online_logfiles,primary_role) db_unique_name=stby';
--备库
alter system set log_archive_dest_2='service=orcl sync valid_for=(online_logfiles,primary_role) db_unique_name=orcl';
*/

--备库自动创建数据文件
alter system set standby_file_management=auto;
shutdown immediate;
startup

--配置打开归档
archive log list;
shutdown immediate
startup mount;
alter database archivelog;
alter database open;
--创建文本参数文件
create pfile from spfile;

--对全库进行备份并且打开控制文件自动备份备份数据库用于恢复
--rman target /
--CONFIGURE CONTROLFILE AUTOBACKUP on;
--backup database plus archivelog;

--配置ORCL 和STBY 端tnsnames.ora 要求每个tnsnames.ora 文件中都需要包含至orcl 和stby 的连接串
--生产环境中注意更改~/.bash_profile中的ORACLE_SID=stby; export ORACLE_SID,否则不能以操作系统身份登录sqlplus，备库配置系统环境变量并安装好软件包
cd /u01/app/oracle/product/11.2/db_1/network/admin/
vi tnsnames.ora 改为

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = source)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = target)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)

--配置ORCL 和STBY 端静态注册要求orcl 和stby 分别在每个listener 端注册静态注册注意如果使用了ASM 需要用grid 用户编辑对应的listener.ora 文件可以使用netmgr进行编辑
vi listener.ora 增加

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = source)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2/db_1)
(SID_NAME = orcl)
)
)

--STBY 端listener.ora 范例如下
vi listner.ora

# listener.ora Network Configuration File: /u01/11.2.0/grid/product/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = target)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stby)
(ORACLE_HOME = /u01/app/oracle/product/11.2/db_1)
(SID_NAME = stby)
)
)

lsnrctl stop
lsnrctl start

--状态如下重启监听
lsnrctl status
scp tnsnames.ora listener.ora 192.168.92.101:/u01/app/oracle/product/11.2/db_1/network/admin
--passwd oracle
cd /u01/app/oracle/product/11.2/db_1/dbs
scp orapworcl 192.168.92.101:/u01/app/oracle/product/11.2/db_1/dbs/orapwstby
scp initorcl.ora 192.168.92.101:/u01/app/oracle/product/11.2/db_1/dbs/initstby.ora

--在stby 端编辑intistby.ora
:1,$ s/orcl/stby/g
1.将orcl替换为stby，但是db_name db_file_name_conver log_file_name_convert log_archive_config 保持不变
3.将log_archive_dest_2='service=stby 改为service=orcl 并且db_unique_name=stby 改为orcl
4.添加 *.db_unique_name='stby'

--检查参数
/*
show parameter standby_file_management;
show parameter db_filee_name_convert;
show parameter log_filee_name_convert;
*/

--使用RMAN进行备库stby 创建在orcl端
--若主备库目录不相同，则创建相应的目录
su - oracle
mkdir -p /u01/app/oracle/oradata/stby
mkdir -p /u01/app/oracle/admin/stby/adump
mkdir -p /u01/app/oracle/fast_recovery_area/stby

#mkdir -p /u01/app/oracle/oradata/stby/datafile
#mkdir -p /u01/app/oracle/oradata/stby/controlfile
#mkdir -p /u01/app/oracle/oradata/stby/disk1
#mkdir -p /u01/app/oracle/oradata/stby/disk2
#mkdir -p /u01/app/oracle/oradata/stby/disk3

--若为ASM文件系统,只需创建adump和+OCP_DATA/stby
su - grid
asmcmd
cd +OCP_DATA
mkdir stby
su - oracle

--6.在备库将pfile 创建为spfile 后启动至nomount状态
--/u01/app/oracle/product/11.2/db_1
lsnrctl start
sqlplus sys/oracle@stby as sysdba
create spfile from pfile;
startup nomount;
exit

--duplicate复制数据库到远端非catalog，在rman 连接时，加上nocatalog关键字
rman target sys/oracle@orcl auxiliary sys/oracle@stby nocatalog
duplicate target database for standby from active database;

--等待备库搭建完成日志输出为
Starting Duplicate Db at 20-OCT-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=785 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2/db_1/dbs/orapworcl' auxiliary format
'/u01/app/oracle/product/11.2/db_1/dbs/orapwstby' ;
}
executing Memory Script

Starting backup at 20-OCT-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=400 device type=DISK
Finished backup at 20-OCT-20

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/stby/control01.ctl';
restore clone primary controlfile to '/u01/app/oracle/fast_recovery_area/stby/control02.ctl' from
'/u01/app/oracle/oradata/stby/control01.ctl';
}
executing Memory Script

Starting backup at 20-OCT-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2/db_1/dbs/snapcf_orcl.f tag=TAG20201020T150348 RECID=3 STAMP=1054307028
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-OCT-20

Starting restore at 20-OCT-20
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 20-OCT-20

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/stby/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/stby/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/stby/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/stby/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/stby/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/stby/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/stby/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/stby/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/stby/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/stby/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/stby/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/stby/temp01.dbf in control file

executing command: SET NEWNAME

Starting backup at 20-OCT-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=/u01/app/oracle/oradata/stby/system01.dbf tag=TAG20201020T150354
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/u01/app/oracle/oradata/stby/sysaux01.dbf tag=TAG20201020T150354
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output file name=/u01/app/oracle/oradata/stby/example01.dbf tag=TAG20201020T150354
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/u01/app/oracle/oradata/stby/undotbs01.dbf tag=TAG20201020T150354
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output file name=/u01/app/oracle/oradata/stby/users01.dbf tag=TAG20201020T150354
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-OCT-20

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=1054307049 file name=/u01/app/oracle/oradata/stby/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=1054307049 file name=/u01/app/oracle/oradata/stby/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1054307049 file name=/u01/app/oracle/oradata/stby/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1054307049 file name=/u01/app/oracle/oradata/stby/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1054307049 file name=/u01/app/oracle/oradata/stby/example01.dbf
Finished Duplicate Db at 20-OCT-20

------------
--duplicate后备库查询是否恢复了归档日志
alter database open;
ll /u01/app/oracle/fast_recovery_area/STBY/archivelog/

create user zyj identified by zyj;
grant dba to zyj;
create table zyj.tab1(id number,name varchar2(200));
insert into zyj.tab1 select 1,'n1' from dual;
insert into zyj.tab1 select 2,'n2' from dual;
insert into zyj.tab1 select 3,'n3' from dual;
insert into zyj.tab1 select 4,'n4' from dual;
delete from zyj.tab1 where id>=3;
commit;
select * from zyj.tab1;
alter system switch logfile;
archive log list
select status,database_status from v$instance;
select name,db_unique_name,open_mode,database_role from v$database;

--若备库未执行alter database open;
--若报错：ORA-16057: server not in Data Guard configuration
--若报错：ERROR ORA-03135: connection lost contact
Select dest_id,dest_name,status,error from v$archive_dest where dest_id=2;
解决：重新生效log_archive_dest_state_2
ALTER SYSTEM SET log_archive_dest_state_2='DEFER'SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE'SCOPE=BOTH;

------------
--主库自定义wallet目录是listener无法打开，应该无影响
mkdir /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/wallet
vi sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=(DIRECTORY=/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/wallet))
)

mkdir /u01/app/oracle/admin/stby/wallet

alter system set encryption key identified by "oracle";
alter system set wallet open identified by "oracle";
select * from v$encryption_wallet;

--当主库开启存储加密时
--备库创建wallet目录
mkdir /u01/app/oracle/admin/stby/wallet
--备库拷贝 ewallet.p12
scp ewallet.p12 oracle@target:/u01/app/oracle/admin/stby/wallet/
--备库不需要set encryption key
--mount状态可以walle open
alter system set wallet open identified by "oracle";

--备库设置，不需要set encryption key：set encryption key报错：ORA-28388: database is not open in read/write mode
alter system set wallet open identified by "oracle";

--备库需要先wallet open再database open
SQL> alter database open;
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/stby/system01.dbf'
解决：
recover managed standby database cancel;
alter system set wallet open identified by "oracle";
select * from v$encryption_wallet;
select status,database_status from v$instance;
select name,db_unique_name,open_mode,database_role from v$database;

--------------------------------------------------------------------
--查询oracle进程
ps -aux | grep oracle
--查询主、备库ARCH RFS LNS进程状态
select process,status,client_process,sequence# from v$managed_standby;

--------------------------------------------------------------------
--备库配置stby 端打开日志apply 进程mrp 主库需要switch logfile或自动切换时备库才能查询到同步的数据,可以重新传输并应用日志
--alter database recover managed standby database disconnect;
--duplicate后打开备库
alter database open;
alter database recover managed standby database using current logfile disconnect;
alter system switch logfile;
--取消日志应用模式，重新打开日志apply 进程mrp
--11.2.0.1.0当主库打开wallet事，备库无法打开wallet且无法执行ORA-16136: Managed Standby Recovery not active
recover managed standby database cancel;
recover automatic standby database;
alter database recover managed standby database using current logfile disconnect;
--通过查询 LOG_ARCHIVE_DEST_2 是否支持同步
select dest_name,status,error from v$archive_dest;

--配置参数fal_server ,fal_client 要求为fal_server 指向远端，fal_client 指向自己本身
alter system set fal_server='orcl';
alter system set fal_client='stby';

--验证DG STBY 已经配置完成在主库可以通过检查v$archived_log 和备库的v$managed_standby 2个视图来进行验证
--查看实例和数据库状态
--测试手动switchover过程需要断开所有应用连接 orcl需要查询状态
select status,database_status from v$instance;
select name,db_unique_name,open_mode,database_role from v$database;
SELECT SWITCHOVER_STATUS FROM V$DATABASE;

--切换至备库，如果状态是session active 的话需要查看session 是否是系统的连接，不然需要kill掉应用连接
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

--在备库查询并且切换成主库。随后打开数据库。
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
alter database open;

--切换后开启备库日志应用
alter database recover managed standby database using current logfile disconnect;

--查询主备库归档目录状态
select dest_name,status,error from v$archive_dest;

码农公寓