Categories
12c 12cR2 18c 19c dba

Select from cdb_* views

There is no privileges strong enough for you to view all objects in all databases

Let’s try

as sys:

SQL> select con_id, count(*) from cdb_objects group by con_id;

    CON_ID   COUNT(*)
---------- ----------
         1      22749
         3      22721

as non-sys

SQL> create user c##u identified by ***;
User created.
SQL> grant create session, select any dictionary to c##u;
Grant succeeded.
SQL> conn c##u/x
Connected.
SQL> select con_id, count(*) from cdb_objects group by con_id;
    CON_ID   COUNT(*)
---------- ----------
         1      22749

You can try to grant and grant and grant, it won’t help

SQL> conn / as sysdba
Connected.
SQL> grant dba, cdb_dba, pdb_dba, all privileges, sysdba to c##u with admin option container=all;
Grant succeeded.
SQL> conn c##u/x
Connected.
SQL> select con_id, count(*) from cdb_objects group by con_id;
    CON_ID   COUNT(*)
---------- ----------
         1      22749

This is not what you are missing…

SQL> revoke dba, cdb_dba, pdb_dba, all privileges, sysdba from c##u container=all;
Revoke succeeded.
SQL> grant create session, select any dictionary to c##u;
Grant succeeded.

you need container data

SQL> alter user c##u set container_data=all container=current;
User altered.
SQL> conn c##u/x
Connected.
SQL> select con_id, count(*) from cdb_objects group by con_id;
    CON_ID   COUNT(*)
---------- ----------
         1      22749
         3      22721

Here you go …

Categories
dba OEM

register database in Cloud Control with command line

a common task of the DBA is to add databases in EM. If you do it 1000 times, you’ll get bored. I guess…

sure you could go to EM Cloud Management Pack, but maybe you use SQLPLUS + Create database + catalog/catproc.

Okay, there are some ways/hacks to do it with emcli/emctl, but let’s face it, there is a tool around the corner that does everything for you :
DBCA

Wait, that GUI thing? Yes and no. Just use the silent option. It is that simple.


dbca -silent
-createDatabase
-templateName General_Purpose.dbc
-gdbname cdb1 -sid cdb1
-sysPassword ***
-systemPassword ***
-emConfiguration CENTRAL
-dbsnmpPassword ***
-emUser sysman
-emPassword ***
-omsHost em.example.com
-omsPort 4900

Wait, that simple?

Yep ๐Ÿ˜‰

More details on how to use silent dbca on oracle-base.com

Categories
12c 12cR2 18c 19c dba security

Audit pluggable database

In the old now-deprecated maybe-soon-desupported non-cdb infrastructure, AUDIT’ing was done right after connect / as sysdba.

In single-tenant (or multi-tenant), things get complicated.

Once again, the doc must be read at least twice ๐Ÿ˜‰

If you issue an audit statement in the root, then the database performs auditing across the entire CDB, that is, in the root and all PDBs […] all common users are audited

This is very tricky, because you don’t want to audit common users only

Let’s try

SQL> conn / as sysdba
Connected.
SQL> create user c##u identified by ***;
User created.
SQL> grant create session to c##u container=all;
Grant succeeded.
SQL> alter session set container=pdb01;
Session altered.
SQL> create user u identified by ***;
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> alter session set container=cdb$root;
Session altered.
SQL> audit connect container=all;
Audit succeeded.
SQL> select AUDIT_OPTION, CON_ID from cdb_STMT_AUDIT_OPTS;
AUDIT_OPTION   CON_ID
-------------- ------
CREATE SESSION      1
SQL> sho parameter audit_trail
NAME         VALUE
------------ -------------
audit_trail  DB, EXTENDED
SQL> 

Audit is now logging all connections of all common users on all databases…

SQL> conn c##u/***
Connected.
SQL> sho user
USER is "C##U"
SQL> sho con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> conn c##u/***@pdb01
Connected.
SQL> sho user
USER is "C##U"
SQL> sho con_name
CON_NAME
------------------------------
pdb01

Let’s verify :

SQL> conn / as sysdba
Connected.
SQL> select * from cdb_audit_trail order by timestamp;

CON_ID USERNAME ACTION TIMESTAM
------ -------- ------ --------
     1 C##U     LOGON  18:01:05
     1 C##U     LOGOFF 18:01:06
     3 C##U     LOGON  18:01:07
     3 C##U     LOGOFF 18:01:08

So far so good. What about local users?

