Change timezone on Linux using /etc/localtime file

September 11, 2017 Leave a comment

Assume we have timezone UTC:

[root@oradbfw-hq01-mgmt ~]# date
Mon Sep 11 09:29:27 UTC 2017

On many distributions , like OEL, CentOS.. . the timezone is conntrolled by localtime file located in /etc directory.

# cd /etc

Localtime is a symbolic link , so we can remove it

# rm localtime

All timezones are located under /usr/share/zoneinfo.
I live and my servers are located in Tbilisi, Georgia. So searching my timezone file:

[root@oradbfw-hq01-mgmt etc]# ll /usr/share/zoneinfo/Asia/Tbilisi
-rw-r–r–. 1 root root 1024 Mar 24 09:21 /usr/share/zoneinfo/Asia/Tbilisi

My country is not in Asia, but in Europe 🙂 this condition changed recently so Linux has not updated and still thinks we in Asia. (Don’t tell this to my goverment 🙂 )

Let’s create correct symbolic link:

# cd /etc
# ln -s /usr/share/zoneinfo/Asia/Tbilisi localtime

Let’s check that it was changed:

[root@oradbfw-hq01-mgmt etc]# date
Mon Sep 11 13:47:04 +04 2017

Filed under Linux, Real World Scenarios Tagged with , , , , ,

Installing DB Vault to an Oracle 12c non-Container Database

August 30, 2017 Leave a comment

Database Vault is a product by which you can restrict access even for SYS, DBA users and so on , so this tool is for Security Administrators to control even DBAs.

We are configuring Oracle Audit Vault and Database Firewall  which is one product and used to find SQL injections and block harmful SQL statements . This product is perfect when you are not connecting locally. But if you connected to the server via ssh and from there connected to the database using sqlplus then Oracle AVDF will not help. For that case there exists Database Vault.

Don’t be consufed : Database Vault  and Audit Vault are two different products. DB Vault should be inabled from the database , let’s see how we do it:

— To check Oracle Label security And DB Vault use below SQL

SQL> select comp_id,status from dba_registry where comp_id in (‘OLS’,’DV’);
COMP_ID STATUS
—————————— ——————————————–
OLS VALID

Note: As you see in my database Label Security is already installed but Database Vault not.

— Check DB vault if already registered

SQL> SELECT parameter,value FROM gv$OPTION WHERE PARAMETER in
2 ( ‘Oracle Database Vault’,’Oracle Label Security’);
PARAMETER VALUE
—————————————————————- —————————————————————-
Oracle Label Security TRUE
Oracle Database Vault FALSE

–Take  backup of  some tables and views.

SQL> create table a_dba_network_acls as
select * FROM  cdb_network_acls;

SQL> create table a_dba_network_acl_privileges as
select * from cdb_network_acl_privileges;

SQL> create table a_gv$parameter as
select * from gv$parameter ;

SQL> create table a_dba_tab_privs as
Select * from dba_tab_privs;

SQL> create table a_dba_sys_privs as
Select * from dba_sys_privs;

SQL> create table a_dba_sys_privs as
Select * from dba_sys_privs;

SQL> create table a_dba_role_privs as
Select * from dba_role_privs;

SQL> create table a_dba_objects as
select owner,object_name,object_type
from dba_objects
where status=’INVALID’ and object_type <> ‘SYNONYM’ ;

SQL> create table a_dba_registry as
select * from dba_registry;

Note: You must consider that enabling Database Vault causes the followng:

1. Enables parameter sql92_security (needs database restart) .

“The SQL92 standards specify that security administrators should be able to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in  WHERE or SET clause. SQL92_SECURITY specifies whether users must have been granted the SELECT object privilege in order to execute such UPDATE or DELETE statements.”

So better to generate script that will grant necessary SELECT privileges:

select ‘grant select on ‘||owner||’.”‘||table_name||'” to ‘||grantee||’;’
from (
select grantee, owner, table_name
from dba_tab_privs
where privilege in (‘UPDATE’,’DELETE’,’INSERT’)
minus
select grantee, owner, table_name
from dba_tab_privs
where privilege=’SELECT’
)

Run grants generated above not to have problems after restart.

2. Revokes some privileges from roles and users (even from SYS). So use the following queries to generate grant scripts and in case it is necessary regrant them.

select ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘|| grantee||’;’
from (
select * from a_dba_tab_privs
minus
select * from dba_tab_privs
);

select ‘grant ‘||privilege||’ to ‘|| grantee||’;’
from (
select * from a_dba_sys_privs
minus
select * from dba_sys_privs
);

select ‘grant ‘||granted_role||’ to ‘|| grantee||’;’
from (
select * from a_dba_role_privs
minus
select * from dba_role_privs
)

–Create DB Vault owner and User Administrator users

SQL> CREATE USER dvowner IDENTIFIED BY oracle
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;

SQL> GRANT CREATE SESSION TO dvowner;

SQL> CREATE USER dvacctmngr IDENTIFIED BY oracle
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;

