Connecting to CDB and PDB - Oracle 12c

Creation on a CDB (Container database) creates a service named is the CDB name. This is a side effect of creating a PDB (Pluggable Database) in the CDB, a service is created inside it with a property that identifies it as the initial current container. The service is also started as a side effect of creating the PDB. Although its metadata is recorded inside the PDB, the invariant is maintained so that a service name is unique within the entire CDB.



Use the Easy Connect syntax to connect to the root unless a net service name is configured in the tnsnames for the root service.




. oraenv

[enter cdb1 at the prompt]


sqlplus sys/oracle@localhost:1521/cdb1 as sysdba

show con_name

show con_id

Connect to the root by using OS authentication.



connect / as sysdba

show con_name

show con_id 






Display the list of available services for the root and the PDBs.



select name, con_id from v$active_services order by 1;



Use the Easy Connect syntax to connect to the PDB unless a net service name is configured in the tnsnames for the PDB service.



connect sys/oracle@localhost:1521/pdb1 as sysdba

show con_name

show con_id

exit

Trigger to backup the data before delete or update on a table - Oracle

Lets create a table :

 CREATE TABLE "ABC" 
   ( "EMPNO" NUMBER(4,0), 
"ENAME" VARCHAR2(10 BYTE), 
"JOB" VARCHAR2(9 BYTE), 
"MGR" NUMBER(4,0), 
"HIREDATE" DATE, 
"SAL" NUMBER(7,2), 
"COMM" NUMBER(7,2), 
"DEPTNO" NUMBER(2,0)
   ) TABLESPACE "USERS" ;

Now we will insert some data into it :

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);

select count(*) from abc;
-- 5 rows

Lets create a backup table to store data. we are only storing part of the original table data, we can edit as per requirement :

  CREATE TABLE "SCOTT"."ABC_BAK" 
   ( "EMPNO" NUMBER(4,0), 
"ENAME" VARCHAR2(10 BYTE), 
"JOB" VARCHAR2(9 BYTE), 
"MGR" VARCHAR2(20 BYTE), 
"T_STAMP" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
   ) TABLESPACE "USERS" ;


   select count(*) from abc_bak;
   
   0 rows
   
   
Now lets create a trigger, that will triger all the data from the table and store it in backup table before delete or update:

CREATE OR REPLACE TRIGGER ABC_BAK1 
BEFORE UPDATE OR DELETE 
  ON ABC FOR EACH ROW
  BEGIN
      INSERT INTO ABC_BAK
 ( EMPNO,
   ENAME,
   JOB,MGR
    )
VALUES
 ( :old.EMPNO,
   :old.ENAME,
   :old.JOB,
   :old.MGR); 
END;


===========================================================

NOTE : You also add username and Host machine by adding below to trigger
You need to declare the variable first and then assgn values to it

DECLARE
   v_username varchar2(10);
      
  BEGIN
  -- Find username of person performing the DELETE on the table

   SELECT user INTO v_username FROM dual;

Add these to values ----

V_USERNAME,
   sys_context('userenv','host')   --- You can also add host machine here

===========================================================

Lets test our trigger is working fine.

delete from abc where ename=SCOTT;
delete from abc where ename=ALENN;
update abc set ename=ADAM where ename=ADAMS;
.
.
.
.
do some activity and test then validate the bak table.


Select * from abc_bak;

    EMPNO ENAME      JOB       MGR                  T_STAMP                       

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

      7788 SCOTT                                     09-FEB-15 06.35.28.862219000 PM 

      7499 ALLEN                                     09-FEB-15 06.35.28.862219000 PM 

      7521 WARD                                      09-FEB-15 06.35.28.862219000 PM 

      7876 ADAMS      CLERK                          09-FEB-15 06.35.53.457126000 PM 

      7900 JAMES      CLERK                          09-FEB-15 06.35.53.462433000 PM 

      7902 FORD       ANALYST                        09-FEB-15 06.35.53.466738000 PM 

      7934 MILLER     CLERK                          09-FEB-15 06.35.53.472108000 PM 

      7788 SCOTT      ANALYST   7566                 09-FEB-15 06.37.30.307425000 PM 

 8 rows selected 

