025_DG_5_Online

5.2 確認Archive log都傳送到Standby
SQL>
SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER
(PARTITION BY thread#)       AS LAST from  V$ARCHIVED_LOG;


5.3 確認archive log no gaps
SQL>
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM  V$ARCHIVE_GAP;

5.4 Stop Redo Apply
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  CANCEL;

5.5 完成Apply所有redo data
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

5.6
確認Standby Database可切換成
SQL>
SELECT SWITCHOVER_STATUS FROM V$DATABASE;


看到 TO PRIMARY 代表正常


SWITCHOVER_STATUS
--------------------
TO PRIMARY


5.7
將standby切換為primary
SQL> ALTER DATABASE COMMIT TO
SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;



5.8 開啟資料庫

SQL> ALTER DATABASE OPEN;

024_DG_42_START_Prod

024_DG_42_START_Parmary

檢查開啟狀態

-- 請全貼
-- ===check===
select dbid,name from v$database;
select
log_mode , OPEN_MODE , DATABASE_ROLE
from v$database;
--DataGuard

select SWITCHOVER_STATUS , PROTECTION_MODE , PROTECTION_LEVEL
from
v$database;

Process
SELECT
PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM
V$MANAGED_STANDBY;

Process
SQL>   -- 找 LNS
PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM
V$MANAGED_STANDBY;

SQL>  -- 傳送狀態

SELECT DEST_NAME,STATUS,TYPE,RECOVERY_MODE,GAP_STATUS
FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

SQL>  --
log_archive_dest_2
alter system set
log_archive_dest_2='SERVICE=STANDBY compression=disable' scope=both;


SQL> -- # Deffer ENABLE   

alter system set log_archive_dest_state_2=defer ;
alter system set
log_archive_dest_state_2=enable ;


SQL>  --log switch

alter system switch logfile;

設定

1  lsnrctl 要先通

ORCL  =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.48.10 )(PORT = 1521)))
(CONNECT_DATA
=
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL  )))


STANDBY2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL
= TCP)(HOST = 192.168.48.20 )(PORT = 1521)))
(CONNECT_DATA =

(SERVER = DEDICATED)
(SERVICE_NAME = ORCL  )))

2  tnsping 測試看

[oracle]$
tnsping standby2
Used
TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION
= (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.20 )(PORT =
1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL  )))

OK (10 msec)

4.9 調整Primary Database參數  這個很重要   最重要的一動

 
SQL>   --  ## 11G 專用

alter system set log_archive_dest_2='SERVICE=STANDBY compression=disable'
scope=both;


SQL> -- 10G 專用

alter system set log_archive_dest_2=

'SERVICE=STANDBY LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=orcl';

SQL> show parameter
log_archive_dest_2  ;

SQL> -- #
Deffer ENABLE   
alter system set
log_archive_dest_state_2=defer ;
alter system set
log_archive_dest_state_2=enable ;

4.10.3 確認log傳送的狀態

SQL>  -- 11G 以上
SELECT
DEST_NAME,STATUS,TYPE,RECOVERY_MODE,GAP_STATUS FROM V$ARCHIVE_DEST_STATUS
WHERE DEST_ID=2;


SQL> -- 10G 專用

 SELECT DEST_NAME,STATUS,TYPE,RECOVERY_MODE FROM
V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ;

使用下列語法於Primary
Database確認Standby Database apply機制是否為real-time apply。

DEST_NAME

--------------------------------------------------------------------------------

STATUS TYPE RECOVERY_MODE GAP_STATUS
--------- --------------
----------------------- ------------------------
LOG_ARCHIVE_DEST_2

VALID PHYSICAL MANAGED REAL
TIME APPLY NO GAP

4.10.2 Primary Database switch logfile  PROD

PROD

使用下列語法於Primary Database switch logfile
SQL>
alter system switch logfile;

SQL> SELECT
PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM
V$MANAGED_STANDBY;

PROCESS STATUS CLIENT_P SEQUENCE# BLOCK#
BLOCKS
--------- ------------ -------- ---------- ---------- ----------

ARCH CLOSING ARCH 20 1 149
ARCH CLOSING ARCH 21 43008 1546
ARCH
CLOSING ARCH 17 1 248
ARCH CLOSING ARCH 19 1 448

LNS  WRITING
LNS 22
55382 1

ORACLE BASE 11G

https://oracle-base.com/articles/11g/data-guard-setup-11gr2

ORACLe 官網 11G
https://docs.oracle.com/cd/E11882_01/server.112/e41134/create_ps.htm#SBYDB00200

ORACLE 官網 10g
https://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ps.htm#i68937

rman restore newname

startup nomount ;
set DBID=1438520397;
run {
restore controlfile     from    '/rmanbackup/Control.ctl';
startup mount ;
restore database ;
recover database ;
};



startup nomount ;
set DBID=1438520397;
run {
restore controlfile    
 from    '/rmanbackup/Control.ctl';
set newname for datafile  5
    to '/oradata/data/userdata2.dbf' ;
set newname for datafile  6
    to '/oradata/data/user2datafile2.dbf' ;
set newname for datafile  7
    to '/oradata/data/user2datafile1.dbf' ;
startup mount ;
restore database ;
switch datafile all;
recover database ;
};

