oracle單例項通過dataguard遷移到RAC 轉

warehouse發表於2016-07-23
Oracle單例

原文連結，防止連結過去，內容拷貝如下：http://www.bkjia.com/oracle/949585.html


One instance primary to RAC standbyDataGuard Configuration:

 

 

Primary

standby

Clusterware

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

11g R2 Grid Infrastructure (11.2.0.4)

Cluster Nodes

Cltrac1

Srvrac1,srvrac2

DB_UNIQUE_NAME

Test1

Test2

DB_NAME

TEST1

Test1

DB_instance

Test1

Test11,test22

DB_listener

listener

Listener2

DB storage

ASM

Linux file sys

ASM diskgroup for DB files

DATA

 

ORACLE_HOME

/bee/app/oracle/product/11.2.0/db_1

/bee/app/oracle/product/11.2.0/db_1

OS

CentOS release 6.4 (Final)

CentOS release 6.4 (Final)

 

主庫準備：

1. 主庫必須為歸檔模式，修改主庫為歸檔模式的方法：

SQL>shutdown immediate

SQL>startup mount

SQL>alter database archivelog;

SQL>alter database open;

SQL>archive log list;

2. 啟用主庫的強制日誌功能

SQL> alter database force logging;

3. 當主庫新增或刪除資料檔案時，這些檔案也會在備庫新增或刪除。啟用此功能的方法如下：

SQL> alter system set standby_file_management = 'AUTO';

4. 給主庫新增standby日誌：

alterdatabase add standby logfile '/bee/app/oracle/oradata/test1/standby01.log' size50M;

alterdatabase add standby logfile '/bee/app/oracle/oradata/test1/standby02.log' size50M;

alter database add standby logfile'/bee/app/oracle/oradata/test1/standby03.log' size 50M;

5. 建密碼檔案，並且設定引數 REMOTE_LOGIN_PASSWORDFILE 為 EXCLUSIVE 或 SHARED。一般資料庫預設就有密碼檔案，並且此引數預設為 EXECUSIVE。先檢查下這兩項，如果不是預設，設定方法如下：

SQL>alter system set remote_login_passwordfile=exclusive scope=spfile;

OS> orapwd password=<sys 使用者密碼>

Scp mvorapwSID

6. 檢查資料庫的 db_unique_name 引數是否設定。如果沒有，使用 alter system 進行設定：

SQL> show parameter db_unique_name;

SQL> alter system set db_unique_name=some_namescope=spfile;

7. 配置歸檔位置：

alter system set log_archive_dest_1 ='location=/bee/app/oracle/archivelog valid_for=(all_logfiles, all_roles)db_unique_name=test1';

alter system set log_archive_dest_2 = 'service=test21 asyncvalid_for=(online_logfile,primary_role) db_unique_name=test2';

 

8. SQL> alter system setfal_server = 'test2';

SQL> alter system set log_archive_config ='dg_config=(test1,test2)';

9. 設定檔案轉換方式:後面為本地存放位置

altersystem set DB_FILE_NAME_CONVERT='+DATA/test2/datafile/','/bee/app/oracle/oradata/test1/'scope=spfile;

alter system setLOG_FILE_NAME_CONVERT='+DATA/test2/onlinelog/','/bee/app/oracle/oradata/test1/'scope=spfile;

10. createpfile='/tmp/test2_pfile.ora' from spfile;

11.

 

 

監聽及tnsnames配置：

1. 我們要用 RMAN 的 duplicate from active database 命令建立備庫，需要配置靜態監聽和 TNS 名,黃色部分是我手動新增的靜態監聽，其餘部分為資料庫原來的動態監聽，兩節點都要新增：

[grid@srvrac2admin]$ cat listener.ora

LISTENER_TT=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TT)))) # line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_TT=ON # line added by Agent

LISTENER2 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.31)(PORT = 1522))

(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1522))

)

)

 

SID_LIST_LISTENER2 =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = test1)

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

(SID_NAME = test22)

)

)

啟動時要帶上監聽名：

[grid@srvrac2 admin]$ lsnrctl start LISTENER2

2. 新增tnsnames.ora

從庫節點1：

TEST1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test1)

)

)

TEST21 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.30)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test1)

)

)

從庫節點2：

TEST1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test1)

)

)

TEST21 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.31)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test1)

)

)

主庫：

test1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test1)

)

)

test21 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.30)(PORT = 1522))

(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.31)(PORT = 1522))

(LOAD_BALANCE = yes)

)

