PL/SQL varray and record examples

using varray

set serveroutput ON

DECLARE 
   type aud_int_type IS varray(10) of integer; 
   type aud_str_type IS varray(10) of varchar2(60); 
   audits aud_int_type;
   audit_names aud_str_type;
   total integer;
begin
  audits := aud_int_type(SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
                         SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML);
  
  audit_names := aud_str_type('SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD',
                              'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML');

  total := audits.count;
  for i in 1 .. total LOOP
    if DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(audit_trail_type => audits(i)) then
      dbms_output.put_line(audits(i) || ' initialized');
    else
      dbms_output.put_line(audits(i) || ' not initialized');
    end if;
  end LOOP;

  total := audit_names.count;
  for i in 1 .. total LOOP
    dbms_output.put_line('exec DBMS_AUDIT_MGMT.DEINIT_CLEANUP(audit_trail_type  => ' || (audit_names(i) || ');');
  end LOOP;
end;
/

using record

set serveroutput ON

DECLARE 
   type aud_type IS record (value integer, name varchar2(60)); 
   type aud_tab IS table of aud_type INDEX by binary_integer;
   audits aud_tab;
   total integer;
begin
  audits(1).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD;
  audits(2).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD;
  audits(3).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES;
  audits(4).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS;
  audits(5).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD;
  audits(6).value := SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML;
  
  audits(1).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD';
  audits(2).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD';
  audits(3).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES';
  audits(4).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS';
  audits(5).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD';
  audits(6).name := 'SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML';

  total := audits.count;
  for i in 1 .. total LOOP
    if DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(audit_trail_type => audits(i).value) then
      dbms_output.put_line(audits(i).name || ' initialized');
    else
      dbms_output.put_line(audits(i).name || ' not initialized');
    end if;
  end LOOP;

  for i in 1 .. total LOOP
    dbms_output.put_line('exec DBMS_AUDIT_MGMT.DEINIT_CLEANUP(audit_trail_type  => ' || audits(i).name || ');');
  end LOOP;
end;
/

Advertisements

LRM-00109: could not open parameter file

The error LRM-00109 could happen when spfile is lost, or spfile is re-created using pfile but cluster still uses old filename.
To fix the issue, you need to config database ins cluster to use the new filename.

error message

$ srvctl start instance -d ORCL -i ORCL1
PRCR-1013 : Failed to start resource ora.orcl.db
PRCR-1064 : Failed to start resource ora.orcl.db on node node01
CRS-5017: The resource action "ora.orcl.db start" encountered the following error:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initORCL1.ora'
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/node01/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.orcl.db' on 'node01' failed

re-create spfile

SQL> startup mount pfile='/<>/init_orcl.ora';
SQL> create spfile from pfile='<>/init_orcl.ora';

check the full path of the new spfile in asm disk group

$ asmcmd -p
ASMCMD> cd datac1/orcl/parameterfile/
ASMCMD [+datac1/orcl/parameterfile] > ls -l
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  MIRROR  COARSE   JAN 30 12:00:00  Y    spfile.7909.966773931
PARAMETERFILE  MIRROR  COARSE   JAN 30 13:00:00  Y    spfile.7910.966777041

config database using new spfile

-- assign new spfile to database
$ srvctl modify database -d ORCL -p +DATAC1/ORCL/PARAMETERFILE/spfile.7910.966777041

-- verify
$ srvctl config database -d ORCL

-- restart database
$ srvctl start database -d ORCL

unified auditing

view audit policies

col user_name for a30
col object_name for a30
col proxy_name for a30
col policy_name for a30

select * from AUDIT_UNIFIED_ENABLED_POLICIES;

select * from dba_stmt_audit_opts;

select * from dba_priv_audit_opts;

select owner, object_name, object_type from dba_obj_audit_opts o 
where o.owner not in ('LBACSYS', 'DVSYS')
;

select distinct policy_name from AUDIT_UNIFIED_POLICIES;

view tablespaces

select s.tablespace_name, 
       s.owner, 
       s.segment_name, 
       s.bytes/1024/1024 size_mb
from dba_segments s 
where s.segment_name in ('AUD$', 'FGA_LOG$')
or s.owner = 'AUDSYS'
order by 1
;

select t.owner, t.table_name, t.tablespace_name 
from dba_tables t 
where t.table_name in ('AUD$', 'FGA_LOG$')
;

move audit to other tablespaces


create bigfile tablespace sys_audit datafile '+DATAC1' size 4G logging default compress for OLTP;

exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'SYS_AUDIT');
exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, audit_trail_location_value => 'SYS_AUDIT');
exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => 'SYS_AUDIT');