024_DG_4_START_Standby

檢查開啟狀態

-- 請全貼

-- ===check===

select dbid,name from v$database;

select
log_mode , OPEN_MODE , DATABASE_ROLE

from v$database;

--DataGuard

select SWITCHOVER_STATUS , PROTECTION_MODE , PROTECTION_LEVEL

from
v$database;

Process   要找 MRP0 

SELECT
PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM
V$MANAGED_STANDBY;



LOG APPLY START

ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE USING CURRENT LOGFILE DISCONNECT;
LOG APPLY STOP

ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE CANCEL;

4.8 啟動listener on standby server

Standby

lsnrctl start



範列︰

cdTNS

vi listener.ora

===================================================================================

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl)

)

)



LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.221.20)(PORT = 1521))

)

)



ADR_BASE_LISTENER = /u01/app/oracle

===================================================================================

開始apply

SQL> ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE USING CURRENT LOGFILE DISCONNECT;

ALTER DATABASE RECOVER
MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

*

ERROR at
line 1:

ORA-38500: USING CURRENT LOGFILE option not available without
stand

出現 ORA-38500  使用以下部驟

4.7.7 Create Standby logfile

先使用下列語法查詢，Primary有幾個online redo log，以及online redo log file的size。





SQL>

select group#,bytes from v$log;



GROUP# BYTES

---------- ----------

1 52428800

3 52428800

2
52428800



SQL>

使用下列語法新增stnadby logfile，所需standby
logfile數量為Primary
logfile

數+1，size大小與Primary一致，

因上一步驟查詢Primary logfile數為3，size大小為50M，故需新增standby
logfile數量為4，size大小為50M



，紅字部份請依環境進行調整。

[oracle]$

LPATH=/oracle/oradata/redo
mkdir -p
$LPATH
for i in `seq 1 4`
do
echo " alter database add standby
logfile '$LPATH/redo${i}_std.log' size 50M; "
done

alter
database add standby logfile '/oracle/oradata/redo/redo1_std.log' size 50M;

alter database add standby logfile '/oracle/oradata/redo/redo2_std.log' size
50M;

alter database add standby logfile
'/oracle/oradata/redo/redo3_std.log' size 50M;

alter database add standby
logfile '/oracle/oradata/redo/redo4_std.log' size 50M;

SQL>

alter database add standby logfile
'/oracle/oradata/redo/redo1_std.log' size 50M;
alter database add standby
logfile '/oracle/oradata/redo/redo2_std.log' size 50M;
alter database add
standby logfile '/oracle/oradata/redo/redo3_std.log' size 50M;
alter
database add standby logfile '/oracle/oradata/redo/redo4_std.log' size 50M;

重新apply

SQL>
ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE USING CURRENT LOGFILE DISCONNECT;



Database altered.

檢查process

SQL>

SELECT
PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM
V$MANAGED_STANDBY;



PROCESS STATUS CLIENT_P SEQUENCE# BLOCK#
BLOCKS

--------- ------------ -------- ---------- ---------- ----------

ARCH CONNECTED ARCH 0 0 0

ARCH CONNECTED ARCH 0 0 0

MRP0
WAIT_FOR_GAP N/A 27 0 0



以上紅色有落差



SQL>


select * from v$recovery_log;



THREAD# SEQUENCE# TIME

---------- ---------- ---------

ARCHIVE_NAME

--------------------------------------------------------------------------------

1 27 14-APR-17

/oracle/u01/app/oracle/product/10.2.0/dbs/arch1_27_940991096.dbf

SQL>

SELECT
PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE#, BLOCK#,BLOCKS FROM
V$MANAGED_STANDBY;



PROCESS STATUS CLIENT_P SEQUENCE# BLOCK#
BLOCKS

--------- ------------ -------- ---------- ---------- ----------

ARCH CLOSING ARCH 28 6145 581

ARCH CLOSING ARCH 29 1 22

MRP0
APPLYING_LOG N/A 30 15727 102400

RFS IDLE
UNKNOWN 0 0 0

RFS IDLE LGWR 30 15729 1

以上紅色為正常

4.10.5 開啟檔案自動管理

SQL> alter system set standby_file_management = auto;



SQL> show parameter standby ;



NAME TYPE VALUE

------------------------------------ -----------
------------------------------

standby_archive_dest string           
?/dbs/arch

standby_file_management string         
AUTO

4.11 停止Standby database apply

SQL> 
ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE CANCEL;

023_DG_3_restore

   

4.7 restore database on standby

4.7.1 startup nomount on standby

STAGE

[oracle@Standby rmanbackup]$
export ORACLE_SID=SID;
[oracle@Standby
rmanbackup]$ rman target / nocatalog

RMAN> startup nomount;

Oracle instance started

Total System
Global Area 6747725824 bytes

