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')>　　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/sys@fc auxiliary sys/sys@ciwong
　　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'>
　　alter database add standby logfile group 6 '/data/u01/oracle/oradata/fc/redo06.log'>
　　alter database add standby logfile group 7 '/data/u01/oracle/oradata/fc/redo07.log'>
　　alter database add standby logfile group 8 '/data/u01/oracle/oradata/fc/redo08.log'>　　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/ciwong@fc auxiliary sys/ciwong@sales
　　duplicate target database for standby from active database nofilenamecheck;
　　主库：dgmgrl /
　　create configuration dg_test11g as
　　primary database is fc

　　connect>　　add database sales as

　　connect>　　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
　　--lsnrctl stop
　　--alter database recover managed standby database cancel;
　　--shutdown immediate;
　　--startup nomount;
　　--alter database mount standby database;
　　--alter database open read only;
　　--alter database recover managed standby database using current logfile disconnect from session;
　　--lsnrctl stop
　　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;
　　select * from v$dataguard_stats;