(CONNECT_DATA =

(SERVICE_NAME = test1)

(FAILOVER_MODE =

(TYPE = select)

(METHOD = basic)

(RETRIES = 200)

(DELAY = 5)

)

)

)

紅色的網路服務名對應配置log_archive_dest_2中的service

測試方法：

sqlplus sys/oracle@test21 as sysdba

3.

備庫環境準備

1. 建好了主庫的 pfile 後，將其複製到備庫伺服器的相同位置，使用備庫的 SID 修改其名字。需要對 pfile 做如下修改：

根據你備庫的配置和檔案位置，你可能需要修改AUDIT_FILE_DEST，CONTROL_FILES 和 DISPATCHERS 引數（也許還有其他需要修改的引數）。

LOG_ARCHIVE_DEST_1引數中的 db_unique_name 修改為備庫的相應唯一名（這裡是 JED2）。

LOG_ARCHIVE_DEST_2引數，修改為主庫對應的服務名和資料庫唯一名（這裡是 JED）。

FAL_SERVER引數修改指向主庫的服務名。

增加如下引數：

db_unique_name=JED2

altersystem set standby_file_management = 'AUTO';

db_file_name_convert和 log_file_name_convert。如果主備庫的資料檔案、日誌檔案位置不同，需要設定這兩個引數。

然後在備庫伺服器上建立所需目錄結構和修改相關檔案

2. 如：

[oracle@srvrac1dbs]$ cat test2_pfile.ora

test1.__db_cache_size=883027968

test1.__java_pool_size=23554432

test1.__large_pool_size=30331648

test1.__pga_aggregate_target=673741824

test1.__sga_target=922122547

test1.__shared_io_pool_size=0

test1.__shared_pool_size=203979776

test1.__streams_pool_size=11777216

*.audit_file_dest='/bee/app/oracle/admin/test2/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='+DATA/test2/controlfile/control01.ctl','+DATA/test2/controlfile/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/bee/app/oracle/oradata/test1/','+DATA/test2/datafile/'

*.log_file_name_convert='/bee/app/oracle/oradata/test1/','+DATA/test2/datafile/'

*.db_name='test1'

*.db_unique_name='test2'

*.diagnostic_dest='/bee/app/oracle'

*.dispatchers='(PROTOCOL=TCP)(SERVICE=test2XDB)'

*.fal_server='test1'

*.log_archive_config='dg_config=(test1,test2)'

*.log_archive_dest_1='location=+DATA/test2/archivelogvalid_for=(all_logfiles, all_roles) db_unique_name=test2'

*.log_archive_dest_2='service=test1async valid_for=(online_logfile,primary_role) db_unique_name=test1'

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.pga_aggregate_target=1072693248

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=3218079744

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

3. 啟動備庫export ORACLE_SID=test11

startup nomount pfile='/bee/app/oracle/product/11.2.0/db_1/dbs/test2_pfile.ora'

4. 建立備庫,在從庫 RMAN 恢復：

[oracle@srvrac1~]$ export ORACLE_SID=test11

[oracle@srvrac1 ~]$ rman target sys/oracle@test1 auxiliary sys/oracle@test21

 

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASENOFILENAMECHECK;

5. 啟動重做應用：

sql> alter database recover managed standbydatabase disconnect from session

或實時同步ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENTLOGFILE DISCONNECT FROM SESSION;

6. 測試：

在主庫更新：

SQL>insert into test.test_table values (17,'jhpcc');

 

1 rowcreated.

 

SQL>commit;

 

Commitcomplete.

 

SQL>alter system archive log current;

 

System altered.

在備庫檢視是否同步過來，分析問題看兩邊的alert.log

sql> alter database recover managed standbydatabase cancel;

sql>alter database open read only;

sql>SQL> select * from test.test_table;

 

ID NAME

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

 

17 jhpcc

7. 11g已經支援活動備庫，可以讓資料庫在只讀狀態下開啟，同時啟動日誌應用:

alter database recover managed standby databasedisconnect

 

8. createspfile='+DATA/cltdbhz1/spfilecltdbhz1.ora' from pfile='/bee/app/oracle/product/11.2.0/db_1/dbs/cltdbhz1_pfile.ora';

 

 

註冊第二個節點到 CRS

1. [oracle@srvrac2 dbs]$ catinittest22.ora

2. spfile='+DATA/test2/spfiletest2.ora

3. 從庫的第二個節點上， $ export ORACLE_SID=test22

$ sqlplus / as sysdba sql> startup mount;

