Move Table Partition between Tables

I was recently asked if we can move partition of one table into another? Basically a developers had truncated the partition and the team wanted to move the partition from one table to another.Both the table were structurally the same and had same data.

Here I will be showing how we can do that

1. Create a monthly partitioned table –

SQL> create table sales_part
    (product char(25),channel_id number,cust_id number,
    amount_sold number, time_id date)
    partition by range (time_id)
    (
    partition sale_jan values less than (to_date('01-02-2013','dd-mm-yyyy')),
    partition sale_feb values less than (to_date('01-03-2013','dd-mm-yyyy')),
    partition sale_mar values less than (to_date('01-04-2013','dd-mm-yyyy')),
    partition sale_apr values less than (to_date('01-05-2013','dd-mm-yyyy')),
    partition sale_may values less than (to_date('01-06-2013','dd-mm-yyyy')),
    partition sale_jun values less than (to_date('01-07-2013','dd-mm-yyyy')),
    partition sale_jul values less than (to_date('01-08-2013','dd-mm-yyyy')),
    partition sale_aug values less than (to_date('01-09-2013','dd-mm-yyyy')),
    partition sale_sep values less than (to_date('01-10-2013','dd-mm-yyyy')),
    partition sale_oct values less than (to_date('01-11-2013','dd-mm-yyyy')),
    partition sale_nov values less than (to_date('01-12-2013','dd-mm-yyyy')),
    partition sale_dec values less than (to_date('01-01-2014','dd-mm-yyyy'))
   ) TABLESPACE DEMO
 19  ;

Table created.

2. Insert some data into the table

SQL> insert into sales_part
    select
    'Oracle Enterprise Edition' as product,
     mod(rownum,5) as channel_id,
     mod(rownum,1000) as cust_id ,
     5000 as amount_sold,
     to_date
     ('01.' || lpad(to_char(mod(rownum,6)+4),2,'0') || '.2013' ,'dd.mm.yyyy')
     as time_id
     from dual connect by level commit;

Commit complete.

3. Create Index

SQL> create index idx_sale_local on sales_part (cust_id,time_id)local tablespace DEMO;

Index created.

SQL>
SQL> create index idx_sale_global on sales_part (channel_id)  tablespace DEMO;

Index created.

4. Gathers stats

