oracle同步数据adg_搭建adg - oracle数据库 - 相相哥运维笔记

摘要：

临时文件位置set linesize 200 pagesize 200col file_name for a50col tablespace_name for a20select...

临时文件位置

set linesize 200 pagesize 200

col file_name for a50

col tablespace_name for a20

select file_name,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024 from  dba_temp_files order by 2;

查询数据文件位置

set linesize 200 pagesize 200

col file_name for a50

col tablespace_name for a20

select file_name,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024 from  dba_Data_files order by 2;

查询log文件位置

set linesize 150;

set pagesize 50;

column MB format a10;

column STATUS format a12;

column MEMBER format a60;

select l.GROUP#,l.THREAD#,l.members,l.BYTES/1024/1024||'MB' MB,l.STATUS, lf.TYPE,lf.MEMBER from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#;

/app/oracle/oradata/JiekeXutest

/app/oracle/oradata/JiekeXutest/

alter user sys identified by 123456;

1 、开logging

alter database force logging;

alter system set cluster_database=false scope=spfile;

alter system set db_recovery_file_dest_size=2g scope=both;

alter system set db_recovery_file_dest='+FRA' scope=both;

shutdown immediate;

startup mount

alter database archivelog;

alter system set cluster_database=true scope=spfile;

shutdown immediate

select force_logging from v$database; 查看强制日志

增加日志文件

select   a."GROUP#",a."THREAD#",a."BYTES",a."MEMBERS"

from     v$log  a,

v$logfile  b

where    a."GROUP#" = b."GROUP#"

order by group#;

standby redo log的大小与redo log大小相同。组数为比在线日志多一组。

alter system set standby_file_management=manual scope=both sid='*';

alter database add standby logfile thread 1 group 7 '+DATADATA' SIZE 50M;

alter database add standby logfile thread 1 group 8 '+DATADATA' SIZE 50M;

alter database add standby logfile thread 1 group 9 '+DATADATA' SIZE 50M;

alter database add standby logfile thread 2 group 11 '+DATADATA' SIZE 50M;

alter database add standby logfile thread 2 group 12 '+DATADATA' SIZE 50M;

alter database add standby logfile thread 2 group 13 '+DATADATA' SIZE 50M;

ALTER DATABASE ADD LOGFILE GROUP 5 '+DATADATA' SIZE 50M;

ALTER DATABASE ADD LOGFILE thread 2 GROUP 6 '+DATADATA' SIZE 50M;

select group#,thread#,sequence#,members,archived,status,first_time,next_time from v$log;

修改主库的参数文件

alter system set db_unique_name='xmb'  scope=spfile sid='*';

alter system set log_archive_config='dg_config=(xmb,xmbdg)'   scope=both sid='*';

alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=xmb'  scope=both sid='*';

alter system set log_archive_dest_2='service=xmbdg LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=xmbdg'   scope=both sid='*';

alter system set fal_client='xmb'   scope=both sid='*';

alter system set fal_server='xmbdg'   scope=both sid='*';

alter system set standby_file_management=AUTOscope=both sid='*';

alter system set db_file_name_convert='/u01/app/oracle/product/11.2.0/db_1/oradata/xmbdg/','+DATADATA/xmb/DATAFILE/','/u01/app/oracle/product/11.2.0/db_1/oradata/xmbdg/','+DATADATA/xmb/tempfile/' scope=spfile sid='*';

alter system set log_file_name_convert='/u01/app/oracle/product/11.2.0/db_1/oradata/xmbdg/online/','+DATADATA/xmb/onlinelog/','/u01/app/oracle/product/11.2.0/db_1/oradata/xmbdg/','+FRA' scope=spfile sid='*';

'/u01/app/oracle/product/11.2.0/db_1/oradata/xmbdg/'

'+DATADATA/xmb/DATAFILE/'

create pfile='/home/oracle/standby9.pfile' from spfile;

动态监听

xmb =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.80)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = xmb)

)

)

xmb1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.111)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = xmb)

(SID = xmb1)

)

)

xmb2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.112)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = xmb)

(SID = xmb2)

)

)

xmbdg =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.200)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = xmbdg)

)

)

备库方案

sid_list_listener =

(sid_list =

(sid_desc =

(global_dbname= xmbdg)

(ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = xmbdg)

)

)

更改spfile

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

*.compatible='11.2.0.4.0'

*.db_block_size=8192

*.db_domain=''

*.db_name='xmb'

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

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

*.open_cursors=300

*.pga_aggregate_target=196083712

*.processes=150

*.remote_login_passwordfile='exclusive'

*.sga_target=589299712

*.undo_tablespace='UNDOTBS1'

之前整理

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.db_block_size=8192

*.db_domain=''

*.db_name='xmb'

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

*.open_cursors=300

*.pga_aggregate_target=196083

*.processes=150

*.sga_target=589299712

*.standby_file_management='AUTO'

*.db_unique_name='xmbdg'

*.remote_login_passwordfile='exclusive'

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

*.control_files='./oradata/xmbdg/control01.ctl','./oradata/xmbdg/control02.ctl'

