Oracle 11g DataGuard migration (ASM to non ASM)

http://oracleinstance.blogspot.tw/2010/08/duplicatecloneconvert-asm-file-system.html

重點1. Standby DB listener.ora (須改為靜態註冊)

SID_LIST_LISTENER_RAC1 =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
     (PROGRAM = extproc)
   )
    (SID_DESC =
    (GLOBAL_DBNAME = dupdb)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (SID_NAME = dupdb)
   )
 )

重點2. Standby DB parameter

db_file_name_convert='+DATA/DEMODB/DATAFILE/','/u01/app/oracle/oradata/dupdb/'
log_file_name_convert='+DATA/DEMODB/ONLINELOG/','/u01/app/oracle/oradata/dupdb/'

重點3. ODG broker 的設定如下:
Ref:
http://gavinsoorma.com/2010/03/11g-data-guard-broker-dgmgrl-configuration-quick-steps/

重點4. 記得在standby db 加上

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
或
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;


重點5. Primary DB 須設定參數:
LOG_ARCHIVE_MAX_PROCESSES=30

重點6. 11g 可開啟archive log 壓縮
LOG_ARCHIVE_DEST_3='SERVICE=denver SYNC COMPRESSION=ENABLE'
其他參數:
http://docs.oracle.com/cd/E11882_01/server.112/e25608/log_arch_dest_param.htm#BABBFBDI

重點7. 在DataGuard環境中，為了減少故障時資料損失，我們可以設置ARCHIVE_LAG_TARGET參數，強制進行日誌切換。(這在10g OCM也曾經考過)

http://www.eygle.com/archives/2006/11/archive_lag_target_force_logswitch.html

重點8. 設定 自動刪除Logical Standby archive log :   LOG_AUTO_DEL_RETENTION_TARGET 
Ref:
http://www.databasejournal.com/features/oracle/article.php/3929131/Top-10-New-Features-for-Oracle-11g-Data-Guard---Part-One.htm

To change the setting to something like three days you would use the following syntax:

DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DEL_RETENTION_TARGET','4320');

[Script] unload Oracle table data 到文字檔(flat file)的方法

sqlplus "/ as sysdba"<< EOF

set heading off

set lines 1000

set pagesize 0

set termout off

set trimspool on

set linesize 9999    

set term off

set feedback off

spool /mnt/table_name.csv

set colsep ","
--以下為想倒出的表格 , 可自行定義之

select * from TABLE where datetime2 between '${1}0101 00:00:00' and '${1}1231 23:59:59';

spool off

EOF

#把一個或一個以上的空白用一個空白代替(上面的linesize 9999 會造成許多空白output...故在這邊把多餘空白資料合併)

sed 's/\ \ */\ /g' /mnt/table_name.csv > /mnt/table_name.csv.ok

產生完以後 就可以使用database load tool 去搬資料囉!

後來發現sql loader會ignore 其他SQL> 的prompt 字串到discard file去
所以Oracle 還是很強大的!

Oracle data pump 指令參照

預測dmp檔佔多少空間
expdp test/test directory=expdp_dir logfile=test_dmp.log estimate_only=y
                ESTIMATE=BLOCKS  (預設)
                ESTINATE=STATISTICS  (依照表格的STATISTICS預測空間大小)
指定dump時用幾個執行緒
expdp test/test  directory=expdp_dir dumpfile=test.dmp logfile=test_dmp.log parallel=4

排除某個表格
expdp test/test schemas=TEST exclude=TABLE:"in\('TEST'\)" directory=expdp_dir dumpfile=expdp.dmp logfile=expdp.log

排除某個USER
expdp \'/ as sysdba\' full=y EXCLUDE=SCHEMA:"in\('TEST'\)" directory=dp dumpfile=full.dmp logfile=full.log

排除一
expdp \'/ as sysdba\' full=y EXCLUDE=SCHEMA:"in\('WKSYS'\)" directory=dp dumpfile=full.dmp logfile=full.log CONTENT=METADATA_ONLY

expdp \'/ as sysdba\' full=y EXCLUDE=SCHEMA:"in\('WKSYS','OLAPSYS','TSMSYS','SYSMAN','OUTLN','WK_TEST'\)" directory=dp dumpfile=full.dmp logfile=full.log