SQL> conn u/***@pdb01
Connected.
SQL> sho user
USER is "U"
SQL> sho con_name
CON_NAME
------------------------------
pdb01

And???

SQL> conn / as sysdba
Connected.
SQL> select * from cdb_audit_trail where USERNAME='U';
no rows selected

Nope! DBA like me and you don’t care about those C## users, we want ALL users, not all common users.

For this purpose, we need to activate audit on every pluggable.

SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb01;
Session altered.
SQL> audit connect;
Audit succeeded.
SQL> alter session set container=cdb$root;
Session altered.
SQL> select AUDIT_OPTION, CON_ID from cdb_STMT_AUDIT_OPTS;
AUDIT_OPTION   CON_ID
-------------- ------
CREATE SESSION      1
CREATE SESSION      3

Now it should work

SQL> conn u/***@pdb01
Connected.
SQL> sho user
USER is "U"
SQL> sho con_name
CON_NAME
------------------------------
pdb01
SQL> host sleep 1

… and …

SQL> select * from cdb_audit_trail where username='U';
CON_ID USERNAME ACTION TIMESTAM
------ -------- ------ --------
     3 U        LOGON  18:01:12
     3 U        LOGOFF 18:01:13

If you already switched from non-cdb to single-tenant, please check your audit strategy NOW !!!

Categories
dba security

Privileges on a view

Granting too many privileges on a view could be disastrous. A view is often used as a security element; you grant access to only a subset of columns and rows to one user. Mostly only SELECT. If you want to grant update to only some rows, the security could be enhanced with the WITH CHECK OPTION.

But let’s talk about granting too much privs.
disclaimer: it may damaged your database forever

SQL> create or replace view v as select trunc(sysdate) today from dual;
View created.
SQL> create public synonym v for v;
Synonym created.
SQL> grant all on v to public;
Grant succeeded.
SQL> conn u/***@db01
Connected.
SQL> select * from v;
TODAY
----------
2018-10-15
SQL> select * from v;
TODAY
----------
2018-10-15
SQL> delete from v;
1 row deleted.
SQL> select * from v;
TODAY
----------
2018-10-15
SQL> select count(*) from v;
COUNT(*)
----------
1
SQL> select count(dummy) from dual;
COUNT(DUMMY)
------------
0
SQL> rollback;
Rollback complete.

Wait… what happened ???

SQL> delete from v;
1 row deleted.
SQL> select count(*) from v;
COUNT(*)
----------
1

This is a biaised test, because nobody creates view in SYS schema and nobody shall ever do GRANT ALL TO PUBLIC. But sometimes, people do. Because of the grant, you have emptied dual. ๐Ÿ˜ฎ

The COUNT(*) is a magic thing. select count(*) from dual returns 1. Unless your instance collapses.


SQL> delete dual;
1 row deleted.
SQL> alter session set "_fast_dual_enabled"=false;
Session altered.
SQL> select count(*) from dual;
COUNT(*)
----------
1
SQL> rollback;

One reader once asked for assistance because he tried it and its db was broken. I won’t help you. Just do it for fun on a database that you can recreate afterwards.

Okay, enough fun for today, let’s see another side effect of excessive rights.

SQL> create user u identified by ***;
User created.
SQL> create role r;
Role created.
SQL> grant create view, create session to u;
Grant succeeded.
SQL> conn u/***@db01
Connected.
SQL> create view v as select trunc(trunc(sysdate)-.5) yesterday from dual;
View created.
SQL> create role r;
Role created.
SQL> select * from v;
YESTERDAY
----------
2018-10-14
SQL> delete from v;
delete from v
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> grant delete on v to r;
grant delete on v to r
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.DUAL'
SQL> grant select on v to r;
Grant succeeded.

So far so good, I have created a view and granted select only on that view. I cannot delete DUAL. I cannot grant delete.

Now learn about this less-known annoyance

SQL> conn / as sysdba
Connected.
SQL> grant select, update, insert, delete on u.v to r;
Grant succeeded.

What? SYS can give access to my view to a role, even if I have no DELETE right on the underlying?

SQL> grant create session, r to user2;
Grant succeeded.
SQL> conn user2/***@DB01
Connected.
SQL> select * from u.v;
YESTERDAY
----------
2018-10-14
SQL> delete from v;
delete from v
*
ERROR at line 1:
ORA-01031: insufficient privileges

Sofar, it didn’t have so many side effect. It is not uncommon to see scripts that automatically generate grants ; and it is also not uncommon to see those script going doolally.

But, one side effect is preventing future CREATE OR REPLACE statements.

SQL> create or replace view v as select trunc(trunc(sysdate+6,'YYYY')+400,'YYYY')-7 xmas from dual;
create or replace view v as select trunc(trunc(sysdate+6,'YYYY')+400,'YYYY')-7 xmas from dual;
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.DUAL'

CREATE OR REPLACE no longer work. You need to revoke the right. Either with a DROP VIEW or with


SQL> revoke insert, update, delete on v from r;
Revoke succeeded.
SQL> create or replace view v as select trunc(trunc(sysdate+6,'YYYY')+400,'YYYY')-7 xmas from dual;
View created.
SQL> select * from v;
XMAS
----------
2018-12-25

I’d recommend against using SYS to grant access to user tables. Use the schema owner.

Categories
dba

in doubt transaction

Distributed transactions allows you to have multiple DML’s over multiple databases within a single transaction

For instance, one local and one remote

insert into t values(1);
insert into t@db02 values(2);
commit;

If you lose connection to db02 and wants to commit, your database server may/does not know about the state of the remote transaction. The transaction then shows up als pending.

Oracle documentation mentions about ORA-2PC-CRASH-TEST transaction comment to test this behavior, however, anything like note 126069.1 who starts with grant dba to scott; should be banned.

Apart from granting DBA to scott and using commit tansaction commment 'ORA-2PC-CRASH-TEST-7', I can still use my good (bad?) old shutdown abort.


SQL> insert into t values(1);
1 row created.
SQL> insert into t@db02 values(2);
1 row created.
SQL> -- shutdown abort on db02
SQL> commit;
commit
*
ERROR at line 1:
ORA-02054: transaction 2.7.4509 in-doubt
ORA-03150: end-of-file on communication channel for database link
ORA-02063: preceding line from DB02
SQL> select LOCAL_TRAN_ID, STATE from dba_2pc_pending;

LOCAL_TRAN_ID STATE
---------------------- ----------------
2.7.4509 prepared

Now you’ve got an issue. Not only the state of the transaction is unknown, but the in-doubt transaction may prevent further DMLs

SQL> update t set x=x+1;
update t set x=x+1
*
ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction 2.7.4509

You need to decide whether to commit or rollback the transaction. Let’s say I want to rollback. I need to have FORCE TRANSACTION privilege


SQL> rollback force '2.7.4509';
Rollback complete.
SQL> select LOCAL_TRAN_ID, STATE from dba_2pc_pending;

LOCAL_TRAN_ID STATE
---------------------- ----------------
2.7.4509 forced rollback
SQL> update t set x=x+1;
0 rows updated.
SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('7.20.4519')
PL/SQL procedure successfully completed.
SQL> select LOCAL_TRAN_ID, STATE from dba_2pc_pending;
no rows selected

The lock disappears, dbms_transaction.purge_log_db_entry can also cleanup old entries.

Categories
18c dba

How to migrate non-cdb to pdb

In case you are desesperately looking for an upgrade from non-cdb to pdb with two clicks, here is the answer from Mike : you can’t

If your database is huge and you don’t want to move anything nor change SID nor change path, this is my own receipt.

  1. Backup your database
  2. Create a XML description of your CDB

    shutdown immediate
    startup read only
    exec dbms_pdb.describe('/save/DB01/DB01.xml')
    shutdown immediate
  3. Setup init.ora parameter for PDB

    startup force nomount restrict
    alter system set enable_pluggable_database=true scope=spfile;
    alter system set db_name=CDBDB01 scope=spfile;
    alter system set instance_name=DB01 scope=spfile;
    startup force nomount
  4. Create a CDB database
    Yes, you have read it correctly. You are required to create a database. There is -so far I’ve googled- no way to avoid this step. You can use dbca or sqlplus.
    Don’t overwrite the datafile. Use for instance /data/DB01/cdb/system.dbf instead of /data/DB01/system.dbf

    create database CDBDB01
    user sys identified by ***
    user system identified by ***
    character set al32utf8
    national character set al16utf16
    logfile
    ('/onlinelog/DB01/redo01a.dbf','/onlinelog/DB01/redo01b.dbf') size 32M REUSE,
    ('/onlinelog/DB01/redo02a.dbf','/onlinelog/DB01/redo02b.dbf') size 32M REUSE
    extent management local
    datafile '/data/DB01/cdb/system01.dbf' size 10M autoextend on
    sysaux datafile '/data/DB01/cdb/sysaux01.dbf' size 10M autoextend on
    default tablespace admin datafile '/data/DB01/cdb/admin01.dbf' size 10m autoextend on
    extent management local autoallocate
    default temporary tablespace temp
    tempfile '/data/DB01/cdb/temp01.dbf' size 10M autoextend on
    undo tablespace undotbs1 datafile '/data/DB01/cdb/undotbs1_01.dbf' size 10M autoextend on
    set time_zone='Europe/Zurich'
    ENABLE PLUGGABLE DATABASE
    SEED FILE_NAME_CONVERT = ('/data/DB01/cdb/', '/data/DB01/seed/')
    LOCAL UNDO ON
    USER_DATA TABLESPACE users datafile '/data/DB01/cdb/users01.dbf' size 10m autoextend on;
    perl catcon.pl -u sys/*** -d oh/rdbms/admin -b catalog_DB01 -e -l log catalog.sql
    perl catcon.pl -u sys/*** -d oh/rdbms/admin -b catproc_DB01 -e -l log catproc.sql

    It’s a pretty cumbersome step. Maybe you need Oracle Text or maybe you have DEC character set… all those details must be engineered by the DBA. This is pretty insane Oracle doesn’t offer a tool to migrate to a non-deprecated architecture.
  5. Plug-in the database

    CREATE PLUGGABLE DATABASE DB01 USING '/save/DB01/DB01.xml' nocopy tempfile reuse;

    If you miss this step, ร” rรขge, ร” dรฉsespoir, you probably must restart from the beginning.
  6. run noncdb_to_pdb

    alter session set container=DB01;
    alter pluggable database DB01 open;
    alter session set container=DB01;
    @?/rdbms/admin/noncdb_to_pdb.sql

Once you are so far, you’ll have all advantages of Multi-Tenant. PDB-Clone. PDB-Flashback. Lockdown profiles. And you’ll be able to consolidate if you buy the multitenant option. You’ll save money on hardware (SGA/CPU) to spent on software (Multitenant option).

Most importantly, you’ll have moved to a non-deprecated architecture

Conclusion: there is more than one way to move to pluggable. But there is no way to migrate to pluggable without creating a new database

Categories
12cR2 dba sql

FBI = function based index

Let me today tell you, I now hate FBI for real.

Let’s start with an easy working example


SQL> CREATE TABLE t(x NUMBER PRIMARY KEY)
Table created.
SQL> insert into t(x) values (1)
1 row created.
SQL> insert into t(x) values (2)
1 row created.
SQL> insert into t(x) values (3)
1 row created.
SQL> commit
Commit complete.
SQL> CREATE FUNCTION f (x NUMBER)
RETURN NUMBER DETERMINISTIC IS
BEGIN
RETURN x * x;
END;
Function created.
SQL> CREATE INDEX i
ON t (f (x))
Index created.
SQL> select * from t where f(x)=4

X
----------
2
Execution Plan
------------------------------------------------
0 SELECT STATEMENT
1 0 TABLE ACCESS BY INDEX ROWID BATCHED T
2 1 INDEX RANGE SCAN I

Okay, this is a nice-working example. I can use where f(x)=4.

A non-fbi code would be something like

SQL> create or replace type tn as table of number;
Type created.
SQL> create or replace function f2(y number)
return tn deterministic is
begin
if (y<0) then return null; end if; return tn (sqrt(y), -sqrt(y)); end; Function created. SQL> select * from t where x member of f2(4)

X
----------
2

Execution Plan
------------------------------------
0 SELECT STATEMENT
1 0 INDEX FULL SCAN SYS_C0026437

The reverse function is somehow more challenging to code, but the benefit is enormous, I have no more fbi.

What’s wrong with fbi?

First example : I recreate my function:

SQL> DROP FUNCTION f
Function dropped.
SQL> CREATE FUNCTION f (x NUMBER)
RETURN NUMBER DETERMINISTIC IS
BEGIN
RETURN power(x,2);
END;
Function created.
SQL> select * from t where f(x)=4
*
Error at line 0
ORA-30554: function-based index I is disabled
SQL> SELECT object_type, object_name, status
FROM user_objects
WHERE object_name IN ('F','I')

OBJECT_TYPE OBJECT_NAME STATUS
----------------------- ------------ -------
INDEX I VALID
FUNCTION F VALID
SQL> SELECT index_name,
table_name,
index_type,
status,
funcidx_status
FROM user_indexes
WHERE index_name = 'I'

INDEX TABLE INDEX_TYPE STATUS FUNCIDX_STATUS
----- ----- -------------- ------ --------------
I T FUNCTION-BASED VALID DISABLED

Remember this error. ORA-30554. And this not-so-well-known column, USER_INDEXES.FUNCIDX_STATUS. The behavior is pretty agressive, every object is valid, but you can no longer select from the table.

A small parenthese. We all know about unusable indexes. Index often get unusable due to partition maintenance and the like.


SQL> create table t2(x number)
Table created.
SQL> insert into t2 values (1)
1 row created.
SQL> create index i2 on t2(x) unusable
Index created.
SQL> SELECT index_name,
table_name,
status
FROM user_indexes
WHERE index_name = 'I2'

INDEX TABLE STATUS
----- ----- ---------
I2 T2 UNUSABLE
SQL> insert into t2 values (2)
1 row created.
SQL> select * from t2 where x=2

X
----------
2

Execution Plan
---------------------------------
0 SELECT STATEMENT
1 0 TABLE ACCESS FULL T2

The index is not unused, but it prevents neither INSERT nor SELECT.

Let’s add a constraint

SQL> alter index i2 rebuild
Index altered.
SQL> alter table t2 add primary key (x)
Table altered.
SQL> alter index i2 unusable
Index altered.
SQL> insert into t2 values (2)
ORA-01502: index 'I2' or partition of such index is in unusable state
SQL> select * from t2 where x=2

X
----------
2

If the index is used by a constraint or is unique, then insert is prevented. But no select is prevented ever.

Okay, frequent readers may wonder why I did DROP FUNCTION and CREATE FUNCTION instead of CREATE OR REPLACE FUNCTION.

Fine, let’s try.

SQL> CREATE or replace FUNCTION f (x NUMBER)
RETURN NUMBER
DETERMINISTIC
IS
BEGIN
RETURN power(x,2);
END;
Function created.
SQL> alter index i rebuild
Index altered.
SQL> alter index i enable
Index altered.
SQL> select x, f(x) from t where f(x)=4

X F(X)
---------- ----------
2 4
SQL> create or replace function f(x number)
return number deterministic is
begin
return 1;
end;
Function created.
SQL> select x, f(x), f(2) from t where f(x)=4

X F(X) F(2)
---------- ---------- ----------
2 4 1

Oh my goodness, select returns completly wrong result, but the index is valid and enabled.

There is more than way to solve this

  1. rebuild your index after create function. You could find the candidates by looking at the last ddl time and dependencies

  2. SQL> select name from user_dependencies d
    where referenced_type = 'FUNCTION'
    and type = 'INDEX' and
    (
    select last_ddl_time
    from user_objects i
    where i.object_name=d.name
    ) < ( select last_ddl_time from user_objects f where f.object_name=d.referenced_name ) NAME ----- I SQL> alter index i rebuild
    Index altered.
    SQL> select x, f(x), f(2) from t where f(x)=4
    no rows selected.
    SQL> select x, f(x), f(2) from t where f(x)=1

    X F(X) F(2)
    ---------- ---------- ----------
    1 1 1
    2 1 1
    3 1 1

    SQL> select name from user_dependencies d
    where referenced_type = 'FUNCTION'
    and type = 'INDEX' and
    (
    select last_ddl_time
    from user_objects i
    where i.object_name=d.name
    ) < ( select last_ddl_time from user_objects f where f.object_name=d.referenced_name ) no rows selected.

  3. file an SR and encourage Oracle to test features before making them available
  4. stop using FBI immediately
Categories
12cR2 dba security

Administrative privileges like SYSDBA

The most well-known administrative privilege is sysdba. Back in Oracle 7.2, oracle recommended to no longer use the INTERNAL but to connect as sysdba. A second one, seldom used, is SYSOPER.

When you use sqlplus / as sysdba, or connect / as sysdba in sqlplus / srvmgrl, the you log as SYS.

That’s the most powerfull user in the database.

SYSOPER has less privileges, it can shutdown the database and the like.

While often refered as the SYSDBA role, or SYSDBA system privilege, SYSDBA is an administrative privilege. It is not listed in DBA_ROLES, DBA_ROLE_PRIVS, DBA_SYS_PRIVS, etc…

In non-cdb, you’ll look in V$PWFILE_USERS.
In cdb/pdb, look into cdb_local_admin_privs


grant SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM to u;

SYSRAC and SYSASM also exists, but I have not tested them yet.

SQL> select CON, USERNAME, SYSDBA, SYSOPER, SYSASM,
SYSBACKUP, SYSDG, SYSKM from V$PWFILE_USERS;

CON USERNAME SYSDBA SYSOPER SYSBACKUP SYSDG SYSKM
--- -------- ------ ------- --------- ----- -----
0 SYS TRUE TRUE FALSE FALSE FALSE
3 U TRUE TRUE TRUE TRUE TRUE

SQL> select * from cdb_local_admin_privs;

CON_NAME GRANTEE SYSDBA SYSOPER SYSBACKUP SYSDG SYSKM
-------- ------- ------ ------- --------- ----- -----
PDB01 U TRUE TRUE TRUE TRUE TRUE

What’s really happening when you log as sysdba?


SQL> conn u/***@DB01 as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> select privilege from session_privs
where privilege like 'SYS%';
PRIVILEGE
------------------------------
SYSDBA
SYSOPER
SQL> def _PRIVILEGE
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
SQL> select privilege from dba_sys_privs
where privilege like 'SYS%';
no rows selected

Even if you use your U-credentials, you are SYS and you have SYSDBA privielege. It is not a System privilege but an an administrative privilege. You also get SYSOPER for free.

What happens when you log as SYSOPER?


SQL> conn u/***@DB01 as sysoper
Connected.
SQL> sho user
USER is "PUBLIC"
SQL> select * from session_privs;
PRIVILEGE
------------------------------
CREATE SESSION
RESTRICTED SESSION
SYSOPER

SQL> create public synonym p for x.y;
create public synonym p for x.y
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> create synonym p for x.y;
create synonym p for x.y
*
ERROR at line 1:
ORA-01031: insufficient privileges

You are logged as the pseudo-user PUBLIC. It doesn’t give you the right to create “PUBLIC” objects, but you could do a few things, with your SYSOPER privilege, like shutdown;

For the other users, it is no longer matched to SYS or PUBLIC, but they have they own underlying users;


SQL> conn u/***@DB01 as sysbackup;
Connected.
SQL> sho user
USER is "SYSBACKUP"
SQL> select * from session_privs;

PRIVILEGE
------------------------------
ALTER SYSTEM
ALTER SESSION
ALTER TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE ANY TABLE
CREATE ANY CLUSTER
AUDIT ANY
ALTER DATABASE
CREATE ANY DIRECTORY
RESUMABLE
SELECT ANY DICTIONARY
SELECT ANY TRANSACTION
SYSBACKUP
SQL> conn u/***@DB01 as sysdg;
Connected.
SQL> sho user
USER is "SYSDG"
SQL> select * from session_privs;

PRIVILEGE
------------------------------
ALTER SYSTEM
ALTER SESSION
ALTER DATABASE
SELECT ANY DICTIONARY
SYSDG
SQL> conn u/***@DB01 as syskm
Connected.
SQL> sho user
USER is "SYSKM"
SQL> select * from session_privs;

PRIVILEGE
------------------------------
SYSKM
ADMINISTER KEY MANAGEMENT

This allows different administrators, that are not in the core dba team, to execute some administrative operation like Dataguard-Switchover / Restore / Shutdown /Startup on their databases, without SYS.

Remember, SYS can now be locked in a PDB ๐Ÿ™‚ see lock sys

Categories
dba

check invalid directories

To get the status of a directory, I wrote my own function, which uses DBMS_LOB.FILEEXISTS.


CREATE FUNCTION
status (DIRECTORY_NAME VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
IF (DBMS_LOB.FILEEXISTS(
BFILENAME (DIRECTORY_NAME, '.')) = 1)
THEN
RETURN 'VALID';
ELSE
RETURN 'INVALID';
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN SQLERRM;
END;
/


SELECT
directory_name NAME,
directory_path PATH,
status (directory_name) STATUS
FROM dba_directories;

NAME PATH STATUS
---- ---- ---------
FOO /foo INVALID
TMP /tmp VALID
BAK /u99 VALID

Categories
dba

check invalid database link for the DBA

followup of check invalid database link
If you need to check db link in another schema, you need to create code that run with that schema.

base on the example from yesterday, here is an extended version for the dba


CREATE FUNCTION dba_status
(owner VARCHAR2, db_link VARCHAR2)
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
status VARCHAR2 (4000);
BEGIN
EXECUTE IMMEDIATE
'create or replace function "'
|| owner
|| '".status(db_link varchar2) return varchar2 is '
|| 'x number;'
|| 'begin execute immediate ''select 1 from dual@"''
||DB_LINK||''"'' into x;'
|| 'return ''OK'';'
|| 'exception when others then return sqlerrm;'
|| 'end;';
EXECUTE IMMEDIATE
'begin :1 := "' || owner
||'".status(''' || db_link || '''); end;'
USING OUT status;
EXECUTE IMMEDIATE 'drop function "' || owner || '".status';
COMMIT;
RETURN status;
END;
/


SELECT
owner, db_link, dba_status (owner, db_link)
FROM dba_db_links;
OWNER DB_LINK DBA_STATUS(OWNER,DB_LINK)
----- ------- --------------------------------
SCOTT L3.EXAM OK
SCOTT L2.EXAM ORA-12154: TNS:could not resolve
SCOTT L1.EXAM ORA-01017: invalid username/pass

Categories
dba

check invalid database link

If one database link is invalid, you cannot select through it

SQL> select * from dual@z;
select * from dual@z
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

However, there is no STATUS command in your user_db_links table that you could use. For this purpose, write a function.

create function status(db_link varchar2)
return varchar2 is
x number;
begin
execute immediate 'select 1 from dual@"'||
DB_LINK||
'"' into x;
return 'OK';
exception
when others then return sqlerrm;
end;
/


select db_link, status(db_link) from user_db_links;
DB_LINK STATUS(DB_LINK)
------- --------------------------------------------------
L1.EXAM ORA-01017: invalid username/password; logon denied
L2.EXAM ORA-12154: TNS:could not resolve the connect ident
L3.EXAM OK

Categories
dba plsql sqlplus

execute sql script from pl/sql

When you want to run a SQL script, you rather use sqlplus. If you want to run it within a PL/SQL stored procedure, you are screwed. You could redo the logic of sqlplus, this is what any decent IDE and code runnner does, and it’s a pile of work and you will get lot’s of bugs…

Another approach is to use sqlplus.

Wait! calling sqlplus from plsql? Well… why not. For instance via external job

  1. create a table with sql scripts
  2. create table t(id number primary key, text clob);
    insert into t(id, text) values (
      42, 'update emp set sal=sal*2 /* XMas gift */;'
    );
    
  3. create a shell script, /tmp/ddl, that spool and executes T.text content
  4. #!/bin/ksh
    ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
    export ORACLE_HOME
    $ORACLE_HOME/bin/sqlplus -s -L scott/tiger <
    
  5. create a UNIX credential
  6. begin 
      dbms_scheduler.create_credential(
        'C','USER01','PW');
    end;
    /
    
  7. create and run an external job
  8. begin
      DBMS_SCHEDULER.CREATE_JOB(
        job_name             => 'J',
        job_type             => 'EXECUTABLE',
        number_of_arguments  => 1,
        job_action           => '/tmp/ddl',
        credential_name      => 'C');
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
        'J', 1, '42');
      DBMS_SCHEDULER.run_job('J');
    end;
    /
    

