SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL>
--linux:
orapwd file=/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/orapwtardb password=orcL#2016 entries=5 force=y;
scp 172.16.28.40:/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/orapwtardb /home/oracle/app/oracle/product/12.1.0/db_1/dbs
shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list;
啟用自動歸檔
Alter system set log_archive_start=true scope=spfile
參看參數
SQL> show parameter cluster_database
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
在一台主機上執行：
alter system set cluster_database=false scope=spfile sid='*';
在兩台主機上執行：
shutdown immediate
在一台主機上執行：
startup mount（必需要等2台機器同時shutdown完畢即可）
alter database archivelog;
alter database open;
alter system set cluster_database=true scope=spfile sid='*';
shutdown immediate;
在兩台主機上執行：
Startup
此時修改完畢即可啟用歸檔，關閉歸檔方法類似。
vi initauxdb.ora
*.audit_file_dest='/home/oracle/app/oracle/admin/auxdb/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/home/oracle/app/oracle/oradata/auxdb/control01.ctl','/home/oracle/app/oracle/oradata/auxdb/control02.ctl'
*.db_block_size=8192
*.db_name='auxdb'
db_file_name_convert=('+DATA','/home/oracle/app/oracle/oradata/','/oracle/app/oracle/oradata/oradb/','/home/oracle/app/oracle/oradata/oradb/')
log_file_name_convert=('+DATA','/home/oracle/app/oracle/fast_recovery_area/','+FLASH','/home/oracle/app/oracle/fast_recovery_area/')
mkdir -p auxdb/adump
mkdir -p auxdb/datafile
mkdir -p auxdb/tempfile
mkdir -p auxdb/tablespaces
mkdir -p auxdb/onlinelog
(SID_DESC =
(GLOBAL_DBNAME = auxdb)
(ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0/db_1)
(SID_NAME = auxdb)
)
sqlplus / as sysdba
startup nomount pfile='/home/oracle/app/oracle/product/12.1.0/db_1/dbs/initauxdb.ora';
rman target sys/[email protected]/tardb auxiliary sys/[email protected]/auxdb
duplicate target database to auxdb from active database ;
duplicate target database to auxdb from active database nofilenamecheck;
ALTER system SET sga_max_size=8g scope=spfile;
ALTER system SET sga_target=5g scope=spfile;
alter system set shared_pool_size=2g scope=spfile;
alter system set open_cursors=1000 scope=both;
---------------------------------------------------------------------------------------------------
--ggs
---------------------------------------------------------------------------------------------------
-- source 、target 端執行
1.1 設定資料庫為歸檔模式
資料庫是否處于歸檔模式：
archive log list;
Select log_mode from v$database;
開啟歸檔模式：
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
1.2 開啟資料庫附加日志
1) 檢查附加日志情況
使用以下sql語句檢查資料庫附加日志的打開狀況:
Select
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
2) 打開資料庫附加日志
打開附加日志并切換日志（保證Online redo log和Archive log一緻）
alter database add supplemental log data ;
alter database add supplemental log data (primary key, unique,foreign key) columns;
alter system switch logfile;
3) 确認附加日志情況
使用以下sql語句檢查資料庫附加日志的打開狀況:
Select
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
注：確定最小附加日志,pk,uk,fk附加日志打開。而all columns的附加日志關閉；
如果all columns的附加日志打開的話，則需要使用以下語句予以關閉：
alter database drop supplemental log data (ALL) columns;
1.3 開啟資料庫強制日志模式
Alter database force logging;
注：該模式的打開需要和業務部門進行相關确認和讨論；如果資料庫不能打開到force logging的模式下，則no logging的表無法進行同步；
1.4
alter system set enable_goldengate_replication=true;
1.5 建立運作GoldenGate的使用者
1) 在源端采用ORACLE使用者運作GoldenGate
2) 安裝位置：/ogg
3) 建立GoldenGate資料庫使用者ggs
--create tablespace
create tablespace ggstab datafile '/oracle/app/oracle/oradata/oradb/goldengate02.dbf' size 1024M autoextend on;
create tablespace ggstab datafile '/home/oracle/app/oracle/oradata/oradb/goldengate02.dbf' size 1024M autoextend on;
-- Create the user
create user ggs identified by ggs default tablespace ggstab;
-- Grant role privileges
grant dba to ggs;
1.6 關閉資料庫的recyclebin
查詢目前recyclebin的參數值：
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- -------------------
recyclebin string OFF
SQL>
如不是off，需要關閉recyclebin：
SQL>alter system set recyclebin=off scope=both;
SQL>alter system set recyclebin=off deferred scope=both;
1.7 建立OGG的DDL對象
sqlplus "/ as sysdba"
SQL> @marker_setup.sql
Enter GoldenGate schema name:ggs
SQL> @ddl_setup.sql
Enter GoldenGate schema name:ggs
SQL> @role_setup.sql
Grant this role to each user assigned to the Extract, Replicat, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate processes.
注意這裡的提示：需要手工将這個GGS_GGSUSER_ROLE指定給extract所使用的資料庫使用者（即參數檔案裡面通過userid指定的使用者），可以到sqlplus下執行類似的sql:
GRANT GGS_GGSUSER_ROLE TO ggs;
注：這裡的goldengate是extract使用的使用者。如果你有多個extract，使用不同的資料庫使用者，則需要重述以上過程全部賦予GGS_GGSUSER_ROLE權限。
安裝sequence複制對象：
SQL> @sequence.sql
1.8 安裝提升性能工具
本步驟為可選步驟，一般情況下建議運作該步驟。
為了提供OGG的DDL複制的性能，可以将ddl_pin腳本加入到資料庫啟動的腳本後面，該腳本需要帶一個OGG的DDL使用者（即安裝DDL對象的使用者，本例中是ggs）的參數:
SQL> @ddl_pin ggs
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- source
GGSCI >create subdirs
-- edit params ./GLOBALS
GGSCHEMA ggs
CHECKPOINTTABLE ggs.checkpoint
-- edit params mgr
PORT 7839
DYNAMICPORTLIST 7840-7914
USERID ggs, PASSWORD ggs
--AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 4
--1 edit params extemr
EXTRACT extemr
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
--setenv (ORACLE_SID="tardb1")
USERID [email protected], PASSWORD ggs
tranlogoptions dblogreader
GETTRUNCATES
ddl include all
DDLOPTIONS NOCROSSRENAME REPORT
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS INCLUDEREGIONID,EXCLUDEUSER ggs
ddloptions addtrandata, report
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/extemr.dsc,APPEND,MEGABYTES 1024
--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENS 60000
DBOPTIONS ALLOWUNUSEDCOLUMN
WARNLONGTRANS 2h,CHECKINTERVAL 300s
EXTTRAIL ./dirdat/em
FETCHOPTIONS NOUSESNAPSHOT
--TRANLOGOPTIONS CONVERTUCS2CLOBS
TABLE TEST.*;
--2 edit params dpemr
EXTRACT dpemr
PASSTHRU
RMTHOST 10.16.28.30, MGRPORT 7809, compress, PARAMS -w 30
RMTTRAIL ./dirdat/em
TABLE TEST.*;
--3 添加抽取/傳輸程序與隊列檔案
add ext extemr,tranlog ,begin 2016-10-01 15:30,THREADS 2
add exttrail ./dirdat/em,ext extemr,megabytes 200
add ext dpemr,exttrailsource ./dirdat/em
add rmttrail ./dirdat/em,ext dpemr,megabytes 200
添加OGG使用者資料庫檢查點表：
dblogin userid ggs,password ggs
add checkpointtable ggs.checkpoint
--target
GGSCI >create subdirs
-- edit params ./GLOBALS
GGSCHEMA ggs
CHECKPOINTTABLE ggs.checkpoint
-- edit params mgr
PORT 7839
DYNAMICPORTLIST 7840-7914
USERID ggs, PASSWORD ggs
--AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 4
--1 edit params repemr
REPLICAT repemr
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
setenv (ORACLE_SID="auxdb")
USERID ggs, PASSWORD ggs
DBOPTIONS NOSUPPRESSTRIGGERS
DDL include mapped
ddloptions report
REPORTCOUNT EVERY 30 MINUTES, RATE
--REPERROR DEFAULT, ABEND
REPERROR DEFAULT, DISCARD
--REPERROR (1, DISCARD)
--REPERROR (1403, DISCARD)
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/repemr.dsc, APPEND, MEGABYTES 1024
GETTRUNCATES
ALLOWNOOPUPDATES
MAP HYEMR.*, TARGET HYEMR.*;
--2 添加OGG使用者資料庫檢查點表：
dblogin userid ggs,password ggs
add checkpointtable ggs.checkpoint
添加replication程序：
add replicat repemr, exttrail ./dirdat/em
GGSCI > start mgr
GGSCI > start er *
GGSCI > info all
GGSCI > view report ext
![MVVM + RAC 實踐[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)