purge audit


begin 
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type  => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, 
    last_archive_time => SYSTIMESTAMP-5/1440);

  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, 
    use_last_arch_timestamp => true);
end;

enable/disable/drop audit policies


audit POLICY policy1;
audit POLICY policy2;

noaudit POLICY policy1;
noaudit POLICY policy2;

drop audit POLICY policy1;
drop audit POLICY policy2;

create audit policies


SELECT NAME FROM SYSTEM_PRIVILEGE_MAP;

CREATE AUDIT POLICY policy_name
{ {privilege_audit_clause [action_audit_clause ] [role_audit_clause ]}
| { action_audit_clause [role_audit_clause ] }
| { role_audit_clause }
}
[WHEN audit_condition EVALUATE PER {STATEMENT|SESSION|INSTANCE}]
[CONTAINER = {CURRENT | ALL}]
;


## audit privileges and roles

CREATE AUDIT POLICY policy1
PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE
ROLES emp_admin, sales_admin;

## audit object level

CREATE AUDIT POLICY policy1
ACTIONS SELECT ON OE.ORDERS, UPDATE ON HR.EMPLOYEES
;

## audit actions

CREATE AUDIT POLICY policy1
ACTIONS
CREATE TABLE,
CREATE VIEW,
DROP ROLE,
DROP TABLE,
DROP VIEW,
GRANT,
TRUNCATE TABLE
;

CREATE AUDIT POLICY policy1
ACTIONS LOGON 
WHEN 'SYS_CONTEXT (''USERENV'', ''OS_USER'') 
not IN (''admin'',''oracle'')' 
EVALUATE PER SESSION
;

## audit PRIVILEGES

CREATE AUDIT POLICY policy1
PRIVILEGES 
ALTER ANY ROLE,
CREATE ANY INDEX,
CREATE TABLE,
CREATE TABLESPACE,
DROP ANY INDEX,
DROP ANY MATERIALIZED VIEW,
DROP ANY VIEW,
DROP TABLESPACE
;

PDB timezone upgrade

After PDB migration, you may need to upgrade timezone to the latest version.

pre-upgrade

## check timezone files
ls -lt $ORACLE_HOME/oracore/zoneinfo

## check database timezone verion 
SELECT PROPERTY_NAME, property_value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE '%DST%'
;

## open PDB in upgrade mode
SQL> alter pluggable database pdb01 close immediate instances = all;
SQL> alter pluggable database pdb01 open upgrade;
SQL> alter session set container = pdb01;

## prepare
SQL> EXEC DBMS_DST.CREATE_AFFECTED_TABLE('AFFECTED_TABLES');
SQL> EXEC DBMS_DST.CREATE_ERROR_TABLE('ERR_TABLE');
SQL> exec DBMS_DST.BEGIN_PREPARE(26);
SQL> exec DBMS_DST.FIND_AFFECTED_TABLES(affected_tables  => 'AFFECTED_TABLES', log_errors_table => 'ERR_TABLE', log_errors => TRUE);
SQL> exec DBMS_DST.END_PREPARE();
SQL> select * from ERR_TABLE;

upgrade

## begin upgrade
SQL> exec DBMS_DST.BEGIN_UPGRADE(26);

## open PDB in normal mode
SQL> alter pluggable database pdb01 close immediate instances = all;
SQL> alter pluggable database pdb01 open read write;
SQL> alter session set container = pdb01;

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

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

## verify
SQL> exec select * from V$TIMEZONE_FILE;

duplicate cluster database using RMAN backup

Using RMAN backups, we can duplicate a database to different environments for testing purposes, or validate the backup itself.
Here is the procedure and command examples,

The high level procedure

0) using dbca create an empty database (only needed for first time)
1) generate asm file list (see instructions below)
2) alter system set cluster_database=FALSE scope=spfile;
3) srvctl stop database -d TEST2 -o immediate
4) drop asm files (see instructions below)
5) srvctl start instance -d TEST2 -i TEST21 -o nomount
6) rman auxiliary / catalog rcat/rcat@rcat  msglog logfile cmdfile rman_script
7) alter system set cluster_database=TRUE scope=spfile;
8) srvctl stop database -d TEST2 -o immediate
9) srvctl start database -d TEST2

