Pluggable database Service Level Access Control “Firewall”

If you are using VNC (Valid Node Checking) to implement a TNS firewall in non-CDB Oracle database architecture and wondering if there is a way to perform the same thing in Multitenant Architecture (CDB)…..Yes…through  a package DBMS_SFW_ACL_ADMIN  and its under the account: DBSFWUSER

The account has three tables: ACL$_OBJ , EXADIRECT_ACL , IP_ACL

1

First, you need to add your listener.ora file the following:

LOCAL_REGISTRATION_ADDRESS_LISTENER=ON

2

The firewall On clause means only connection coming validated against ACL will be accepted, others will be rejected. This is documented in the package specification comments as follows:

3

Running the following SQL Query in the CDB$root will provide the information of the services available:

SELECT service_id,name,network_name,pdb FROM   cdb_services;

4

To configure PDB Level Access, execute the following in CDB$root:

BEGIN

  dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_pdb_ace(‘pdb_test2‘,’192.142.56.136‘);

  dbsfwuser.DBMS_SFW_ACL_ADMIN.commit_acl;

END;

/

In the package specification you can see the list of procedures included within the package to provide a guide what input parameters are required :

5

Checking ACL has been added:

6

To remove access control entry:

BEGIN

  dbsfwuser.DBMS_SFW_ACL_ADMIN.IP_REMOVE_PDB_ACE(‘pdb_test2′,’192.142.56.136’);

  dbsfwuser.DBMS_SFW_ACL_ADMIN.commit_acl;

END;

/

 

 

 

 

 

 

Oracle RMAN Errors RMAN-03002 RMAN-03014 RMAN-06004

The below error was received after restoring the database to a previous point in time:

RMAN-03002: failure of backup command at 08/10/2017 14:00:15

RMAN-03014: implicit resync of recovery catalog failed

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high RECID

To resolve this either implement OPTION 1 OR OPTION 2 :

OPTION 1. unregister/register the database with recovery catalog:

— make sure you have a backup of your recovery catalog, because

it retains old  backup information of all of  your  databases.

rman target /

connect catalog_usr/catalog_usr_pass@CATALOG

RMAN> unregister database;

RMAN> register database;

OPTION 2. Open Database with Resetlogs:

sqlplus / as sysdba

SQL> ALTER DATABASE MOUNT;

SQL> ALTER DATABASE RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;

SQL> ALTER DATABASE RECOVER CANCEL;

SQL> ALTER DATABASE OPEN RESETLOGS;

rman target /

connect catalog_usr/catalog_usr_pass@CATALOG

RMAN> reset database;

RMAN> backup database;

New Features for Oracle Database 12cR2 In-Memory

Oracle 12cR2 release introduced many new features for the in-memory database technology, the following is some of the new features introduced:

Dynamically increase In-Memory area on the fly

Starting in 12.2, it is possible to increase the size of the In-Memory area on the fly, by increasing the INMEMORY_SIZE parameter using ALTER SYSTEM command. Also, worht mentioning is that The INMEMORY_SIZE parameter must be increased by 128MB or more in order for this change to take effect. However, It is not possible to shrink the size of the In-Memory area on the fly.

In-Memory FastStart

Enabling In-Memory FastStart (IM FastStart) the system will checkpoints the IMCUs from the IM column store to the FastStart area on disk. Consequnetly, when database restarts takes place, data is populated via the FastStart area rather than from the base tables.

Note that FAST_START_TBS is a dedicated tablespace configured for FastStart,is allowed for each PDB or non-CDB.the FastStart tablespace should be at least twice the size of the INMEMORY_SIZE setting.

CREATE TABLESPACE FAST_START_TBS DATAFILE
‘D:\APP\EMAD\VIRTUAL\ORADATA\ORACLE\FASTSTARTD01.DBF’ SIZE 400M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

SQL> BEGIN dbms_inmemory_admin.faststart_enable(‘FAST_START_TBS’); END;

So, basically The FastStart area defines what data is loaded when the database reopens. Population of data is still controlled by the “priority” settings, it won’t affect it.

SQL> SELECT * FROM V$INMEMORY_FASTSTART_AREA;

inmem1

In-Memory Expressions

In-Memory Expressions provide the ability to materialize commonly used expressions in the In-Memory column store. Materializing these expressions will improve the query performance by preventing the re-computation of the expression for every row. Also, it enables us to take advantage of all of the In-Memory query performance optimization when we access them.

* Analytic queries often contain complex expressions in the select list or where clause predicates that need to be evaluated for every row processed by the query

* The evaluation of these complex expressions can be very resource intensive and time consuming

* In-Memory Expressions greatly improve the performance of analytic queries that use computationally intensive expressions and access large data sets

* In-Memory expressions speed queries of large data sets by pre-computing computationally intensive expressions

In-Memory Virtual Columns

user-defined virtual columns can now be populated in the In-Memory column store. Virtual columns will be materialized as they are populated and since the expression is evaluated at population time it can be retrieved repeatedly without re-evaluation.

To illustrate:

SQL> alter system set inmemory_virtual_columns=ENABLE scope=spfile;

inmem2

CREATE TABLE HR.EMPLOYEE_SALES (
employee_id NUMBER(6) PRIMARY KEY ,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
salary NUMBER(8,2)
);

The target query against the table will be:

SQL> select employee_id,last_name,salary,(salary/12) as monthly_sal, 3*(salary+2000) as bonus from HR.EMPLOYEE_SALES order by bonus;

SQL> ALTER TABLE HR.EMPLOYEE_SALES ADD monthly_sal AS (salary/12);
SQL>ALTER TABLE HR.EMPLOYEE_SALES ADD bonus AS (3*(salary+2000));

SQL> ALTER TABLE HR.EMPLOYEE_SALES INMEMORY PRIORITY HIGH;

SQL>DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS(‘CURRENT’);
SQL> EXEC DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS();

SQL>SELECT OWNER, TABLE_NAME, COLUMN_NAME, SQL_EXPRESSION
FROM DBA_IM_EXPRESSIONS;

 

 

 

 

 

 

 

 

 

Oracle Virtual Private Database

The idea of Virtual Private Database is to enable users having access to a table to see ONLY subset of
data within the table. This technique is based on a function that will enable SQL execution to append a WHRE clause predicate. The subset of data can be from row-level or column-level perspective.

For demonstration, I will be using a table called EMP under SCOTT schema.

1.PNG

I will also create 2 accounts:
SQL> create user manager identified by mono670;
SQL>grant create session to to manager;
SQL>grant select on SCOTT.EMP to manager;
SQL>create user clerk identified by clerk332;

SQL>grant create session to clerk;
SQL>grant select on SCOTT.EMP to clerk;
connecting as accounts MANAGER & CLERK I can see the full table data !!!

2

3.PNG

