賈斯汀：IT技術隨手筆記: 8月 2016

環境：
RAC(以下稱TARGET端)環境下兩節點HOSTNAME，IP ADDRESS等相關設定如下
# Public
192.168.100.110 racnode01.dba.local racnode01
192.168.100.120 racnode02.dba.local racnode02
# Private
10.0.0.110 racnode01-priv.dba.local racnode01-priv
10.0.0.120 racnode02-priv.dba.local    racnode02-priv
# Virtual
192.168.100.111 racnode01-vip.dba.local racnode01-vip
192.168.100.121 racnode02-vip.dba.local racnode02-vip
# SCAN
192.168.100.101 racnode.dba.local racnode
192.168.100.102 racnode.dba.local racnode
192.168.100.103 racnode.dba.local racnode

單主機(以下稱SOURCE端)HOSTNAME，IP ADDRESS等相關設定如下
192.168.100.50 oggs.dba.local

STORAGE(DNS)主機相關設定
192.168.100.10 racstorage.dba.local

設定程序如下：
在SOURCE端
建立GOLDENGATE安裝目錄
      [oracle@oggs ~]$ mkdir /u01/app/goldengate
      [oracle@oggs ~]$ mkdir /u01/app/goldengate/ogg
將p17952585_1121017_Linux-x86-64.zip解壓縮到/u01/app/goldengate/ogg路徑下
      [oracle@oggs ~]$ unzip p17952585_1121017_Linux-x86-64.zip
      [oracle@oggs ~]$ tar –xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

在TARGET端
建立GOLDENGATE安裝目錄(此路徑為ACFS，好處是當一個節點有問題時，另一個節點也可以使用，設定方式會有另一篇文章教學)
      [oracle@racnode01 ogg]$ mkdir /u01/app/goldengate
      [oracle@racnode01 ogg]$ mkdir /u01/app/goldengate/ogg

將p17952585_1121017_Linux-x86-64.zip解壓縮到/u01/app/goldengate/ogg路徑下
      [oracle@racnode01 ogg]$ unzip p17952585_1121017_Linux-x86-64.zip
      [oracle@racnode01 ogg]$ tar –xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

在SOURCE端
建立goldengate工作目錄
       [oracle@oggs ogg]$ ./ggsci
       GGSCI (oggs.dba.local) 2> create subdirs

在TARGET端
建立goldengate工作目錄
        [oracle@racnode01 ogg]$ ./ggsci
        GGSCI (racnode01.dba.local) 1> create subdirs

在SOURCE端
建立GOLDENGATE的tablespace，user及權限
       [oracle@oggs ogg]$ sqlplus / as sysdba
       SQL> create tablespace ogg_data datafile '/oradata/ogg_data_01.dbf' size 2048M AUTOEXTEND ON;
      SQL> CREATE USER OGG
                 IDENTIFIED BY ogg
                 DEFAULT TABLESPACE ogg_data
                 TEMPORARY TABLESPACE TEMP
                 PROFILE DEFAULT
                 ACCOUNT UNLOCK;
      SQL> GRANT CONNECT TO OGG;
      SQL> GRANT RESOURCE TO OGG;
      SQL> GRANT DBA TO OGG;
      SQL> ALTER USER OGG DEFAULT ROLE ALL;
      SQL> GRANT CREATE ANY SYNONYM TO OGG;
      SQL> GRANT UNLIMITED TABLESPACE TO OGG;
      SQL> GRANT CREATE SYNONYM TO OGG WITH ADMIN OPTION;
      SQL> ALTER USER OGG QUOTA UNLIMITED ON ogg_data;
      SQL> grant create any table to ogg;
      SQL> grant create any view to ogg;
      SQL> grant create any procedure to ogg;
      SQL> grant create any sequence to ogg;
      SQL> grant create any index to ogg;
      SQL> grant create any trigger to ogg;

在SOURCE端
啟用supplemental log
        SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
        SQL> ALTER SYSTEM SWITCH LOGFILE;