How to generate asm file list

SQL> spool file_list.txt
SQL> select file_name from cdb_data_files;
SQL> select file_name from cdb_temp_files;
SQL> select member from v$logfile;
SQL> alter session set container = pdb$SEED;
SQL> select file_name from dba_data_files;
SQL> select file_name from dba_temp_files;
SQL> spool off

How to drop asm file

SQL> alter diskgroup "DG1" drop file '+DG1/TEST1/615776D7ECB497D5E053425A030AC5D8/DATAFILE/users.9257.963829293';

Example ramn script

run
{
ALLOCATE auxiliary CHANNEL c1 TYPE 'SBT' parms='BLKSIZE=..,SBT_LIBRARY=..,ENV=(..)';
ALLOCATE auxiliary CHANNEL c2 TYPE 'SBT' parms='BLKSIZE=..,SBT_LIBRARY=..,ENV=(..)';
ALLOCATE auxiliary CHANNEL c3 TYPE 'SBT' parms='BLKSIZE=..,SBT_LIBRARY=..,ENV=(..)';
ALLOCATE auxiliary CHANNEL c4 TYPE 'SBT' parms='BLKSIZE=..,SBT_LIBRARY=..,ENV=(..)';
set until time="to_date('2017-12-28 09:00:00', 'YYYY-MM-DD HH24:MI:SS')";
duplicate database TEST1 to TEST2 ;
}

migrate pluggable database to oracle 12.2

To migrate pluggable database from 12.1 to 12.2, you can follow these steps:
1. create CDB in 12.2
2. run prerequisite check in 12.1
3. run script generated by prerequisite check
4. extend temp tablespace if needed
5. unplug pdb
6. create pdb in 12.2 using unplugged pdb
7. upgrade new pdb
8. post-upgrade

create CDB in oracle 12.2

pre-upgrade

$ /u01/app/oracle/product/12.1.0.2/dbhome_1/jdk/bin/java -jar \
  /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/preupgrade.jar \
  -c "pdb1" TEXT TERMINAL

prerequisite result

....
....
==============
BEFORE UPGRADE
==============

  Run /preupgrade_fixups_TESTDB.sql to complete all
  of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
   + Adjust TABLESPACE SIZES as needed.
                                                Auto      12.2.0.1.0
     Tablespace                        Size     Extend    Min Size    Action
     ----------                     ----------  --------  ----------  ------
     SYSAUX                            4000 MB  DISABLED     1502 MB  None
     SYSTEM                            4000 MB  DISABLED     2782 MB  None
     TEMP                                20 MB  DISABLED      150 MB  Extend
....
....
Preupgrade generated files:
    /u01/app/oracle/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql
    /u01/app/oracle/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql

unplug PDB from oracle 12.1

SQL> alter pluggable database pdb1 unplug into '/home/oracle/pdb1.xml';

create PDB in oracle 12.2 using unplugged PDB

SQL> create pluggable database pdb1 using '/home/oracle/pdb1.xml';

upgrade newly created PDB

SQL> alter pluggable database pdb1 open upgrade;
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl -c "PDB1" -l /home/oracle catupgrd.sql
$ 

post upgrade

SQL> alter pluggable database pdb1 close immediate instances=all;
SQL> alter pluggable database pdb1 open read write instances=all;
$ datapatch -verbose

optional : re-plug original pdb in 12.1

SQL> drop pluggable database pdb1 keep datafiles;
SQL> create pluggable database pdb1 using '/home/oracle/pdb1.xml' NOCOPY TEMPFILE REUSE;;

sqlplus login without typing password

If you use sqlplus very often, you should put all your passwords into wallet so that you do not have to type password whenever you logon to a database.
Follow these simple commands, you will be able to create wallet, add credentials into wallet, and use it.

create wallet

mkstore -wrl c:\ora-wallet -create

create credential

mkstore -wrl c:\ora-wallet -createCredential db_name username password

show credential

mkstore -wrl c:\ora-wallet -listCredential

update sqlnet.ora

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = c:\ora-wallet)
     )
   )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

sqlplus login

sqlplus /@db_name