dataguard配置成功脚本

点击数：1351发布日期：2020-04-20 14:00:42　来源：老鹰

--改为归档模式
alter system set log_archive_dest_1='location=/arch1' scope=spfile;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;




dgmgrl
#################################源端######################################
archive log list;
ALTER DATABASE FORCE LOGGING;
select force_logging from v$database;
#################################源端配置tnsnames.ora源端rac两端都要配######
在$ORACLE_HOME/network/admin/tnsnames.ora中添加备机的tnsname。如下
webdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.8.201)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = webdg)
    )
  )


#################################源端配log_archive_config#################################
select name,db_unique_name from gv$database;


alter system set log_archive_config='dg_config=(yingsdb1,yingsdg1)' scope=both sid='*';
alter system set log_archive_dest_2='service=yingsdg1 LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=yingsdg1' scope=both sid='*';
#############################################tnsnames里配的东西###################
alter system set standby_file_management=auto scope=both sid='*';
alter system set fal_server='yingsdg1' scope=both sid='*';
alter system set fal_client='yingsdb1' scope=both sid='*';
alter system set db_file_name_convert='/u01/app/oracle/oradata/yingsdb1','/u01/app/oracle/oradata/yingsdb1' scope=spfile sid='*';
alter system set log_file_name_convert='/u01/app/oracle/oradata/yingsdb1','/u01/app/oracle/oradata/yingsdb1' scope=spfile sid='*';


################################学习园地##########################################
log_archive_config='dg_config=(newwebdb,newwebdg)'  --pecifies the unique database name
log_archive_dest_2 'service=newwebdg LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=newwebdg' scope=both sid='*';                                              --关键点
valid_for=(传的什么东西,在什么角色的时候起作用)
select * from v$dataguard_config;
affirm  [əˈfə:m]vt.断言,坚持声称；证实,确认
LNSn  network server (LNSn) processes
Fetch archive log (FAL)
ARCn


RFS        Remote file server (RFS) process: RFS    接受来自主库的redo信息，写到standby redo，或直接写到归档日志文件。
MRP        Managed recovery process 管理还原进程，只用于物理standby，应用归档日志到物理stdby
LSP        Logical standby process (LSP)


--这两个参数在主库上不起作用。当是standby的时候才起作用。
fal_server
fal_client




问题：
valid_for=(STANDBY_LOGFILE,怎么还能传standby_logfile文件吗？
standby_log日志的作用


V$MANAGED_STANDBY
V$STANDBY_APPLY_SNAPSHOT


select recovery_mode from v$archive_dest_status where dest_id=2;
select process,block#,blocks ,status ,sequence# from v$managed_standby;
select process,pid from V$MANAGED_STANDBY;
--查是否是primary或PHYSICAL STANDBY,保护模式。
select database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
select max(sequence#) from v$archived_log;
################################添加standby日志################################
ALTER DATABASE ADD STANDBY LOGFILE  GROUP 4 ('/u01/app/oracle/product/oradata/newweb/stdredo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE  GROUP 5 ('/u01/app/oracle/product/oradata/newweb/stdredo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE  GROUP 6 ('/u01/app/oracle/product/oradata/newweb/stdredo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE  GROUP 7 ('/u01/app/oracle/product/oradata/newweb/stdredo04.log') SIZE 50M;




SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; 
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOGfile; 


--源端制作备用数据库控置文件
选择用RMAN制作


$ rman target /




run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup database
include current controlfile format '/orabak/orclfull_%d_%T_%s';
}


run {
allocate channel c1 type disk;
backup current controlfile for standby format '/orabak/control01.ctl.rman';
}




--生成参数文件
create pfile='/orabak/inityingsdg1.ora' from spfile; 








##############################################################################
--配置目标端


export LANG="en_US.UTF-8" 
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db
export ORACLE_SID=newwebdg
PATH=/bin:/usr/bin:/usr/openwin/bin:/usr/ucb:/usr/sbin:/usr/ccs/bin:/sbin:/usr/local/bin:$ORACLE_HOME/bin:$CRS_HOME/bin:/usr/bin/X11:/etc:$PATH:.
export PATH




--建立相关目录
cd $ORACLE_BASE
mkdir -p /oracle/admin/newwebdg/adump
mkdir -p /oracle/admin/newwebdg/bdump
mkdir -p /oracle/admin/newwebdg/cdump
mkdir -p /oracle/admin/newwebdg/udump




--目标配置tnsname


在$ORACLE_HOME/network/admin/tnsnames.ora中添加到主机的tnsname。如下
NEWWEBDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = newwebdb2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = newwebdb)
      (INSTANCE_NAME = newwebdb2)
    )
  )


NEWWEBDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = newwebdb1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = newwebdb)
      (INSTANCE_NAME = newwebdb1)
    )
  )


NEWWEBDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = newwebdb1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = newwebdb2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = newwebdb)
    )
  )






