Data_gard搭建



-- 1. check file list
-- 1.1 on primary server, check files which are needed to be restore on standby
select name from v$datafile;

-- 1.2 on primary server, check init.ora and standby controle file
E:\oracle\oradata\jasmine\backup\bakstbyctrljasmine.ctl
e:\oracle\oradata\jasmine\backup\initjasmine.ora

-- 1.3 on primary server, check for network files
d:\app\oracle\product\11.1.0\db_1\NETWORK\ADMIN\tnsnames.ora
d:\app\oracle\product\11.1.0\db_1\NETWORK\ADMIN\listener.ora
d:\app\oracle\product\11.1.0\db_1\NETWORK\ADMIN\sqlnet.ora

-- 1.4 on primary server, check for pwd file
C:\oracle\product\11.2.0\dbhome_1\database\PWDdemo1.ora

-- 2. Building standby server
-- 2.1 BACKUP DB on primary db and copy them to standby server, we can use the automatic backup files.

-- 2.2 create new DIRECTORY  on standby server
MKDIR E:\oracle
MKDIR E:\oracle\oradata
MKDIR E:\oracle\oradata\jasmine
MKDIR E:\oracle\oradata\jasmine\archive
MKDIR E:\oracle\oradata\jasmine\bdump
MKDIR E:\oracle\oradata\jasmine\udump
MKDIR E:\oracle\oradata\jasmine\cdump

-- 2.3 CREATE INSTANCE on standby server
-- 2.3.1 create instance
oradim -new -sid JASMINE -intpwd ***
-- 2.3.2 copy pwd file of primary server to standby server, so it replace the origal one

-- 2.4 configure network on standby server
-- 2.4.1 add new listener
-- 2.4.2 add two tnsnames JASMINE_STANDBY/JASMINE and test the one of JASMINE(connect to primary server)
-- 2.4.3 restart service of listener on standy server

-- 2.5 adjust INITJASMINE.ORA on standby server
-- 2.5.1 copy INITjasmine.ORA to standby server %oracle_home%/database
-- 2.5.2 modify the parmeter file as below
/*
*.DB_NAME='Jasmine'
*.DB_UNIQUE_NAME='JASMINE_STANDBY'
*.SERVICE_NAMES='JASMINE'
*.INSTANCE_NAME='JASMINE'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(JASMINE,JASMINE_STANDBY)'
#*.DB_FILE_NAME_CONVERT='E:\oracle\oradata\jasmine','E:\oracle\oradata\jasmine'
#*.LOG_FILE_NAME_CONVERT='E:\oracle\oradata\jasmine','E:\oracle\oradata\jasmine'
*.LOG_ARCHIVE_DEST_1='LOCATION=E:\oracle\oradata\jasmine\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=JASMINE_STANDBY'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_2='SERVICE=JASMINE_STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=JASMINE'
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.FAL_SERVER=JASMINE
*.FAL_CLIENT=JASMINE_STANDBY
*.STANDBY_FILE_MANAGEMENT=AUTO
*.standby_archive_dest='E:\oracle\oradata\jasmine\archive'
#*.background_dump_dest='E:\oracle\oradata\jasmine\bdump'
#*.user_dump_dest='E:\oracle\oradata\jasmine\udump'
#*.core_dump_dest='E:\oracle\oradata\jasmine\cdump'
*/

-- 2.6 copy backup datafiles to suitable space for recovery
-- 2.6.1 db files to E:\oracle\oradata\jasmine
-- 2.6.2 copy 3 copy of control files to E:\oracle\oradata\Jasmine
E:\oracle\oradata\Jasmine\control01.ctl
E:\oracle\oradata\Jasmine\control02.ctl
E:\oracle\oradata\Jasmine\control03.ctl

-- 2.7 start standby server
-- 2.7.1 startup momount
set oracle_sid=jasmine
sqlplus/nolog
conn sys/manager as sysdba
-- need to alter directory accordingly
startup nomount pfile='E:\OraData\backup\INITjasmine.ORA';
create spfile from pfile;
-- 2.7.2 mount db
alter database mount;

-- 2.7.3 apply log
alter database recover managed standby database disconnect from session;

-- 2.7.4 check whether two servers are in sync, refer to 3.2,3.3

-- 2.8 finish rebuilding data guard.

-- 3. REFERENCE SQL
-- 3.1 FYI
select protection_mode,protection_level from v$database;
select t.DATABASE_ROLE,t.FORCE_LOGGING,t.name,t.DB_UNIQUE_NAME,t.RESETLOGS_CHANGE#,t.CHECKPOINT_CHANGE#,t.ARCHIVE_CHANGE#,t.ARCHIVELOG_CHANGE# from v$database t;
select t.INSTANCE_NAME,t.HOST_NAME,t.VERSION,t.STATUS,t.INSTANCE_ROLE from v$instance t;
select t.* from v$logfile t;
select t.STAMP,t.SEQUENCE#,t.THREAD#,t.FIRST_CHANGE#,t.RESETLOGS_CHANGE#,to_char(t.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') as FIRST_TIME from v$log_history t order by t.STAMP desc;
select T.STAMP,T.NAME,T.SEQUENCE#,T.ARCHIVED,T.APPLIED,to_char(t.COMPLETION_TIME,'yyyy-mm-dd hh24:mi:ss') AS COMPLETION_TIME from v$archived_log t order by t.DEST_ID,t.sequence# desc;
select t.LOG_SEQUENCE,t.APPLIED_SCN,t.valid_type,t.VALID_ROLE,t.DB_UNIQUE_NAME,t.REGISTER,t.DEST_ID,t.DEST_NAME,t.DESTINATION,t.TARGET,t.ARCHIVER from v$archive_dest t
where t.STATUS = 'VALID' order by t.dest_id;

-- 3.2 PRIMARY SERVER
SELECT THREAD#, MAX(SEQUENCE#)
FROM V$LOG_HISTORY
WHERE RESETLOGS_CHANGE# =
(SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;

Alter system switch logfile;

-- 3.3 STANDBY SERVER
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
      from v$archived_log
      where resetlogs_change#=(select resetlogs_change# from v$database)
      group by thread#) al,
     (select thread# thrd, max(sequence#) lhmax
      from v$log_history
      where first_time=(select max(first_time) from v$log_history)
      group by thread#) lh
where al.thrd = lh.thrd;

-- 3.4 backup db
select name from v$datafile;
alter database begin backup;
copy files
alter database end backup;

alter database create standby controlfile as 'E:\OraData\backup\demo1\standby2.ctl';

-- 3.5 pfile
create pfile from spfile;
create spfile from pfile='E:\OraData\backup\INITjasmine.ORA';

-- 3.6 active standby server
-- 3.6.1 active standby server
recover managed standby database cancel;
alter database activate standby database;
shutdown immediate;

-- 3.6.2 alter parameter of initjasmine.ora
- Change the Enable the log_archive_dest_state_2=’defer’ to ‘enable’
- Remove standby_archive_dest=’e:\oracle\oradata\jasmine\archive’
- Remove standby_file_management=auto
--create spfile from pfile=’d:\oracle\ora92\database\initjasmine.ora’;

-- 3.6.3
startup;
alter tablespace temp add  tempfile 'E:\oracle\oradata\jasmine\temp01.dbf' size 1200M reuse;

-- 3.6.4 backup db
 
-- 3.7 manage standby server
alter database recover managed standby database disconnect from session;
alter database recover managed standby database using current logfile disconnect from session;
 
alter database recover managed standby database cancel;