SQL> GRANT CREATE SESSION TO dvacctmngr;

 

–Configure DB Vault

SQL> BEGIN
DVSYS.CONFIGURE_DV (
dvowner_uname => ‘dvowner’,
dvacctmgr_uname => ‘dvacctmngr’);
END;
/

If your environment is like mine than you will see the followng error and go directly to the section “Scenario 2”

ORA-06550: line 2, column 1:
PLS-00201: identifier ‘DVSYS.CONFIGURE_DV’ must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Let’s continue the Scenario 1 when you have no errors after running DVSYS.CONFIGURE_DV and then I will write how to avoid this error.

–Recompile objects

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

–Enable Database Vault

SQL> EXEC DBMS_MACADM.ENABLE_DV;
SQL> commit;

–Startup the Database and the installation is finished

SQL> shutdown immediate;
SQL> startup;

That’s it.

Scenario 2: Having errors: 

We should use DBCA to add option DV, I don’t support GUIs so writing script in silent mode:

–Check what options we have in DBCA

[oracle@TCIPreRel bin]$ dbca -CONFIGUREDATABASE -silent -h

-configureDatabase
-sourceDB <Database unique name for RAC Database and SID for Single Instance Database>
[-sysDBAUserName <user name with SYSDBA privileges>
-sysDBAPassword <password for sysDBAUserName user name>]
[-registerWithDirService|-unregisterWithDirService|-regenerateDBPassword <true | false>
-dirServiceUserName <user name for directory service>
-dirServicePassword <password for directory service >
-walletPassword <password for database wallet >]
[-addDBOption <Specify any of the following DB Options as a comma separated list: JSERVER | ORACLE_TEXT | IMEDIA | CWMLITE | SPATIAL | OMS | APEX | DV>]

[-dvConfiguration <true | false Specify “true” to configure and enable Database Vault
-dvUserName <Specify Database Vault Owner user name>
-dvUserPassword <Specify Database Vault Owner password>
-dvAccountManagerName <Specify separate Database Vault Account Manager >
-dvAccountManagerPassword <Specify Database Vault Account Manager password>]

–Run the configuration

[oracle@TCIPreRel bin]$ dbca -CONFIGUREDATABASE -silent -sourceDB LBTCI -sysDBAUserName sys -sysDBAPassword oracle -addDBOption DV -dvConfiguration true -dvUserName dvowne -dvUserPassword oracle -dvAccountManagerName dvacctmngr -dvAccountManagerPassword oracle

Preparing to Configure Database
2% complete
5% complete
28% complete
Adding Oracle Database Vault
85% complete
Completing Database Configuration
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/LBTCI/LBTCI0.log” for further details.

–Restart database

srvctl stop database -db LBTCI
srvctl start database -db LBTCI

–Let’s check

SQL> SELECT parameter,value
FROM gv$OPTION
WHERE PARAMETER in ( ‘Oracle Database Vault’,’Oracle Label Security’);
PARAMETER VALUE
—————————————————————- —————————————————————-
Oracle Label Security TRUE
Oracle Database Vault TRUE

 

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

ORACLE-BASE: Network ACL ddl generator script

August 30, 2017 Leave a comment

After enabling Database Vault in our database there was a chance that this option may have changed ACL entries , so decided to save old entries and generated as a script.

If you have many ACLs than this job becomes time consuming.

I found the simple script that does it for us.

ORACLE-BASE site : https://oracle-base.com/dba/script?category=script_creation&file=network_acls_ddl.sql

SET SERVEROUTPUT ON FORMAT WRAPPED LINESIZE 300
DECLARE
 l_last_acl dba_network_acls.acl%TYPE := '~';
 l_last_principal dba_network_acl_privileges.principal%TYPE := '~';
 l_last_privilege dba_network_acl_privileges.privilege%TYPE := '~';
 l_last_host dba_network_acls.host%TYPE := '~';

FUNCTION get_timestamp (p_timestamp IN TIMESTAMP WITH TIME ZONE)
 RETURN VARCHAR2
 AS
 l_return VARCHAR2(32767);
 BEGIN
 IF p_timestamp IS NULL THEN
 RETURN 'NULL';
 END IF;
 RETURN 'TO_TIMESTAMP_TZ(''' || TO_CHAR(p_timestamp, 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM') || ''',''DD-MON-YYYY HH24:MI:SS.FF TZH:TZM'')';
 END;
BEGIN
 FOR i IN (SELECT a.acl,
 a.host,
 a.lower_port,
 a.upper_port,
 b.principal,
 b.privilege,
 b.is_grant,
 b.start_date,
 b.end_date
 FROM dba_network_acls a
 JOIN dba_network_acl_privileges b ON a.acl = b.acl
 ORDER BY a.acl, a.host, a.lower_port, a.upper_port)
 LOOP
 IF l_last_acl <> i.acl THEN
 -- First time we've seen this ACL, so create a new one.
 l_last_host := '~';

