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;
selectfrom 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;
页:
[1]