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

032_DG_2_OPENTEST

4. Activate the physical standby database.

4.1 active standby database

SQL>
ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

SQL>


aletr log 會產生以下的訊息

AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
ACTIVATE STANDBY: Complete - Database mounted as primary
Completed: ALTER DATABASE ACTIVATE STANDBY DATABASE

4.2 確認control file 狀態

SQL>
select CONTROLFILE_TYPE from v$database;

CONTROL
-------
CURRENT

4.3 open database

SQL>
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
ALTER DATABASE OPEN;

aletr log 會產生以下的訊息
Completed: ALTER DATABASE OPEN

4.4 確認database 狀態

SQL>
select status from v$thread ;

STATUS
------
OPEN

5 6 7 一連串的測試  自已體會

5. 手動 bind ip for DB VIP
6. 修改listener.ora  /etc/hosts 
7. 連線確認 DB 可正常讀取

該注意 
lisrctl IP   VIP是否正確
/etc/hosts   VIP對是否正確
sqlnet.ora   db的白名單是否正確

8. 還原 Physical cold standby database

8.1 重啟 database on mount mode

SQL>
shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
startup mount
ORACLE instance started.

Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 507514504 bytes
Database Buffers 260046848 bytes
Redo Buffers 6828032 bytes
Database mounted.
SQL>

8.2 flashback database to before open

SQL>
FLASHBACK DATABASE TO RESTORE POINT BEFORE_OPEN_STANDBY;

Flashback complete.

aletr log 會產生以下的訊息 FLASHBACK DATABASE TO RESTORE POINT BEFORE_OPEN_STANDBY
Flashback Restore Start
Flashback Restore Complete
Completed: FLASHBACK DATABASE TO RESTORE POINT BEFORE_OPEN_STANDBY

8.3 可以確認control file type 為 backup

SQL>
select controlfile_type from v$database ;

CONTROL
-------
BACKUP

8.4 轉換 database 為 physical standby

SQL>
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

aletr log 會產生以下的訊息


Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY

8.5 重啟 standby database on mount mode

SQL>
shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
startup mount
ORACLE instance started.

Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 507514504 bytes
Database Buffers 260046848 bytes
Redo Buffers 6828032 bytes
Database mounted.
SQL>

8.6 重啟 standby databas apply

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

Database altered.

aletr log 會產生以下的訊息


Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE

9. 恢復primary database 歸檔至standby database

SQL>
alter system set log_archive_dest_state_2=enable ;

System altered.

SQL>

aletr log 會產生以下的訊息
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************

10. Drop the restore point

10 .1 drop and off

SQL>
DROP RESTORE POINT BEFORE_OPEN_STANDBY;
alter database flashback off;

Restore point dropped.
Database altered.

aletr log 會產生以下的訊息


Drop guaranteed restore point BEFORE_OPEN_STANDBY
Guaranteed restore point BEFORE_OPEN_STANDBY dropped

Completed: alter database flashback off

10.2 再重啟一次並恢復連線

SQL>
shutdown immediate;
startup mount;

ORACLE instance started.

Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 507514504 bytes
Database Buffers 260046848 bytes
Redo Buffers 6828032 bytes
Database mounted.
SQL>
SQL>
alter database recover managed standby database using current logfile disconnect from session;

Database altered.

11 恢復 檢查

SQL>
select log_mode , OPEN_MODE , DATABASE_ROLE from v$database;
select SWITCHOVER_STATUS , PROTECTION_MODE , PROTECTION_LEVEL from v$database;

LOG_MODE OPEN_MODE DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG MOUNTED PHYSICAL STANDBY

SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
-------------------- -------------------- --------------------
NOT ALLOWED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

031_DG_1_OPENTEST

1. 啟動 flashback database功能

1.1 檢查 Cold Standby DB 是否開始 archive log mode  注意紅字

SQL>
archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archivelog
Oldest online log sequence 45
Next log sequence to archive 47
Current log sequence 47
SQL>

1.2 設定 flashback 相關參數

注意 資料夾要存在，不然要先建立
這邊要求的空間，不一定要有，反正只是測試一下而以，不用擔心

[oracle]$ mkdir -p /data/flash_recovery_area
[oracle]$ SS

SQL>
alter system set db_recovery_file_dest_size=100g;
alter system set db_recovery_file_dest='/data/flash_recovery_area';
alter system set db_flashback_retention_target=1440;

1.3 先暫停 Cold Standby apply，並確認 Standby database in mount mode

SQL>
alter database recover managed standby database cancel;
select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

如果出現 READ ONLY  也沒關系

1.4 開啟 flashback database 功能

SQL>
alter database flashback on;

Database altered.

1.5 重啟 Cold Standby apply

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


Database altered.

Prod Alter log  會產生以下的字眼
***************************************************
LGWR: Setting 'active' archival for destination LOG
***************************************************