4. srvctl add database -d test2 -ntest1 -o /bee/app/oracle/product/11.2.0/db_1 -p +DATA/test2/spfiletest2.ora -rphysical_standby -a DATA（oracle使用者執行）

5. srvctl add instance -d test2 -itest11 -n srvrac1

6. srvctl add instance -d test2 -itest22 -n srvrac2

7. srvctl start database -d test2

8. srvctl modify database -d test2-s mount

9. srvctl status database –d test2

 

在第三步startup mount時

由於引數檔案是從單例項的主庫生成的，作為RAC第二個節點啟動時會先後報了幾個錯：

問題1：

ORA-00304: requested INSTANCE_NUMBER isbusy

解決：

alter system set instance_number=1scope=spfile sid='test11';

alter system set instance_number=2scope=spfile sid='test22';

然後重啟

 

問題2：

ORA-01102: cannot mount database inEXCLUSIVE mode

解決:

SQL> show parameter cluster_databas

 

NAME TYPE VALUE

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

cluster_database boolean FALSE

cluster_database_instances integer 1

SQL> alter system setcluster_database=true scope=spfile;

alter system setcluster_database_instances=2 scope=spfile;

System altered.

 

select instance_name,status from gv$instance;

 

問題3：

ORA-01620: no public threads are availablefor mounting

Cause: The value of the initializationparameter THREAD is zero, its default value. There are no threads which havebeen publicly enabled, and not mounted.

 

Action: Shut down the instance, change thevalue of the initialization parameter to a thread which is privately enabledand not mounted. If the database is open in another instance, then a thread maybe publicly enabled.

 

 

 

SQL> show parameter thread

 

NAME TYPE VALUE

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

parallel_threads_per_cpu integer 2

thread integer 0

alter system set thread=1 scope=spfilesid='test11';

alter system set thread=2 scope=spfilesid='test22';

檢查狀態：

select * from V$DATAGUARD_STATUSorder by TIMESTAMP;

select STATUS, GAP_STATUS fromV$ARCHIVE_DEST_STATUS where DEST_ID = 2;

select SEQUENCE#, FIRST_TIME,NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG where name = 'test2' order byFIRST_TIME;

select DEST_ID, STATUS,DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID<=2;

 

Dataguard 主從切換

在 RAC 狀態下，主從資料庫的切換時主從庫只能有一個例項在執行狀態。在切換前需關掉其他節點，在切換完成後再啟動其他節點。查詢主從庫狀態

sql> select database_role,switchover_status from v$database;

將主庫切換到從庫狀態

sql> alter database commit to switchover to physical standbywithsession shutdown;

如果主庫狀態為 “sessions active”，則需要在命令後加上 with sessionshutdown 語句。狀態。

重起原資料庫到 mount 狀態。

SQL>shutdown immediate;

SQL>startup mount;

 

在原從庫上執行切換命令

sql> alter database commit to switchover to primary withsession shutdown;

SQL>alter database open;

完成主備切換後，在新備庫上啟用日誌應用：

 

SQL> alter database recover managed standby databaseusing current logfile disconnect from session;

 

[root@srvrac2~]# srvctl start instance -d test2 -i test22

PRCR-1013: 無法啟動資源ora.test2.db

PRCR-1064: 無法在節點 srvrac2上啟動資源ora.test2.db

CRS-5017:The resource action "ora.test2.db start" encountered the followingerror:

ORA-01618:redo thread 2 is not enabled - cannot mount

. Fordetails refer to "(:CLSN00107:)" in"/bee/app/11.2.0/grid/log/srvrac2/agent/crsd/oraagent_oracle/oraagent_oracle.log".

 

CRS-2674:Start of 'ora.test2.db' on 'srvrac2' failed

 

 

 

ORA-01618:redo thread 2 is not enabled - cannot mount

http://tiany.blog.51cto.com/513694/1415383

 

alterdatabase add logfile thread 2 ('+DATA') size 50m;

alterdatabase add logfile thread 2 ('+DATA') size 50m;

alterdatabase add logfile thread 2 ('+DATA') size 50m;

alterdatabase enable thread 2;

 

 

ORA-01092:ORACLE instance terminated. Disconnection forced

ORA-30013:undo tablespace 'UNDOTBS1' is currently in use

SQL>alter system set db_create_file_dest='+DATA';

SQL>create undo tablespace undotbs2;

altersystem set undo_tablespace='UNDOTBS1' scope=spfile sid='test11';

altersystem set undo_tablespace='UNDOTBS2' scope=spfile sid='test22';

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-2122427/，如需轉載，請註明出處，否則將追究法律責任。

相關文章