--目标端修改pfile
bakdbsvr:/oracle/product/10.2.0/db/dbs#>cat initnewwebdb.ora
*.archive_lag_target=1800
*.audit_file_dest='/oracle/admin/newwebdg/adump'
*.audit_sys_operations=TRUE
*.audit_trail='NONE'
*.background_dump_dest='/oracle/admin/newwebdg/bdump'
*.cluster_database=false
*.compatible='10.2.0.5.0'
*.control_file_record_keep_time=365
*.control_files='/webdata/newwebdata/control01.ctl','/webdata/newwebdata/control02.ctl','/webdata/newwebdata/control03.ctl'
*.core_dump_dest='/oracle/admin/newwebdg/cdump'
*.db_block_size=8192
*.db_create_file_dest='/webdata/newwebdata'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='newwebdb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=newwebdgXDB)'
*.log_archive_config='dg_config=(newwebdb,newwebdg)'
*.log_archive_dest_1='location=/webdata/newwebarch'
*.log_archive_dest_2='service=newwebdb1 LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=newweb'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=500
*.pga_aggregate_target=848297984
*.processes=500
*.remote_login_passwordfile='exclusive'
*.db_unique_name=newwebdg
*.instance_name=newwebdg
*.sessions=555
*.sga_target=1610612736
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.thread=1
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/newwebdg/udump'
*.fal_server='newwebdb1','newwebdb2'
*.fal_client='newwebdg'
*.service_names='newwebdg'
*.db_file_name_convert=('+DATA/newwebdb/datafile','/webdata/newwebdata')
*.log_file_name_convert=('+DATA/newwebdb/onlinelog','/webdata/newwebdata')
bakdbsvr:/oracle/product/10.2.0/db/dbs#>




--目标端建密码文件
orapwd file=/oracle/product/10.2.0/db/dbs/orapwnewwebdg password="Uv9a/psF" entries=5






--目标库恢复备用数据库控置文件
将主数据库生成的控置文件ftp到备机上，然后作恢复
restore controlfile to '/webdata/newwebdata/control01.ctl' from '/home/oracle/control01.ctl.rman15';
restore controlfile to '/webdata/newwebdata/control02.ctl' from '/home/oracle/control01.ctl.rman15';
restore controlfile to '/webdata/newwebdata/control03.ctl' from '/home/oracle/control01.ctl.rman15';








--加载备用数据库
sqlplus “/as sysdba”
startup mount pfile='/oracle/product/10.2.0/db/dbs/initnewwebdb.ora';
startup nomount;






run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
restore database;
}




alter database mount standby database; 


startup mount pfile='/oracle/product/10.2.0/db/dbs/initnewwebdb.ora';






--校验连接
conn sys/"Uv9a/psF"@newweb1 AS SYSDBA
conn sys/"Uv9a/psF"@newwebdg AS SYSDBA




rman target sys/sys@newweb auxiliary sys/sys@newwebdg




--
run {
duplicate target database for standby
set db_file_name_convert='+DATA/newwebdb/datafile', '/webdata/newwebdata'
set log_file_name_convert='+DATA/newwebdb/onlinelog', '/webdata/newwebdata';
}


--查数据库状态
select instance_name,status from v$instance;


'+DATA/newwebdb/datafile', '/webdata/newwebdata'




'+DATA/newwebdb/onlinelog', '/webdata/newwebdata'






     FILE# NAME
---------- ------------------------------------------------------------
         1 +DATA/webdb/datafile/system.264.738413573
         2 +DATA/webdb/datafile/undotbs1.265.738413583
         3 +DATA/webdb/datafile/sysaux.266.738413583
         4 +DATA/webdb/datafile/undotbs2.268.738413589
         5 +DATA/webdb/datafile/users.269.738413591
         6 +DATA/webdb/datafile/web_a.277.738420041
         7 +DATA/webdb/datafile/web_b.278.738420043
         8 +DATA/webdb/datafile/web_2011.279.738420043
         9 +DATA/webdb/datafile/web_2012.280.738420045
        10 +DATA/webdb/datafile/web_2013.281.738420045
        11 +DATA/webdb/datafile/web_2014.282.738420045






--目标端全备


run {
backup full tag 'newweb' database
include current controlfile format '/oracle/backup/data/orclfull_%d_%T_%s'
plus archivelog format '/oracle/backup/data/arch_%d_%T_%s';
backup current controlfile for standby format '/home/oracle/control01.ctl.rman15';
}






ALTER DATABASE drop STANDBY LOGFILE THREAD 1 GROUP 1;


drop 










scp *NEWWEB* user@10.0.8.201:/home/guest/






startup nomount pfile='/oracle/product/10.2.0/db/dbs/initnewwebdb.ora';










ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;










alter database drop standby logfile group 4;
alter database drop standby logfile group 5;




alter system set log_archive_config='dg_config=(newweb,newwebdg)' scope=both sid='*';


 select max(sequence#) from v$log_history group by thread#;
select max(sequence#) from v$archived_log;


select * from v$logfile;




--关备机
alter database recover managed standby database cancel;


startup nomount;