南方報業物流數據庫單機至RAC環境

準備工作 90分鍾

將生產數據庫網卡三，直連rac02網卡三，配制相同的IP地址。 10分鍾
停止生產系統的業務，並停止監聽服務. ==>> lsnrctl stop 10分鍾
在rac02上開啓samba服務並設置好對應的/home/samba共享目錄。 5分鍾
在生產數據庫上掛載rac02的共享目錄爲本地硬盤"z:"，做好映射關系。 3分鍾
修改生產數據庫中的expdp dump目錄. ==>> SQL> CREATE OR REPLACE DIRECTORY dump_dir AS 'z:\dump_dir\'; 2分鍾
在生產數據庫中導出fxwl&fxwlq兩個用戶的數據。 60分鍾

expdp system/system schemas=fxwlq directory=dump_dir dumpfile=expdp_FXWLQ_user.dmp logfile=expdp_FXWLQ_user.log
expdp system/system schemas=fxwl directory=dump_dir dumpfile=expdp_fxwl_user.dmp logfile=expdp_fxwl_user.log

執行工作 80分鍾

在rac02節點上執行數據庫導入工作。

# 首先清空兩個用戶的數據。 10分鍾

sqlplus / as sysdba
drop user fxwlq cascade;
drop user fxwl cascade;

CREATE OR REPLACE DIRECTORY dump_dir AS '/var/ftp/incoming/dump_dir';
exit

# 使用oracle用戶登錄執行. 70分鍾

impdp \"system as sysdba\" schemas=fxwlq directory=dump_dir dumpfile=EXPDP_FXWLQ_USER.DMP logfile=impdp_FXWLQ_user.log

impdp \"system as sysdba\" schemas=fxwl directory=dump_dir dumpfile=EXPDP_FXWL_USER.DMP logfile=impdp_fxwl_user.log remap_datafile='D:\ORACLE\ORADATA\FXWL\SYSTEM01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\SYSAUX01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\UNDOTBS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\USERS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD102.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD103.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD104.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD106.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD107.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_TEMP2101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_T_FXWL_STANDARD101.ORA':'+SSD_DATA'

後續任務（當前可選） 30分鍾

啓用FRA和Archivelog功能. 15分鍾

# 啓用FRA功能 5分鍾

ALTER SYSTEM SET db_recovery_file_dest_size=500G scope=both sid='*';
ALTER SYSTEM SET db_recovery_file_dest='+FRA' sid='*';

# 開啓歸檔 10分鍾

$ sqlplus / as sysdba
archive log list;

# 停止數據庫
srvctl stop database -d fxwl -o immediate

# 加載數據庫
srvctl start database -d fxwl -o mount

# 啓用歸檔
sqlplus / as sysdba
ALTER DATABASE ARCHIVELOG;

# 停止數據庫
srvctl stop database -d fxwl -o immediate

# 最後開啓數據庫
srvctl start database -d fxwl

sqlplus / as sysdba
SQL> archive log list;

配制自動刪除歸檔策略和腳本 5分鍾

http://www.hhutzler.de/blog/backup-and-recovery-for-rac-systems/

# ALTER SYSTEM SET log_archive_dest_1='location=+FRA' SCOPE=spfile;
# ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;

啓用jumbo frame支持 10分鍾

H3C S5120

system-view
interface range Gigabitethernet1/0/9 to Gigabitethernet1/0/12
jumboframe enable

ifcfg-bond1 MTU=9000

驗證工作 30分鍾

grid登錄執行檢查整體環境是否正常 ==> srvctl status resource -t
查看數據庫alert日志有無異常。
connect fxwl/fxwl

