Drop multiple columns faster in Oracle

May 30, 2017 Leave a comment

From documentation: http://docs.oracle.com/database/122/ADMIN/managing-tables.htm#GUID-74A86E52-E2D2-405E-B888-94164E3973B9

“If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE...SET UNUSED statement.

This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. In most cases, constraints, indexes, and statistics defined on the column are also removed. The exception is that any internal indexes for LOB columns that are marked unused are not removed.

To mark the hiredate and mgr columns as unused, execute the following statement:

ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);

You can later remove columns that are marked as unused by issuing an ALTER TABLE...DROP UNUSED COLUMNS statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.

The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. The COUNT field shows the number of unused columns in the table.

SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER                       TABLE_NAME                  COUNT
--------------------------- --------------------------- -----
HR                          ADMIN_EMP                       2

The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.

In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

”

Filed under 10g, 11g, 12c, DB Tagged with ,

Upgrade 12.1.0.2 to 12.2.0.1 fails at 109 phase with “Unexpected error encountered”

May 20, 2017 1 Comment

We were trying to upgrade our database one week ago and it failed with the following error:

Serial Phase #:108 [ORCL] Files:1 Time: 1s 
******************* Migration ****************** 
Serial Phase #:109 [ORCL] Files:1 wait_for_completion: unexpected error in next_proc() 
catconExec: unexpected error in wait_for_completions 

Unexpected error encountered in catconExec; exiting 

Unexpected error encountered in catctlMain; Error Stack Below; exiting 
Died at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 7822. 
at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 7822. 
main::catctlDie("\x{a}Unexpected error encountered in catconExec; exiting\x{a} 2") called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 4556 
main::catctlExecutePhaseFiles(109, 1, undef, undef, undef) called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 1862 
main::catctlRunPhase(109, 1, undef, undef, undef) called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 2006 
main::catctlRunPhases(0, 116, 116, undef, undef, undef) called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 2171 
main::catctlRunMainPhases() called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 1341 
main::catctlMain() called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 1256 
eval {...} called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 1254

We asked Oracle Support. It seemed they did not have such situation before.
12.2 is the newest version and any existing bugs are not known at this time.

We postponed upgrade for the next week after upgrade failure. And I started to read all the documentation steps carefully. I almost learned by heart 😀

Documentation id where the upgrade steps are written is  2173141.1

I will write down upgrade steps and the error that happened at the phase 109.
Then let’s correct an error and finish upgrade successfully.

================ Preupgrade steps

0. Copy orapwORCL, sqlnet.ora, tnsnames.ora, listener.ora files from old home to new home.

  1. Execute Preupgrade script from source home 
    export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.1.0/db/bin
$ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.2.0/db/rdbms/admin/preupgrade.jar TERMINAL TEXT

    It will generate two scripts pre/post fixups.

  2. Run preupgrade fixup , it will not correct all problems. Some steps must be done manually. 
    sqlplus /  as sysdba
@?/rdbms/admin/preupgrade_fixups.sql
  3. In documentation there is written to backup whole database, but our database is 11TB in size, so backing up before upgrade is not an option.
    But if you read doc carefully , there is written that you may make schema tablespaces offline(read only) which means that it is not necessary to backup schema tablesapces, because they are not changed during upgrade.
    So we can simply shutdown cleanly (normal, immediate, transactional)  our database and cold copy system, sysaux datafiles to another safe location. You must also copy one of the controlfile.
    Copying undo tablespace datafiles is not necessary, you are still able to recover database when it is cleanly shutdown. So if you backup undo , you may be saved with extra steps that is necessary to recover database without undo.Take user tablespaces read only. So output of this select:

    SELECT distinct 'alter tablespace '|| tablespace_name||' read only;' 
from dba_data_files
where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS01','UNDOTBS02');

Please indicate your UNDO tablesapce name instead of mine.

4. Disable any custom DDL trigger , if exists. I have had created DDL trigger, so disabling it.

           alter trigger sys.audit_ddl_trg disable;

5. Purge recyclebin and gather dictionary statistics

           purge dba_recyclebin;
           exec dbms_stats.gather_dictionary_stats;

6. Shutdown database with clean option! or you will not be able to restore after upgrade fails.

           sqlplus / as sysdba
           alter database checkpoint;
           shutdown immediate;
           lsnrctl stop

7.  cold copy SYSTEM, SYSAUX, control files

            cp /ud01/oradata/ORCL/sysaux01.dbf /ud02/backup_mk/
            cp /ud01/oradata/ORCL/system01.dbf /ud02/backup_mk/
            cp /ud01/oradata/ORCL/control01.ctl /ud02/backup_mk/
            cp /ud01/oradata/ORCL/control02.ctl /ud02/backup_mk/

======================Upgrade