*.db_unique_name='xmbdg'

*.log_archive_config='dg_config=(xmbdg,xmb)'

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=xmbdg'

*.log_archive_dest_2='service=xmb valid_for=(online_logfiles,primary_role) db_unique_name=xmb'

*.fal_server='xmb'

*.fal_client='xmbdg'

*.db_file_name_convert='+DATADATA/xmb/datafile/','/oradata/xmb/datafile/'

*.log_file_name_convert=,'+DATADATA/xmb/onlinelog/' ,'/oradata/xmbdg/onlinelog/'

*.db_recovery_file_dest_size=2g

*.db_recovery_file_dest='/u01/app/oracle/admin/xmbdg/adump'

拷贝到oracle/dbs/ 并改名 initsid.ora

rman target sys/123456@xmb auxiliary sys/123456@xmbdg

duplicate target database to xmb(源) from active database nofilenamecheck;

duplicate target database to xmb from active database nofilenamecheck;

select file_name from dba_temp_files;

select * from datafile;

duplicate target database for standby nofilenamecheck;

duplicate target database to xmb from active database nofilenamecheck;  --在线迁移adg rman

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=123456 entries=30;

startup nomount pfile='/home/oracle/pfile_20190818';

duplicate target database for standby;

alter database recover managed standby database disconnect from session;

alter database recover managed standby database cancel;

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

alter system switch logfile;

alter system archive log current;

select name,open_mode,protection_mode,database_role,switchover_status from v$database; --备库adg数据库状态

select thread#,low_sequence#,high_sequence# from v$archive_gap;

select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';

select PROCESS,SEQUENCE#,STATUS from v$managed_standby; --查看adg运用进程

select sequence#, applied from v$archived_log where applied='YES' order by sequence#;查看归档应用

create pfile='/u01/soft/standby.pfile' from spfile;

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

mkdir -p /home/oracle/app/admin/xmbdg/adump

mkdir -p /home/oracle/xmbdg

mkdir -p /home/oracle/app/oradata/xmbdg

mkdir -p /home/oracle/app/flash_recovery_area/xmbdg

sqlplus "sys/123456@xmbdg as sysdba"

startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/standby3.pfile' nomount;

create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/standby3.pfile';

alter system set log_archive_dest_state_1=enable   scope=spfile sid='*';

alter system set log_archive_dest_state_2=enable   scope=spfile sid='*';

alter system set standby_file_management='auto'   scope=spfile sid='*';

alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl/data','+DATA/leo/datafile','/u01/app/oracle/oradata/orcl/tempfile','+DATA/leo/tempfile' scope=spfile sid='*';

alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl/redo','+DATA/leo/onlinelog' scope=spfile sid='*';

alter system set log_archive_format='%t_%s_%r.arch' scope=spfile sid='*';

create temporary tablespace temp01 tempfile '/u01/app/oracle/oradata/xmbdg/temp01.dbf' size 100m;

tnstestdb =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.33)(PORT = 1522))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.34)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = testdb)

)

)

tnstestdbdg =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.43)(PORT = 1522))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.44)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = testdbdg)

)

)

startup nomount pfile='u01/app/oracle/product/11.2.0/db_1/dbs/initxmbdg.ora';

orapwd FILE=u01/app/oracle/product/11.2.0/db_1/dbs/orapwxmb1 password=123456 entries=30;

-----------------------备库变主库(源主库切断)------------------------------

select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS from v$database;

DATABASE_ROLE        OPEN_MODE       PROTECTION_MODE      SWITCHOVER_STATUS

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

PHYSICAL STANDBY     MOUNTED         MAXIMUM PERFORMANCE  NOT ALLOWED

alter database recover managed standby database finish force;

alter database commit to switchover to primary;

shutdown immediate;

startup;

select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS from v$database;

DATABASE_ROLE        OPEN_MODE       PROTECTION_MODE      SWITCHOVER_STATUS

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

PRIMARY              READ WRITE      MAXIMUM PERFORMANCE  FAILED DESTINATION

----------------------备库和主库互切---------------------------------------------------

1将主库转换为备库

alter database commit to switchover to physical standby with session shutdown;

startup mount;

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

select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS from v$database;

DATABASE_ROLE         OPEN_MODE        PROTECTION_MODE      SWITCHOVER_STATUS

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

PHYSICAL STANDBY      MOUNTED          MAXIMUM PERFORMANCE  TO PRIMARY

2将备库转换为主库

column DATABASE_ROLE format a20

column OPEN_MODE format a15

column PROTECTION_MODE format a20

column SWITCHOVER_STATUS format a20

select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS from v$database;

DATABASE_ROLE        OPEN_MODE       PROTECTION_MODE      SWITCHOVER_STATUS

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

PHYSICAL STANDBY     MOUNTED         MAXIMUM PERFORMANCE  TO PRIMARY

alter database commit to switchover to primary;

alter database open;

select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS from v$database;

DATABASE_ROLE        OPEN_MODE      PROTECTION_MODE       SWITCHOVER_STATUS

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

PRIMARY              READ WRITE     MAXIMUM PERFOR

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