oracle 11g dg 检查,ORACLE 11G DG

PRIMARY:

1.检查PRIMARY端归档日志

startup mount;

alter database archivelog;

alter database open;

select force_logging from v$database;

alter database force logging;

select * from v$logfile;

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

select group#,bytes/1024/1024 from v$standby_log;

创建standby redo log

alter database add standby logfile group 5 (‘/data/u01/oracle/oradata/fc/standbyredo02.log‘) size 200M;

2.配置监听listener.ora

lsnrctl stop

lsnrctl start

配置网络服务名tnsnames.ora

tnsping

3.创建密钥文件，复制到STANDYBY相应目录下

orapwd file=/data/u01/app/oracle/product/11.2.0/dbhome_1/database/orapwciwong password=ciwong entries=30

4.通过PRIMARY当前SPFILE创建pfile

create pfile=‘/tmp/fc.ora‘ from spfile;

修改参数

vim /tmp/fc.ora

db_name 同一个DG下DB_NAME相同

db_unique_name=fc

log_archive_config=‘DB_CONFIG‘=(ciwong,fc)

log_archive_dest_2=‘SERVICE=10.204.243.44 arch valid_for=(online_logfiles,primary_role) db_unique_name=ciwong‘

log_archive_dest_state_2=DEFER

#remote_login_passwordfile

standby端参数,在主库也可设置

fal_server=10.204.243.44

fal_client=10.204.243.45

db_file_name_convert=‘/data/u01/oracle/oradata/fc/‘,‘/data/u01/oracle/oradata/ciwong‘

log_file_name_convert=‘/data/u01/oracle/oradata/fc/‘,‘/data/u01/oracle/oradata/ciwong‘

standby_file_management=AUTO

5.修改参数好后：

主库：

shutdown immediate

create spfile from pfile=‘/tmp/fc.ora‘

startup

alter database create standby controlfile as ‘/tmp/ciwong.ctl‘

6.复制相关文件到standby服务器

1.pfile、standby控制文件

2.数据文件

alter tablespace books begin backup;

copy

alter tablespace books end backup;

7.从库：

修改PFILE文件

sqlplsu / as sysdba

create spfile from pfile;

1.修改从主库拷过来的pfile文件

.audit_file_dest=‘/data/u01/oracle/admin/ciwong/adump‘.background_dump_dest=‘/data/u01/oracle/diag/rdbms/fc/fc/trace‘

*.control_files=

db_name

2.连接到STANDBY生成spfile

create spfile from pfile=‘/tmp/fc.ora‘

startup mount

alter system set log_archive_dest_state_2=ENABLE;

查看主从库的binlog位置

