Differences between revisions 2 and 45 (spanning 43 versions)
Revision 2 as of 2016-11-04 15:55:01
Size: 2256
Editor: localhost
Comment:
Revision 45 as of 2016-11-05 12:02:31
Size: 9778
Editor: localhost
Comment:
Deletions are marked like this. Additions are marked like this.
Line 2: Line 2:
<<TableOfContents()>>
Line 4: Line 5:
== 準備工作 ==
 I. 將生產數據庫網卡三,直連rac01/rac02網卡三,配制相同的IP地址。 10分鍾
== 準備工作 90分鍾 ==
 I. 將生產數據庫網卡三,直連rac02網卡三,配制相同的IP地址。       10分鍾
Line 7: Line 8:
 I. 在rac02上開啓samba服務並設置好對應的/home/samba共享目錄。 5分鍾
 I. 在生產數據庫上掛載rac02的共享目錄爲本地硬盤"z:",做好映射關系。 2分鍾
 I. 修改生產數據庫中的expdp dump目錄. ==>> SQL> CREATE OR REPLACE DIRECTORY dump_dir AS 'z:\dump_dir\'; 2分鍾
 I. 在生產數據庫中導出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
}}}

== 執行工作 ==
 I. 修改生產數據庫中的expdp dump目錄. ==>> SQL> CREATE OR REPLACE DIRECTORY dump_dir AS 'e:\dump_dir\'; 2分鍾
 I. 在生產數據庫中導出fxwl&fxwlq兩個用戶的數據,通过FTP上传至rac02。60分鍾
{{{
 User (10.77.77.11:(none)): anonymous
 331 Please specify the password.
 Password:
 230 Login successful.
 ftp> ls
 200 PORT command successful. Consider using PASV.
 150 Here comes the directory listing.
 incoming
 pub
 226 Directory send OK.
 ftp: 15 bytes received in 0.02Seconds 0.94Kbytes/sec.
 ftp> cd incoming
 250 Directory successfully changed.
 ftp> ls
 200 PORT command successful. Consider using PASV.
 150 Here comes the directory listing.
 dump_dir
 226 Directory send OK.
 ftp: 10 bytes received in 0.02Seconds 0.63Kbytes/sec.
 ftp> put E:\oraclebackup\EXPDP_FXWL_USER.DMP
 200 PORT command successful. Consider using PASV.
 150 Ok to send data.
 226 Transfer complete.
 ftp: 60190834688 bytes sent in 1199.44Seconds 50182.53Kbytes/sec.
}}}

{{{
# 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

expdp system/system dumpfile=expdp_FXWLQ_user.dmp logfile=expdp_FXWLQ_user.log directory=EASBK schemas=fxwlq parallel=2 version=11.2.0.1.0
expdp system/system dumpfile=expdp_FXWL_user.dmp logfile=expdp_FXWL_user.log directory=dump_dir schemas=fxwl parallel=2 version=11.2.0.1.0
}}}

== 執行工作 80分鍾 ==
Line 18: Line 47:
{{{
Line 20: Line 48:
{{{
Line 24: Line 53:
CREATE OR REPLACE DIRECTORY dump_dir AS '/home/samba'; CREATE OR REPLACE DIRECTORY dump_dir AS '/var/ftp/incoming/dump_dir';
Line 26: Line 55:

# 使用oracle用戶登錄執行. 60分鍾
impdp \"system as sysdba\" schemas=fxwlq directory=dump_dir dumpfile=expdp_FXWLQ_user.dmp logfile=impdp_FXWL_user.log

[ 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'
}}}
}}}
# 使用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分鍾 ===
 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))
}}}

== processes setting ==
{{{
# processes=x sessions=x*1.1+5 transactions=sessions*1.1

select name, value from v$parameter where name in ('sessions','processes','transactions');

alter system set processes=6000 scope=spfile sid='*';
alter system set sessions=6655 scope=spfile sid='*';
alter system set transactions=7300 scope=spfile sid='*';
}}}

== Configuring a Redundancy-Based Retention Policy ==
{{{
rman target /
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+FRA';
}}}

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

準備工作 90分鍾

  1. 將生產數據庫網卡三,直連rac02網卡三,配制相同的IP地址。 10分鍾
  2. 停止生產系統的業務,並停止監聽服務. ==>> lsnrctl stop 10分鍾

  3. 修改生產數據庫中的expdp dump目錄. ==>> SQL> CREATE OR REPLACE DIRECTORY dump_dir AS 'e:\dump_dir\'; 2分鍾

  4. 在生產數據庫中導出fxwl&fxwlq兩個用戶的數據,通过FTP上传至rac02。60分鍾

 User (10.77.77.11:(none)): anonymous
 331 Please specify the password.
 Password:
 230 Login successful.
 ftp> ls
 200 PORT command successful. Consider using PASV.
 150 Here comes the directory listing.
 incoming
 pub
 226 Directory send OK.
 ftp: 15 bytes received in 0.02Seconds 0.94Kbytes/sec.
 ftp> cd incoming
 250 Directory successfully changed.
 ftp> ls
 200 PORT command successful. Consider using PASV.
 150 Here comes the directory listing.
 dump_dir
 226 Directory send OK.
 ftp: 10 bytes received in 0.02Seconds 0.63Kbytes/sec.
 ftp> put E:\oraclebackup\EXPDP_FXWL_USER.DMP
 200 PORT command successful. Consider using PASV.
 150 Ok to send data.
 226 Transfer complete.
 ftp: 60190834688 bytes sent in 1199.44Seconds 50182.53Kbytes/sec.

# 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

expdp system/system dumpfile=expdp_FXWLQ_user.dmp logfile=expdp_FXWLQ_user.log directory=EASBK schemas=fxwlq parallel=2 version=11.2.0.1.0
expdp system/system dumpfile=expdp_FXWL_user.dmp logfile=expdp_FXWL_user.log directory=dump_dir schemas=fxwl parallel=2 version=11.2.0.1.0

執行工作 80分鍾

  1. 在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分鍾

# 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分鍾

  1. H3C S5120

system-view
interface range Gigabitethernet1/0/9 to Gigabitethernet1/0/12
jumboframe enable
  1. ifcfg-bond1 MTU=9000

驗證工作 30分鍾

  1. grid登錄執行檢查整體環境是否正常 ==> srvctl status resource -t

  2. 查看數據庫alert日志有無異常。
  3. 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))

processes setting

#  processes=x sessions=x*1.1+5 transactions=sessions*1.1 

select name, value from v$parameter where name in ('sessions','processes','transactions');

alter system set processes=6000 scope=spfile sid='*';
alter system set sessions=6655 scope=spfile sid='*';
alter system set transactions=7300 scope=spfile sid='*';

Configuring a Redundancy-Based Retention Policy

rman target /
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+FRA';

首頁/2016-11-05 (last edited 2018-07-05 09:28:23 by merlyn)