CREATE EXTERNAL JOB privilege is powerfull and your DBA won't like it ๐Ÿ˜‰

Categories
12cR2 dba

To shrink or to move

If you delete most of the rows in one table, you probably want to regain that space, to minimize IO and fragmentation.

If you delete most of the rows in most of the tables, you probably also want to resize the datafile to reduce storage and backup space.

In this case, you could move your data in a new tablespace

alter table t move tablespace newts;

I wrote about this here : 2006/08/tablespace-maintenance-tasks.html

There is also an option to SHRINK. In oldish (pre-12cR2) releases, this had the advantage of being an online operation. In 12.2, include the online keyword.

SHRINK requires ROW MOVEMENT. I don’t like the idea of having the rowid’s changing. You have also a bunch of restrictions, amoung others on materialized view fast refreshes and index organized tables.

I am impatient to get this 12cR2. According to the doc, during an alter table move, transactions run uninterrupted on the table

Categories
dba

What is the instance name?

If your Oracle SID doesn’t match your instance name in init.ora, this is quite confusing.

Check my previous post, what is sid in oracle

In the instance_name column of the view v$instance, as well as in USERENV context, it matches the ORACLE_SID of the underlying operating system.

SQL> var ORACLE_SID varchar2(9)
SQL> set autoprint on
SQL> exec dbms_system.get_env('ORACLE_SID',:ORACLE_SID)
PL/SQL procedure successfully completed.
ORACLE_SID
------------
ORA001
SQL> select sys_context('USERENV','INSTANCE_NAME') from dual;
SYS_CONTEXT('USERENV','INSTANCE_NAME')
---------------------------------------
ORA001
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
ORA001
SQL>