SQL> exec dbms_stats.gather_table_stats('ANAND','SALES_PART', cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.56
SQL> @partition_table
Enter value for table_name: SALES_PART
old  12: WHERE  a.table_name  = UPPER('&table_name')
new  12: WHERE  a.table_name  = UPPER('SALES_PART')
Enter value for owner: ANAND
old  13: AND    a.table_owner = Upper('&owner')
new  13: AND    a.table_owner = Upper('ANAND')

TABLE_NAME      PARTITION_NAME            HIGH_VALUE                LAST_ANAL TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE   NUM_ROWS AVG_ROW_LEN
--------------- ------------------------- ------------------------- --------- ------------------------------ -------------- ----------- ------------ ---------- -----------
.....................
.....................
SALES_PART      SALE_APR                  TO_DATE(' 2013-05-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')

SALES_PART      SALE_MAY                  TO_DATE(' 2013-06-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')

SALES_PART      SALE_JUN                  TO_DATE(' 2013-07-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')

SALES_PART      SALE_JUL                  TO_DATE(' 2013-08-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')

SALES_PART      SALE_AUG                  TO_DATE(' 2013-09-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')

SALES_PART      SALE_SEP                  TO_DATE(' 2013-10-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')
......................
.....................


12 rows selected.

5. Lets create another table, exactly the same

SQL> create table sales_part_new
    (product char(25),channel_id number,cust_id number,
    amount_sold number, time_id date)
    partition by range (time_id)
    (
    partition sale_jan values less than (to_date('01-02-2013','dd-mm-yyyy')),
    partition sale_feb values less than (to_date('01-03-2013','dd-mm-yyyy')),
    partition sale_mar values less than (to_date('01-04-2013','dd-mm-yyyy')),
    partition sale_apr values less than (to_date('01-05-2013','dd-mm-yyyy')),
    partition sale_may values less than (to_date('01-06-2013','dd-mm-yyyy')),
    partition sale_jun values less than (to_date('01-07-2013','dd-mm-yyyy')),
    partition sale_jul values less than (to_date('01-08-2013','dd-mm-yyyy')),
    partition sale_aug values less than (to_date('01-09-2013','dd-mm-yyyy')),
    partition sale_sep values less than (to_date('01-10-2013','dd-mm-yyyy')),
    partition sale_oct values less than (to_date('01-11-2013','dd-mm-yyyy')),
    partition sale_nov values less than (to_date('01-12-2013','dd-mm-yyyy')),
    partition sale_dec values less than (to_date('01-01-2014','dd-mm-yyyy'))
   ) TABLESPACE DEMO
 19  ;

Table created.

6. Inserted the data using the same insert command,gathered the stats and created index as previously done in step 2,3 and 4.

7. Truncated one of the partition SALE_JUN, from SALES_PART_NEW

SQL> alter table SALES_PART_NEW truncate partition SALE_JUN;

Table truncated.

Now that the data is truncated from partition SALE_JUN of table SALES_PART_NEW, can we move same table partition of SALES_PART to SALES_PART_NEW.

We can use EXCHANGE PARTITION for the same

1. Create an interim table – Same structure without any partition

SQL> create table sales_part_interim
     (product char(25),
     channel_id number,
     cust_id number,
     amount_sold number,
     time_id date)
     TABLESPACE DEMO
    ;

Table created.

SQL> select count(1) from sales_part_interim;

  COUNT(1)
----------
         0

2. Perform Exchange Partition between Interim Table and SALE_JUN partition of SALES_PART.

SQL> alter table sales_part exchange partition SALE_JUN with table sales_part_interim;

Table altered.

Elapsed: 00:00:00.12
SQL> select count(1) from sales_part_interim;

  COUNT(1)
----------
    165000

SQL> @partition_table
Enter value for table_name: SALES_PART
old   8: WHERE  a.table_name  = UPPER('&table_name')
new   8: WHERE  a.table_name  = UPPER('SALES_PART')
Enter value for owner: ANAND
old   9: AND    a.table_owner = Upper('&owner')
new   9: AND    a.table_owner = Upper('ANAND')

TABLE_NAME      PARTITION_NAME            HIGH_VALUE                                              LAST_ANAL TABLESPACE_NAME                  NUM_ROWS
--------------- ------------------------- ------------------------------------------------------- --------- ------------------------------ ----------
SALES_PART      SALE_MAY                  TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS 14-AUG-15 DEMO                               165000
                                          ', 'NLS_CALENDAR=GREGORIAN')

SALES_PART      SALE_JUN                  TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS           DEMO
                                          ', 'NLS_CALENDAR=GREGORIAN')

SALES_PART      SALE_JUL                  TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS 14-AUG-15 DEMO                               165000
                                          ', 'NLS_CALENDAR=GREGORIAN')
.................

3. Rebuild the global indexes on SALES_PART as it would be UNUSABLE.

4. Now the 2nd Exchange Partition to where INTERIM table will be exchanged with the Partition SALE_JUN of SALE_PART_NEW table

SQL> alter table sales_part_new exchange partition SALE_JUN with table  sales_part_interim;

Table altered.

Elapsed: 00:00:00.27
SQL> select count(1) from sales_part_interim;

  COUNT(1)
----------
         0

Elapsed: 00:00:00.01
SQL>
SQL> select count(*) from sales_part_new partition (sale_jun);

  COUNT(*)
----------
    165000

Elapsed: 00:00:00.03
SQL>

5. Check the index status and rebuild the required ones.

Exchange Partition – Exchanging Table Partitions

Recently I came across a requirement of moving table to new tablespace. The table had 12 range based partitions and each partition had ~8K list subpartitions and was storing historic data. After few discussion, it was decided to use exchange partition instead of dbms_redefinition.

As I did not have much practical experience in exchange partition, I thought to test it and I am just posting my test here in the blog

CREATE TABLE composite_rng_list (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
cust_state VARCHAR2(2),
time_id DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY LIST (cust_state)
SUBPARTITION TEMPLATE(
SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE demo,
SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE demo,
SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE demo) (
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2014','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2015','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2016','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN(MAXVALUE));

Table created.

SQL> insert into composite_rng_list values (1,'A','NY',to_date('01/02/2015','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.06
SQL> insert into composite_rng_list values (2,'B','CT',to_date('09/09/2014','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.06
SQL> insert into composite_rng_list values (3,'C','TX',to_date('10/10/2015','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.01
SQL> insert into composite_rng_list values (4,'D','WA',to_date('11/11/2013','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.01
SQL> insert into composite_rng_list values (5,'E','OK',to_date('21/02/2014','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.01
SQL> insert into composite_rng_list values (6,'F','OR',to_date('07/07/2013','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('ANAND','COMPOSITE_RNG_LIST');

PL/SQL procedure successfully completed.


SQL>  select table_owner,table_name,partition_name,subpartition_count,num_rows,tablespace_name from dba_tab_partitions where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST'
;

TABLE_OWN TABLE_NAME             PARTITION_NAME  SUBPARTITION_COUNT   NUM_ROWS TABLESPACE_NAME
--------- ---------------------- --------------- ------------------ ---------- ------------------------------
ANAND     COMPOSITE_RNG_LIST     FUTURE                           3          0 DEMO
ANAND     COMPOSITE_RNG_LIST     PER1                             3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     PER2                             3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     PER3                             3          2 DEMO

SQL> select table_owner,table_name,partition_name,subpartition_count from dba_tab_partitions where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST';

TABLE_OWN TABLE_NAME             PARTITION_NAME  SUBPARTITION_COUNT
--------- ---------------------- --------------- ------------------
ANAND     COMPOSITE_RNG_LIST     FUTURE                           3
ANAND     COMPOSITE_RNG_LIST     PER1                             3
ANAND     COMPOSITE_RNG_LIST     PER2                             3
ANAND     COMPOSITE_RNG_LIST     PER3                             3

SQL> select table_owner,table_name,partition_name,subpartition_name,high_value,num_rows from dba_tab_subpartitions  where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST';

TABLE_OWN TABLE_NAME             PARTITION_NAME  SUBPARTITION_NA HIGH_VALUE      NUM_ROWS
--------- ---------------------- --------------- --------------- --------------- ----------
ANAND     COMPOSITE_RNG_LIST     FUTURE          FUTURE_CENT     'OK', 'TX'          0
ANAND     COMPOSITE_RNG_LIST     FUTURE          FUTURE_EAST     'NY', 'CT'          0
ANAND     COMPOSITE_RNG_LIST     FUTURE          FUTURE_WEST     'OR', 'WA'          0
ANAND     COMPOSITE_RNG_LIST     PER3            PER3_CENT       'OK', 'TX'          1
ANAND     COMPOSITE_RNG_LIST     PER3            PER3_EAST       'NY', 'CT'          1
ANAND     COMPOSITE_RNG_LIST     PER3            PER3_WEST       'OR', 'WA'          0
ANAND     COMPOSITE_RNG_LIST     PER2            PER2_CENT       'OK', 'TX'          1
ANAND     COMPOSITE_RNG_LIST     PER2            PER2_EAST       'NY', 'CT'          1
ANAND     COMPOSITE_RNG_LIST     PER2            PER2_WEST       'OR', 'WA'          0
ANAND     COMPOSITE_RNG_LIST     PER1            PER1_CENT       'OK', 'TX'          0
ANAND     COMPOSITE_RNG_LIST     PER1            PER1_EAST       'NY', 'CT'          0
ANAND     COMPOSITE_RNG_LIST     PER1            PER1_WEST       'OR', 'WA'          2

Created a local and a global index on the table

SQL> create index i_crl_state on composite_rng_list(cust_state);

Index created.

SQL> create index i_crl_time on composite_rng_list(time_id) local;

Index created.

Index details

SQL> @index
Enter value for owner: ANAND
Enter value for table_name: composite_rng_list

TABLE_OWNER     TABLE_NAME            INDEX_OWNER     INDEX_NAME            TABLESPACE_NAME   NUM_ROWS      CLUST STATUS   INDEX_TYPE
--------------- --------------------- --------------- --------------------- --------------- ---------- ---------- -------- ------------
ANAND           COMPOSITE_RNG_LIST    ANAND           I_CRL_STATE           DEMO                     6          6 VALID    NORMAL
ANAND           COMPOSITE_RNG_LIST    ANAND           I_CRL_TIME                                     6          5 N/A      NORMAL

SQL> @partition_index
Enter value for index_name: I_CRL_TIME
Enter value for owner: ANAND

INDEX_NAME            PARTITION_NA STATUS   TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE   NUM_ROWS
--------------------- ------------ -------- --------------- -------------- ----------- ------------ ----------
I_CRL_TIME            PER1         N/A                                                                    2
I_CRL_TIME            PER2         N/A                                                                    2
I_CRL_TIME            PER3         N/A                                                                    2
I_CRL_TIME            FUTURE       N/A                                                                    0

Now lets create a table with which we would be exchanging the partition. Below I have created EXP_TEMP_PER1 table, which will exchange partition PER1 from COMPOSITE_RNG_LIST table. Note table is created as list partition (based on subpartition of original table). In case you create the table same as source table (range-list partition) and try exchange partition you would be hit with

ORA-14292: Partitioning type of table must match subpartitioning type of composite partition


SQL> create table ANAND.EXP_TEMP_PER1 (
   CUST_ID NUMBER(10)
   , CUST_NAME VARCHAR2(25)
   , CUST_STATE VARCHAR2(2)
   , TIME_ID DATE
 ) tablespace TEST
 partition by LIST (CUST_STATE)
 (
     partition PER1_WEST values ('OR', 'WA')
   , partition PER1_EAST values ('NY', 'CT')
   , partition PER1_CENT values ('OK', 'TX')
 )
 ;

Table created.

Insert the data, into the newly created table.

SQL> insert into EXP_TEMP_PER1 select * from composite_rng_list partition (per1);

2 rows created.

Elapsed: 00:00:00.26
SQL> commit;

Commit complete.

SQL> select * from composite_rng_list partition (per1);

   CUST_ID CUST_NAME                 CU TIME_ID
---------- ------------------------- -- ---------
         4 D                         WA 11-NOV-13
         6 F                         OR 07-JUL-13
SQL> select * from EXP_TEMP_PER1 ;

   CUST_ID CUST_NAME                 CU TIME_ID
---------- ------------------------- -- ---------
         4 D                         WA 11-NOV-13
         6 F                         OR 07-JUL-13

Note, the table created has partitions, no subpartitions

SQL> select table_owner,table_name,partition_name,subpartition_count,num_rows,tablespace_name from dba_tab_partitions where table_owner='ANAND' and table_name='EXP_TEMP_PER1';

TABLE_OWN TABLE_NAME             PARTITION_NAME  SUBPARTITION_COUNT   NUM_ROWS TABLESPACE_NAME
--------- ---------------------- --------------- ------------------ ---------- ---------------
ANAND     EXP_TEMP_PER1          PER1_WEST                        0          2 TEST
ANAND     EXP_TEMP_PER1          PER1_EAST                        0          0 TEST
ANAND     EXP_TEMP_PER1          PER1_CENT                        0          0 TEST

Elapsed: 00:00:00.10
SQL> select table_owner,table_name,partition_name,subpartition_name,high_value,num_rows from dba_tab_subpartitions where table_owner='ANAND' and table_name='EXP_TEMP_PER1';

no rows selected

Lets create local indexes similar to source table on the new table

SQL> create index i_exptper1_time on EXP_TEMP_PER1 (time_id) local;

Index created.

SQL> @partition_index
Enter value for index_name: i_exptper1_time
Enter value for owner: ANAND

INDEX_NAME            PARTITION_NA STATUS   TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE   NUM_ROWS
--------------------- ------------ -------- --------------- -------------- ----------- ------------ ----------
I_EXPTPER1_TIME       PER1_WEST    USABLE   TEST                   8388608     1048576                       2
I_EXPTPER1_TIME       PER1_EAST    USABLE   TEST                                                             0
I_EXPTPER1_TIME       PER1_CENT    USABLE   TEST                                                             0

Lets try the exchange partition

SQL> alter table COMPOSITE_RNG_LIST exchange partition per1 with table EXP_TEMP_PER1 including indexes with validation update global indexes;

Table altered.

SQL> select table_owner,table_name,partition_name,subpartition_name,high_value,num_rows,tablespace_name from dba_tab_subpartitions  where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST' and subpartition_name='PER1';

TABLE_OWN TABLE_NAME             PARTITION_NA SUBPARTITION HIGH_VALUE   NUM_ROWS TABLESPACE_NAME
--------- ---------------------- ------------ ------------ ---------- ---------- ---------------
ANAND     COMPOSITE_RNG_LIST     PER1         PER1_WEST    'OR', 'WA'          2 TEST
ANAND     COMPOSITE_RNG_LIST     PER1         PER1_EAST    'NY', 'CT'          0 TEST
ANAND     COMPOSITE_RNG_LIST     PER1         PER1_CENT    'OK', 'TX'          0 TEST

SQL> select index_name,partition_name,subpartition_name,tablespace_name,status from dba_ind_subpartitions where index_owner='ANAND' and index_name='I_CRL_TIME';

INDEX_NAME            PARTITION_NA SUBPARTITION TABLESPACE_NAME STATUS
--------------------- ------------ ------------ --------------- --------
I_CRL_TIME            FUTURE       FUTURE_CENT  DEMO            USABLE
I_CRL_TIME            FUTURE       FUTURE_EAST  DEMO            USABLE
I_CRL_TIME            FUTURE       FUTURE_WEST  DEMO            USABLE
I_CRL_TIME            PER3         PER3_CENT    DEMO            UNUSABLE
I_CRL_TIME            PER3         PER3_EAST    DEMO            UNUSABLE
I_CRL_TIME            PER3         PER3_WEST    DEMO            UNUSABLE
I_CRL_TIME            PER2         PER2_CENT    DEMO            UNUSABLE
I_CRL_TIME            PER2         PER2_EAST    DEMO            UNUSABLE
I_CRL_TIME            PER2         PER2_WEST    DEMO            UNUSABLE
I_CRL_TIME            PER1         PER1_CENT    TEST            USABLE
I_CRL_TIME            PER1         PER1_EAST    TEST            USABLE
I_CRL_TIME            PER1         PER1_WEST    TEST            USABLE

SQL> select table_owner,table_name,partition_name,subpartition_count,num_rows,tablespace_name from dba_tab_partitions where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST';


TABLE_OWN TABLE_NAME             PARTITION_NA SUBPARTITION_COUNT   NUM_ROWS TABLESPACE_NAME
--------- ---------------------- ------------ ------------------ ---------- ---------------
ANAND     COMPOSITE_RNG_LIST     PER1                          3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     PER2                          3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     PER3                          3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     FUTURE                        3          0 DEMO

As we can see above the local indexes status is “USABLE”. For partitions PER3 and PER2 we see status “UNUSABLE” because I did not use “including indexes” attribute in exchange partition for them.

From Oracle Documentation

When you exchange partitions, logging attributes are preserved. You can optionally specify if local indexes are also to be exchanged (INCLUDING INDEXES clause), and if rows are to be validated for proper mapping (WITH VALIDATION clause).

When you specify WITHOUT VALIDATION for the exchange partition operation, this is normally a fast operation because it involves only data dictionary updates. However, if the table or partitioned table involved in the exchange operation has a primary key or unique constraint enabled, then the exchange operation is performed as if WITH VALIDATION were specified to maintain the integrity of the constraints.
To avoid the overhead of this validation activity, issue the following statement for each constraint before doing the exchange partition operation:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name KEEP INDEX
Then, enable the constraints after the exchange.

If you specify WITHOUT VALIDATION, then you must ensure that the data to be exchanged belongs in the partition you exchange.

Unless you specify UPDATE INDEXES, the database marks UNUSABLE the global indexes or all global index partitions on the table whose partition is being exchanged. Global indexes or global index partitions on the table being exchanged remain invalidated. (You cannot use UPDATE INDEXES for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)

DBA_TAB_SUPARTITIONS shows TEST tablespace, but still the view dba_tab_partitions, show DEMO tablespace for the partition. What needs to be done, so dba_tab_partitions show TEST tablespace for the partition? Please do respond🙂

Reference

Exchanging Partitions

http://psoug.org/reference/partitions.html

Groups – OSDBA OSOPER OSASM

This one is quick and short. I have been asked few time, how can we check what value was specified to OSDBA, OSOPER and OSASM,especially during upgrades (to know what was it set to which initial installation)

# The DBA_GROUP is the OS group which is to be granted OSDBA privileges.
# The OPER_GROUP is the OS group which is to be granted OSOPER privileges.
# The OSASM_GROUP is the OS group which is to be granted OSASM privileges.

The below is from GRID_HOME :-

[grid@testdb1 dbs]$ grep “define SS_” $ORACLE_HOME/rdbms/lib/config.c
#define SS_DBA_GRP “asmdba”
#define SS_OPER_GRP “”
#define SS_ASM_GRP “asmadmin”

Even installation logfiles can be checked.

Silent Upgrade Oracle GoldenGate 12.1.2.0.0 to 12.1.2.1.2

Today I worked on silent upgrade of Oracle GoldenGate from 12.1.2.0.0 to 12.1.2.1.2, so thought to blog it which might help some of you. These are the steps I performed

Current GoldenGate Home –> /oracle/app/product/ogg12.1.2

1. Gather the details of GoldenGate Processes before stopping them

GGSCI> INFO EXTRACT EXTL, SHOWCH
INFO EXTRACT DPUMP, SHOWCH
GGSCI> SEND EXTRACT EXTL, SHOWTRANS
GGSCI> STOP EXTRACT DPUMP
GGSCI> STOP EXTRACT EXTL
GGSCI> SEND REPLICAT REPL STATUS
GGSCI> STOP REPLICAT REPL
GGSCI> STOP JAGENT
GGSCI> STOP MANAGER

2. Backup the existing binaries and associated files needed for your environment.

3. Edit the response file and run the runInstaller in silent mode

In the response file

#-------------------------------------------------------------------------------
# Specify the installation option.
# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and
#         ORA11g for installing Oracle GoldenGate for Oracle Database 11g
#-------------------------------------------------------------------------------
INSTALL_OPTION=ORA11g

#-------------------------------------------------------------------------------
# Specify a location to install Oracle GoldenGate
#-------------------------------------------------------------------------------
SOFTWARE_LOCATION=/oracle2/app/product/ogg12.1.2

#-------------------------------------------------------------------------------
# Specify true to start the manager after installation.
#-------------------------------------------------------------------------------
START_MANAGER=false

#-------------------------------------------------------------------------------
# Specify a free port within the valid range for the manager process.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
MANAGER_PORT=

#-------------------------------------------------------------------------------
# Specify the location of the Oracle Database.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
DATABASE_LOCATION=

#-------------------------------------------------------------------------------
# Specify the location which holds the install inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=/oracle/oraInventory

#-------------------------------------------------------------------------------
# Unix group to be set for the inventory directory.
# This parameter is not applicable if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=oinstall

Run the runInstaller in Silent mode

[oracle@oracle gg]$ export ORACLE_HOME=/oracle/app/product/ogg12.1.2
[oracle@oracle gg]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
[oracle@oracle gg]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
[oracle@oracle Disk1]$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile /opt/oracle/ogg_upg.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 181836 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 11999 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-04-21_12-23-03AM. Please wait ...You can find the log of this install session at:
 /oracle/oraInventory/logs/installActions2015-04-21_12-23-03AM.log

Prepare in progress.
..................................................   10% Done.

Prepare successful.

Copy files in progress.
..................................................   44% Done.
..................................................   50% Done.
..................................................   60% Done.
..................................................   65% Done.
..................................................   72% Done.
....................
Copy files successful.

Link binaries in progress.
..........
Link binaries successful.
..................................................   82% Done.

Setup files in progress.
..................................................   100% Done.

Setup files successful.
The installation of Oracle GoldenGate Core was successful.
Please check '/oracle/oraInventory/logs/silentInstall2015-04-21_12-23-03AM.log' for more details.
Successfully Setup Software.
[oracle@oracle1 Disk1]$

4. Confirm GoldenGate has been upgraded

[oracle@oracle Disk1]$ cd $ORACLE_HOME
[oracle@oracle ogg12.1.2]$
[oracle@oracle ogg12.1.2]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

5. Apply Patch 20265694: Oracle GoldenGate V12.1.2.1.2 for Oracle 11G

[oracle@oracle1 20265694]$ opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/product/ogg12.1.2
Central Inventory : /oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /oracle/app/product/ogg12.1.2/cfgtoollogs/opatch/opatch2015-04-21_00-24-15AM.log

Applying interim patch '20265694' to OH '/oracle/app/product/ogg12.1.2'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/app/product/ogg12.1.2')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.oggcore.ora11g, 12.1.2.1.0...
Patch 20265694 successfully applied
Log file location: /oracle/app/product/ogg12.1.2/cfgtoollogs/opatch/opatch2015-04-21_00-24-15AM.log

OPatch succeeded.
[oracle@oracle1 20265694]$
[oracle@oracle1 20265694]$ opatch lsinv
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.
........................
........................
Patch  20265694     : applied on Tue Apr 21 00:24:27 EDT 2015
Unique Patch ID:  18593256
   Created on 3 Feb 2015, 15:03:21 hrs PST8PDT
   Bugs fixed:
     19818362, 19602692, 19241234, 17423191, 19781984, 19535319, 19724915
     19721652, 19516537, 19441114, 19132627, 19889991, 19681035

6. Confirm the GoldenGate version is now 12.1.2.1.2

[oracle@oracle ogg12.1.2]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.2 20133048 OGGCORE_12.1.2.1.0OGGBP_PLATFORMS_141228.0533_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 28 2014 13:19:44
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

7. Start the GoldenGate Processes


GGSCI (oracle.test.com) 1> dblogin userid ggate, Password "xxxxxxxx"
Successfully logged into database.

GGSCI> START MANAGER
GGSCI> START JAGENT
GGSCI> START EXTRACT EXTL
GGSCI> START EXTRACT DPUMP
GGSCI> START REPLICAT REPL

Primary on FileSystem and Standby on ASM

For one of the client, standby server went down. We had another standby server which was kept down for more than a month. Decision was taken to start the server and apply incremental SCN based backup on the standby database.

The standby was on ASM and the Primary on filesystem.Incremental backup was started from the SCN reported by below query

select min(fhscn) from x$kcvfh;

Once the backup completed, it was transferred to standby, standby was mounted (using the old controlfile), backups were cataloged and recovery performed using ‘recover database noredo’.

The recovery was going on, and was handed over to me. After the recovery completed I restored the latest controlfile from Primary and mounted the standby. At this point the controlfile was with the information of the filesystem as they were on the Primary side. The next step was to register everything we had on Standby side:

[oracle@oracle3:~ (db)]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 8 04:57:01 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ABCD (DBID=1463580380, not open)

RMAN> catalog  start with '+DATA/adbc_oracle3/DATAFILE/';

searching for all files that match the pattern +DATA/adbc_oracle3/DATAFILE/

List of Files Unknown to the Database
=====================================
File Name: +data/adbc_oracle3/DATAFILE/TEST.256.844395985
File Name: +data/adbc_oracle3/DATAFILE/TEST.257.844397067
.................
.................
File Name: +data/adbc_oracle3/DATAFILE/TEST.416.865953683

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +data/adbc_oracle3/DATAFILE/TEST.256.844395985
File Name: +data/adbc_oracle3/DATAFILE/TEST.257.844397067
................
...............
File Name: +data/adbc_oracle3/DATAFILE/TEST.416.865953683

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ABCD_ORACLE3

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     /oradata/abcd_oracle2/datafile/o1_mf_system_825xkscr_.dbf
2    0        SYSAUX               ***     /oradata/abcd_oracle2/datafile/o1_mf_sysaux_825y451r_.dbf
3    0        TEST                 ***     /oradata/abcd_oracle2/datafile/o1_mf_test_825s84mw_.dbf
4    0        TEST                 ***     /oradata2/abcd_oracle2/datafile/o1_mf_test_8dr1v332_.dbf
................
................
................
147  0        TEST                   ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_b8k8hcrh_.dbf
148  0        TEST                   ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_b8k8hdhf_.dbf
149  0        TEST                   ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_b8k8hf6o_.dbf
150  0        TEST                   ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_b8k8hg1j_.dbf
151  0        TEST                   ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_bb318bhs_.dbf
152  0        TEST                   ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_bb318cff_.dbf
153  0        TEST_INDEX             ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_index_bb318pmy_.dbf
154  0        TEST_NOLOGGING         ***     /oradata3/abcd_oracle2/datafile/o1_mf_test_nolog_bbm2s7vk_.dbf
155  0        TESTINDEX             ***     /oradata3/abcd_oracle2/datafile/o1_mf_test_index_bbm2z7nv_.dbf
156  0        PERFSTAT             ***     /oradata3/abcd_oracle2/datafile/o1_mf_perfstat_bbm312pf_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
3    15104    TEMP                 32767       /oradata4/abcd_oracle2/datafile/o1_mf_temp_b633ppbr_.tmp
4    25600    TEMP                 32767       /oradata4/abcd_oracle2/datafile/o1_mf_temp_b633ppcf_.tmp

After catalog completed, it was time to switch database to copy and it failed with below error

RMAN> switch database to copy;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 01/08/2015 05:00:25
RMAN-06571: datafile 151 does not have recoverable copy

After some analysis found, it was due to missing datafile on standby. The datafile was created on Primary after the standby was down and the recovery using the incremental backup was done with the older controlfile which had no information about the new datafiles.

Datafile # 151 – 156 were missing, so cataloged the backup pieces again as the controlfile was restored, and started restoring the datafile

RMAN> restore datafile 151;

Starting restore at 08-JAN-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00151 to /oradata4/ABCD_ORACLE2/datafile/o1_mf_ct_bb318bhs_.dbf
channel ORA_DISK_1: reading from backup piece +BACKUP/ABCD_ORACLE3/backupset/restore/incr_standby_5sps58rd_1_1

channel ORA_DISK_1: piece handle=+BACKUP/ABCD_ORACLE3/backupset/restore/incr_standby_5sps58rd_1_1 tag=TAG20150107T192825
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:19:05
Finished restore at 08-JAN-15

RMAN>

After the restoration completed, report schema showed

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name adbc_oracle3

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     /oradata/ABCD_ORACLE2/datafile/o1_mf_system_825xkscr_.dbf
2    0        SYSAUX               ***     /oradata/ABCD_ORACLE2/datafile/o1_mf_sysaux_825y451r_.dbf
.................
151  30720    TEST                  ***     +DATA/adbc_oracle3/datafile/test.417.868424659 
152  30720    TEST                  ***     +DATA/adbc_oracle3/datafile/test.418.868424659 
................

Tried to perform “switch database to copy” which again failed with the same error “RMAN-06571: datafile 151 does not have recoverable copy” . At this point I though to use “switch datafile to copy” for which generated dynamic sql from primary and ran in to standby.

Generated switch command sql from Primary :-

SQL> select 'switch datafile '||file#||' to copy;' from v$datafile;

[oracle@oracle3:~ (db)]$ vi swtch_copy.rman
[oracle@oracle3:~ (db)]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 8 06:09:46 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ABCD (DBID=1463580380, not open)

RMAN> @swtch_copy.rman

RMAN> switch datafile 2 to copy;
using target database control file instead of recovery catalog
datafile 2 switched to datafile copy "+DATA/adbc_oracle3/datafile/sysaux.277.844418721"

............................
...........................
...........................
RMAN> switch datafile 149 to copy;
datafile 149 switched to datafile copy "+DATA/adbc_oracle3/datafile/ct.415.865953681"

RMAN> switch datafile 156 to copy;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 01/08/2015 06:28:54
RMAN-06571: datafile 156 does not have recoverable copy

RMAN>
RMAN> **end-of-file**

Performed ‘recover database noredo’ again and this time it was pretty quick and then tried recover standby database

[oracle@oracle3:~/working/anand (abcd)]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 8 06:31:38 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> recover standby database;
ORA-00279: change 38328244436 generated at 01/07/2015 19:28:32 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 38328244436 for thread 1 is in sequence #98501


Specify log: {=suggested | filename | AUTO | CANCEL}
^C
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/adbc_oracle3/datafile/system.357.844454911'


Did few changes in DGMGRL parameters and enabled standby configuration. After few hours, standby was insync with Primary. Stopped the MRP , dropped the standby
redo logfiles as they showed filesystem, and created on ASM. Opened the standby in read only mode and started the MRP.

ORA-00600: [ktbdchk1: bad dscn]

Last week I had performed switchover activity of database on version 11.2.0.3 The switchover was performed using dgmgrl “swicthover to standby” command. After sometime we started receiving “ORA-00600: [ktbdchk1: bad dscn]” on the primary database.

Tue Dec 16 10:33:26 2014
Errors in file /ora_software/diag/rdbms/db02_dbv/dbv/trace/db02_ora_16271.trc  (incident=434103):
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /ora_software/diag/rdbms/db02_dbv/dbv/incident/incdir_434103/db02_ora_16271_i434103.trc

The trace file showed

*** ACTION NAME:() 2014-12-16 10:33:26.857

Dump continued from file: /ora_software/diag/rdbms/db02_dbv/dbv/trace/db02_ora_16271.trc
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [], [], [], [], []

========= Dump for incident 434103 (ORA 600 [ktbdchk1: bad dscn]) ========
----- Beginning of Customized Incident Dump(s) -----
[ktbdchk] -- ktbgcl4 -- bad dscn
dependent scn: 0x0008.f197f24e recent scn: 0x0008.c7313a4c current scn: 0x0008.c7313a4c
----- End of Customized Incident Dump(s) -----
*** 2014-12-16 10:33:26.961
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=dmrpvzvbbsuy5) -----
INSERT INTO MSG_OPEN( SITE_ID, CLIENT_ID, CAMP_ID, MESSAGE_ID, ID, USER_ID, ADDRESS, DATE_OPENED) VALUES( :B7 , :B6 , :B5 , :B4 , LOG_SEQ.NEXTVAL, :B3 , :B2 , :B1 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x32276ee18       611  package body TP.EM_PKG
0x31cbac388         3  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+36        call     kgdsdst()            000000000 ? 000000000 ?
                                                   7FFFFDA13028 ? 000000001 ?
                                                   000000001 ? 000000002 ?
ksedst1()+98         call     skdstdst()           000000000 ? 000000000 ?
                                                   7FFFFDA13028 ? 000000001 ?
                                                   000000000 ? 000000002 ?
ksedst()+34          call     ksedst1()            000000000 ? 000000001 ?
                                                   7FFFFDA13028 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbkedDefDump()+2741  call     ksedst()             000000000 ? 000000001 ?
                                                   7FFFFDA13028 ? 000000001 ?
                                                   000000000 ? 000000002 ?
........................

Searching for the issue on Metalink pointed to the below Document:-

ALERT Description and fix for Bug 8895202: ORA-1555 / ORA-600 [ktbdchk1: bad dscn] ORA-600 [2663] in Physical Standby after switchover (Doc ID 1608167.1)

As per metalink

In a Data Guard environment with Physical Standby (including Active Data Guard), invalid SCNs can be introduced in index blocks after a switchover.

Symptoms ORA-1555 / ORA-600 [ktbdchk1: bad dscn] / ktbGetDependentScn / Dependent scn violations as the block ITL has higher COMMIT SCN than block SCN. DBVERIFY reports the next error when the fix of Bug 7517208 is present; reference Note 7517208.8 for interim patches: itl[] has higher commit scn(aaa.bbb) than block scn (xx.yy) Page failed with check code 6056 There is NO DATA CORRUPTION in the block.

To Resolve

The fix of Bug 8895202 is the workaround.

Although the fix of Bug 8895202 is included in patchset 11.2.0.2 and later, the fix needs to be enabled by setting parameter _ktb_debug_flags = 8.

SQL> alter system set "_ktb_debug_flags"=8 scope=both sid='*';

System altered.

SQL> exit

If you are using Oracle version less than 11.2.0.2, then rebuilding index is the option, as we did for one of the client on 11.1.0.7 version.

One thing to note is —

In rare cases blocks healed by this fix may cause queries to fail with an ORA-600 [ktbgcl1_KTUCLOMINSCN_1] as described in Note 13513004.8 / Bug 13513004.

For more detail Metalink Doc 1608167.1

Upgrading to 11.2.0.4 – Dictionary View Performing Poor

Just a quick blog post on things you might see after upgrading to 11.2.0.4. We recently upgraded database from 11.2.0.3 to 11.2.0.4 and query on some data dictionary views ran too slow.

1. Performace of query on dba_free_space degraded
2. Performance of query involving dba_segments is slow

DEV01> select ceil(sum(b.bytes)/1024/1024) b from sys.dba_free_space b;

Elapsed: 01:31:45.78

Searching MOS pointed to these Doc Ids :-

Insert Statement Based Upon a Query Against DBA_SEGMENTS is Slow After Applying 11.2.0.4 Patchset (Doc ID 1915426.1)

Query Against DBA_FREE_SPACE is Slow After Applying 11.2.0.4 (Doc ID 1904677.1)

DEV01> select ceil(sum(b.bytes)/1024/1024) b from sys.dba_free_space b;

Elapsed: 00:00:01.38