DBMS_OUTPUT.put_line('-- -------------------------------------------------');
 DBMS_OUTPUT.put_line('-- ' || i.acl);
 DBMS_OUTPUT.put_line('-- -------------------------------------------------');
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.drop_acl (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.create_acl (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' description => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' principal => ''' || i.principal || ''',');
 DBMS_OUTPUT.put_line(' is_grant => ' || i.is_grant || ',');
 DBMS_OUTPUT.put_line(' privilege => ''' || i.privilege || ''',');
 DBMS_OUTPUT.put_line(' start_date => ' || get_timestamp(i.start_date) || ',');
 DBMS_OUTPUT.put_line(' end_date => ' || get_timestamp(i.end_date) || ');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 l_last_acl := i.acl;
 l_last_principal := i.principal;
 l_last_privilege := i.privilege;
 END IF;

IF l_last_principal <> i.principal 
 OR (l_last_principal = i.principal AND l_last_privilege <> i.privilege) THEN
 -- Add another principal to an existing ACL.
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.add_privilege (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' principal => ''' || i.principal || ''',');
 DBMS_OUTPUT.put_line(' is_grant => ' || i.is_grant || ',');
 DBMS_OUTPUT.put_line(' privilege => ''' || i.privilege || ''',');
 DBMS_OUTPUT.put_line(' start_date => ' || get_timestamp(i.start_date) || ',');
 DBMS_OUTPUT.put_line(' end_date => ' || get_timestamp(i.end_date) || ');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 l_last_principal := i.principal;
 l_last_privilege := i.privilege;
 END IF;

IF l_last_host <> i.host||':'||i.lower_port||':'||i.upper_port THEN
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.assign_acl (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' host => ''' || i.host || ''',');
 DBMS_OUTPUT.put_line(' lower_port => ' || NVL(TO_CHAR(i.lower_port),'NULL') || ',');
 DBMS_OUTPUT.put_line(' upper_port => ' || NVL(TO_CHAR(i.upper_port),'NULL') || ');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 l_last_host := i.host||':'||i.lower_port||':'||i.upper_port;
 END IF;
 END LOOP;
END;
/

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

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 , , , , ,

Disabling/enabling general job execution before/after maintenance

April 27, 2017 Leave a comment

During the maintenance you may need to disable jobs.

To disable jobs created by dbms_jobs set job_queue_processes to zero.

–Save old value

show parameter job_queue_processes
1000

–disable

alter system set job_queue_processes=0;

If you are using dbms_scheduler, this parameter does not work for you.

You will have to run the following:

dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

After finishing maintenance, enable them:

alter system set job_queue_processes=1000;
dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

 

Filed under 11g, 12c, Real World Scenarios Tagged with , , ,

SYS_OP_C2C internal function, implicit data type converstion

March 15, 2017 Leave a comment

Our database is heavily loaded. In addition, developers are writing codes that make even Exadata to hang 🙂  They are the best hackers ever :):)

Investigating database performance using 13c Cloud Control found one very interesting SQL.
At glance everything is fine, but there is one thing that is important and makes SQL  heavy.

Top SQL:

SELECT nvl(max(bl.id), 0) 
FROM schemaname.eb_restriction_balance bl 
WHERE bl.restrictcode = :b1

SCHEMANAME.EB_RESTRICTION_BALANCE table structure:

SQL> DESC schemaname.eb_restriction_balance

Name Type Nullable Default Comments 
-------------- ------------ -------- ------- -------- 
ID NUMBER 
RESTRICTCODE VARCHAR2(30) 
CURRENCY VARCHAR2(3) 
RESTRICTAMOUNT NUMBER(14) 0 
BALANCE NUMBER(14) 0 
STATE NUMBER(5) 5 
INSERTDATE DATE sysdate 
UPDATEDATE DATE Y 
INN VARCHAR2(30) Y

RESTRICTCODE column is indexed.

So in ideal way the above select should use the index on RESTRICTCODE.

Cloud Control shows that CBO chooses TABLE ACCESS STORAGE FULL.

You can run SQL Tuning Advisor from Cloud Control easily. Advisor generated the following recommendation:

The execution plan of this statement can be improved by creating one or more indices. Consider running the Access Advisor to improve the physical schema design or creating the recommended index.schemaname.EB_RESTRICTION_BALANCE(SYS_OP_C2C(“RESTRICTCODE”))

SYS_OP_C2C means that there happened implicit data type conversion.
So we must find the exact bind value that was used at the time sql was run.

SQL> SELECT name, datatype_string, value_string
 2 FROM v$sql_bind_capture
 3 WHERE sql_id='dnb1771sbm98x';

NAME DATATYPE_STRING VALUE_STRING
------- ------------------ -------------
:B1 NVARCHAR2(128) BR16215493
:B1 NVARCHAR2(128) BR16213680

As you see bind value type was NVARCHAR, that is why oracle converted varchar2 to nvarchar2 and did not use index on RESTRICTCODE.

Solution is to make developer change code and pass the parameter with the type varchar2.

 

Filed under 11g, 12c, 13C Tagged with , , , , ,

← Older posts

Newer posts →