This is not the same as the init.ora parameter

SQL> select name, value, description from v$parameter where name='instance_name';

NAME          VALUE     DESCRIPTION
------------- --------- ----------------------------------------
instance_name INS001    instance name supported by the instance
SQL>

The instance_name doesn’t have to match anything. It’s of relevance if you use ADR. And you probably do. Background dump dest and family are deprecated now. In your ADR docu you’ll read

{ORACLE_BASE}/diag/rdbms/{DB_UNIQUE_NAME}/{SID}/trace

But this SID is actually your init.ora instance name. And not your ORACLE_SID.

Categories
dba

Drop table cascade and reimport

Happy new year ๐Ÿ™‚

Today I had to import a subset of a database and the challenge was to restore a parent table without restoring its children. It took me some minutes to write the code, but it would have taken days to restore the whole database.

CREATE TABLE t1(
  c1 NUMBER CONSTRAINT t1_pk PRIMARY KEY);
INSERT INTO t1 (c1) VALUES (1);
CREATE TABLE t2(
  c1 NUMBER CONSTRAINT t2_t1_fk REFERENCES t1,
  c2 NUMBER CONSTRAINT t2_pk PRIMARY KEY);
INSERT INTO t2 (c1, c2) VALUES (1, 2);
CREATE TABLE t3(
  c2 NUMBER CONSTRAINT t3_t2_fk REFERENCES t2,
  c3 NUMBER CONSTRAINT t3_pk PRIMARY KEY);
INSERT INTO t3 (c2, c3) VALUES (2, 3);
CREATE TABLE t4(
  c3 NUMBER CONSTRAINT t4_t3_fk REFERENCES t3,
  c4 NUMBER CONSTRAINT t4_pk PRIMARY KEY);
INSERT INTO t4 (c3, c4) VALUES (3, 4);
COMMIT;

expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=scott.dmp reuse_dumpfiles=y

Now what happen if I want to restore T2 and T3 ?

If possible, I check the dictionary for foreign keys from other tables pointing to T2 and T3.

SELECT constraint_name
FROM user_constraints
WHERE (r_constraint_name) IN (
    SELECT constraint_name
    FROM user_constraints
    WHERE table_name IN ('T2', 'T3'))
  AND table_name NOT IN ('T2', 'T3');

TABLE_NAME                     CONSTRAINT_NAME               
------------------------------ ------------------------------
T4                             T4_T3_FK                      

T4 points to T3 and T4 has data.

Now I can drop my tables with the cascade options

drop table t2 cascade constraints;
drop table t3 cascade constraints;

Now I import, first the tables, then the referential constraints dropped with the cascade clause and not on T2/T3.

impdp scott/tiger tables=T2,T3 directory=DATA_PUMP_DIR dumpfile=scott.dmp

impdp scott/tiger  "include=ref_constraint:\='T4_T3_FK'" directory=DATA_PUMP_DIR dumpfile=scott.dmp

It’s probably possible to do it in one import, but the include syntax is horrible. I tried there

Categories
dba security

Generate 11g password hash

An easy way to generate a value string from the ssl is to use openssl

Let’s take a random salt of ABCDEFGHIJ. The length of 10 is important.

The hexadecimal representation is -41-42-43-44-45-46-47-48-49-4A-


$ echo "SafePassw0rDABCDEFGHIJ\c" | openssl dgst -sha1
(stdin)= 47cc4102144d6e479ef3d776ccd9e0d0158842bb

With this hash, I can construct my value

SQL> create user testuser identified by values 'S:47CC4102144D6E479EF3D776CCD9E0D0158842BB4142434445464748494A';

User created.

SQL> grant create session to testuser;

Grant succeeded.

SQL> conn testuser/SafePassw0rD
Connected.

If you prefer PL/SQL over shell, use DBMS_CRYPTO


SQL> exec dbms_output.put_line('S:'||dbms_crypto.hash(utl_raw.cast_to_raw('SafePassw0rDABCDEFGHIJ'),dbms_crypto.HASH_SH1)||utl_raw.cast_to_raw('ABCDEFGHIJ'))
S:47CC4102144D6E479EF3D776CCD9E0D0158842BB4142434445464748494A

PL/SQL procedure successfully completed.

In 12c there is also a “T” String. According to the doc
The cryptographic hash function used for generating the 12C verifier is based on a de-optimized algorithm involving PBKDF2 and SHA-512.

Categories
dba

delete all data

How do you delete all data? The simplistic approach would be to truncate all tables


SQL> select table_name from user_tables;
TABLE_NAME
----------
T1
SQL> truncate table t1;
Table truncated.