匯出所有METADATA
expdp \'/ as sysdba\' SCHEMAS=TEST,JAYCHU directory=dp dumpfile=full.dmp logfile=full.log CONTENT=METADATA_ONLY
by default, the datapump points to $ORACLE_BASE_admin/SID/dpdump

--export table
drop directory expdp_dir ;
create directory expdp_dir as '/na02/sysage/0825';
expdp \'/ as sysdba\' tables=MISUSER.ST02PF directory=expdp_dir content=all dumpfile=ST02PF.dmp logfile=ST02PF.log

create directory expdp_dir as '/na02/sysage/0825';
impdp \'/ as sysdba\' tables=MISUSER.ST02PF directory=expdp_dir content=all dumpfile=ST02PF.dmp logfile=ST02PF_imp.log TABLE_EXISTS_ACTION=replace
--

impdp \'/ as sysdba\'  DIRECTORY=dp REMAP_SCHEMA=JAYCHU:JAYCHU123  SQLFILE=JAYCHU123.sql LOGFILE=impdp.log DUMPFILE=full.dmp INCLUDE=GRANT,TABLE
                       ESTIMATE=STATISTICS
test:
impdp \'/ as sysdba\'  DIRECTORY=dp REMAP_SCHEMA=JAYCHU:JAYCHU123 LOGFILE=impdp.log DUMPFILE=full.dmp INCLUDE=GRANT,TABLE  ESTIMATE=STATISTICS


(PS: CONTENT=DATA_ONLY is invalid for SQL_FILE jobs.)
   
impdp \'/ as sysdba\'  DIRECTORY=dp REMAP_SCHEMA=JAYCHU:JAYCHU123  PARALLEL=4 LOGFILE=impdp.log DUMPFILE=full.dmp
                       CONTENT= ALL, (METADATA_ONLY), DATA_ONLY
                       INCLUDE=TABLE_DATA or TABLE or PROCEDURE or FUNCTION or TRIGGER or GRANT or INDEXES or CONSTRAINT
                       EXCLUDE=TABLE_DATA or TABLE or PROCEDURE or FUNCTION or TRIGGER or GRANT or INDEXES or CONSTRAINT(可自行斟酌須排除schema object type)
                       remap_tablespace=old_tablespace:new_tablespace
                       TABLE_EXISTS_ACTION   匯入之物件已經存在時所要採取的動作. : (SKIP , TRUNCATE , REPLACE)
                                                                                    ^^^^ default

create directory expdp_dir as '/na02/sysage/0825';
expdp \'/ as sysdba\' full=y  PARALLEL=4 flashback_time=\"TO_TIMESTAMP\(\'2010-07-19 20:40:00\', \'YYYY-MM-DD HH24:MI:SS\'\)\"  dumpfile=full0719.dmp

create directory expdp_dir as '/na02/sysage/0719';
impdp \'/ as sysdba\' DIRECTORY=expdp_dir PARALLEL=4 dumpfile=full0719.dmp logfile=venus_impdp_1.log CONTENT=DATAONLY TABLE_EXISTS_ACTION=REPLACE INCLUDE=INDEXES&



查看job
col OWNER_NAME for a10
col JOB_NAME for a20
col OPERATION for a6
col JOB_MODE for a6
col STATE for a10
col DEGREE for 9.9
col ATTACHED_SESSIONS for 9
col DATAPUMP_SESSIONS for 9

select * from dba_datapump_jobs;

查看expdp進度
col opname for a30
select opname , sofar , totalwork , ROUND(SOFAR/TOTALWORK*100,2) "%_Complete" from v$session_longops where sofar !=totalwork and opname like '%EXPORT%';

impdp進度
col opname for a30
select opname , sofar , totalwork , ROUND(SOFAR/TOTALWORK*100,2) "%_Complete" from v$session_longops where sofar !=totalwork and opname like '%IMPORT%';

查看import進度

select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi: ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;

此附件為 imp utility 於import 時可看到

http://forums.oracle.com/forums/thread.jspa?messageID=6495966&tstart=0

select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,

 rows_processed,

 round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,

 trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi: ss'))*24*60)) rows_per_min

 from sys.v_$sqlarea

 where sql_text like 'INSERT %INTO "%'

 and command_type = 2

 and open_versions > 0;

Oracle 快速得到資料庫編碼 的NLS_LANG設定

SQL> select userenv('language') from dual;

        USERENV('LANGUAGE')

        ----------------------------------------------------

        AMERICAN_AMERICA.ZHS16GBK