2. Active Cold Standby 前準備 on Standby database

2.1 停止 cold standby db apply

SQL>
alter database recover managed standby database cancel;

Database altered.

2.2 建立還原點

SQL>
create restore point before_open_standby guarantee flashback database;

Restore point created.

2.3 確認所建立的還原點 有Select 到即可

SQL>
select scn, storage_size, time, name from v$restore_point;

SCN STORAGE_SIZE TIME
---------- ------------ ---------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------
1481019 52428800 08-APR-17 08.23.39.000000000 PM
BEFORE_OPEN_STANDBY


SQL>

3. Active Cold Standby 前準備 on Primary database

3.1 switch log file

SQL>
alter system switch logfile;

System altered.

3.2 暫停 archive log 歸檔至standby database

SQL>
alter system set log_archive_dest_state_2=defer ;

System altered.

041_DG_Check

1 檢查 Process

1  STANDBY PROCESS 一定要是 APPLYING_LOG

SQL>
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
ARCH CONNECTED ARCH 0 0 0
ARCH CLOSING ARCH 54 28672 1262
MRP0 APPLYING_LOG N/A 55 567 102400
RFS IDLE ARCH 0 0 0
RFS IDLE LGWR 55 567 1
RFS IDLE UNKNOWN 0 0 0

8 rows selected.

Database altered.

SQL>
不是的話
===============================================================================================
沒有MRP0的話
執行
# 啟動APPLY
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

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
ARCH  CONNECTED    ARCH 0    0       0
ARCH  CONNECTED    ARCH 0    0       0
MRP0  WAIT_FOR_LOG N/A 55    0       0

就是有排掉LOG 要補檔

2  PROD PROCESS  一定要是 WRITING

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

PROCESS STATUS CLIENT_P SEQUENCE# BLOCK# BLOCKS
--------- ------------ -------- ---------- ---------- ----------
ARCH CLOSING ARCH 53 1 3036
ARCH CLOSING ARCH 54 28672 1262
ARCH CONNECTED ARCH 0 0 0
ARCH CLOSING ARCH 54 2 29932
LNS  WRITING LNS 55 3846 1

SQL>
不是的話
===============================================================================================
沒有LNS 的話
執行
#
SQL>
alter system set log_archive_dest_state_2=enable ;

2 檢查 傳送是否正常，是否新增

4.3 open database

SQL>
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#)
"Difference"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE
(THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

aletr log 會產生以下的訊息
Completed: ALTER DATABASE OPEN

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

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;

021_DG_1_ARCHIVELOG_MODE

  

Check Log Mode

SQL>

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;

 DBID NAME
---------- ---------
1438520397 ORCL

SQL> 2

LOG_MODE OPEN_MODE DATABASE_ROLE
------------ --------------------
----------------
NOARCHIVELOG MOUNTED PRIMARY

SQL> SQL> 2

SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
--------------------
-------------------- --------------------

NOT ALLOWED MAXIMUM
PERFORMANCE UNPROTECTED

How to change an Oracle database to noarchivelog mode

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

SQL> alter
database mount;

Database altered.

SQL> alter database
noarchivelog;

Database altered.

SQL> alter database open;


Database altered.
================================================

How to change an Oracle database to noarchivelog mode

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


SQL> alter
database mount;

Database altered.

SQL> alter database
noarchivelog;

Database altered.

SQL> alter database open;


Database altered.
================================================

How to change an Oracle database to archivelog mode

SQL> SELECT LOG_MODE FROM V$DATABASE;


LOG_MODE
------------
NOARCHIVELOG

SQL>
select force_logging from v$database;


FOR
---
NO

1 開啟Archive log mode

1.設定archive log
destination

SQL> alter system set
log_archive_dest_1='LOCATION=/oradata/archivelog'
scope=both;

System altered.

SQL>



2.重啟資料庫於mount

SQL> startup force mount ;

ORACLE instance started.

Total System Global Area 409194496 bytes

Fixed Size 2213856 bytes
Variable Size 272631840 bytes
Database
Buffers 130023424 bytes
Redo Buffers 4325376 bytes
Database mounted.

SQL>


3.開啟archive log mode

SQL> alter
database archivelog;

Database altered.

4.確認資料庫歸檔模式



SQL> archive log list
Database log
mode Archive Mode
Automatic archival Enabled
Archive destination
/oradata/archivelog
Oldest online log sequence 8
Next log sequence to
archive 10
Current log sequence 10
SQL> SELECT
LOG_MODE FROM V$DATABASE;

LOG_MODE
------------

ARCHIVELOG

logging

SQL>
select force_logging from v$database;

FOR
---
NO

SQL>
alter
database force logging;

Database altered.

SQL>
select
force_logging from v$database;

FOR
---
YES

SQL>

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，紅字部份請依環境調整。