You cannot truncate if you have referential integrity constraints.

SQL> truncate table t2;
ORA-02266: unique/primary keys in table
referenced by enabled foreign keys

Ok, let’s disable the RIC


SQL> select table_name, constraint_name
from user_constraints
where constraint_type='R';
TAB CONSTRAINT
--- ----------
T3 SYS_C00107
SQL> alter table t3 disable constraint SYS_C00107;
Table altered.
SQL> truncate table t2;
Table truncated.
SQL> truncate table t3;
Table truncated.

You cannot truncate cluster tables

SQL> truncate table t4;
ORA-03292: Table to be truncated is part of a cluster

Cluster tables could be dropped with TRUNCATE CLUSTER.

SQL> select cluster_name from user_clusters;
CLUSTER_NAME
------------
C
SQL> truncate cluster c;
Cluster truncated.

The code above doesn’t work with Partitioned cluster (12.1.0.2) because it was not properly implemented at the time of the writing.
Check Bug 20284579 : CAN NOT QUERY DYNAMIC CLUSTER PARTITIONS

For reference partitioning, it is not possible to disable the foreign key

SQL> alter table t6 disable constraint fk;
ORA-14650: operation not supported for
reference-partitioned tables

In 12c, if the foreign key is defined with ON DELETE CASCADE, you can truncate cascade the parent.

SQL> select table_name, REF_PTN_CONSTRAINT_NAME
from user_part_tables
where partitioning_type='REFERENCE';
TAB REF
--- ---
T6 FK
SQL> select r_constraint_name, delete_rule
from user_constraints
where constraint_name='FK';
R_CON DELETE_RULE
----- -----------
PK CASCADE
SQL> select table_name
from user_constraints
where constraint_name='PK';
TAB
---
T5
SQL> truncate table t5 cascade;
Table truncated.

But if one of child or child-child table is using reference partitioning without the ON DELETE CASCADE, then the parent or grand-parent could not be truncated. And truncate cascade for reference partitioning is not documented (yet).

But there is very nice alternative to TRUNCATE called is DELETE ๐Ÿ™‚


SQL> select table_name, REF_PTN_CONSTRAINT_NAME
from user_part_tables
where partitioning_type='REFERENCE';
TAB REF
--- ---
T8 FK
SQL> select r_constraint_name, delete_rule
from user_constraints
where constraint_name='FK';
R_CON DELETE_RULE
----- -----------
PK NO ACTION
SQL> select table_name
from user_constraints
where constraint_name='PK'
TAB
---
T7
SQL> truncate table t7 cascade;
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "SCOTT"."T8"
SQL> truncate table t8;
Table truncated.
SQL> delete from t7;
2 rows deleted

To get the tables in the right order, parent tables after children, you can do some hierarchical query and then order by rownum desc, a construct I’m using for the first time I confess. Note the leaf tables are truncable.


select c_owner owner, child table_name
FROM
(
SELECT
p_OWNER, parent, nvl(c_owner, a.owner) c_owner,
nvl(child, a.table_name ) child
FROM
(
SELECT
PT.OWNER P_owner, pt.table_name parent,
pt2.owner c_owner, pt2.table_name child
FROM all_part_tables pt
JOIN all_constraints c
ON pt.OWNER = c.owner
AND PT.TABLE_NAME = c.table_name
AND c.constraint_type = 'P'
AND c.status = 'ENABLED'
JOIN all_constraints r
ON r.r_owner = c.owner
AND r.r_constraint_name = c.constraint_name
AND r.constraint_type = 'R'
AND r.status = 'ENABLED'
JOIN all_part_tables pt2
ON r.owner = pt2.owner
AND r.constraint_name = pt2.REF_PTN_CONSTRAINT_NAME
AND pt2.partitioning_type = 'REFERENCE'
) t
RIGHT JOIN all_tables a
ON child = table_name and a.owner = T.c_OWNER
)
where connect_by_isleaf=0
CONNECT BY parent = PRIOR child and p_owner=PRIOR c_owner
start with parent is null
order by rownum desc;

OWNER TAB
----- ---
SCOTT T10
SCOTT T9

Note the query above is very slow. If dictionary-performance is an issue, maybe we could delete all tables and catch exceptions and loop until all tables are empty

SQL> delete from t9;
ORA-02292: integrity constraint (SCOTT.F10) violated - child record found
SQL> delete from t10;
ORA-02292: integrity constraint (SCOTT.F11) violated - child record found
SQL> delete from t11;
1 row deleted.
SQL> delete from t9;
ORA-02292: integrity constraint (SCOTT.F10) violated - child record found
SQL> delete from t10;
1 row deleted.
SQL> delete from t11;
0 row deleted.
SQL> delete from t9;
1 row deleted.
SQL> delete from t10;
0 row deleted.
SQL> delete from t11;
0 row deleted.
SQL> delete from t9;
0 row deleted.
SQL> delete from t10;
0 row deleted.
SQL> delete from t11;
0 row deleted.

If you have close to zero reference-partitioning table, this approach will be more efficient.

Categories
dba

tracefile tim to readable date format

In trace file, the time is recorded, this could be used to measure time between two timestamps

But how do you convert 31796862227375 to a human format?

This is how I proceeded :

SQL> oradebug SETMYPID
Statement processed.
SQL> oradebug tracefile_name
/u01/log/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_32440740.trc
SQL> alter session set 
  nls_date_format='YYYY-MM-DD_HH24:MI:SS' 
  sql_trace=true;
Session altered.
SQL> select '&_DATE','Start' from dual;
2015-09-23_13:17:50 Start
SQL> select '&_DATE','End' from dual;
2015-09-23_13:18:38 End
SQL> alter session set sql_trace=false;
Session altered.

I use the sqlplus variable _date and waited about a minute between the select’s.

Now let’s look at the trace

PARSING IN CURSOR #4859519800 tim=31796814530524
select '2015-09-23_13:17:50','Start' from dual

PARSING IN CURSOR #4859511384 tim=31796862227375 
select '2015-09-23_13:18:38','End' from dual

Note the tim=

Between 13:17:50 and 13:18:38 I have 48 seconds; the difference between 31796862227375 and 31796814530524 is 47696851, roughly 48 millions of microseconds.

So the time on this specific version of Oracle is in microseconds.

Now if I substracted 31796862227375 from 2015-09-23_13:17:50 and I get 2014-09-20_12:50:08 on this specific instance.

So to convert tim= to human readable date, I simply add tim microseconds to Sep 20, 2014, 12:50:08.

Categories
dba

job_name cannot be null


exec dbms_scheduler.create_job(job_name=>null,job_type=>'PLSQL_BLOCK',job_action=>'BEGIN NULL; END;')
ORA-27451: JOB_NAME cannot be NULL
ORA-06512: at "SYS.DBMS_ISCHED", line 146
ORA-06512: at "SYS.DBMS_SCHEDULER", line 288
ORA-06512: at line 1

This sounds like a proper error message. A bit less obvious is the drop_job message


SQL> exec dbms_scheduler.drop_job(job_name=>null)
ORA-20001: comma-separated list invalid near
ORA-06512: at "SYS.DBMS_UTILITY", line 236
ORA-06512: at "SYS.DBMS_UTILITY", line 272
ORA-06512: at "SYS.DBMS_SCHEDULER", line 743
ORA-06512: at line 1

comma-separated list invalid near what?

Ok, why would you create an empty job? Obviously you wouldn’t. But remember job_name could be a very long expression that won’t fit in your VARCHAR2(30) variable.


SQL> begin
dbms_scheduler.create_job(job_name=>
' "SCOTT" '||
' . '||
' "JOB10000000000000000000001" ',
job_type=>'PLSQL_BLOCK',
job_action=>'BEGIN NULL; END;');
end;
/

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.drop_job('scott.job10000000000000000000001')

PL/SQL procedure successfully completed.

If you use drop job in the exception clause without catching the exception of the exception, it could lead to this ORA-20001 if job name is null

For exception handling, we could improve

BEGIN
CREATE JOB
RUN JOB
DROP JOB
EXCEPTION
WHEN OTHERS THEN
DROP JOB
output message
RAISE
END

into

BEGIN
CREATE JOB
RUN JOB
DROP JOB
EXCEPTION
WHEN OTHERS THEN
BEGIN
DROP JOB
EXCEPTION
WHEN IS_RUNNING
sleep
WHEN OTHERS
output message
END LOOP
output message
RAISE
END

Categories
dba

DELETE is faster than TRUNCATE

Truncate is useful in some serial batch processing but it breaks the read-write consistency, generates stranges errors and results for running selects, and it needs DROP ANY TABLE when run over a table that you do not own.

But also, DELETE is faster in the following test case.

In 12c, you could have over one million partition in a table, but for the sake of the universe, I’ll try with 10000.