8.  Start DB in upgrade mode from target ORACLE_HOME

           export ORACLE_HOME=/u01/app/oracle/product/12.2.0/db
           export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.2.0/db/bin:/u01/app/oracle/product/12.2.0/db

           sqlplus / as sysdba
           startup upgrade;
           exit

           cd /u01/app/oracle/product/12.2.0/db/bin
           ./dbupgrade -n 33 -T
           

-T option tells upgrade that schema tablespaces need to be offline(we already done it, just indicate -T)

So, here at phase 109 it fails with unexpected error , mentioned previously.

You must see all the logs that the upgrade creates.. the main error was written in catupgrd0.log

              catrequtlmg: b_StatEvt = TRUE 
              catrequtlmg: b_SelProps = FALSE 
              catrequtlmg: b_UpgradeMode = TRUE 
              catrequtlmg: b_InUtlMig = TRUE 
              catrequtlmg: Deleting table stats 
              catrequtlmg: Gathering Table Stats OBJ$MIG 
              catrequtlmg: Gathering Table Stats USER$MIG 
              declare 
              * 
              ERROR at line 1: 
              ORA-01422: exact fetch returns more than requested number of rows 
              ORA-06512: at "SYS.DBMS_STATS", line 36873 
              ORA-06512: at "SYS.DBMS_STATS", line 36507 
              ORA-06512: at "SYS.DBMS_STATS", line 35428 
              ORA-06512: at "SYS.DBMS_STATS", line 34760 
              ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22496 
              ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22483 
              ORA-06512: at "SYS.DBMS_STATS", line 34416 
              ORA-06512: at "SYS.DBMS_STATS", line 35168 
              ORA-06512: at "SYS.DBMS_STATS", line 36230 
              ORA-06512: at "SYS.DBMS_STATS", line 36716 
              ORA-06512: at line 149