在SOURCE端
在OGG的目錄下登入資料庫，設定並啟用DDL Replication
        [oracle@oggs ogg]$ sqlplus / as sysdba
        SQL> @marker_setup
               --在Enter Oracle GoldenGate schema name輸入[ogg]
        SQL> @ddl_setup
               --Enter Oracle GoldenGate schema name輸入[ogg]
        SQL> @role_setup
               --Enter GoldenGate schema name輸入[ogg]
        SQL> GRANT GGS_GGSUSER_ROLE TO ogg;
        SQL> @ddl_enable
        SQL> @ddl_pin ogg
        SQL> @SEQUENCE

在TARGET端
建立GOLDENGATE的tablespace，user及權限
        SQL> create tablespace ogg_data datafile '+DATA01' size 2048M AUTOEXTEND ON;
        SQL> CREATE USER OGG
                   IDENTIFIED BY ogg
                   DEFAULT TABLESPACE ogg_data
                   TEMPORARY TABLESPACE TEMP
                   PROFILE DEFAULT
                   ACCOUNT UNLOCK;
        SQL> GRANT CONNECT TO OGG;
        SQL> GRANT RESOURCE TO OGG;
        SQL> GRANT DBA TO OGG;
        SQL> ALTER USER OGG DEFAULT ROLE ALL;
        SQL> GRANT CREATE ANY SYNONYM TO OGG;
        SQL> GRANT UNLIMITED TABLESPACE TO OGG;
        SQL> GRANT CREATE SYNONYM TO OGG WITH ADMIN OPTION;
        SQL> ALTER USER OGG QUOTA UNLIMITED ON ogg_data;
        SQL> grant create any table to ogg;
        SQL> grant create any view to ogg;
        SQL> grant create any procedure to ogg;
        SQL> grant create any sequence to ogg;
        SQL> grant create any index to ogg;
        SQL> grant create any trigger to ogg;
        SQL> grant create any view to ogg;

在TARGET端
啟用supplemental log
        SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
        SQL> ALTER SYSTEM SWITCH LOGFILE;

在TARGET端
在OGG的目錄下登入資料庫，設定並啟用DDL Replication
       [oracle@racnode01 ogg]$ sqlplus / as sysdba
       SQL> @marker_setup
                 --在Enter Oracle GoldenGate schema name輸入[ogg]
       SQL> @ddl_setup
                --Enter Oracle GoldenGate schema name輸入[ogg]
       SQL> @role_setup
               --Enter GoldenGate schema name輸入[ogg]
       SQL> GRANT GGS_GGSUSER_ROLE TO ogg;
       SQL> @ddl_enable
       SQL> @ddl_pin ogg
       SQL> @SEQUENCE

在SOURCE端
啟用transaction data change capture
        [oracle@oggs ogg]$ ./ggsci
        GGSCI (oggs.dba.local) 1> dblogin userid ogg@orcl Password ogg
        GGSCI (oggs.dba.local) 2> add checkpointtable ogg.chktab
        GGSCI (oggs.dba.local) 3> add trandata OD_CAS.*   代表OD_CAS SCHEMA下都要同步
驗證補充日誌已經在那些TABLE打開
        GGSCI (oggs.dba.local) 4> info trandata OD_CAS.*
        SQL> select table_name from dba_tables where owner='OD_CAS' minus select table_name from dba_log_groups where owner='OD_CAS';

                   no rows selected

在TARGET端
啟用transaction data change capture
        [oracle@racnode01 ogg]$ ./ggsci
        GGSCI (racnode01.dba.local) 5> dblogin userid ogg@orcl Password ogg
        GGSCI (racnode01.dba.local) 6> add checkpointtable ogg.chktab

在SOURCE端
配置Goldengate Manager Process
        [oracle@oggs ogg]$ ./ggsci
        GGSCI (oggs.dba.local) 1> edit params mgr
             PORT 7809
             DYNAMICPORTLIST 7810-7820, 7830
             userid ogg@orcl password ogg
             --AUTOSTART ER *
             --AUTORESTART ER *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60
             STARTUPVALIDATIONDELAY 5
             PURGEOLDEXTRACTS /u01/app/goldengate/ogg/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
             PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5
             PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5
啟動Manager
        GGSCI (oggs.dba.local) 2> start mgr