Fixed Size 2213976 bytes
Variable
Size 3623880616 bytes
Database Buffers 3087007744 bytes
Redo Buffers
34623488 bytes

4.7.2 設定DBID  STAGE

STAGE

RMAN> set dbid=2330223285;


executing command: SET DBID

4.7.3 restore standby controlfile

使用下列指令restore standby controlfile，紅字部份請依環境進行調整。

RMAN> restore standby controlfile from '/rmanbackup/standby.ctl';

alter
database mount; 
restore database;  recover database;

 

4.7.4 mount database

##  使用下列語法mount database。

RMAN> alter
database mount;

4.7.5 restore database

##  使用下列指令執行restore database。

RMAN>
restore database;

4.7.6 recover database

使用下列指令執行recover database，底下出現的Error Message表示需要sequence=24的archive
log，但因2.4備份Primary Database時，當時archive log只到sequence 23，故此Error Message為正常的。


RMAN> recover database;

Starting
recover at 29-APR-16
using channel ORA_DISK_1
using channel ORA_DISK_2

using channel ORA_DISK_3
using channel ORA_DISK_4

starting media
recovery

archived log for thread 1 with sequence 475102 is already on
disk as file
/oracle/u01/app/oracle/flash_recovery_area/ADPMOBILE/archivelog/2016_04_29/o1_mf_1_475102_cl6g66qf_.arc

archived log for thread 1 with sequence 475103 is already on disk as file
/oracle/u01/app/oracle/flash_recovery_area/ADPMOBILE/archivelog/2016_04_29/o1_mf_1_475103_cl6g67pw_.arc

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log
archived log thread=1
sequence=475098
channel ORA_DISK_1: reading from backup piece
/rmanbackup/PRMY_ifr491kb_1_1.bk
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002:
failure of recover command at 04/29/2016 18:59:53
ORA-19870: error while
restoring backup piece /rmanbackup/PRMY_ifr491kb_1_1.bk
ORA-19809: limit
exceeded for recovery files
ORA-19804: cannot reclaim 202038272 bytes
disk space from 4070572032 limit

022_DG_2_Backup

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 29 15:43:31 2016


Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected
to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options

SQL> SELECT LOG_MODE FROM
V$DATABASE;

如果是出現  ARCHIVELOG 
沒事
LOG_MODE
------------
ARCHIVELOG


SQL>
select force_logging from v$database;


如果是出現  YES  沒事
FOR
---

YES


$> 

4.3.1 建立TNS-Alias on Primary

PROD

orcl:MADPDB:PROD-oracle$ cd
$ORACLE_HOME/network/admin
orcl:MADPDB:PROD-oracle$
 vi tnsnames.ora

4.3.2 建立所需目錄於Standby Server

STAGE

orcl:Standby:dbs oracle$
cd $ORACLE_HOME/dbs
orcl:Standby:dbs
oracle$ vi initorcl.ora

4.3.3 Copy SPFILE from Primary to Standby

orcl:PROD-oracle$ cd
$ORACLE_HOME/dbs
orcl:PROD-oracle$
vi initorcl.ora

4.4 備份Primary資料庫

PROD

orcl:MobileAD_DB1:archive_log oracle$ rman target /


Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 29 17:17:23
2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All
rights reserved.

connected to target database: ADPMOBIL
(DBID=2330223285)

RMAN>

RMAN> backup
database format '/rmanbackup/PRMY_%U.bk'
       
plus archivelog format '/rmanbackup/PRMY_AR%U.bk';

4.4  如果有 完整備份的話   

不要用完整備份會出人命

也可以直接將後面的 archivelog 備份即可

RMAN>    backup archivelog all format '/rmanbackup/PRMY_AR%U.bk';

4.5 備份controlfile for standby

RMAN> backup current controlfile for standby format '/rmanbackup/standby.ctl';

4.6 傳送備份至Standby Server

使用scp指令將Primary Database的備份傳送到Standby Server，紅字部份請依環境調整。

rman FORMAT字符串替代变量

一、FORMAT字符串替代变量

使用FORMAT参数时可使用的各种替换变量，如下：

%c：备份片的拷贝数(从1开始编号)；

%d：数据库名称；

%D：位于该月中的天数 (DD)；

%M：位于该年中的月份 (MM)；

%F：一个基于DBID 唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ,

   IIIIIIIIII 为该数据库的DBID，

          YYYYMMDD 为日期，

          QQ 是一个1-256 的序列

%n：数据库名称，并且会在右侧用x字符进行填充，使其保持长度为8；

%u：是一个由备份集编号和建立时间压缩后组成的8字符名称。

   利用%u可以为每个备份集生成一个唯一的名称；

%p：表示备份集中备份片段的编号，从1 开始编号；

%U：是%u_%p_%c的简写形式，利用它可以为每一个备份片段（即磁盘文件）

   生成一个唯一名称，这是最常用的命名方式；

%s：备份集的号；

%t：备份集时间戳；

%T：年月日格式(YYYYMMDD)；s

注：如果在BACKUP命令中没有指定FORMAT选项，则RMAN默认使用%U为备份片段命名。