DELETE OS AUDIT FILES IN ORACLE

[atoorpu@ORACLE1 adump]$ pwd
/u01/app/oracle/admin/ORCL/adump
[atoorpu@ORACLE1 adump]$ ls -1 /u01/app/oracle/admin/ORCL/adump | wc -l
22273
[atoorpu@ORACLE1 adump]$ ls -lrt *aud | wc -l
11363
[atoorpu@ORACLE1 adump]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 9 10:40:58 2015

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


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

If the audit files are in the database (sys.aud$). They can be cleaned up using:

SQL> DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);

If the audit files are in the OS. They can be cleaned up using:
SQL> BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);
   END;
/  

PL/SQL procedure successfully completed.

The CLEAN_AUDIT_TRAIL procedure is the basic mechanism for manually purging the audit trail. It accepts two parameters.

AUDIT_TRAIL_TYPE: The audit trail whose timestamp is to be set (Constants). Only individual audit trails are valid, not the constants that specify multiples.

Types :
AUDIT_TRAIL_XML  ---- For Auditing on XML (XML files)
AUDIT_TRAIL_OS   --- For Auditing on OS (text files)
AUDIT_TRAIL_AUD_STD  --- For Standard Auditing

USE_LAST_ARCH_TIMESTAMP: Set to FALSE to purge all records/files, or TRUE to only purge records/files older than the timestamp specified for the audit trail.

 Lets see if the Last  Archive TS is  set for OS Audit files.


SQL> set pagesize 150
set linesize 150
col last_archive_ts format a40
select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;


AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ----------------------------------------
STANDARD AUDIT TRAIL            0 23-DEC-14 03.34.45.000000 PM +00:00



 You can also set the Last  Archive TS  if it is not set 
(in below it will set OS_AUDIT TS to sydate-45) :

SQL> BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    last_archive_time => SYSTIMESTAMP-45);
END;
/  

PL/SQL procedure successfully completed.


 Now Lets confirm the last Archive Time stamp in DB.

SQL> COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40

SELECT * FROM dba_audit_mgmt_last_arch_ts;SQL> SQL> SQL>

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ----------------------------------------
STANDARD AUDIT TRAIL            0 23-DEC-14 03.34.45.000000 PM +00:00
OS AUDIT TRAIL                  1 26-DEC-14 10.43.05.000000 AM -06:00

SQL> BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);
   END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[atoorpu@ORACLE1 adump]$ ls -lrt *aud | wc -l
1602
[atoorpu@ORACLE1 adump]$

Generate sql file from EXPDP/IMPDP

It’s straight forward to generate DDL SQL Script from any specific schema. However, it has nothing to do with expdp.


expdp arvind/password schemas=hr directory=DPUMP dumpfile=HR_2014_08_13.dmp logfile=impsql_HR_2014_08_13.log


while importing you need to specify .sql file name using impdp, it will generate DDL SQL Script for any specific schema.

 [oracle@qpdbdev202 dpump]$ impdp arvind/password directory=DPUMP  dumpfile=HR_2014_08_13.dmp sqlfile=HR_2014_08_13.sql logfile=impsql_HR_2014_08_13.log

Create sample table and insert random data in database

if you are trying to do some testing and want to insert some random data into tables.

Lets say table name is T1.

CREATE TABLE T1
(  ID NUMBER ,
NAME VARCHAR2(20 BYTE) )
TABLESPACE USERS;

Insert Data :
Here is a simple plsql block to insert 1500 rows into a table with unique ID and random string of 20
characters.

DECLARE
  i NUMBER :=1;
