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]$