select max(sequence#) from v$archived_log;

9.启动REDO应用：

alter database recover managed standby database disconnect from session;

暂停

alter database recover managed standby database cancel;

startup nomount;

rman target sys/[email protected] auxiliary sys/[email protected]

backup current controlfile for standby database;

duplicate target database for standby from active database;

--duplicate target database for standby nofilenamecheck dorecover;

select open_mode from v$database;

alter database recover managed standby database disconnect from session;

primary:

.db_unique_name=fc.log_archive_config=‘dg_config=(fc,ciwong)‘

.log_archive_dest_1=‘LOCATION=E:\oracle_DB_arch\arch valid_for=(online_logfiles,primary_role) db_unique_name=fc‘.log_archive_dest_2=‘SERVICE=ciwonglgwr async affirm valid_for=(online_logfiles,primary_role) db_unique_name=ciwong‘

.log_archive_dest_3=‘LOCATION=E:\oracle_DB_arch\arch_std valid_for=(standby_logfiles,standby_role) db_unique_name=fc‘.log_archive_dest_state_1=‘enable‘

.log_archive_dest_state_2=‘enable‘.log_archive_dest_state_3=‘enable‘

.fal_server=ciwong.fal_client=fc

.standby_file_management=auto.db_file_name_convert=(‘F:\app\Administrator\oradata\ciwong‘,‘E:\oracle\oradata\fc‘)

*.log_file_name_convert=(‘F:\app\Administrator\oradata\ciwong‘,‘E:\oracle\oradata\fc‘)

primary:

.db_unique_name=ciwong.log_archive_config=‘dg_config=(ciwong,fc)‘

.log_archive_dest_1=‘LOCATION=F:\oracleDB\archvalid_for=(online_logfiles,primary_role) db_unique_name=ciwong‘.log_archive_dest_2=‘SERVICE=fc lgwr async affirm valid_for=(online_logfiles,primary_role) db_unique_name=fc‘

.log_archive_dest_3=‘LOCATION=F:\oracleDB\arch_std valid_for=(standby_logfiles,standby_role) db_unique_name=ciwong‘.log_archive_dest_state_1=‘enable‘

.log_archive_dest_state_2=‘enable‘.log_archive_dest_state_3=‘enable‘

.fal_server=fc.fal_client=ciwong

.standby_file_management=auto.db_file_name_convert=(‘E:\oracle\oradata\fc‘,‘F:\app\Administrator\oradata\ciwong‘)

*.log_file_name_convert=(‘E:\oracle\oradata\fc‘,‘F:\app\Administrator\oradata\ciwong‘)

主库准备：

alter database add standby logfile group 5 ‘/data/u01/oracle/oradata/fc/redo05.log‘ size 50M;

alter database add standby logfile group 6 ‘/data/u01/oracle/oradata/fc/redo06.log‘ size 50M;

alter database add standby logfile group 7 ‘/data/u01/oracle/oradata/fc/redo07.log‘ size 50M;

alter database add standby logfile group 8 ‘/data/u01/oracle/oradata/fc/redo08.log‘ size 50M;

alter system set standby_file_management=auto scope=both;

alter system set db_broker_start=True scope=both;

alter system set local_listener=FC scope=both;

ps -ef|grep dmon_fc ##ora_dmon_fc

配置监听和网络服务名

备库：

复制密码文件、参数文件(spfile)到备库，在备库生成pfile，然后修改下面的参数

$ORACLE_HOME/dbs/orapwfc --$ORACLE_HOME/database/PWD%ORACLE_SID%.ora

$ORACLE_HOME/dbs/spfilefc.ora

create pfile=‘pfilesales‘ from spfile; ##在当前目录生成pfile

db_unique_name=sales

local_listener=sales

select from dba_data_files;

select from v$logfile;

db_file_name_convert=‘/data/u01/oracle/oradata/fc/‘,‘/data/u01/oracle/oradata/sales/‘

log_file_name_convert=‘/data/u01/oracle/oradata/fc/‘,‘/data/u01/oracle/oradata/sales/‘

.fal_server=‘10.204.243.45‘.fal_client=‘10.204.243.44‘

##检查其它参数，路径 --audit_file_dest control_files

create spfile from pfile=‘pfilesales‘

startup nomount

##ORA-00845: MEMORY_TARGET not supported on this system 增大/dev/shm共享内存

##ORA-12528: TNS:listener: all appropriate instances are blocking new connections vim tnsnames.ora (UR=A)

rman target sys/[email protected] auxiliary sys/[email protected]

duplicate target database for standby from active database nofilenamecheck;

主库：dgmgrl /

create configuration dg_test11g as

primary database is fc

connect identifier is fc;

add database sales as

connect identifier is sales

maintained as physical;

show configuration;

enable configuration;

enable database fc;

enable database sales;

备库：

alter database open;

recover managed standby database using current logfile disconnect from session; ##开启ADG

--alter database recover managed standby database using current logfile;

alter database recover managed standby database cancel;###停止ADG

delete archivelog all completed before ‘sysdate - 3‘

select * from v$flash_recovery_area_usage;

select sequence#,applied from v$archived_log;

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

select max(sequence#) from v$archived_log where applied=‘YES‘;

select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

select * from v$dataguard_status;