BEGIN
  LOOP
    i := i+1;
    insert into T1 values(i,(SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL));
    commit;
    dbms_output.put_line (i);
    EXIT
  WHEN i >= 1500;
  END LOOP;
END;

select count (*) from T1;
-- result : 1500 rows

-- Note : You can alter the insert statement in pl/sql block as you want.
-- You can increase the data by increasing i value to what ever you desire. Example i >= 15000;
-- You can also increase the sequence by altering i := i +2 etc..

STRING

The STRING function returns a string of random characters of the specified length. The OPT parameter determines the type of string produced as follows:

• 'u', 'U' - uppercase alpha characters
• 'l', 'L' - lowercase alpha characters
• 'a', 'A' - mixed case alpha characters
• 'x', 'X' - uppercase alpha-numeric characters
• 'p', 'P' - any printable characters

To include sysdate :

There are no specific functions for generating random dates, but we can add random numbers to an existing date to make it random. The following example generates random dates over the next year.

DECLARE
  i NUMBER :=10;
BEGIN
  LOOP
    i := i+10;
    insert into T1 values(i,(SELECT DBMS_RANDOM.STRING('A', 10) FROM DUAL),(SELECT TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)) from dual));
    commit;
    dbms_output.put_line (i);
    EXIT
  WHEN i >= 150;
  END LOOP;
END;

-- Note : By doing the correct divisions, we can add random numbers of hours, seconds or minutes to a date. We can do that by simple sql :

(TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day))

Tablespaces DDL - Oracle

There may be situation where you are trying to create a new database similar to old one and it is a fresh install and you need to get the Tablespaces DDL from the old one. This query will be very help full.

SQL>Set pages 999;
SQL>set long 90000;
SQL>spool ddl_list.sql

SQL>select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;

SQL>spool off

Sample Output :

"  CREATE TABLESPACE "USERS" DATAFILE
  '/u02/oracle/oradata/ORCL/datafiles/users01.dbf' SIZE 5242880
  AUTOEXTEND ON NEXT 52428800 MAXSIZE 20000M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  '/u02/oracle/oradata/ORCL/datafiles/users01.dbf' RESIZE 2097152000";

"  CREATE TABLESPACE "TOOLS" DATAFILE
  '/u02/oracle/oradata/ORCL/datafiles/tools01.dbf' SIZE 67108864
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO";

" CREATE TABLESPACE "INDX" DATAFILE
  '/u02/oracle/oradata/ORCL/datafiles/indx01.dbf' SIZE 268435456
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO";

Audit failed logon attempts - Oracle

How to audit failed logon attempts

Oracle Audit -- failed connection
Background:
In some situation DBA team wants to audit failed logon attempts when "unlock account"  requirement becomes frequently and user cannot figure out who from where is using incorrect password to cause account get locked.

Audit concern:
Oracle auditing may add extra load and require extra operation support. For this situation DBA only need audit on failed logon attempts and do not need other audit information. Failed logon attempt is only be able to track through Oracle audit trail, logon trigger does not apply to failure logon attempts


Hint: The setting here is suggested to use in a none production system. Please evaluate all concern and load before use it in production.



Approach:
1. Turn on Oracle audit function by set init parameter:
               audit_trail=DB
Note:
database installed by manual script, the audit function may not turn on:
database installed by dbca, the default audit function may already turn on:
Check:

SQL> show parameter audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      NONE

Turn on Oracle audit
a. If database use spfile
SQL> alter system set audit_trail=DB scope=spfile ;
System altered.

b. if database use pfile, modify init<Sid>.ora directly.

Restart database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup ;
ORACLE instance started.


2. Turn off Oracle default audit
Privilege audit information stored in dba_priv_audit_opts;
Note: Oracle 11g has couple of audit turned on default when the audit_trail is set. It will just utilize more resources that you might not want.
Oracle 10g, audit options is setup by explicit command (we can audit these options back any time).