Now I will create security administrator account:
SQL> alter session set container=CDB$root;
SQL> create user security_admin identified by zorro3_g;
SQL> grant create session to security_admin;
SQL> grant create procedure to security_admin;
Now I am going to create the function:
SQL> CREATE OR REPLACE FUNCTION VPD_FUNC
(schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN ‘upper(ename) = SYS_CONTEXT(”USERENV”, ”CURRENT_USER”) OR upper(job) =
SYS_CONTEXT(”USERENV”, ”CURRENT_USER”)’;
END;
/

 

4

Then as a sys user in the pluggable database PDB_ORIGIN configure the policy:
SQL> begin
sys.dbms_rls.add_policy(
object_schema=>’SCOTT’,
object_name=>’EMP’,
policy_name=>’VPD1_POLICY’,
function_schema=>’security_admin’,
policy_function=>’VPD_FUNC’,
statement_types => ‘SELECT’);
end;
/
Connecting as account “manager” I can see data related to “manager” records:

5

Same to “CLERK

6

This is a very nice security feature that many organizations can use to restrict  access to data within the table itself.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ORA-06512: at “CTXSYS.TEXTINDEXMETHODS”, line 366

while creating a domain index as a SYS user for a table…..the following error was thrown:

ERROR at line 1:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-20000: Oracle Text error:

DRG-50857: oracle error in drvxtab.create_index_tables

ORA-01031: insufficient privileges

ORA-06512: at “CTXSYS.DRUE”, line 160

ORA-06512: at “CTXSYS.TEXTINDEXMETHODS”, line 366

 

Solution:

Grant create table to the schema account user of the table.

ORA-27211: Failed to load Media Management Library

while configuring RMAN for a new database, the following errors were thrown while running the backup:

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 10/09/2017 15:14:51

ORA-19554: error allocating device, device type: SBT_TAPE, device name:

ORA-27211: Failed to load Media Management Library

Additional information: 2

 

SOLUTION:

cd $ORACLE_HOME\lib

relink all

ln -s /usr/openv/netbackup/bin/libobk.so64 libobk.so

 

Oracle Database Control File

An Oracle Database Control file contains crucial information such as:

  • The database name
  • The timestamp of database creation
  • The names and locations of associated datafiles and redo log files
  • Tablespace information
  • Datafile offline ranges
  • The log history
  • Archived log information
  • Backup set and backup piece information
  • Backup datafile and redo log information
  • Datafile copy information
  • The current log sequence number
  • Checkpoint information

 

You can’t start the database to “MOUNT” state unless a control file exists.

Parameter CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused, the default value is 7.

An Auto-Backup control file can be set as follows:

RMAN> SHOW CONTROLFILE AUTOBACKUP;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Autobackup will be triggered whenever a structural changes takes place such as adding new data file, dropping data file, renaming data file. Of course, a control backup will be included with full database backup with datafile 1 that belongs to system tablespace.

 

How to Restore database control file when it’s damaged or removed by mistake:

 

METHOD NO.1: re-creating the control file:

  1. Execute the below SQL statement to have a copy definition of the control file

SQL> alter database backup controlfile to trace as ‘C:\app\emodb\diag\rdbms\hero\hero\trace\cntl_aug5_backup.trc’;

  1. Gather information:

 

SQL>  spool gather_info

SQL>  SELECT MEMBER FROM V$LOGFILE;

SQL>  SELECT NAME FROM V$DATAFILE;

SQL>  SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘control_files’;

SQL>  spool off

 

  1. Shutdown the database: SQL> shutdown immediate;

 

  1.  SQL>STARTUP NOMOUNT

 

  1. Create a new control file for the database using the CREATE CONTROLFILE statement, When creating a new control file (from trace file), specify the RESETLOGS clause if you have lost any redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs. You must specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.

 

 

  1. If you are creating the control file as part of recovery, recover the database. If the new control file was created using the NORESETLOGS clause you can recover the database with complete, closed database recovery.

 

  1. If you did not perform recovery, or you performed complete, closed database recovery in step open the database normally.

SQL> ALTER DATABASE OPEN;

 

 

METHOD NO.2 : RMAN

 

RMAN> list backup of controlfile;

RMAN>  SHOW CONTROLFILE AUTOBACKUP;

 

RMAN> restore controlfile from autobackup;

OR

RMAN> restore controlfile;

OR

 

Restore the controlfile from a specific autobackup file to a temporary disk location the replicate the temp controlfile to the respective locations and names given in control_files.

 RMAN> restore controlfile from ‘/tmp/c-1140771490-2008050203’ to ‘/tmp/control.tmp’;

 RMAN> replicate controlfile from ‘/tmp/control.tmp’;

Oracle ERROR ORA-28221: REPLACE not specified

I am going to simulate a scenario when an account owner would like to change the password of his account by himself.

—– as sys user or user with “DBA” role i will create an account “dropme”:

SQL> create user dropme identified by drop23_k23;

SQL> grant create session to dropme;

exit

—– i will now connect using the account “dropme” against DB1 database:

sqlplus dropme/drop23_k23@DB1

SQL> alter user dropme identified by kitk38_x9 ;

an error ORA-28221 is thrown !!!

ORA ERROR

To fix that you need to specify the old password:

SQL> alter user dropme identified by kitk38_x9 REPLACE drop23_k23;

Unfortunately some third party applications (from my experience) , the “REPLACE” command clause won’t be executed through the application (password change will be through the application) ……. How to solve that in this case ??

1. You can either grant the account “alter user” permission temporarily:

SQL> grant alter user to dropme;

OR

2. Remove the password verify function  (set it to NULL) from the profile…the user is assigned to:

SQL> ALTER PROFILE “DEFAULT” LIMIT

  SESSIONS_PER_USER UNLIMITED

  CPU_PER_SESSION UNLIMITED

  CPU_PER_CALL UNLIMITED

  CONNECT_TIME UNLIMITED

  IDLE_TIME UNLIMITED

  LOGICAL_READS_PER_SESSION UNLIMITED

  LOGICAL_READS_PER_CALL UNLIMITED

  COMPOSITE_LIMIT UNLIMITED

  PRIVATE_SGA UNLIMITED

  FAILED_LOGIN_ATTEMPTS 3

  PASSWORD_LIFE_TIME UNLIMITED

  PASSWORD_REUSE_TIME UNLIMITED

  PASSWORD_REUSE_MAX UNLIMITED

  PASSWORD_LOCK_TIME UNLIMITED

  PASSWORD_GRACE_TIME 5

  PASSWORD_VERIFY_FUNCTION NULL;

 

 

 

 

 

 

 

 

 

 

 

Applying PSU results ORA-22308: operation not allowed on evolved type

While applying Oracle PSU patches on 12cR1 database and checking the view dba_registry_sqlpatch the STATUS was “WITH ERRORS”.

 

In order to fix this you need to check the log file for patch (you can find the location for the log file  from dba_registry_sqlpatch OR using DBMS_QOPATCH package) , in my case the error was referring to database vault type:

 

create or replace type dvsys.ku$_dv_realm_member_t as object
*
ERROR at line 1:
ORA-22308: operation not allowed on evolved type

 

to fix this:

 

SQL> drop type dvsys.ku$_dv_realm_member_t validate;

 

Then,

 

cd $ORACLE_HOME/OPatch

 

./datapatch -verbose

Managing RMAN Configuration using DBMS_BACKUP_RESTORE Package

In this blog article, I will illustrate the ability to change RMAN configuration through SQL using DBMS_BACKUP_RESTORE package. This package is not well known and is not documented.

 

Traditionally we use RMAN interface utility as shown:

rman target /

RMAN> show all;

rman1

Let us now explore changing RMAN configuration through the package:

SQL> VARIABLE rman_config NUMBER;

SQL> EXECUTE :rman_config := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(‘BACKUP OPTIMIZATION’,’ON’);

rman2

Checking through the RMAN interface utility, we can see that the configuration change is reflected successfully:

rman3

 

What are the benefits?

This will help you with the provisioning and automation of your new databases to have identical RMAN configuration. Also, this will enable you to propagate RMAN configuration changes to all of your landscape.