SQL> create table scott.t(x)
partition by range(x)
interval(1)
(partition values less than (0))
as
select rownum
from dual
connect by level<10001; SQL> select count(*) from scott.t;

COUNT(*)
----------
10000

The 10K rows table is created, each row is its partition

SQL> delete scott.t;

10000 rows deleted.

Elapsed: 00:00:04.02
SQL> rollback;

Rollback complete.

Not tuned or parallelized or whatever. It took 4 seconds for 10’000 rows. If you have one billion rows, it is doable in a few hours. But you better do it in chunks then.

Anyway, let’s truncate

SQL> truncate table scott.t;

Table truncated.

Elapsed: 00:05:19.24

Five minutes !!! to truncate that tiny table.

If you have one million partitions and underlying indexes and lobs, it will probably failed with out of memory errors after hours and a large impact on the dictionary, sysaux, undo.

The dictionary changes are here very slow.

Categories
dba rman

Irrecoverable part III : a fix

After part I:the problem, Part II: two reports here is part III.

We have backed up archivelog during a full and deleted them before the full completed.

RMAN> list backup of archivelog all;

List of Backup Sets
===================

BS Size Type Elapsed Completion
-- ------ ---- -------- ----------
15 4.00K DISK 00:00:00 13:31:08
BP Key: 15 Status: AVAILABLE
Piece Name: /FULL/0fq7gc0s_1_1

List of Archived Logs in backup set 15
Seq LowSCN LowTime NextSCN NextTime
--- ------- -------- ------- --------
15 355533 13:29:55 355777 13:31:08

BS Size Type Elapsed Completion
-- ------ ---- -------- ----------
25 4.00K DISK 00:00:00 13:31:26
BP Key: 25 Status: AVAILABLE
Piece Name: /ARCH/0pq7gc1e_1_1

List of Archived Logs in backup set 25
Seq LowSCN LowTime NextSCN NextTime
--- ------- -------- ------- --------
15 355533 13:29:55 355777 13:31:08

BS Size Type Elapsed Completion
-- ------ ---- -------- ----------
26 3.00K DISK 00:00:00 13:31:26
BP Key: 26 Status: AVAILABLE
Piece Name: /ARCH/0qq7gc1e_1_1

List of Archived Logs in backup set 26
Seq LowSCN LowTime NextSCN NextTime
--- ------- -------- ------- --------
16 355777 13:31:08 355827 13:31:22

BS Size Type Elapsed Completion
-- ------ ---- -------- ----------
28 2.50K DISK 00:00:00 13:31:28
BP Key: 28 Status: AVAILABLE
Piece Name: /FULL/0sq7gc1g_1_1

List of Archived Logs in backup set 28
Seq LowSCN LowTime NextSCN NextTime
--- ------- -------- ------- --------
17 355827 13:31:22 355863 13:31:28

Sequence 16 is missing in /FULL/ and is available in /ARCH/

Ok, let’s copy the missing pieces from ARCH to FULL