Generate a script to turn off default privilege audit which we don't need here.
SQL>  SELECT 'noaudit '|| privilege||';' from dba_priv_audit_opts where user_name is NULL;
'NOAUDIT'||PRIVILEGE||';'
-------------------------------------------------
noaudit ALTER SYSTEM;
noaudit AUDIT SYSTEM;
noaudit CREATE SESSION;
noaudit CREATE USER;
noaudit ALTER USER;
noaudit DROP USER;
noaudit CREATE ANY TABLE;
noaudit ALTER ANY TABLE;
noaudit DROP ANY TABLE;
noaudit CREATE PUBLIC DATABASE LINK;
noaudit GRANT ANY ROLE;
noaudit ALTER DATABASE;
noaudit CREATE ANY PROCEDURE;
noaudit ALTER ANY PROCEDURE;
noaudit DROP ANY PROCEDURE;
noaudit ALTER PROFILE;
noaudit DROP PROFILE;
noaudit GRANT ANY PRIVILEGE;
noaudit CREATE ANY LIBRARY;
noaudit EXEMPT ACCESS POLICY;
noaudit GRANT ANY OBJECT PRIVILEGE;
noaudit CREATE ANY JOB;
noaudit CREATE EXTERNAL JOB;
23 rows selected.

-- run above commands

3. Turn on audit on failed connection
SQL> AUDIT CONNECT WHENEVER NOT SUCCESSFUL;

Audit succeeded.

SQL> SELECT PRIVILEGE,SUCCESS,FAILURE FROM dba_priv_audit_opts;

PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
CREATE SESSION                           NOT SET    BY ACCESS

4. Retrieve information
Note: audit information is stored on sys.aud$. There multiple views Oracle provide to help you read sys.aud$. Logon failed information can be retrieve from  dba_audit_session

SQL>   select os_username,  username, userhost,  to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') logon_time,  action_name, returncode from dba_audit_session;

OS_USERNAME                    USERNAME                       USERHOST                                           TIMESTAMP           ACTION_NAME                  RETURNCODE
------------------------------ ------------------------------ -------------------------------------------------- ------------------- ---------------------------- ----------
Arvind                    machine1         
HOME-Arvind                                       12/06/2014 13:40:12 LOGON                              1017
Arvind                    machine1         
HOME-lArvind                                       12/06/2014 13:40:25 LOGON                              1017
Arvind                   machine1         
HOME-Arvind                                       12/06/2014 15:31:29 LOGON                              1017
Arvind                   machine1         
HOME-Arvind                                       12/06/2014 15:31:38 LOGON                              1017
4 rows selected.

Note: RETURNCODE is the ORA error code return to user.
ORA-1017 is incorrect password
ORA-28000 is account locked
ORA-1045 is missing connect privilege

------------------------------------------------------------
Up here, we be able to audit who is the bad boy causing account locked.

Turning off the audit:
If you no longer need the audit on failed attempts, run this command to turn off
SQL> noaudit CONNECT;

Noaudit succeeded.

SQL> SELECT PRIVILEGE,SUCCESS,FAILURE FROM dba_priv_audit_opts;

no rows selected


Oracle use system tablespace for sys.aud$. For enhancement, you may consider to move sys.aud$ to separate tablespace.


6. Move sys.aud$ out of system tablespace.
Oracle 11g provide package dbms_audit_mgmt.set_audit_trail_location to relocate the aud$ table.
SQL>  SELECT table_name, tablespace_name FROM dba_tables WHERE table_name ='AUD$';
TABLE_NAME                     TABLESPACE_NAME
----------------------------- ------------------------------
AUD$                           SYSTEM

Following example shows how to move sys.aud$ from system tablespace to user_data1 tablespace.

SQL> exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'USER_DATA1');


PL/SQL procedure successfully completed.

SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name ='AUD$';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           USER_DATA1



7. Clean up AUD$

You can simply run delete or truncate command


delete from sys.AUD$;
truncate table sys.AUD$;