[grid@rac02 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dg
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.CRS.dg
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.FRA.dg
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.REDO.dg
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.SSD_DATA.dg
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.VOTE.dg
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.asm
               ONLINE  ONLINE       rac01                    Started             
               ONLINE  ONLINE       rac02                    Started             
ora.gsd
               OFFLINE OFFLINE      rac01                                        
               OFFLINE OFFLINE      rac02                                        
ora.net1.network
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.ons
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.registry.acfs
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac01                                        
ora.cvu
      1        ONLINE  ONLINE       rac01                                        
ora.fxwl.db
      1        ONLINE  ONLINE       rac02                    Open                
      2        ONLINE  ONLINE       rac01                    Open                
ora.oc4j
      1        ONLINE  ONLINE       rac01                                        
ora.rac01.vip
      1        ONLINE  ONLINE       rac01                                        
ora.rac02.vip
      1        ONLINE  ONLINE       rac02                                        
ora.scan1.vip
      1        ONLINE  ONLINE       rac01

 select tablespace_name from user_tablespaces; 
 select count(table_name) from user_tables; 

SQL> select tablespace_name from user_tablespaces; 

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
UNDOTBS2
EAS_D_FXWL_STANDARD
EAS_D_FXWL_TEMP2
EAS_T_FXWL_STANDARD

9 rows selected.

SQL> select count(table_name) from user_tables; 

COUNT(TABLE_NAME)
-----------------
             4432

应用程序连接群集配制

连接配置写成如下格式即可，具体写法如下

FAILOVER启用SELECT模式(TYPE=SELECT)

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=YES)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.217)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SPECTRA))(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=77)(DELAY=5))

FAILOVER启用SESSION模式(TYPE=SESSION)

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=YES)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.217)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SPECTRA))(FAILOVER_MODE=(TYPE=SESSION)(METHOD=BASIC)(RETRIES=77)(DELAY=5))

-  ⇤ ← Revision 3 as of 2016-11-04 15:55:20 → 
  Size: 2250
  Editor: localhost
  Comment:
+   ← Revision 39 as of 2016-11-05 06:41:48 → ⇥
  Size: 8289
  Editor: localhost
  Comment:
-Deletions are marked like this.
+Additions are marked like this.
 Line 2:
+<<TableOfContents()>>
-Line 4:
+Line 5:
-== 準備工作 ==
 I. 將生產數據庫網卡三，直連rac02網卡三，配制相同的IP地址。   10分鍾
+== 準備工作 90分鍾 ==
 I. 將生產數據庫網卡三，直連rac02網卡三，配制相同的IP地址。         10分鍾
-Line 8:
+Line 9:
- I. 在生產數據庫上掛載rac02的共享目錄爲本地硬盤"z:"，做好映射關系。 2分鍾
+ I. 在生產數據庫上掛載rac02的共享目錄爲本地硬盤"z:"，做好映射關系。 3分鍾
-Line 16:
+Line 17:
-== 執行工作 ==
+== 執行工作 80分鍾 ==
-Line 18:
+Line 19:
+# 首先清空兩個用戶的數據。      10分鍾
-Line 19:
+Line 21:
-# 首先清空兩個用戶的數據。      10分鍾
-Line 24:
+Line 25:
-CREATE OR REPLACE DIRECTORY dump_dir AS '/home/samba';
+CREATE OR REPLACE DIRECTORY dump_dir AS '/var/ftp/incoming/dump_dir';
-Line 26:
+Line 27:
+}}}
# 使用oracle用戶登錄執行.       70分鍾
{{{
impdp \"system as sysdba\" schemas=fxwlq directory=dump_dir dumpfile=EXPDP_FXWLQ_USER.DMP logfile=impdp_FXWLQ_user.log
-Line 27:
+Line 32:
-# 使用oracle用戶登錄執行.       60分鍾
impdp \"system as sysdba\" schemas=fxwlq directory=dump_dir dumpfile=expdp_FXWLQ_user.dmp logfile=impdp_FXWL_user.log
+impdp \"system as sysdba\" schemas=fxwl directory=dump_dir dumpfile=EXPDP_FXWL_USER.DMP logfile=impdp_fxwl_user.log remap_datafile='D:\ORACLE\ORADATA\FXWL\SYSTEM01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\SYSAUX01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\UNDOTBS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\USERS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD102.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD103.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD104.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD106.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD107.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_TEMP2101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_T_FXWL_STANDARD101.ORA':'+SSD_DATA'
}}}
-Line 30:
+Line 35:
-[ oracle $]  impdp \"system as sysdba\" schemas=fxwl directory=dump_dir dumpfile=expdp_fxwl_user.dmp logfile=impdp_fxwl_user.log remap_datafile='D:\ORACLE\ORADATA\FXWL\SYSTEM01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\SYSAUX01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\UNDOTBS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\USERS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD102.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD103.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD104.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD106.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD107.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_TEMP2101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_T_FXWL_STANDARD101.ORA':'+SSD_DATA'
+== 後續任務（當前可選） 30分鍾 ==
=== 啓用FRA和Archivelog功能. 15分鍾 ===