$ cd /
$ cp -p ARCH/* FULL/

Now let’s try. I save my directory on an offline location (tape/usb/cdrom) and dropped my database including backups.


cd /
tar cvf /tmp/FULL.tar FULL

Let’s drop (it’s just a test)

RMAN> startup force mount dba
RMAN> drop database including backups;
database dropped

Ok let’s get this USB stick back from the moon to my datacenter and see if I can restore …

cd /
tar xvf /tmp/FULL.tar

Let’s do this !

RMAN> startup force nomount
RMAN> restore controlfile from '/FULL/c-2414975447-20150521-01';
RMAN> alter database mount;

Now I want RMAN to look for other files in FULL and also marked expired stuff. The start with does the magic.

RMAN> catalog start with '/FULL/' noprompt;
RMAN> crosscheck backup;
RMAN> crosscheck copy;
RMAN> crosscheck archivelog all;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;

As @dbastorage said on twitter:
“if it was easy, we would not need DBAs! ”

Categories
dba rman

Irrecoverable full backup part II : reporting

After my post Can you restore from a full online backup ?, I needed to come up with a report.

Assuming that each backup goes in a different directory, I just wrote two reports.

  1. Report gaps in v$backup_redolog (or rc_backup_redolog if you use the catalog)

    DIR FIRST_CHANGE# NEXT_CHANGE#
    ------- ------------- ------------
    /bck01/ 284891 285140
    /bck01/ 285140 285178

    /bck02/ 284891 285140
    === GAP ===
    /bck02/ 285178 285245

    /bck03/ 285178 285245
    /bck03/ 285245 286931
    /bck03/ 286931 287803
    /bck03/ 287803 288148

    This could be done with analytics, by checking where the last next_change is not the current first_change, within a directory

    SELECT dir,
    LAG missing_from_change#,
    first_change# missing_to_change#
    FROM (
    SELECT REGEXP_REPLACE (handle, '[^/\]+$') dir,
    first_change#,
    next_change#,
    LAG(next_change#) OVER (
    PARTITION BY REGEXP_REPLACE (handle, '[^/\]+$')
    ORDER BY first_change#
    ) LAG
    FROM v$backup_piece p
    JOIN v$backup_redolog l
    USING (set_stamp, set_count))
    WHERE LAG != first_change#;

    DIR MISSING_FROM_CHANGE# MISSING_TO_CHANGE#
    ------- -------------------- ------------------
    /bck02/ 285140 285178

  2. Reports directories where archivelogs don’t include changes (backup redolog) from the earliest to the latest checkpoint (backup datafile)

    SELECT
    REGEXP_REPLACE (handle, '[^/\]+$') dir,
    MIN (checkpoint_change#),
    MAX (checkpoint_change#),
    MIN (first_change#),
    MAX (next_change#)
    FROM v$backup_piece p
    LEFT JOIN v$backup_datafile f
    USING (set_stamp, set_count)
    LEFT JOIN v$backup_redolog l
    USING (set_stamp, set_count)
    WHERE handle IS NOT NULL
    HAVING
    MIN (checkpoint_change#) < MIN (first_change#) OR MAX (checkpoint_change#) > MAX (next_change#)
    GROUP BY REGEXP_REPLACE (handle, '[^/\]+$');

    DIR MINCHECKPT MAXCHECKPT MINFIRST MAXNEXT
    ------- ---------- ---------- ---------- ----------
    /bck04/ 954292 954299 959487 1145473

    the archives for the changes from 954292 to 959487 are missing.

If some archive backups are missing in one directory, it does not mean the database is irrecoverable, the archive backups could be in another directory. But it means that single directory would no longer permit you to restore or duplicate.

Another approach with RESTORE PREVIEW was provided by Franck in my previous post : List all RMAN backups that are needed to recover.

Usual disclaimer: there are plenty of other irrecoverabilty causes from hardware defect to backup “optimization” that are beyond the scope of this post.

Categories
dba rman

Can you restore from a full online backup ?

The question is not HOW TO DO IT but WHETHER YOU CAN DO IT !

A typical backup script would contains something like BACKUP DATABASE PLUS ARCHIVELOG:

backup database format
'/u99/backup/DB01/20150518/full_0_%d_s%s_p%p'
plus archivelog format
'/u99/backup/DB01/20150518/arc_%d_s%s_p%p';

Starting backup at 2015-05-18_18:27:55
current log archived
input archived log thread=1 sequence=469
...
piece handle=
/u99/backup/DB01/20150518/arc_DB01_s86_p1
Finished backup at 2015-05-18_18:27:58

Starting backup at 2015-05-18_18:27:58
input datafile file number=00002 name=
/u02/oradata/DB01/undots01.dbf
...
including current control file in backup set
including current SPFILE in backup set
piece handle=
/u99/backup/DB01/20150518/full_0_DB01_s88_p1
Finished backup at 2015-05-18_18:28:16

Starting backup at 2015-05-18_18:28:16
current log archived
input archived log thread=1 sequence=20
piece handle=
/u99/backup/DB01/20150518/arc_DB01_s89_p1
Finished backup at 2015-05-18_18:28:17

This sounds pretty safe, but what happened if you keep this full backup for ever ? do you have all what you need in it to restore ?

It depends. Chance exists that you can restore. To annoy the paranoiacs, here is a counter example.

  1. you start your full backup at 6pm.
    backuping sequence 21,22,23,24
    backup datafiles…

  2. at 7 pm one cronjob issue backup archivelog all delete input;
    backuping and deleting sequence 21,22,23,24,25,26,27,28,29,30

  3. at 8pm your backup is about to finish
    backuping sequence 31

Well, where is my sequence 27 ?

Let’s try

rm /tmp/arch_DB01*
startup force mount;
crosscheck backup;restore database;


Starting restore at 2015-05-18_18:47:45
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 2015-05-18_18:49:51

Ok, the restore was fine. Now what?

RMAN> recover database;

Starting recover at 2015-05-18_18:50:35
using channel ORA_DISK_1

starting media recovery

RMAN-00571: =================================================
RMAN-00569: ========== ERROR MESSAGE STACK FOLLOWS ==========
RMAN-00571: =================================================
RMAN-03002: failure of recover command at 05/18/2015 18:50:36
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 30
RMAN-06025: no backup of archived log for thread 1 with sequence 29
RMAN-06025: no backup of archived log for thread 1 with sequence 27

RMAN> alter database open;

RMAN-00571: =================================================
RMAN-00569: ========== ERROR MESSAGE STACK FOLLOWS ==========
RMAN-00571: =================================================
RMAN-03002: failure of alter db command at 05/18/2015 18:51:29
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u02/oradata/DB01/system01.dbf'

You have lost your database. Even if you did a full online backup plus archivelog, it did not contain all the archivelogs and RMAN did not tell you.

There is more than one way to reduce this annoyance :

1) don’t backup archivelogs during full backup
2) don’t delete archivelog backups done during a full backup
3) report any archivelog backups that run during a full. Either by looking in the RMAN dictionary or in your house logfiles
4) do offline backup for long term archives
5) don’t trust RMAN

And as well :
Test your backups 😀

Categories
dba sqlplus

bypass ora-20

When you really need to run one script, at all cost, an annoying error is ORA-00020: maximum number of processes (40) exceeded, which can even occurs as sysdba.

Test case (21 is a not something to do in real life):

SQL> alter system set processes=21 scope=spfile;
System altered.
SQL> startup force quiet
ORACLE instance started.
Database mounted.
Database opened.
SQL> quit

From now on, sqlplus as sysdba is impossible.
$ sqlplus -s -L / as sysdba
ERROR:
ORA-00020: maximum number of processes (40) exceeded
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus

Okay, if you really really need to run one script, you could connect with sqlplus -prelim and restart the database.

if :|sqlplus / as sysdba|grep ORA-00020
then
echo shu abort|sqlplus -prelim / as sysdba
echo startup quiet|sqlplus / as sysdba
fi

If ORA-20 is detected, then the database will be restarted.

Categories
dba security sqlplus

switch user in Oracle

Almost a decade ago I wrote about su in sqlplus. This 10gR2 “new” feature allows delegation ร  la sudo.

By checking the DBA_USERS in 12c I found PROXY_ONLY_CONNECT. According to Miguel Anjo, there is a secret syntax for allowing only the proxy user.


SQL> ALTER USER app_user PROXY ONLY CONNECT;
SQL> CONNECT app_user/xyz
ERROR:ORA-28058: login is allowed only through a proxy

Categories
dba

Unusable index

After table maintenance, like move or split partition, underlying indexes are marked as unusable.

This boils down to segment reorganisation, not dictionary change.

For instance :

CREATE TABLE t(x NUMBER)
PARTITION BY RANGE(x)
(PARTITION p1 VALUES LESS THAN (MAXVALUE));

CREATE INDEX i ON t (x);


INSERT INTO t VALUES (1);

ALTER TABLE T SPLIT PARTITION p1 AT
(100) INTO (PARTITION p2, PARTITION p3);

SELECT index_name, status
FROM user_indexes
WHERE index_name = 'I';

INDEX_NAME STATUS
---------- --------
I VALID

The partition p1 is splitted into two p2 and p3, all rows from p1 belongs to p2, p3 is created empty, no data manipulation, the index remains valid


delete from t;
INSERT INTO t VALUES (250);

ALTER TABLE T SPLIT PARTITION p3 AT
(200) INTO (PARTITION p4, PARTITION p5);

SELECT index_name, status
FROM user_indexes
WHERE index_name = 'I';

INDEX_NAME STATUS
---------- --------
I VALID

Same here, all rows from p3 moved to p5, p4 is created empty, no problem


delete from t;
INSERT INTO t VALUES (250);
INSERT INTO t VALUES (350);

ALTER TABLE T SPLIT PARTITION p5 AT
(300) INTO (PARTITION p6, PARTITION p7);

SELECT index_name, status
FROM user_indexes
WHERE index_name = 'I';
INDEX_NAME STATUS
---------- --------
I UNUSABLE

One row goes to p6, one row to p7, the index is invalidated.

To avoid this, use ALTER TABLE T SPLIT PARTITION p5 AT (300) INTO (PARTITION p6, PARTITION p7) UPDATE INDEXES;

What are the consequences?


INSERT INTO t VALUES (320);
1 row inserted

It does not prevent DML in this case.

select * from t where x=320;

X
----------
320

Execution Plan
---------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=ALL_ROWS
1 0 PARTITION RANGE SINGLE
2 1 TABLE ACCESS FULL SCOTT.T

The row is retrieved, but the unusable index is not used.

In some case, DML may be prevented.

alter index i rebuild;
alter table t modify (x primary key using index);
ALTER TABLE T SPLIT PARTITION p7 AT
(330) INTO (PARTITION p8, PARTITION p9) ;

insert into t values (450);
ORA-01502: index 'SCOTT.I' or partition of such index is in unusable state

The index can no longer be used for constraint enforcement and the INSERT fails.

If the index is partitioned, the index is not marked as unusable as a whole, only the affected (sub)partitions are marked.

Check all your indexes status in dba_indexes.status, dba_ind_partitions.status and dba_ind_subpartitions.status.

Rebuild them with alter index i rebuild, alter index i rebuild partition p and alter index i rebuild subpartition sp.

Categories
dba installation

Did you forget to run root.sh?

Not easy to detect, and depending on the product (agent/database), it may have only limited side effects.

Like external jobs not running, operating systems statistics not collected.

But it is not always easy to diagnose.

For instance if you patch from OMS 12cR2 to 12cR3, and you run the root.sh only in 12cR2, they are very few statistics missing (one is the OS_STORAGE_ENTITY).

Running the root.sh doesn’t generate a log file or an entry in the inventory.

To check if it was executed, check what it is supposed to do. It is a bit different in each version. One think it always does is changing the ownership to root and set the sticky bit for a few binaries. For the database, this is done in sub-scripts called rootadd.sh (10g) or rootadd_rdbms.sh (11g/12c).

eval ls -l $(find $ORACLE_HOME -name "rootadd*sh" -exec awk '$1="$CHOWN"&&$2=="root"{print $3}' {} \;|sort -u)

-rwsr-x--- root dba .../product/11.2.0/db_4/bin/extjob
-rwsr-x--- root dba .../product/11.2.0/db_4/bin/jssu
-rws--x--- root dba .../product/11.2.0/db_4/bin/nmb
-rws--x--- root dba .../product/11.2.0/db_4/bin/nmhs
-rws--x--- root dba .../product/11.2.0/db_4/bin/nmo
-rwsr-x--- root dba .../product/11.2.0/db_4/bin/oradism
-rw-r----- root dba ...11.2.0/db_4/rdbms/admin/externaljob.ora

If the ownership is root, you definitely did run the root.sh.

On the 12c agent, there is a FULL_BINARY_LIST variable that point to list of root binaries in sbin

eval $(grep FULL_BINARY_LIST= $AGENT_HOME/root.sh)
cd $AGENT_HOME/../../sbin
ls -l $FULL_BINARY_LIST

-rws--x--- root dba nmb
-rws--x--- root dba nmhs
-rws--x--- root dba nmo

If all files exist and belong root, it looks like you did run the root.sh.

Categories
dba sql

last partition

if you really need to quickly find the latest partition per table, I have written this little gem


WITH FUNCTION d (b BLOB, len number) RETURN DATE IS
d DATE;
BEGIN
IF DBMS_LOB.SUBSTR (b, 1, 1) = hextoraw('07') and len=83
THEN
DBMS_STATS.convert_raw_value (DBMS_LOB.SUBSTR (b, 12, 2), d);
ELSE
d := NULL;
END IF;
RETURN d;
END;
SELECT
u.name owner,
o.name table_name,
max(d (bhiboundval, hiboundlen)) last_partition
FROM sys.tabpart$ tp
JOIN sys.obj$ o USING (obj#)
JOIN sys.user$ u ON u.user# = o.owner#
WHERE u.name='SCOTT'
group by u.name, o.name
order by last_partition desc;

It doesn’t cover all partitioning type, but it is pretty fast and simple

Categories
dba

Do you have a partition in 2015

You need to check the high_value from dba_tab_partitions.
Or you you could metadata.

With metadata, it is not a long, it either clob or clob-xml.

SELECT t.table_name,
MAX (
TO_DATE (
REGEXP_SUBSTR (
EXTRACT ( (VALUE (x)), 'HIBOUNDVAL').getStringVal (),
' \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}'),
'YYYY-MM-DD HH24:MI;:SS'))
high_value
FROM user_tables t,
TABLE (
XMLSEQUENCE (
EXTRACT (
xmltype (DBMS_METADATA.get_xml ('TABLE', t.table_name)),
'//ROWSET/ROW/TABLE_T/PART_OBJ/PART_LIST/PART_LIST_ITEM/HIBOUNDVAL'))) x
WHERE partitioned = 'YES'
HAVING MAX (
TO_DATE (
REGEXP_SUBSTR (
EXTRACT ( (VALUE (x)), 'HIBOUNDVAL').getStringVal (),
' \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}'),
'YYYY-MM-DD HH24:MI;:SS')) < DATE '2015-01-02' GROUP BY t.table_name ORDER BY 1; TABLE_NAME HIGH_VALUE ---------- ------------------------------ T1 2015-01-01 T11 2000-01-01 T20 1436-03-09 T6 2000-01-01 T7 2014-12-21 T8 2015-01-01

Ok, out of xml, I get all HIBOUNDVAL and hazardously try some regular expression to find a pattern.

It already returned most of my table that could have caused me New Eve headache. But partitioned is not that simple. Line 3 for instance is not in a gregorian format (it's a bug). And it does not cover index partition, subpartitions, interval partitions, neither partition with more than one date column as key.

This sounds a lot, and there is only one HIGH_VALUE for multiple key, and the High_value is something like 0,TO_DATE(' 3543-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),MAXVALUE

Okay, if you forget about non-gregorian calendars, and ignore about MAXVALUE, we could use something like


curid := DBMS_SQL.open_cursor;
txt :=
'select '
|| REPLACE (g.high_value, 'MAXVALUE', 'NULL')
|| ' from dual';
DBMS_SQL.parse (curid, txt, DBMS_SQL.NATIVE);

Then from dba_PART_KEY_COLUMNS (pkc) or dba_SUBPART_KEY_COLUMNS, joined with dba_tab_columns, we could retrieve the datatype (date and timestamp with or without time zone) and the pkc.column_position.


DBMS_SQL.DEFINE_COLUMN (curid,
f.column_position,
'date',
12);

Casting to date should be fine.

Then we fetch and retrieve the Nth column

IF DBMS_SQL.FETCH_ROWS (curid) > 0
THEN
DBMS_SQL.COLUMN_VALUE (curid, f.column_position, namevar);
END IF;

If you read me that far, you probably can write the rest of the code for yourself.

Could be useful to do this before end-of-year ๐Ÿ˜‰

Categories
dba ldap security

poor man ActiveDirectory password checker

To have the same users in multiple databases and no single sign on is quite a nightmare for password expiration, synchronisation and validation.

You probably were discouraged by the long long route to kerberos, where the 11.2.0.2 bugs are fixed in 11.2.0.4, the 12.1 bugs are fixed in 12.2. And lot’s of system changes that won’t be welcome by your sysadmins / winadmins.

Okay, to partly cover the password expiration issue, you could check in a profile function that the password is the one from AD.

Firstly, without SSL


CREATE OR REPLACE FUNCTION pw_function_AD
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
sess raw(32);
rc number;
BEGIN
sess := DBMS_LDAP.init(
'example.com',dbms_ldap.PORT);
rc := DBMS_LDAP.simple_bind_s(
sess, username||'@example.com',
password);
rc := DBMS_LDAP.unbind_s(sess);
RETURN(TRUE);
EXCEPTION
WHEN OTHERS THEN
rc := DBMS_LDAP.unbind_s(sess);
raise;
END;
/
GRANT EXECUTE ON pw_function_ad TO PUBLIC;
CREATE PROFILE AD LIMIT
PASSWORD_VERIFY_FUNCTION pw_function_AD;
ALTER PROFILE AD LIMIT
PASSWORD_LIFE_TIME 30;
ALTER PROFILE AD LIMIT
PASSWORD_REUSE_MAX UNLIMITED;

alter user lsc profile AD;

When the password expires, the user must change it to its AD Password.

If I try with a dummy password, the profile will reject this

SQL> conn lsc/pw1
ERROR:
ORA-28001: the password has expired

Changing password for lsc
New password:anypassword
Retype new password:anypassword
ERROR:
ORA-28003: password verification for
the specified password failed
ORA-31202: DBMS_LDAP: LDAP client/server
error: Invalid credentials.
80090308: LdapErr: DSID-0C0903A9,
comment: AcceptSecurityContext error,
data 52e, v1db1
Password unchanged
Warning: You are no longer connected to ORACLE.

I need to enter my Windows password

SQL> conn lsc/pw1
ERROR:
ORA-28001: the password has expired

Changing password for lsc
New password: mywindowspassword
Retype new password: mywindowspassword
Password changed
Connected.

Secondly, with SSL.

Maybe simple bind without SSL is not possible (check http://support.microsoft.com/kb/935834). And for sure it is better to not send unencrypted plain text password over the network.

Create a wallet with password with the ROOT Certification Authority that signed your AD. You probably could download this in your trusted root certification authorities in Internet Explorer.

Internet Explorer – Tools – Internet Options – Content – Certificates – Trusted root.

Then you create a ewallet.p12 with orapki. No need for user certificate and no need for single-sign-on. Only import the trusted root (and intermediaries if applicable).

Here is the modified code

CREATE OR REPLACE FUNCTION pw_function_AD
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
sess raw(32);
rc number;
BEGIN
sess := DBMS_LDAP.init(
'example.com',dbms_ldap.SSL_PORT);
rc := DBMS_LDAP.open_ssl(
sess, 'file:/etc/wallet/MSAD',
'welcome1', 2);
rc := DBMS_LDAP.simple_bind_s(
sess, username||'@example.com',
password);
rc := DBMS_LDAP.unbind_s(sess);
RETURN(TRUE);
EXCEPTION
WHEN OTHERS THEN
rc := DBMS_LDAP.unbind_s(sess);
raise;
END;
/

If you get SSL Handshake, be prepared, it could be anything! Check your wallet, your certificate, your permission, your wallet password.

One step further could be to expire users as soon as they change their password in AD or when they expire there.

For instance with powershell goodies for active directory


PS> (Get-ADuser lsc -properties PasswordLastSet).PasswordLastSet

Montag, 6. Oktober 2014 08:18:23

PS> (Get-ADuser king -properties AccountExpirationDate).AccountExpirationDate

Mittwoch, 16. Juli 2014 06:00:00

And in the database


SQL> SELECT ptime FROM sys.user$
WHERE name ='LSC';

PTIME
-------------------
2014-11-10_10:33:08

If PTIME is less than PasswordLastSet or if AccountExpirationDate is not null, expire the account.

In conclusion : if you do not want to use Kerberos, nor Oracle “OctetString” Virtual Directory ovid nor Oracle Internet directory oid, this workaround may help to increase your security by addressing the “shared” and “expired” accounts problematic

There an additional hidden benefit. You could set up a self-service password reset function and send a generated expired password per mail, that the user won’t be able to change without its AD password

Categories
dba unicode

import into UTF8 database

A common error when you import single-byte characters (e.g. iso8859p1 or mswin1252) into multi-bytes databases (e.g. utf8) is ORA-12899: value too large for column.

The root cause is the default semantics in a database being BYTE


SQL> select VALUE, ISDEFAULT
from v$parameter
where NAME='nls_length_semantics'
VALUE ISDEFAULT
------- ---------
BYTE TRUE

It means, one char equals one byte. But after conversion, one char is larger than one byte and does not fit any longer.

single-byte

SQL> select VALUE
from nls_database_parameters
where parameter='NLS_CHARACTERSET';
VALUE
-------------
WE8MSWIN1252
SQL> create table t(x char(1));
Table created.
SQL> insert into t values ('รฉ');
1 row created.
SQL> commit;
Commit complete.
$ expdp scott/tiger dumpfile=t.dmp tables=t
. . exported "SCOTT"."T" 1 rows

multi-byte

SQL> select VALUE
from nls_database_parameters
where parameter='NLS_CHARACTERSET';
VALUE
-----------
UTF8
$ impdp scott/tiger dumpfile=t.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type
TABLE_EXPORT/TABLE/TABLE_DATA
ORA-02374: conversion error loading table
"SCOTT"."T"
ORA-12899: value too large for column X
(actual: 2, maximum: 1)
ORA-02372: data for row: X : 0X'E9'
. . imported "SCOTT"."T" 0 out of 1 rows

How do I import my data?

1) import the metadata

$ impdp scott/tiger dumpfile=t.dmp content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE

2) change the char_used of the column(s) from (B)yte to (C)har

SQL> select
column_name, char_used, data_length, data_type
from user_tab_columns
where table_name='T' and char_used='B';
COLUMN_NAME C DATA_LENGTH DATA_TYPE
------------ - ----------- ---------
X B 1 CHAR
SQL> alter table t modify x char(1 char);
Table altered.

3) import the data

$ impdp scott/tiger dumpfile=t.dmp content=data_only
Processing object type
TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T" 1 rows

4) check

SQL> select x, length(x), lengthb(x) from t;
X LENGTH(X) LENGTHB(X)
- ---------- ----------
รฉ 1 2

My column has now a length of one char and two bytes.