As you see it was gathering statistics on USER$MIG table and it got ORA-01422 🙂
If you try to gather statistics manually you will also get the same error:

         EXEC sys.dbms_stats.gather_table_stats('SYS', 'USER$MIG',method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');

If you check entries in obj$ table, you will see that there are duplicate entries:

          SQL> select to_char(OBJ#), name,  ctime, STATUS 
               from obj$ 
               where name='USER$MIG'; 

          TO_CHAR(OBJ#) NAME CTIME 
          ----------------- ------------ -------------------- 
          956329 USER$MIG 08-10-2015 12:59:55 
          956328 USER$MIG 08-10-2015 12:59:55 
          956325 USER$MIG 08-10-2015 12:59:53 
          956327 USER$MIG 08-10-2015 12:59:55 
          956326 USER$MIG 08-10-2015 12:59:55 
          1621545 USER$MIG 19-05-2017 22:55:26

There are six entries. Five of them is created in 2015 (It maybe left from previous upgrade in 2015 that also failed for the first time)

Oracle support did not advice to delete old entries from this table, BUT support sometimes is                       wrong. We had no time , we were upgrading database 4 hours , for the first time it was failed, we                 had backup so we risked and deleted 2015 entries from this table.

          delete from obj$ 
          where OBJ# in (956329, 
          956328, 
          956325, 
          956327, 
          956326); 
          commit;

Rerun upgrade from phase 109

          ./dbupgrade -p 109 -T -n 40

And it completed successfully (happy) , so our risk was right !!!!

################Post Upgrade

           SQL> @?/rdbms/admin/postupgrade_fixups.sql

It will fix up some steps that can be done automatically but  it may also write steps that must be                   done manually.  One of them is upgrading timestamp, which I have described in my previous                       post. “Upgrading timezone manually in 12c”

Change compatible variable in your spfile

           From 
           *.compatible='12.1.0.2.0'
           To
           *.compatible='12.2.0.1.0'

Change home to new oracle home in /etc/oratab and in listener.ora.

Recompile invalid objects

           SQL> @?/rdbms/admin/utlrp.sql
           select count(*) from dba_objects where status!='VALID';

Start listener from new home:

           export ORACLE_HOME=/u01/app/oracle/product/12.2.0/db/bin
           export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.2.0/db/bin
           lsnrctl start

Enable trigger if you have disabled before

           alter trigger sys.audit_ddl_trg enable;

Make schema tablespaces read write:

           SELECT distinct 'alter tablespace '|| tablespace_name||' read write;' 
           from dba_data_files
           where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS01','UNDOTBS02');

Filed under 12c, DB, upgrade Tagged with , , , , , ,

Upgrading timezone manually in 12c

May 20, 2017 5 Comments

After manual upgrade from 12.1.0.2 to 12.2.0.1 oracle pre/post upgrade scripts required to upgrade timezone manually.

Our database was using timezone datafile version 18 and the target 12.2.0.1.0 database ships with timezone datafile version 26.

Updating timezone is somehow complicated process.

I will provide you with the steps that we have used:

The whole steps are described on metalink note : Doc ID 1509653.1

  1. Check the current version

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
———-
18

  1. Check aslo
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;

PROPERTY_NAME                VALUE
 --------------------------- ----------

DST_PRIMARY_TT_VERSION      18
DST_SECONDARY_TT_VERSION    0
DST_UPGRADE_STATE           NONE

If DST_PRIMARY_TT_VERSION is <the old DST version number>, DST_SECONDARY_TT_VERSION  is 0 and  DST_UPGRADE_STATE is NONE then continue, otherwise you need to see Note 1509653.1

  1.  Purge recyclebin
sqlplus / as sysdba
purge dba_recyclebin;

–this alter session might speed up DBMS_DST on some db’s
— see Bug 10209691 / Bug 12658443

alter session set "_with_subquery"=materialize;

— to avoid the issue in note 1407273.1

alter session set "_simple_view_merging"=TRUE;

— start prepare window , these steps will NOT update any data yet.

exec DBMS_DST.BEGIN_PREPARE(26);

— truncate logging tables if they exist.

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

SQL> select * from sys.dst$affected_tables;
no rows selected

SQL> select * from sys.dst$error_table;
no rows selected

–If there is no error then end the prepare

EXEC DBMS_DST.END_PREPARE;

5. If error_table is empty we can run the actual timezone upgrade

sqlplus / as sysdba
shutdown immediate;
startup upgrade;

purge dba_recyclebin;
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv')

alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;

exec DBMS_DST.BEGIN_UPGRADE(26);

SQL> SELECT * FROM sys.dst$error_table;
no rows selected

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

PROPERTY_NAME              VALUE
-------------------------  ------------------------------------
DST_PRIMARY_TT_VERSION     26
DST_SECONDARY_TT_VERSION   18
DST_UPGRADE_STATE          UPGRADE

— some oracle provided users may be listed here, that is normal

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS
FROM ALL_TSTZ_TABLES
where UPGRADE_IN_PROGRESS='YES';

shutdown immediate
startup

alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;

— now upgrade the tables who need action

VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

 — this select should return now rows

SELECT * FROM sys.dst$error_table;

 — if there where no failures then end the upgrade.

VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/

— Check

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_26.dat              26          0

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

PROPERTY_NAME             VALUE
-----------------         ----------
DST_PRIMARY_TT_VERSION    26
DST_SECONDARY_TT_VERSION  18
DST_UPGRADE_STATE         NONE

–Check the following:

SELECT VERSION FROM v$timezone_file;

VERSION 
-------------
26
select TZ_VERSION from registry$database;

TZ_VERSION  
---------
18

–if they differ after an upgrade then updating registry$database can be done by

conn / as sysdba
update registry$database 
set TZ_VERSION = (select version 
                  FROM   v$timezone_file);
commit;

 

 

Filed under 12c, upgrade Tagged with , ,

Retrieve first 100 prime numbers using SQL Query

May 11, 2017 1 Comment

On an interview , one of our colleague asked participant the following question:

“How can you generate prime numbers using just query?”

Answering to this question using PL/SQL is simple using loop. But using SELECT 🙂 not so simple 🙂

I found the answer and want to share with you.

select l prime_number
from (select level l from dual connect by level <= 100)
, (select level m from dual connect by level <= 100)
where m<=l
group by l
having count(case l/m when trunc(l/m) then ‘Y’ end) = 2
order by l

Filed under SQL&PL/SQL

Convert Oracle SE to EE

May 4, 2017 Leave a comment

Upgrading Oracle database from Standard Edition to Enterprise Edition is very simple.

For example, we have running Oracle SE in ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

Let’s start converting it…

  1. Install new home in /u01/app/oracle/product/12.1.0/dbhome_2 just indicate EE during installation(not SE).
    Response file entries(db_install.rsp):

    ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2
oracle.install.db.InstallEdition=EE
  2. Shutdown database and listener from old home. 
    . oraenv
ORACLE_SID = [ORCL] ?

lsnrctl stop
sqlplus / as sysdba
shutdown immediate;
  3. Change ORACLE_HOME in oratab and .bash_profile 
    cat /etc/oratab
ORCL:/u01/app/oracle/product/12.1.0/dbhome_2:Y
    cat ~/.bash_profile
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=EYC
export TNS_ADMIN=$ORACLE_HOME/network/admin
export EDITOR=vi
  4. Copy listener.ora, tnsnames.ora, sqlnet.ora, spfileORCL.ora, orapwORCL to new home 
    cp /u01/app/oracle/product/12.1.0/dbhome_1/network/*.ora /u01/app/oracle/product/12.1.0/dbhome_2/network/
cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileORCL.ora /u01/app/oracle/product/12.1.0/dbhome_2/dbs/
cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwORCL /u01/app/oracle/product/12.1.0/dbhome_2/dbs/

    Please verify that you don’t have old ORACLE_HOME indicated anywhere in these files.

  5. Renew environment variables 
    . oraenv
ORACLE_SID = [ORCL] ? 

which sqlplus
/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus

sqlplus / as sysdba
startup;
  6. Check that you have EE 
    SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

Filed under 12c, Real World Scenarios, upgrade Tagged with , , , , ,