# 啓用FRA功能 5分鍾
{{{
ALTER SYSTEM SET db_recovery_file_dest_size=500G scope=both sid='*';
ALTER SYSTEM SET db_recovery_file_dest='+FRA' sid='*';
-Line 32:
+Line 43:
+# 開啓歸檔 10分鍾
{{{
$ sqlplus / as sysdba
archive log list;

# 停止數據庫
srvctl stop database -d fxwl -o immediate

# 加載數據庫
srvctl start database -d fxwl -o mount

# 啓用歸檔
sqlplus / as sysdba
ALTER DATABASE ARCHIVELOG;

# 停止數據庫
srvctl stop database -d fxwl -o immediate

# 最後開啓數據庫
srvctl start database -d fxwl

sqlplus / as sysdba
SQL> archive log list;
}}}

=== 配制自動刪除歸檔策略和腳本      5分鍾 ===
 http://www.hhutzler.de/blog/backup-and-recovery-for-rac-systems/
{{{
# ALTER SYSTEM SET log_archive_dest_1='location=+FRA' SCOPE=spfile;
# ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;
}}}


=== 啓用jumbo frame支持  10分鍾 ===
 i. H3C S5120 
{{{
system-view
interface range Gigabitethernet1/0/9 to Gigabitethernet1/0/12
jumboframe enable
}}}
 i. ifcfg-bond1
 MTU=9000


== 驗證工作 30分鍾 ==
 I. grid登錄執行檢查整體環境是否正常 ==> srvctl status resource -t
 I. 查看數據庫alert日志有無異常。
 I. connect fxwl/fxwl
{{{
[grid@rac02 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dg
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.CRS.dg
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.FRA.dg
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.REDO.dg
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.SSD_DATA.dg
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.VOTE.dg
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.asm
               ONLINE  ONLINE       rac01                    Started             
               ONLINE  ONLINE       rac02                    Started             
ora.gsd
               OFFLINE OFFLINE      rac01                                        
               OFFLINE OFFLINE      rac02                                        
ora.net1.network
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.ons
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
ora.registry.acfs
               ONLINE  ONLINE       rac01                                        
               ONLINE  ONLINE       rac02                                        
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac01                                        
ora.cvu
      1        ONLINE  ONLINE       rac01                                        
ora.fxwl.db
      1        ONLINE  ONLINE       rac02                    Open                
      2        ONLINE  ONLINE       rac01                    Open                
ora.oc4j
      1        ONLINE  ONLINE       rac01                                        
ora.rac01.vip
      1        ONLINE  ONLINE       rac01                                        
ora.rac02.vip
      1        ONLINE  ONLINE       rac02                                        
ora.scan1.vip
      1        ONLINE  ONLINE       rac01                               
}}}

{{{
 select tablespace_name from user_tablespaces; 
 select count(table_name) from user_tables; 

SQL> select tablespace_name from user_tablespaces; 

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
UNDOTBS2
EAS_D_FXWL_STANDARD
EAS_D_FXWL_TEMP2
EAS_T_FXWL_STANDARD

9 rows selected.

SQL> select count(table_name) from user_tables; 

COUNT(TABLE_NAME)
-----------------
             4432

}}}


== 应用程序连接群集配制 ==
连接配置写成如下格式即可，具体写法如下

=== FAILOVER启用SELECT模式(TYPE=SELECT) ===
{{{
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=YES)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.217)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SPECTRA))(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=77)(DELAY=5))
}}}
=== FAILOVER启用SESSION模式(TYPE=SESSION) ===
{{{
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=YES)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.217)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SPECTRA))(FAILOVER_MODE=(TYPE=SESSION)(METHOD=BASIC)(RETRIES=77)(DELAY=5))
}}}

Diff for "首頁/2016-11-05"

南方報業物流數據庫單機至RAC環境

準備工作 90分鍾

執行工作 80分鍾

後續任務（當前可選） 30分鍾

啓用FRA和Archivelog功能. 15分鍾

配制自動刪除歸檔策略和腳本 5分鍾

啓用jumbo frame支持 10分鍾

驗證工作 30分鍾

应用程序连接群集配制

FAILOVER启用SELECT模式(TYPE=SELECT)

FAILOVER启用SESSION模式(TYPE=SESSION)