在TARGET端
配置Goldengate Manager Process
        [oracle@racnode01 ogg]$ ./ggsci
        GGSCI (racnode01.dba.local) 2> edit params mgr
             PORT 7809
             DYNAMICPORTLIST 7810-7820, 7830
             userid ogg@orcl password ogg
            --AUTOSTART ER *
            --AUTORESTART ER *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60
            STARTUPVALIDATIONDELAY 5
            PURGEOLDEXTRACTS /u01/app/goldengate/ogg/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
            PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5
           PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5
啟動Manager
       GGSCI (racnode01.dba.local) 3> start mgr

在SOURCE端
配置EXTRACT
        GGSCI (oggs.dba.local) 2> edit params e_odcas
              extract e_odcas
              setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
              setenv (ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/dbhome_1")
              --setenv (ORACLE_SID="orcl")
              userid ogg@orcl, password ogg
              --tranlogoptions dblogreader asmuser sys@sam2, asmpassword sss433
              --tranlogoptions altarchivelogdest threadid 1 F:\oracle\oradata\Archivelog,
              --altarchivelogdest threadid 2 F:\oracle\oradata\Archivelog
            exttrail /u01/app/goldengate/ogg/dirdat/E1
              ddl include mapped;
              ddloptions ADDTRANDATA, RETRYOP retrydelay 10 maxretries 10, getreplicates
              discardfile /u01/app/goldengate/ogg/dirrpt/e_odcas.dis, purge, megabytes 10
              dboptions ALLOWUNUSEDCOLUMN
              FETCHOPTIONS FETCHPKUPDATECOLS

              table od_cas.*;
              SEQUENCE od_cas.*

在SOURCE端
新增一個Extract group
       GGSCI (oggs.dba.local) 3> add extract e_odcas, tranlog, begin now
            --P.S.threads參數是指RAC節點數 add extract e_odcas, tranlog, threads 2, begin now

在SOURCE端
定義e_odcas trail file路徑
        GGSCI (oggs.dba.local) 4> add exttrail /u01/app/goldengate/ogg/dirdat/E1, extract e_odcas, megabytes 100

在SOURCE端
配置 pump process group
      GGSCI (oggs.dba.local) 5> edit params p_odcas
            extract p_odcas
            setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
            setenv (ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/dbhome_1")
            rmthost racnode.dba.local, mgrport 7809
            rmttrail /u01/app/goldengate/ogg/dirdat/R1
            passthru

           table od_cas.*;
           SEQUENCE od_cas.*

在SOURCE端
新增一組data pump名稱為p_odcas
       GGSCI (oggs.dba.local) 6> add extract p_odcas, exttrailsource /u01/app/goldengate/ogg/dirdat/E1, begin now

在SOURCE端
將p_odcas加入remote trail
       GGSCI (oggs.dba.local) 7> add rmttrail /u01/app/goldengate/ogg/dirdat/R1, extract p_odcas, megabytes 200

在SOURCE端
啟動extract及pump
        GGSCI (oggs.dba.local) 8> start extract e_odcas
        GGSCI (oggs.dba.local) 9> start extract p_odcas

在TARGET端配置replicat
        GGSCI (racnode01.dba.local) 5> edit params r_odcas
              replicat r_odcas
             setenv (ORACLE_HOME= "/u01/app/oracle/product/11.2.0.4/dbhome_1")
             setenv (ORACLE_SID=orcl)
             setenv (NLS_LANG="AMERICAN_TAIWAN.AL32UTF8")
             userid ogg@orcl, password ogg
             assumetargetdefs
             APPLYNOOPUPDATES
             BATCHSQL
             --reperror (default,discard)
             --reperror 1403, ignore
            discardfile /u01/app/goldengate/ogg/dirrpt/r_odcas.dsc,purge,megabytes 100
           map od_cas.*, target od_cas.*;

在TARGET端
新增一個Replicat process
        GGSCI (racnode01.dba.local) 6> add replicat r_odcas, exttrail /u01/app/goldengate/ogg/dirdat/R1, checkpointtable ogg.chktab

在SOURCE端初始化資料(Initial Load)
取得SOURCE端資料庫SCN
        SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;
                  GET_SYSTEM_CHANGE_NUMBER
                  ------------------------
                        1016207

在SOURCE端匯出資料
         expdp system/Aa1234567@orcl directory=dumpfile dumpfile=exp_full.dmp logfile=exp_full.log schemas=od_cas FLASHBACK_SCN=1016207

在TARGET端匯入
         impdp system/Aa1234567@orcl1 directory=dumpfile dumpfile=exp_full.dmp logfile=imp_full.log

在TARGET端
啟動replicat processes
start replicat R_ODCAS, aftercsn 1016207

檢查GOLDENGATE是否正常
檢查重點
1. 所有服務的STATUS皆須為RUNNING
2. Lag at Chkpt需小於10秒
3. Time Since Chkpt 需小於10秒
在SOURCE端
         GGSCI (oggs.dba.local) 6> info all
              Program           Status              Group            Lag at Chkpt    Time Since Chkpt
              MANAGER    RUNNING
              EXTRACT      RUNNING     E_ODCAS     00:00:00           00:00:01
              EXTRACT      RUNNING     P_ODCAS     00:00:00           00:00:03

在TARGET端
         GGSCI (racnode01.dba.local) 5> info all
              Program           Status              Group             Lag at Chkpt    Time Since Chkpt
              MANAGER     RUNNING
              REPLICAT      RUNNING     R_ODCAS     00:00:00           00:00:02

說明：
1. 從AL32UTF8轉換為ZHT16BIG5
2. NODE01的INSTANCE為orcl1(主要操作主機，重點在alter system set cluster_database=false scope=spfile sid='orcl1';)
3. NODE01的INSTANCE為orcl1
4. NODE02的INSTANCE為orcl2
5. 別用srvctl語法下上DB

在NODE01上
[oracle@racnode01 ~]$ sqlplus / as sysdba

SQL> select userenv('language') from dual;
         USERENV('LANGUAGE')
          ----------------------------------------------------
          AMERICAN_AMERICA.AL32UTF8

SQL> select instance_name from v$instance;
          INSTANCE_NAME
          ----------------
          orcl1

SQL> alter system set cluster_database=false scope=spfile sid='orcl1';

SQL> shutdown immediate;

在NODE02上
[oracle@racnode02 ~]$ sqlplus / as sysdba

SQL> select instance_name from v$instance;
          INSTANCE_NAME
          ----------------
          orcl2

SQL> shutdown immediate;

在NODE01上
SQL> startup nomount;

SQL> Alter database mount exclusive;

SQL> Alter system enable restricted session;

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

SQL> Alter database open;

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHT16BIG5;

SQL> alter system set cluster_database=true scope=spfile sid='orcl1';

SQL> shutdown immediate;

SQL> startup;

SQL> select userenv('language') from dual;
         USERENV('LANGUAGE')
         ----------------------------------------------------
         AMERICAN_AMERICA.ZHT16BIG5

在NODE02上
SQL> startup;

SQL> select userenv('language') from dual;
         USERENV('LANGUAGE')
          ----------------------------------------------------
         AMERICAN_AMERICA.ZHT16BIG5

賈斯汀：IT技術隨手筆記

2016年8月16日星期二

[ORACLE] ORA-01114 IO error writing block to file

2016年8月15日星期一

[ORACLE] ORACLE的RAC環境下的連接管理(此篇文章來自Oracle Technology Network)

[ORACLE] 安裝設定GOLDENGATE從單機到RAC

2016年8月9日星期二

[ORACLE] 在RAC環境下變更資料庫端的CHARACTER(字符集)

2016年8月4日星期四

[ORACLE] 建立sequence Part II

2016年8月3日星期三

[ORACLE] ORA-00600：內部錯誤代碼，參數：[qks3tAssert:1]，[35072]

2016年8月1日星期一

[ORACLE] 批次expdp卡住，在expdp log中出現ORA-31693，ORA-02354，ORA-01555的錯誤訊息

2016年8月16日 星期二

2016年8月15日 星期一

2016年8月9日 星期二

2016年8月4日 星期四

2016年8月3日 星期三