|
Size: 4445
Comment:
|
← Revision 48 as of 2018-07-05 09:28:23 ⇥
Size: 11336
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 4: | Line 4: |
| = 南方報業物流數據庫單機至RAC環境 = | = lfby南方報業物流數據庫單機至RAC環境 = |
| Line 8: | Line 8: |
| I. 在rac02上開啓samba服務並設置好對應的/home/samba共享目錄。 5分鍾 I. 在生產數據庫上掛載rac02的共享目錄爲本地硬盤"z:"，做好映射關系。 3分鍾 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 |
| Line 40: | Line 68: |
| ALTER SYSTEM SET db_recovery_file_dest_size=500G scope=both sid='*' ; | ALTER SYSTEM SET db_recovery_file_dest_size=500G scope=both sid='*'; |
| Line 92: | Line 120: |
| [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 }}} {{{ |
|
| Line 94: | Line 185: |
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 |
|
| Line 99: | Line 213: |
| === FAILOVER启用SELECT模式(TYPE=SELECT) === | === FAILOVER启用SELECT模式(TYPE=SELECT) === |
| Line 107: | Line 222: |
== 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'; }}} {{{ *********************************************************************** Fatal NI connect error 12170. VERSION INFORMATION: TNS for Linux: Version 11.2.0.4.0 - Production Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production Time: 05-NOV-2016 19:57:48 Tracing not turned on. Tns error struct: ns main err code: 12535 TNS-12535: TNS:operation timed out ns secondary err code: 12560 nt main err code: 505 TNS-00505: Operation timed out nt secondary err code: 110 nt OS err code: 0 Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.10.39)(PORT=57298)) Sat Nov 05 19:59:55 2016 *********************************************************************** Fatal NI connect error 12170. VERSION INFORMATION: TNS for Linux: Version 11.2.0.4.0 - Production Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production Time: 05-NOV-2016 19:59:55 Tracing not turned on. Tns error struct: ns main err code: 12535 TNS-12535: TNS:operation timed out ns secondary err code: 12560 nt main err code: 505 TNS-00505: Operation timed out nt secondary err code: 110 nt OS err code: 0 Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.10.39)(PORT=60528)) Sat Nov 05 20:15:26 2016 ALTER SYSTEM ARCHIVE LOG }}} |
Contents
lfby南方報業物流數據庫單機至RAC環境
準備工作 90分鍾
- 將生產數據庫網卡三，直連rac02網卡三，配制相同的IP地址。 10分鍾
停止生產系統的業務，並停止監聽服務. ==>> lsnrctl stop 10分鍾
修改生產數據庫中的expdp dump目錄. ==>> SQL> CREATE OR REPLACE DIRECTORY dump_dir AS 'e:\dump_dir\'; 2分鍾
在生產數據庫中導出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分鍾
- 在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分鍾
- 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))
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';
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Time: 05-NOV-2016 19:57:48
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 110
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.10.39)(PORT=57298))
Sat Nov 05 19:59:55 2016
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Time: 05-NOV-2016 19:59:55
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 110
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.10.39)(PORT=60528))
Sat Nov 05 20:15:26 2016
ALTER SYSTEM ARCHIVE LOG