星期四, 8月 18, 2016

FW: MySQL Fabric + Replication高可用

星期四, 7月 07, 2016

informix Enterprise replication

 

Informix 內建之enterprise replication 可以針對表格-表格同步, 或是當作DR備援使用

也可挑選表格所需要的部分欄位, 可設定多個thread來加速同步速度

例如

Select A , B , D from source.tab…..    (ignore C欄位)

如果要做到資料彙整、數值比對、計算之類的事情, 還是要仰賴ETL工具來實現

 

Ref:

http://iiug2015.org/files/IIUG2015Petr_PomykacekMigr_ER_Petr_Pomykacek-final.pdf

http://www.oninit.com/manual/informix/100/25122791.pdf

 

星期五, 3月 25, 2016

RE: 設定xtrabackup for MariaDB/ MySQL

Test OK


[root@RepDB2 tmp]# ./backup_mysql.sh Sunday oracle
mv: target `/source/mysql-backups/last_week' is not a directory
xtrabackup version 2.0.8 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 587)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 1073741824
>> log scanned up to (136644115)
160325 17:17:36  InnoDB: Warning: allocated tablespace 22, old maximum was 9
[01] Copying ./ibdata1 to /source/mysql-backups/this_week/Sunday/ibdata1
>> log scanned up to (136650500)
>> log scanned up to (136652382)
>> log scanned up to (136652382)
>> log scanned up to (136652382)
>> log scanned up to (136652382)
[01]        ...done
[01] Copying ./test/t2#P#d2.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d2.ibd
[01]        ...done
[01] Copying ./test/authors.ibd to /source/mysql-backups/this_week/Sunday/test/authors.ibd
[01]        ...done
[01] Copying ./test/tt.ibd to /source/mysql-backups/this_week/Sunday/test/tt.ibd
[01]        ...done
[01] Copying ./test/t2#P#d7.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d7.ibd
[01]        ...done
[01] Copying ./test/t2#P#d0.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d0.ibd
[01]        ...done
[01] Copying ./test/t2#P#d4.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d4.ibd
[01]        ...done
[01] Copying ./test/t2#P#d6.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d6.ibd
>> log scanned up to (136652382)
[01]        ...done
[01] Copying ./test/task_table2.ibd to /source/mysql-backups/this_week/Sunday/test/task_table2.ibd
[01]        ...done
[01] Copying ./test/t2#P#d1.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d1.ibd
[01]        ...done
[01] Copying ./test/t2#P#d5.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d5.ibd
[01]        ...done
[01] Copying ./test/t2#P#d3.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d3.ibd
[01]        ...done
[01] Copying ./mysql/gtid_slave_pos.ibd to /source/mysql-backups/this_week/Sunday/mysql/gtid_slave_pos.ibd
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /source/mysql-backups/this_week/Sunday/mysql/innodb_table_stats.ibd
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /source/mysql-backups/this_week/Sunday/mysql/innodb_index_stats.ibd
[01]        ...done
[01] Copying ./mydb/authors.ibd to /source/mysql-backups/this_week/Sunday/mydb/authors.ibd
[01]        ...done
[01] Copying ./mydb/t3.ibd to /source/mysql-backups/this_week/Sunday/mydb/t3.ibd
[01]        ...done
[01] Copying ./mydb/t2.ibd to /source/mysql-backups/this_week/Sunday/mydb/t2.ibd
[01]        ...done
[01] Copying ./mydb/task_table1.ibd to /source/mysql-backups/this_week/Sunday/mydb/task_table1.ibd
[01]        ...done
[01] Copying ./mydb/t1.ibd to /source/mysql-backups/this_week/Sunday/mydb/t1.ibd
[01]        ...done
xtrabackup: The latest check point (for incremental): '136652382'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (136652382)

xtrabackup: Transaction log of lsn (136644115) to (136652382) was copied.
[root@RepDB2 tmp]# ls -l    /source/mysql-backups/this_week/Sunday
total 77852
-rw-r----- 1 root root 79691776 Mar 25 17:17 ibdata1
drwx------ 2 root root     4096 Mar 25 17:17 mydb
drwx------ 2 root root     4096 Mar 25 17:17 mysql
drwx------ 2 root root     4096 Mar 25 17:17 test
-rw-r----- 1 root root       81 Mar 25 17:17 xtrabackup_checkpoints
-rw-r----- 1 root root    10752 Mar 25 17:17 xtrabackup_logfile
[root@RepDB2 tmp]#

Subject: 設定xtrabackup for MariaDB/ MySQL

#設定xtrabackup policy
# percona-xtrabackup-2.2.12-1.el7.x86_64.rpm 是從 https://mariadb.com/my_portal/download/ 下載的

#Prerequisite for perxona-xtrabackup
[root@db1 mysql]#
yum install perl-DBD-MySQL.x86_64
yum install perl-Digest-MD5.x86_64

[root@db1 mysql]#
rpm -ivh /source/percona-xtrabackup-2.2.12-1.el7.x86_64.rpm
warning: /source/percona-xtrabackup-2.2.12-1.el7.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID e3c94f49: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:percona-xtrabackup-2.2.12-1.el7  ################################# [100%]
[root@db1 mysql]#

[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Monday
[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Tuesday
[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Wednesday
[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Thursday
[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Friday
[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Saturday
[root@db1 source]# mkdir -p /source/mysql-backups/last_week
[root@db1 source]#

[root@db1 source]# cat backup_mysql.sh
#!/bin/bash
##
#####################################################################################
# "THE BEER-WARE LICENSE" (Revision 42):
# <blagovest@petrovs.info> wrote this file.  As long as you retain this notice you
# can do whatever you want with this stuff. If we meet some day, and you think
# this stuff is worth it, you can buy me a beer in return.   Blagovest Petrov
#####################################################################################
##

DAY=$1
MYSQL_PASS=$2
MYSQL_USER="root"

BACKUPS_DIR=/source/mysql-backups
THIS_WEEK=$BACKUPS_DIR/this_week
LAST_WEEK=$BACKUPS_DIR/last_week

function do_full_backup() {

  if [ -d $LAST_WEEK ]; then
    rm -rf $LAST_WEEK
    mkdir $LAST_WEEK
  fi

  if [ -d $THIS_WEEK ]; then
    mv $THIS_WEEK/* $LAST_WEEK
    mkdir -p $THIS_WEEK/Sunday
  fi

  xtrabackup --backup --target-dir=$THIS_WEEK/Sunday \
  --datadir=/var/lib/mysql \
  --user=$MYSQL_USER --password=$MYSQL_PASS
}

function do_incremental_backup() {

  if [ -d $THIS_WEEK/$DAY ]; then
    rm -rf $THIS_WEEK/$DAY
    mkdir $THIS_WEEK/$DAY
  fi


  xtrabackup --backup --target-dir=$THIS_WEEK/$DAY \
  --incremental-basedir=$THIS_WEEK/Sunday \
  --datadir=/var/lib/mysql \
  --user=$MYSQL_USER --password=$MYSQL_PASS
}

case $1 in
  Sunday)
    do_full_backup
    ;;
  *)
    do_incremental_backup
esac

手動測試
#記得要reset logfile size innodb_log_file_size = 50331648 才能備份
#MySQL Server depending on version has different default value for innodb_log_file_size: 5242880 (MySQL < 5.6.8), 50331648 (MySQL >= 5.6.8) http://dev.mysql.com/doc/refman/5.6/..._log_file_size

[root@db1 source]# ./backup_mysql.sh Sunday root123
xtrabackup version 2.2.12 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 8726828)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
>> log scanned up to (2060136)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /source/mysql-backups/this_week/Sunday/ibdata1
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /source/mysql-backups/this_week/Sunday/mysql/innodb_table_stats.ibd
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /source/mysql-backups/this_week/Sunday/mysql/innodb_index_stats.ibd
[01]        ...done
[01] Copying ./mysql/gtid_slave_pos.ibd to /source/mysql-backups/this_week/Sunday/mysql/gtid_slave_pos.ibd
[01]        ...done
[01] Copying ./test/tab1.ibd to /source/mysql-backups/this_week/Sunday/test/tab1.ibd
[01]        ...done
>> log scanned up to (2060136)
xtrabackup: The latest check point (for incremental): '2060136'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2060136)


#Crontab
0 2 * * 0 /usr/local/bin/backup_mysql.sh Sunday root123 >/dev/null 2>&1
0 2 * * 1 /usr/local/bin/backup_mysql.sh Monday 12345 >/dev/null 2>&1
0 2 * * 2 /usr/local/bin/backup_mysql.sh Tuesday 12345 >/dev/null 2>&1
0 2 * * 3 /usr/local/bin/backup_mysql.sh Wednesday 12345 >/dev/null 2>&1
0 2 * * 4 /usr/local/bin/backup_mysql.sh Thursday 12345 >/dev/null 2>&1
0 2 * * 5 /usr/local/bin/backup_mysql.sh Friday 12345 >/dev/null 2>&1
0 2 * * 6 /usr/local/bin/backup_mysql.sh Saturday 12345 >/dev/null 2>&1



星期三, 2月 03, 2016

[Oracle] 如何從cursor import sql 至baseline.

P.S. 從cursor import sql , 進來之後, SQL的狀態就會是enabled , accepted狀態了

SQL> select /* test_spm */ count(*) from admin.objekti;
  COUNT(*)
----------
     76378
SQL> set lines 200
SQL> col sql_text for a50 wrapped
SQL> select sql_id, sql_text from v$sql
  2  where sql_text like '%test_spm%';
SQL_ID        SQL_TEXT
------------- --------------------------------------------------
cpzgdw9swdvzk select /* test_spm */ count(*) from admin.objekti
ap9td7vafq26n select sql_id, sql_text from v$sql where sql_text
              like '%test_spm%'

select *
from table(dbms_xplan.display_cursor(sql_id => 'ap9td7vafq26n'));


variable cnt number;
execute :cnt :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'ap9td7vafq26n');

select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
from dba_sql_plan_baselines
WHERE sql_text like 'select * from t%';
SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES NO  NO


SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',
    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',
    attribute_name  => 'ENABLED',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

[轉載]Oracle 11g 如何手動Capture SQL至sql baseline

Ref:

https://avdeo.com/tag/load_plan_from_cursor_cache/

 

1.

select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED

from dba_sql_plan_baselines

WHERE sql_text like 'select * from t%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX

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

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES NO  NO

 

SQL handle : 語法相同的SQL

Plan_Name : 不同執行計畫路徑就會有不同的plan name

 

2.

select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920fae82cf72'));

PLAN_TABLE_OUTPUT

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

 

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

SQL handle: SYS_SQL_1447ba3a1d83920f

SQL text: select * from t where col1 = 1

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

 

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

Plan name: SYS_SQL_PLAN_1d83920fae82cf72

Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE

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

 

Plan hash value: 470836197

 

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

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |       |    15 |   735 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    15 |   735 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("COL1"=1)

 

25 rows selected.

 

SQL>

 

3.

SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_1447ba3a1d83920f') from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_1447BA3A1D83920F')

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

 

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

                        Evolve SQL Plan Baseline Report

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

 

Inputs:

-------

  SQL_HANDLE = SYS_SQL_1447ba3a1d83920f

  PLAN_NAME  =

  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

  VERIFY     = YES

  COMMIT     = YES

 

Plan: SYS_SQL_PLAN_1d83920fae82cf72

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

  Plan was verified: Time used .01 seconds.

  Passed performance criterion: Compound improvement ratio >= 7.33

  Plan was changed to an accepted plan.

 

                      Baseline Plan      Test Plan     Improv. Ratio

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

  Execution Status:        COMPLETE       COMPLETE

  Rows Processed:                 1              1

  Elapsed Time(ms):               0              0

  CPU Time(ms):                   0              0

  Buffer Gets:                   22              3              7.33

  Disk Reads:                     0              0

  Direct Writes:                  0              0

  Fetches:                        0              0

  Executions:                     1              1

 

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

                                 Report Summary

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

Number of SQL plan baselines verified: 1.

Number of SQL plan baselines evolved: 1.

 

4.

SET SERVEROUTPUT ON

DECLARE

  l_plans_altered  PLS_INTEGER;

BEGIN

  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(

    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',

    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',

    attribute_name  => 'ENABLED',

    attribute_value => 'YES');

 

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);

END;

/

 

Once you evolve the plan, you can see that plan is ACCEPTED now.

 

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED

  2  from dba_sql_plan_baselines

  3  WHERE sql_text like 'select * from t%';

 

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX

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

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES YES NO

 

 

5.Verify SQL是否已經進入到sql baseline

SQL> explain plan for

  2  select * from t where col1 = 1;

 

Explained.

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 470836197

 

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

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |       |    15 |   735 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    15 |   735 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("COL1"=1)

 

Note

-----

   - SQL plan baseline "SYS_SQL_PLAN_1d83920fae82cf72" used for this statement

 

18 rows selected.

 

SQL>

星期六, 1月 30, 2016

Oracle 11g 如何簡單的測試Storage Array 效能

Prerequisites for I/O Calibration:
Before running I/O calibration, ensure that the following requirements are met:
The user must be granted the SYSDBA privilege
timed_statistics must be set to TRUE(default)
Asynchronous I/O must be enabled

Oracle 11g 如何簡單的測試Storage Array 效能
1.檢查是否有開啟async IO
SQL>
COL NAME FORMAT A50
SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE  F.FILE#=I.FILE_NO
AND    FILETYPE_NAME='Data File';
SQL>
+DATA/xxx/DATAFILE/tbs2.295.902228267
ASYNC_OFF

2.修改filesystemio_options=setAll

3.重新啟動資料庫instance (filesystemio_options是靜態參數, 須重啟才會生效)

4. 檢查是否有開啟async IO
SQL>
COL NAME FORMAT A50
SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE  F.FILE#=I.FILE_NO
AND    FILETYPE_NAME='Data File';
SQL>
+DATA/xxx/DATAFILE/tbs2.295.902228267
ASYNC_ON

5.開始進行測試
set serveroutput on

declare
v_max_iops number;
v_max_mbps number;
v_actual_latency number;
begin
dbms_resource_manager.calibrate_io(num_physical_disks=>2,max_latency=>10,max_iops=>v_max_iops,max_mbps=>v_max_mbps,actual_latency=>v_actual_latency); 
 dbms_output.put_line('IOPS:'||v_max_iops);
dbms_output.put_line('MBPS:'||v_max_mbps);
dbms_output.put_line('ACTUAL_LATENCY:'||v_actual_latency);
end;
/
IOPS:99     --OLTP要求此數字越高越好
MBPS:38     --DW要求此數字越高越好
ACTUAL_LATENCY:9  --OLTP要求此數字越小越好

PL/SQL procedure successfully completed.

星期五, 1月 29, 2016

[FW] Script to get DDL of a schema user (TRIGGER, FUNCTION, PROCEDURE, TABLE DDL, INDEX_DDL)

sqlplus / as sydba<<EOF
set long 999999999
set head off
set echo off
set pagesize 0
set verify off
set feedback off
spool schema_$1_ddl.out

select dbms_metadata.get_ddl(object_type, object_name, owner)
from
(
    --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
    select
        owner,
        object_name,
        decode(object_type,
            'JOB',          'PROCOBJ',
            'PACKAGE',      'PACKAGE_SPEC',
            'PACKAGE BODY', 'PACKAGE_BODY',
            'TYPE',         'TYPE_SPEC',
            'TYPE BODY',    'TYPE_BODY',
            object_type
        ) object_type
   from dba_objects
    where owner in ('$1')
        --These objects are included with other object types.
        and object_type not in ('INDEX PARTITION','LOB','LOB PARTITION','TABLE PARTITION')
        --Ignore system-generated types that support collection processing.
        and not (object_type like 'TYPE' and object_name like 'SYS_PLSQL_%')
)
order by owner, object_type, object_name;

spool off
quit
EOF

Usage:
sh test.sh TEST (depends on which schema you want retrieve~)


方法2:
or Use Oracle DataPump
SQL> create or replace directory dp as '/home/oracle/';

SQL> create or replace directory dp as '/home/oracle/';

expdp \'/ as sysdba\' SCHEMAS=TEST,JAYCHU directory=dp dumpfile=full_meta.dmp logfile=full_meta.log CONTENT=METADATA_ONLY

impdp \'/ as sysdba\' directory=dp dumpfile=full_meta.dmp logfile=full_meta_implog.txt sqlfile=ddl_dump.sql

the example of ddl_Dump.sql
-----------------------------------------------------------------------------------------------------------
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "TEST" IDENTIFIED BY VALUES 'S:D5260B66A5E8C4DC857A1ABE54569A0E45BC39B1B9A4D25B3B5D542BDF0E;H:6A881F008133C22B7B356D9D921817E7;T:FE98C666DDCE9AC8AB5020DCA8464EEF420AB759FAB1B58FF4F73D290F8CAB437EC3F31A3A9F287295AF574FC1B8EF1DFC5440B000532FD1E5DC6634EC5C0E47C2EF37F04FF71B24F1B83FE1F3F3A90B;7A0F2B316C212D67'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT CREATE ANY TRIGGER TO "TEST";
GRANT UNLIMITED TABLESPACE TO "TEST";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "CONNECT" TO "TEST";
 GRANT "RESOURCE" TO "TEST";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "TEST" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT TEST

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'MSG', inst_scn=>'5841316');
COMMIT;
END;
/
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT SYS
CREATE TABLE "TEST"."TAB1"
   (    "A" VARCHAR2(20 BYTE),
        "B" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
-- new object type path: SCHEMA_EXPORT/FUNCTION/FUNCTION
-- CONNECT TEST
CREATE EDITIONABLE FUNCTION       Days_Between (first_dt   DATE,
                                  second_dt  DATE)
RETURN NUMBER IS
 dt_one NUMBER;
 dt_two NUMBER;
BEGIN
 dt_one := TO_NUMBER(TO_CHAR(first_dt, 'DDD'));
 dt_two := TO_NUMBER(TO_CHAR(second_dt, 'DDD'));

 RETURN (dt_two - dt_one);
END Days_Between;
/
-- new object type path: SCHEMA_EXPORT/PROCEDURE/PROCEDURE
CREATE EDITIONABLE PROCEDURE      addtuple1(i IN NUMBER) AS

BEGIN

    INSERT INTO tab1 VALUES(i, 'xxx');

END addtuple1;
/
-- new object type path: SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

ALTER FUNCTION "TEST"."DAYS_BETWEEN"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

 REUSE SETTINGS TIMESTAMP '2016-01-29 09:57:53'
/
-- new object type path: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

ALTER PROCEDURE "TEST"."ADDTUPLE1"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

 REUSE SETTINGS TIMESTAMP '2016-01-29 10:00:16'
/
-- new object type path: SCHEMA_EXPORT/TABLE/TRIGGER
CREATE EDITIONABLE TRIGGER test.MONTOR_linktab1
    AFTER INSERT OR UPDATE OR DELETE
    ON test.tab1
DECLARE
    log_action VARCHAR2(20);
    v_date VARCHAR2(20);
BEGIN
    select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') into v_date FROM DUALf@msgtest;
    IF INSERTING THEN
        log_action := 'Insert';
    ELSIF UPDATING THEN
        log_action := 'Update';
    ELSIF DELETING THEN
        log_action := 'Delete';
    ELSE
        DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
    END IF;
    INSERT INTO test.tab1 VALUES(v_date,log_action);
END;
/

ALTER TRIGGER "TEST"."MONTOR_LINKTAB1" ENABLE;

ALTER TRIGGER "TEST"."MONTOR_LINKTAB1"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'
;
-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-- new object type path: SCHEMA_EXPORT/STATISTICS/MARKER
-----------------------------------------------------------------------------------------------------------


Ref:
http://stackoverflow.com/questions/10886450/how-to-generate-entire-ddl-of-an-oracle-schema-scriptable

星期三, 1月 27, 2016

簡單測試11g interval partition table

CREATE TABLE purchase_interval

(

REGION_ID NUMBER,

ORDER_ID NUMBER,

time_id DATE

)

PARTITION BY RANGE (time_id)

INTERVAL (NUMTOYMINTERVAL(1,'month')) STORE IN (tbs1,tbs2,tbs3)

(

PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2005', 'dd-mm-yyyy')),

PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2007', 'dd-mm-yyyy'))

);

 

desc dba_tab_partitions;

select partition_name from dba_tab_partitions where table_name=UPPER('purchase_interval');

 

PARTITION_NAME

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

P2

P1

 

truncate table purchase_interval ;

insert into purchase_interval values (1,1, TO_DATE('1-1-2005', 'dd-mm-yyyy'));

commit;

 

insert into purchase_interval values (1,1, TO_DATE('1-2-2005', 'dd-mm-yyyy'));

commit;

 

insert into purchase_interval values (1,1, TO_DATE('1-3-2005', 'dd-mm-yyyy'));

commit;

select partition_name from dba_tab_partitions where table_name=UPPER('purchase_interval');

 

PARTITION_NAME

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

P2

P1

--建了兩個table partiton

 

insert into purchase_interval values (1,1, TO_DATE('1-1-2007', 'dd-mm-yyyy'));

commit;

 

select partition_name from dba_tab_partitions where table_name=UPPER('purchase_interval');

PARTITION_NAME

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

SYS_P681  --當塞入最新資料之後(大於1-1-2007以後的新資料) , 就會自動產生一個新的partition table

P2

P1

 

set long 999999999

set pagesize 300

select dbms_metadata.get_ddl('TABLE',  UPPER('purchase_interval')) from dual;

 

  CREATE TABLE "SYS"."PURCHASE_INTERVAL"

   (    "REGION_ID" NUMBER,

        "ORDER_ID" NUMBER,

        "TIME_ID" DATE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM"

  PARTITION BY RANGE ("TIME_ID") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) STORE IN

("TBS1", "TBS2", "TBS3")

(PARTITION "P1"  VALUES LESS THAN (TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD

HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM" ,

PARTITION "P2"  VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD

HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM" )

 

可以看到DDL是存放當初建出來table的語法

另外, NUMTOYMINTERVAL(1,'month') 是自動add partition table by month ,

也可以設定七天自動add partition:  NUMTOYMINTERVAL(1,'week')

 

SQL> select PARTITION_NAME, TABLESPACE_NAME, PARTITION_POSITION from dba_tab_partitions  where table_name=UPPER('purchase_interval');

 

--而且舊的table partition 如果不指定tablespace存放在哪的話, 會自動存到user's default tablespace

PARTITION_NAME

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

TABLESPACE_NAME                PARTITION_POSITION

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

P1

SYSTEM                                          1

 

P2

SYSTEM                                          2

 

SYS_P681

TBS3                                            3

 

--建立第二個表格測試看看, 狀況是一樣的.

 

CREATE TABLE purchase_interval2

PARTITION BY RANGE (time_id)

INTERVAL (NUMTOYMINTERVAL(1,'month')) STORE IN (tbs1,tbs2,tbs3)

(

PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2005', 'dd-mm-yyyy')),

PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2007', 'dd-mm-yyyy'))

)

as select * from sys.purchase_interval;

 

select partition_name from dba_tab_partitions where table_name=UPPER('purchase_interval2');

 

PARTITION_NAME

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

SYS_P707

P2

P1

 

drop table purchase_interval;

drop table purchase_interval2;

crsctl modify resource 遇到 CRS-4995 問題 (The command 'Modify resource' is invalid in crsctl)

我的環境為12c (12.1.0.2)

主要是在手動kill RAC instance 2 兩次以後,

ora.${SID}.db 的資源AUTO_START狀態, always 就自動被改成restore(猜想應該是一種保護機制)

 

 

[grid@db1 ~]$ crsctl modify resource ora.msg.db -attr AUTO_START=1

CRS-4995:  The command 'Modify  resource' is invalid in crsctl. Use srvctl for this command.

 

[grid@db1 ~]$ crs_stat -p ora.msg.db

NAME=ora.msg.db

TYPE=ora.database.type

ACTION_SCRIPT=

ACTIVE_PLACEMENT=0

AUTO_START=restore

CHECK_INTERVAL=1

DESCRIPTION=Oracle Database resource

FAILOVER_DELAY=0

FAILURE_INTERVAL=60

FAILURE_THRESHOLD=1

GEN_USR_ORA_INST_NAME@SERVERNAME(db1)=msg1

GEN_USR_ORA_INST_NAME@SERVERNAME(db2)=msg2

HOSTING_MEMBERS=

PLACEMENT=restricted

RESTART_ATTEMPTS=2

SCRIPT_TIMEOUT=60

START_TIMEOUT=600

STOP_TIMEOUT=600

UPTIME_THRESHOLD=1h

USR_ORA_INST_NAME@SERVERNAME(db1)=msg1

USR_ORA_INST_NAME@SERVERNAME(db2)=msg2

 

解決辦法

As root:

[root@db1 ~]# /u01/app/12.1.0/grid/bin/crsctl modify res ora.msg.db -attr "AUTO_START=always"  -unsupported

 

[root@db1 ~]# su - grid

[grid@db1 ~]$ crs_stat -p ora.msg.db

NAME=ora.msg.db

TYPE=ora.database.type

ACTION_SCRIPT=

ACTIVE_PLACEMENT=0

AUTO_START=always

CHECK_INTERVAL=1

DESCRIPTION=Oracle Database resource

FAILOVER_DELAY=0

FAILURE_INTERVAL=60

FAILURE_THRESHOLD=1

GEN_USR_ORA_INST_NAME@SERVERNAME(db1)=msg1

GEN_USR_ORA_INST_NAME@SERVERNAME(db2)=msg2

HOSTING_MEMBERS=

PLACEMENT=restricted

RESTART_ATTEMPTS=2

SCRIPT_TIMEOUT=60

START_TIMEOUT=600

STOP_TIMEOUT=600

UPTIME_THRESHOLD=1h

USR_ORA_INST_NAME@SERVERNAME(db1)=msg1

USR_ORA_INST_NAME@SERVERNAME(db2)=msg2

 

Ref:

http://www.dbastuff.net/2014/11/oracle-issue-running-12102-clusterware.html

 

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...