Oracle 11g Logo

How to De-configure Enterprise Manager Database Control Manually with Enterprise Manager Configuration Assistant

Following is the only step by step approach to drop EM repos.

Before that:

  1. Ensure environment variable properly set.
  2. Ensure your listener properly configured and started.
  3. Ensure password file is created. (Optional)
  4. You need SYS,SYSMAN & DBSNMP password.
  5. Ensure your /etc/hosts file mentioned below two lines.
[oracle@prod ~]$ cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.17.146 prod.localdomain prod

Environment Variable:

export ORACLE_SID=orcl
export ORACLE_UNQNAME=orcl
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1/
export PATH=$PATH:/home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/

Instantiate following command to deconfig Database Control with the help of EMCA.

[oracle@prod bin]$ ./emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Apr 4, 2015 3:48:11 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user: 
Password for SYSMAN user: 
Password for SYSMAN user: 
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 4, 2015 3:48:25 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /home/oracle/app/oracle/cfgtoollogs/emca/orcl/emca_2015_04_04_03_48_10.log.
Apr 4, 2015 3:48:25 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Apr 4, 2015 3:48:31 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Apr 4, 2015 3:50:13 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 4, 2015 3:50:17 AM

Cheers!! Database Control de-configure successfully.

Stay Tune. ūüôā

Oracle 11g Logo

Oracle Enterprise Manager failed to start, OC4J Configuration issue. Configure Enterprise Manager Database Control Manually with Enterprise Manager Configuration Assistant

OC4J Configuration issue… OC4J_DBConsole_ not found.

I am trying to start EM on my newly created database, while starting EM, getting failed with OC4J Configuration issue… while diagnosed there is no OC4J_DBConsole_prod directory exist in ORACLE_HOME. Means this may be the case where Database Control not configured while database created OR misconfigured.

Error log

[oracle@prod ~]$ emctl start dbconsole
OC4J Configuration issue. /home/oracle/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_prod.localdomain_orcl not found.

Solution is to Configure EM with the help of following “Enterprise Manager Configuration Assistant” (i.e. emca) utility from Oracle user: Before that:

  1. Ensure environment variable properly set like below.
  2. Ensure your listener properly configured and started.
  3. Ensure password file is created. (Optional)
  4. You need SYS,SYSMAN & DBSNMP password.
  5. Ensure your /etc/hosts file mentioned below two lines.
[oracle@prod ~]$ cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.17.146 prod.localdomain prod

Environment Variable:

[oracle@prod ~]$ export ORACLE_SID=orcl
[oracle@prod ~]$ export ORACLE_UNQNAME=orcl
[oracle@prod ~]$ export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1/
[oracle@prod ~]$ export PATH=$PATH:/home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/

Config database control repos:

[oracle@prod ~]$ emca -config dbcontrol db -repos create
STARTED EMCA at Apr 4, 2015 4:02:34 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:

Provide relevant information like below, orcl is my instance and 1521 is default listener port:

Database SID: orcl
Listener port number: 1521
Listener ORACLE_HOME [ /home/oracle/app/oracle/product/11.2.0/dbhome_1 ]: 
Password for SYS user: 
Password for DBSNMP user: 
Password for SYSMAN user: 
Email address for notifications (optional): 
Outgoing Mail (SMTP) server for notifications (optional): 
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /home/oracle/app/oracle/product/11.2.0/dbhome_1
Local hostname ................ prod.localdomain
Listener ORACLE_HOME ................ /home/oracle/app/oracle/product/11.2.0/dbhome_1
Listener port number ................ 1521
Database SID ................ orcl
Email address for notifications ............... 
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
....
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 4, 2015 4:10:55 AM

To access your Enterprise Manager, Browse URL: https://localhost.localdomain:5500/em, like below and Confirm security Exception.

Enterprise Manager URL - Add Exception to browser

Enterprise Manager URL – Add Exception to browser

 

Enterprise Manager URL - Confirm Security Exception

Enterprise Manager URL – Confirm Security Exception

 

Provide Login credentials of SYS users:

Enterprise Manager Login

Enterprise Manager Login

 

Enterprise Manager Instance Dashbord for ORCL, Now you are free to continue your work.

Enterprise Manager Instance Dashbord

Enterprise Manager Instance Dashbord

Cheers!! EM configured successfully.

Stay Tune. ūüôā

PostgreSQL Logo

ANALYZE – PostgreSQL Database Maintenance

ANALYZE collects statistics about the database, i.e. contents of the tables and updates all statistics to pg_statistic system catalog.
Resulting Query planner uses pg_statistic system catalog to find efficient query execution plan.
ANALYZE acquiring only READ LOCK on target table, so that PostgreSQL can run parallel on same table.

Syntax:

ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]

Parameters:

Plain ANALYZE: Examines all tables in database.

ANALYZE table_name: Examines only provided table. In case of provides list of columns than ANALYZE only examines provided columns.

VERBOSE: Provides progress messages of the ANALYZE.

AutoVacuum daemon is take care of automatic analyzing of tables when they are first loaded to database, and throughout regular change in operations. When AutoVacuum is not configured than its recommended activity to perform ANALYZE as database maintenance task.

To simulate environment, Consider following examples on ANALYZE:

Ex 1 : Plain ANALYZE on Postgres default database.

Plain ANALYZE

Plain ANALYZE

 

Ex 2 : ANALYZE with VERBOSE on Postgres default database.

ANALYZE with VERBOSE

ANALYZE with VERBOSE

 

Ex 3 : ANALYZE specific table with VERBOSE on Postgres default database.

ANALYZE specific table with VERBOSE

ANALYZE specific table with VERBOSE

Note: Table demo is created for only to demonstrate above ANALYZE execution, No rows inserted into it. Thats why message tab shows 0 live rows and 0 dead rows.

Cheers!!

PostgreSQL Logo

VACUUM – PostgreSQL Database Maintenance

VACUUM Performs key role in PostgreSQL database maintenance task for performance gain.

Key Benefits to perform VACUUM periodically is to Reclaim storage space occupied by dead tuples. When delete or obsolete performed, tuples are not remove physically i.e. dead tuples. With VACUUM we can reclaim that occupied space.

Syntax:

VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ table [ (column [, ...] ) ] ]

Plain VACUUM:

  • VACUUM process all tables.
  • Reclaim and make space available for reuse.
  • No exclusive lock on table, operates in parallel with normal reading and writing.
  • Reclaimed extra space reused for the table instead of return back to OS.(in most cases)
  • Recommended on daily basis.

FULL:

  • Exclusive lock on table while VACUUM.
  • Rewrites whole table into a new disk file with no extra space.
  • Reclaimed extra space return back to OS.
  • Slower than Plain VACUUM.
  • Recommended weekly not for daily used,

FREEZE:

  • Selects aggressive “freezing” of tuples.

VERBOSE:

  • Provides progress messages of the VACUUM.

ANALYZE:

  • Enhance execution of query by determining most efficient way by updating statistics used by planner.

Notes:

  • User need to be table owner or Super user to perform VACUUM.
  • After large amount of DML operations, It’s highly recommended to perform VACUUM ANALYZE in order to update system catalogs, It allows query planner to determine best future plan for query execution.
  • If large amount of update OR delete transactions suppose to perform on table, It’s good idea to FULL VACUUM that table immediately for performance point of view.
  • VACUUM increases number of I/O during activity, which may be culprit for poor DB performance for the session, So you can use Cost-based Vacuum Delay feature.

Examples:

Ex 1: Plain VACUUM on PostgreSQL default database.

Plain VACUUM

Plain VACUUM

 

Ex 2 : Plain VACUUM with Verbose on PostgreSQL default database.

Plain Vacuum with Verbose

Plain Vacuum with Verbose

 

Ex 3 : FULL VACUUM on PostgreSQL default database.

VACUUM Full

VACUUM Full

 

Ex 4 : FULL VACUUM with Verbose on PostgreSQL default database.

Vacuum Full with Verbose

Vacuum Full with Verbose

 

Ex 5 : FULL VACUUM with ANALYZE and Verbose on PostgreSQL default database.

Vacuum Full with Analyze and Verbose

Vacuum Full with Analyze and Verbose

 

Ex 6 : VACUUM on specific table with ANALYZE and verbose, In my case demo table is created to simulate example only.

VACUUM Table

VACUUM Table

 

This is how the vacuum is beneficial for postgreSQL database maintenance activity.

Oracle 11g Logo

How to relocate/move oracle datafiles in Oracle 11g

We can move/relocate oracle datafile to different location, In this article, we will move datafile with the help of Recovery Manager.

Consider step by step approach to move oracle datafile as below:

Step I:

Get database datafile ID those you wanted to move with the help of REPORT SCHEMA rman command as below:

 RMAN> REPORT SCHEMA;
 Report of database schema for database with db_unique_name PROD
 List of Permanent Datafiles
 ===========================
 File Size(MB) Tablespace RB segs Datafile Name
 ---- -------- -------------------- ------- ------------------------
 1 8880 SYSTEM YES /DATA1/database/prod/system01.dbf
 2 2710 SYSAUX NO /DATA1/database/prod/sysaux01.dbf
 3 17255 UNDOTBS1 YES /DATA1/database/prod/undotbs01.dbf
 4 25 USERS NO /DATA1/database/prod/users01.dbf
 5 6444 PROD_DATA NO /DATA1/database/prod/prodapp_data.dbf
 6 5634 PROD_INDEX NO /DATA1/database/prod/prodapp_index.dbf
 7 32744 PROD_LOB NO /DATA1/database/prod/prodapp_lob.dbf
 List of Temporary Files
 =======================
 File Size(MB) Tablespace Maxsize(MB) Tempfile Name
 ---- -------- -------------------- ----------- --------------------
 1 500 TEMP_NEW 32767 /DATA/database/prod/temp_01.dbf

Note: To simulate scenario, I am going to move PROD_LOB tablespace( i.e. FILE ID:7 ) from ‘/DATA1/database/prod/’ to new location ‘/NewData/prodDB/df/’.

Step II:

Offline tablespace that you want to move.

 RMAN> SQL 'ALTER TABLESPACE PROD_LOB OFFLINE';

Step III:

Copy all the datafiles to destination, In our case PROD_LOB tablespace having only one datafile.

 RMAN> COPY DATAFILE 7 TO '/NewData/prodDB/df/prodapp_lob.dbf';

Step IV:

Switch back to the new datafile copy(s)

 RMAN> SWITCH DATAFILE 7 TO COPY;

Step V:

Bring offline Tablespace to online state with the help of SQL command: ‘ALTER TABLESPACE’.

 RMAN> SQL 'ALTER TABLESPACE PROD_LOB ONLINE';

Step VI:

Ensure above changes with the help of REPORT SCHEMA rman command as below:
 RMAN> REPORT SCHEMA;
 ...
 File Size(MB) Tablespace RB segs Datafile Name
 ---- -------- -------------------- ------- ------------------------
 ...
 7 32744 PROD_LOB NO /NewData/prodDB/df/prodapp_lob.dbf

Step VII:

If Tablespace get back to online state successfuly, than old datafile is useless. Remove it.

 RMAN> HOST 'rm /DATA1/database/prod/prodapp_lob.dbf';

Cheers!! Datafile relocated successfully.

Related Article

Online Move Datafiles in Oracle 12c R1 without getting file offline

 

Oracle 12c Logo

Creating Partial Redaction Policy – Data Redaction Part-IV

Here we are going to discuss Partial Redaction Policy with following points where as in my earlier article we have discussed Full Data Redaction Policy.

  1. Create Partial Redaction Policy
  2. Syntax
  3. Hands-on on Partial Redaction Policy using fixed character short-cut.
  4. Hands-on on Partial Redaction Policy using character data type.
  5. Hands-on on Partial Redaction Policy using number data type.

Lets consider one by one as below:

Create Partial Redaction Policy

Partial Redaction Policy is about Portion of the data would be redacted. For Ex: In your bank monthly statement email, Your account number might redacted in order to read only last 4 digit and rest might replace with ‘*’ or Big DOT.

Note: We can only redact column with character, number, or date-time data types.

Syntax of Creating Partial Redaction Policy

 DBMS_REDACT.ADD_POLICY (
 object_schema IN VARCHAR2 := NULL,
 object_name IN VARCHAR2,
 column_name IN VARCHAR2 := NULL,
 policy_name IN VARCHAR2,
 function_type IN BINARY_INTEGER := NULL,
 function_parameters IN VARCHAR2 := NULL,
 expression IN VARCHAR2,
 enable IN BOOLEAN := TRUE);

function_type: In order to create Partial Redaction, we use DBMS_REDACT.PARTIAL function type.

Hands-on on Partial Redaction Policy using fixed character short-cuts

We can create partial redaction policy with the help of DBMS_REDACT.ADD_POLICY with predefined fixed character short-cut function parameters.

Some function_parameters parameter shortcuts are as below:

DBMS_REDACT.REDACT_US_SSN_F5: Redact first 5 numbers of social security number. Datatype: VARCHAR2. Ex: 546-76-3245 becomes XXX-XX-3245.
DBMS_REDACT.REDACT_US_SSN_L4: Redact last 4 numbers of social security number. Datatype: VARCHAR2. Ex: 546-76-3245 becomes 546-76-XXXX.
DBMS_REDACT.REDACT_US_SSN_ENTIRE: Redact entire social security number. Datatype: VARCHAR2. Ex: 546-76-3245 becomes XXX-XX-XXXX.
DBMS_REDACT.REDACT_NUM_US_SSN_F5: Redact first 5 numbers of social security number. Datatype: NUMBER. Ex: 546763245 becomes XXXXX3245.
DBMS_REDACT.REDACT_NUM_US_SSN_L4: Redact last 4 numbers of social security number. Datatype: NUMBER. Ex: 54676XXXX.
DBMS_REDACT.REDACT_NUM_US_SSN_ENTIRE: Redact entire social security number. Datatype: NUMBER. Ex: 546763245 becomes XXXXXXXXX.
DBMS_REDACT.REDACT_ZIP_CODE: Redact entire 5 digit postal code. Datatype: VARCHAR2. Ex: 96745 becomes XXXXX.
DBMS_REDACT.REDACT_NUM_ZIP_CODE: Redact entire 5 digit postal code. Datatype: NUMBER. Ex: 96745 becomes XXXXX.
DBMS_REDACT.REDACT_DATE_MILLENNIUM: Redacts dates.(DD-MON-YY) becomes 01-JAN-00 (January 1, 2000)
DBMS_REDACT.REDACT_DATE_EPOCH: Redacts all dates to 01-JAN-70.
DBMS_REDACT.REDACT_CCN16_F12: Redact first 12 digit of 16 digit credit card number. 1234-5678-9101-1213 becomes ****-****-****-1213.

To simulate scenario, consider following hands-on on redaction of US social security number upto first 5 number with the help of fixed character short-cuts.

Create table EMPSSN with id,name and ssn column as character datatype as below:

SQL> create table EMPSSN(
 id number(3),
 name varchar2(10),
 ssn varchar2(11));

Insert values accordingly:

 SQL> insert into EMPSSN values(1,'xyz','456-54-3456');
 SQL> insert into EMPSSN values(2,'pqr','955-23-3600');
 SQL> insert into EMPSSN values(3,'abc','652-52-4958');
 SQL> commit;

Query SSN column from EMPSSN table:

 SQL> select * from EMPSSN;
 ID         NAME       SSN
 ---------- ---------- -----------
 1          xyz        456-54-3456
 2          pqr        955-23-3600
 3          abc        652-52-4958

Create partial redacted policy with DBMS_REDACT.REDACT_US_SSN_F5 character short-cut as below:

 BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'EMPSSN',
 column_name => 'ssn',
 policy_name => 'scott_empssn_ssn',
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
 expression => '1=1');
 END;
 /

After creating above policy, query SSN column from EMPSSN, you will find redacted Social security number with last 4 digit visible.

 SQL> select * from EMPSSN;
 ID         NAME       SSN
 ---------- ---------- -----------
 1          xyz        XXX-XX-3456
 2          pqr        XXX-XX-3600
 3          abc        XXX-XX-4958

This is about Partial Redaction Policy using fixed character short-cuts

 

Hands-on on Partial Redaction Policy using character data type

To simulate scenario create EMPSSN_CHAR table with id, name and ssn column with datatype: varchar2.

 SQL> create table EMPSSN_CHAR(
 id number(3),
 name varchar2(10),
 ssn varchar2(11));

Insert values accordingly:

 SQL> insert into EMPSSN_CHAR values(1,'xyz','456-54-3456');
 SQL> insert into EMPSSN_CHAR values(2,'pqr','955-23-3600');
 SQL> insert into EMPSSN_CHAR values(3,'abc','652-52-4958');
 SQL> commit;

Query SSN column from EMPSSN_CHAR as below:

 SQL> select * from EMPSSN_CHAR;
 ID         NAME       SSN
 ---------- ---------- -----------
 1          xyz        456-54-3456
 2          pqr        955-23-3600
 3          abc        652-52-4958

Now, create partial redaction policy function_type: DBMS_REDACT.PARTIAL

 BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'EMPSSN_CHAR',
 column_name => 'ssn',
 policy_name => 'c##scott_EMPSSN_CHAR_ssn',
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => 'VVVFVVFVVVV,VVV-VV-VVVV,*,1,5',
 expression => '1=1');
 END;
 /

Function parameter gives idea about formatting of redaction output.
1st Parameter: Character ‘V’ can be potentially redacted. And character ‘F’ can be use for formatting character like hyphens or blank spaces.
2nd Parameter: asterisk(*) use to mask character, specifies the character to be used for the redaction.
3rd Parameter: Starting digit position: In our case: 1
4th Parameter: Ending digit position: In our case: 5

After creating above policy, query SSN column from EMPSSN_CHAR table, you will find Social Security Number redacted upto first 5 characters and visible last 4 characters, as below:

SQL> select * from EMPSSN_CHAR;
 ID         NAME       SSN
 ---------- ---------- -----------
 1          xyz        ***-**-3456
 2          pqr        ***-**-3600
 3          abc        ***-**-4958

This is about Partial Redaction Policy using character data type.

 

Hands-on on Partial Redaction Policy using number data type

To simulate scenario create EMPSSN_NUM table with id, name and ssn column with datatype: number as below:

 SQL> create table EMPSSN_NUM(
 id number(3),
 name varchar2(10),
 ssn number(9));

Query SSN column from EMPSSN_NUM table as below:

 SQL> select * from EMPSSN_NUM;
 ID NAME SSN
 ---------- ---------- ----------
 1 xyz 456543456
 2 pqr 955233600
 3 abc 652524958

Insert values accordingly to above created table:

 SQL> insert into EMPSSN_NUM values(1,'xyz',456543456);
 SQL> insert into EMPSSN_NUM values(2,'pqr',955233600);
 SQL> insert into EMPSSN_NUM values(3,'abc',652524958);
 SQL> commit;

Now, create partial redaction policy function_type: DBMS_REDACT.PARTIAL

 BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'EMPSSN_NUM',
 column_name => 'ssn',
 policy_name => 'C##SCOTT_EMPSSN_NUM_ssn',
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => '0,1,5',
 expression => '1=1');
 END;
 /

Function parameter gives idea about formatting of redaction output.
1st parameter: Mask character.
2nd parameter: Starting digit position.
3rd parameter: Ending digit position.

Query SSN column from EMPSSN_NUM table as below, you will find first 5 character of SSN column has been redacted with zero ‘0’.

SQL> select * from EMPSSN_NUM;
 ID         NAME       SSN
 ---------- ---------- ----------
 1          xyz             3456
 2          pqr             3600
 3          abc             4958

This is about Partial Redaction Policy using number data type.

Stay Tune. ūüôā

Oracle 12c Logo

Full Data Redaction Policy – Data Redaction Part-III

Full Data Redaction can redact entire column data. Redacted value returned to the querying application depends on the data type of the column. Ex: Character data type would redact to single space or NUMBER data type would redact zero.

Here we are going to discuss following topics where as in my earlier article we covered Configuring Data Redaction Policies.

  • Creating full data redaction policy.
  • Syntax
  • Examples of full data redaction policies.
    • Example based on Number data type.
    • Example based on Character data type.
  • Altering the Default Full Data Redaction Value.
  • Hands-on on altering the Default Full Data Redaction Value.

Lets start one by one:

Creating full data redaction policy

To create data redaction policy with full redaction, you need to set unction_type parameter to DBMS_REDACT.FULL in DBMS_REDACT.ADD_POLICY syntax. By default, Number would be redacted by zero (0) and character would be redacted by single space. Default values can be modify with the help of DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure.

Syntax for creating Full Data Redaction policy:

DBMS_REDACT.ADD_POLICY (
 object_schema IN VARCHAR2 := NULL, 
 object_name IN VARCHAR2,
 column_name IN VARCHAR2 := NULL,
 policy_name IN VARCHAR2,
 function_type IN BINARY_INTEGER := NULL,
 expression IN VARCHAR2,
 enable IN BOOLEAN := TRUE);

Examples of full data redaction policies

Example 1:

Let me demonstrate full data redaction for all the values of MOBILE column from C##SCOTT.EMP table. When any user will access the MOBILE column, expression parameter would apply policy except users who have been granted with EXEMPT REDACTION POLICY system privilege.

Let me connect to C##SCOTT user and create EMP table to simulate environment.

SQL> show user
USER is "C##SCOTT"
create table EMP
(
empid number(5),
ename varchar2(30),
mobile number(11)
);
Table created.

Insert below 5 records to EMP table:

SQL> insert into EMP values(1,'scott',1234567898);
SQL> insert into EMP values(2,'system',1234567898);
SQL> insert into EMP values(3,'sys',1234567898);
SQL> insert into EMP values(4,'abc',1234567898);
SQL> insert into EMP values(5,'xyz',1234567898);
SQL> commit;

Query EMP table:

SQL> select * from emp;
EMPID     ENAME                          MOBILE
---------- ------------------------------ ----------
 1         scott                          1234567898
 2         system                         1234567898
 3         sys                            1234567898
 4         abc                            1234567898
 5         xyz                            1234567898

Create full data redaction policy with name: ‘full_redact_mobile’ on MOBILE column of EMP table as below:

SQL> BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'EMP',
 column_name => 'MOBILE',
 policy_name => 'full_redact_mobile',
 function_type => DBMS_REDACT.FULL,
 expression => '1=1');
END;
/
 function_type => DBMS_REDACT.FULL,
                  *
ERROR at line 7:
ORA-06550: line 7, column 24:
PLS-00201: identifier 'DBMS_REDACT' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored

Above full data redaction policy creation failed with “PLS-00201: identifier ‘DBMS_REDACT’ must be declared” Because we are forgotten to grant DBMS_REDACT procedure execute permission to C##SCOTT user.

Grant EXECUTE permission to C##SCOTT user as below:

SQL> grant execute on DBMS_REDACT to C##SCOTT;
Grant succeeded.

Policy created successfully after assigning execute privilege:

 SQL> BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'EMP',
 column_name => 'MOBILE',
 policy_name => 'full_redact_mobile',
 function_type => DBMS_REDACT.FULL,
 expression => '1=1');
END;
/
PL/SQL procedure successfully completed.

Now query EMP table and you will find redacted MOBILE column as zero (default value)

SQL> select * from emp;
EMPID     ENAME                          MOBILE
---------- ------------------------------ ----------
 1         scott                          0
 2         system                         0
 3         sys                            0
 4         abc                            0
 5         xyz                            0

This is about full data redaction in terms of NUMBER.

 

Example 2:

Let me demonstrate full data redaction for all the values of ENAME column from C##SCOTT.NEW_EMP table.
Note: We can’t consider same EMP table for character data redaction because according to data redaction rules, only one redaction policy can be configure per table.

Creating NEW_EMP table as below:

create table NEW_EMP
(
empid number(5),
ename varchar2(30),
mobile number(11)
);

Insert below values to NEW_EMP as below:

SQL> insert into NEW_EMP values(1,'test',1234567898);
SQL> insert into NEW_EMP values(2,'test',1234567898);
SQL> insert into NEW_EMP values(3,'test',1234567898);
SQL> commit;

Query NEW_EMP table:

 SQL> select * from new_emp;
 EMPID     ENAME                          MOBILE
---------- ------------------------------ ----------
 1         test                           1234567898
 2         test                           1234567898
 3         test                           1234567898

Create full data redaction policy with name:’full_redact_ename’ on ENAME column on newly created table. i.e. NEW_EMP:

 SQL> BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'NEW_EMP',
 column_name => 'ENAME',
 policy_name => 'full_redact_ename',
 function_type => DBMS_REDACT.FULL,
 expression => '1=1');
 END;
 /

Query NEW_EMP table and you will find ENAME column is redacted with single blank space (default) as below:

 SQL> select * from new_emp;
 EMPID     ENAME                          MOBILE
---------- ------------------------------ ----------
 1                                        1234567898
 2                                        1234567898
 3                                        1234567898

 

Altering the Default Full Data Redaction Value.

To alter, Use DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure to modify default value. for Ex: zero is the default redacted value in case of full redaction for number data type. In case of modification of this value we need to run DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure. This modification is applicable to every data redaction policy within instance.

Note: Database bounce would require to take effect of changed value.

Find current default value by querying REDACTION_VALUES_FOR_TYPE_FULL data dictionary view. With this data dictionary view you can find out current default values of NUMBER_VALUE, BINARY_FLOAT_VALUE, BINARY_DOUBLE_VALUE, CHAR_VALUE, VARCHAR_VALUE, NCHAR_VALUE, NVARCHAR_VALUE, DATE_VALUE, TIMESTAMP_VALUE, TIMESTAMP_WITH_TIME_ZONE_VALUE, BLOB_VALUE, CLOB_VALUE and NCLOB_VALUE data type.

Lets consider hands-on to change full data redaction default value for number data type, i.e. zero (0)

Connect as sysdba and query REDACTION_VALUES_FOR_TYPE_FULL data dictionary view for default full data redacted value for NUMBER data type as below:

[oracle@OL712c ~]$ sqlplus / as sysdba
SQL> select NUMBER_VALUE from REDACTION_VALUES_FOR_TYPE_FULL;
NUMBER_VALUE
------------
 0

Execute ‘UPDATE_FULL_REDACTION_VALUES’ of ‘DBMS_REDACT’ procedure in order to change default value to three (3) as below:

SQL> EXEC DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES (number_val => 3);
PL/SQL procedure successfully completed.

Likewise you can modify following values:

binfloat_val : BINARY_FLOAT data type.
bindouble_val : BINARY_DOUBLE data type.
char_val : CHAR data type.
varchar_val : VARCHAR2 data type.
nchar_val : NCHAR data type.
nvarchar_val : NVARCHAR2 data type.
date_val : DATE data type.
ts_val : TIMESTAMP data type.
tswtz_val : TIMESTAMP WITH TIME ZONE data type.
blob_val : BLOB data type.
clob_val : CLOB data type.
nclob : NCLOB data type.

Again query REDACTION_VALUES_FOR_TYPE_FULL data dictionary view and default data redaction value for NUMBER has changed to three (3).

SQL> select NUMBER_VALUE from REDACTION_VALUES_FOR_TYPE_FULL;
NUMBER_VALUE
------------
 3

Bounce back your instance in order to reflect change in your environment:

SQL> shutdown immediate;
SQL> startup;
SQL> exit;

Connect to C##SCOTT user and query EMP table, you will find data redacted value for NUMBER data type to three (3).

[oracle@OL712c ~]$ sqlplus c##scott/tiger
SQL> select * from emp;
 EMPID     ENAME                          MOBILE
---------- ------------------------------ ----------
 1         scott                          3
 2         system                         3
 3         sys                            3
 4         abc                            3
 5         xyz                            3

This is about Full Data Redaction Policy, Kindly stay tune with my next article about Partial Redaction Policy.

Oracle 12c Logo

ORA-01045: user C##SCOTT lacks CREATE SESSION privilege; logon denied

Oracle Error:

ORA-01045: user C##SCOTT lacks CREATE SESSION privilege; logon denied

Scenario:

Here, I was trying to access C##SCOTT user immediately after creation of it, Above mentioned oracle error itself self-explanatory. I had forgotten to grant CONNECT privilege to C##SCOTT user. After granting mentioned privileges, I was able to connect C##SCOTT user.

Error logs and solution:

SQL> create user C##SCOTT identified by tiger default tablespace users;
User created.
SQL> exit

Trying to connect C##SCOTT user:

[oracle@OL712c ~]$ sqlplus c##scott/tiger
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 17 05:13:19 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01045: user C##SCOTT lacks CREATE SESSION privilege; logon denied

Connect as sysdba and grant required privileges:

[oracle@OL712c ~]$ sqlplus / as sysdba
SQL> grant connect,resource to C##SCOTT;
Grant succeeded.
SQL> exit

Successfully connect C##SCOTT after assigning required privileges.

[oracle@OL712c ~]$ sqlplus c##scott/tiger
SQL> show user;
USER is "C##SCOTT"

It was only problem with required privilege.

Oracle 12c Logo

ORA-01950: no privileges on tablespace ‘USERS’

Oracle Error:

ORA-01950: no privileges on tablespace ‘USERS’

Scenario:

I am trying to insert values in EMP table of C##SCOTT schema, but getting filled with above mentioned oracle error, Error itself self explanatory. I have forgotten to assign quota space for C##SCOTT on USERS tablespace. After assigning quota space on USERS, everything was smooth.

Error logs and solution:

On C##SCOTT schema:

SQL> show user;
USER is "C##SCOTT"
SQL> insert into EMP values(1,'scott',1234567898);
insert into EMP values(1,'scott',1234567898)
 *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

Connect as sysdba and assign quota to C##SCOTT on USERS tablespace:

[oracle@OL712c ~]$ sqlplus / as sysdba
SQL> show user;
USER is "SYS"
SQL> alter user c##scott quota unlimited on users;
User altered.

On C##SCOTT schema again:

SQL> show user;
USER is "C##SCOTT"
SQL> insert into EMP values(1,'scott',1234567898);
1 row created.

In my case, it was problem with quota allocation for C##SCOTT schema on USERS tablspace.

Oracle 12c Logo

Configuring Data Redaction Policies – Data Redaction part-II

Oracle Data Redaction policies are responsible to redact data in column based on column data type as well as type of redaction. We can enable and disable policies according to our convenience.

In this article we going to discuss on following topics where as in my earlier article we already cover Data Redaction Introduction part.

  1. About Data Redaction Policies
  2. DBMS package used for Data Redaction and list of procedures in the package.
  3. Privilege required to execute Data Redaction
  4. Ask yourself sort of questions before planning Data Redaction Policies
  5. General syntax

Lets start one by one:

About Data Redaction Policies:

Policies can defines the conditions into which data redaction going to occurs for table/view.  Policies who defines kind of redaction to perform, How the data redaction should occur and when data redaction should take place.
Data redaction policies can be fully redact, partially redact, randomly redact and No redact for test purpose.

Policy can be defined with a policy expression which allows for different application users to be presented with either redacted data or actual data, based on whether the policy expression returns TRUE or FALSE. Redaction takes place when the boolean result of evaluating the policy expression is TRUE.

DBMS_REDACT DBMS package is used for Data Redaction and list of procedures in the package are as follows:

DBMS_REDACT.ADD_POLICY : Used to add policy to a table or view.
DBMS_REDACT.ALTER_POLICY : Used to modify already created policy.
DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES : Globally updates the full redaction value for a given data type. Instance restart will required for the same.
DBMS_REDACT.ENABLE_POLICY : Used to enable policy.
DBMS_REDACT.DISABLE_POLICY : Used to disable policy.
DBMS_REDACT.DROP_POLICY : Used to drop policy.

Privilege required to execute Data Redaction

To create data redaction policy, user must have EXECUTE permission/privilege on DBMS_REDACT PL/SQL package. No need of any privileges required to access the underlying tables or views that will be protected against policies.

Ask yourself following questions before planning Data Redaction Policies:

  1. Have you granted with EXECUTE privilege on the DBMS_REDACT PL/SQL package?
  2. Which data type of the table/view column that you want to redact?
  3. Do you use desire column in Oracle Virtual Private Database (VPD) row filtering condition? Because we cant use same column for data redaction.
  4. Which type of redaction you want to perform on table/view? (i.e. full, random, partial, regular expressions, or none)
  5. Which users to apply the Data Redaction policy to?

Note: When you create policy, it will be automatically ready to redact data.

General syntax of the DBMS_REDACT.ADD_POLICY Procedure:

DBMS_REDACT.ADD_POLICY procedure is used to create data redaction policy.

Syntax is as follows:

 DBMS_REDACT.ADD_POLICY (
 object_schema IN VARCHAR2 := NULL,
 object_name IN VARCHAR2 := NULL,
 policy_name IN VARCHAR2, 
 policy_description IN VARCHAR2 := NULL,
 column_name IN VARCHAR2 := NULL,
 column_description IN VARCHAR2 := NULL,
 function_type IN BINARY_INTEGER := DBMS_REDACT.FULL,
 function_parameters IN VARCHAR2 := NULL,
 expression IN VARCHAR2,
 enable IN BOOLEAN := TRUE,
 regexp_pattern IN VARCHAR2 := NULL,
 regexp_replace_string IN VARCHAR2 := NULL,
 regexp_position IN BINARY_INTEGER :=1,
 regexp_occurrence IN BINARY_INTEGER :=0,
 regexp_match_parameter IN VARCHAR2 := NULL);

Details of above syntax parameter:

object_schema: Schema of the object on which the policy will be applied.

object_name: Name of object on which the Data Redaction policy applies.

policy_name: Name of the policy. Should be unique through out instance. Find already created policies by querying data dictionary view: REDACTION_POLICIES.

policy_description: Purpose to create it with precise description.

column_name: Column name on which policy applies.

  • We can apply policy on multiple columns.
  • We can define only one policy on a table/view.
  • No columns are redacted by the policy, in case you don’t specify a column. (for example, by entering NULL)
  • We can’t define a policy on a column that is involved in the SQL expression of any virtual column.

column_description: Precise description of column that are going to be redacted.

function_type: defines types of data redacted policies. Note: Default function_type would be full redaction in case of we omit to specify it.

function_parameters: In case of partial redaction, parameter specifies how column redaction should appear.

expression: Specifies a Boolean SQL expression to decide how the policy is applied.

enable: If parameter set to TRUE, policy will automatically enable at the time of creation. else FALSE will be considered it to enable letter on.

Rest of all parameters: all used for regular expression to redact data, either full or partial. If regexp_pattern parameter don’t match with anything in target data than full redaction will be take place.

Kindly stay tune with my next article about Hand-on on Full Data Redaction Policy.

Oracle 12c Logo

Introduction to Data Redaction Oracle 12c feature – Data Redaction part-I

Data Redaction is oracle 12c feature, that provides ability to hide your sensitive data in real world.

Best example: While logging to your social site in front of your friends, you wouldn’t hesitate input password because you know while providing your password, It would look like *********. Right? Its nothing but part of security. You will experience the same with the Data Redaction in 12c, lets see how.

In this article, we’ll discuss on Introduction part, Use and benefits of data redaction in real world and scenario simulation to understand the topic.

Introduction:

Data Redaction enables you to change/mask/redact your real data that would be return from database queries issued by applications. Redact your real world data with the help of following types:

  1. Full Redaction:
    With this, you can redact entire column data. Redacted value returned to the querying application depends on the data type of the column. Ex: Character data type would redact to single space or NUMBER data type would redact zero.
  2. Partial Redaction:
    With this, Portion of the data would be redacted. For Ex: In your bank monthly statement email, Your account number might redacted in order to read only last 4 digit and rest might replace with ‘*’ or Big DOT.
  3. Regular Expressions:
    With this, we can redact patterns of data. For Ex: We can use regular expressions to redact land line number OR Email id, those have varying character lengths.
    Note: This type is only suitable with character data types.
  4. Random redaction:
    Each time it generates random data for each application user queries. Depending upon data type of that column.
  5. No Redaction:
    This type is available in order to test your internal operation of your already generated redacted policies, with no effect on the results fetch by application user. Useful to test policies definitions before production environment use.

When application user access data at the same time(at query execution time) oracle database redact real data and display it to user in redacted format. This feature will help you to achieve Industry rules & regulations for security purpose.

Use of Data Redaction:

Whenever you worry about your sensitive data security in order to display to nowise person. Think about data redaction. As we discussed, Redaction is nothing but masking of your real world data, Data redaction enable you to mask the data using different styles available that we discuss above.

Best real world examples:

Bank monthly statement on email OR Call center applications OR applications those are read-only.

Benefits:

Benefits in order to protect your data are as follows:

  • Various redaction methods available.
  • Best fit for those environment where data will be keep on changing.
  • Easy to create data redaction policy and mange it from central location.
  • Policies having wide variety of function conditions based on SYS_CONTEXT values.

These are about the data redaction introduction, its type and benefits, Kindly stay tune with my next article on Configuration of Data Redaction policies.

Oracle 12c Logo

Oracle 12c R1 software and database installation on Oracle Linux 7 64-bit

In this article I am going to cover installation of Oracle 12c Release-1 (i.e. 12.1.0.2.0)(64-bit) on Oracle Linux release 7 (64-bit)

Pre-requisite installation check are as follow:
Swap Size    :  Minimum 2 GB
IP Tables      :  Off
SeLinux        :  permissive

As per pre-requisite list, ensure your swap size with following Linux command, If swap memory don’t meet minimum requirement than increase it. Nice article on increasing swap size is here.

[root@OL712c ~]# free -m
 total used free shared buffers cached
Mem: 2969 2358 611 474 0 998
-/+ buffers/cache: 1359 1610
Swap: 3087 0 3087

Little more information about my environment:

[root@OL712c ~]# uname -a
Linux OL712c 3.8.13-35.3.1.el7uek.x86_64 #2 SMP Wed Jun 25 15:27:43 PDT 2014 x86_64 x86_64 x86_64 GNU/Linux
[root@OL712c ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.0 (Maipo)

Update host file entry:

192.168.17.138     OL712c

Note: OL712c is my server name with associated private IP.

Update hostname entry in “/etc/hostname” file.

OL712c

Append following lines to “/etc/sysctl.conf” file.

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

In order to reflects above changes in current Linux environment, Issue following command:

[root@localhost ~]# /sbin/sysctl -p

Append following lines to “/etc/security/limits.conf” file.

oracle   soft   nofile   1024
oracle   hard   nofile   65536
oracle   soft   nproc    2047
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768

Stop firewall:

[root@localhost ~]# systemctl stop firewalld

Disable firewall:

[root@localhost ~]# systemctl disable firewalld
rm '/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service'
rm '/etc/systemd/system/basic.target.wants/firewalld.service'

Set SeLinux Permissive by editing “/etc/selinux/config” file.

SELINUX=permissive

Note:
Take reboot in order to take effect.

Installed following packages with the help of YUM utility: Most of the packages are already installed while OS installation.

yum install binutils* compat-libstdc++* gcc* gcc-c++* glibc* glibc.i686* glibc-devel* ksh* libgcc* libstdc++* libstdc++-devel* libaio* libaio-devel* libXext* libXtst* libX11* libXau* libxcb * libXi* make* sysstat* unixODBC* unixODBC-devel* zlib-devel* -y

Create oracle groups and users:

[root@OL712c ~]# groupadd -g 54321 oinstall
[root@OL712c ~]# groupadd -g 54322 dba
[root@OL712c ~]# groupadd -g 54323 oper
[root@OL712c ~]# useradd -u 54321 -g oinstall -G dba,oper oracle
[root@OL712c ~]# passwd oracle

Create following directories for oracle home:

[root@OL712c ~]# mkdir -p /u01/app/oracle/product/12.1.0/
[root@OL712c ~]# chown -R oracle:oinstall /u01
[root@OL712c ~]# chmod -R 775 /u01

Download oracle 12c R1 from oracle site, here.

On GUI, open terminal prompt as a root user & issue the following command:

[root@OL712c ~]# xhost +
access control disabled, clients can connect from any host
[root@OL712c ~]# su - oracle
[oracle@OL712c ~]$ cd /opt/database/
[oracle@OL712c database]$ ll
total 24
drwxr-xr-x. 4 root root 4096 Nov 29 10:02 install
drwxrwxr-x. 2 root root 58 Jul 7 10:18 response
drwxr-xr-x. 2 root root 33 Jul 7 09:14 rpm
-rwxr-xr-x. 1 root root 8533 Jul 7 09:14 runInstaller
drwxrwxr-x. 2 root root 28 Jul 7 10:18 sshsetup
drwxr-xr-x. 14 root root 4096 Jul 7 10:19 stage
-rwxr-xr-x. 1 root root 500 Feb 6 2013 welcome.html

Run oracle universal installer and follow the steps by step screen shots as below:

[oracle@OL712c database]$ ./runInstaller

Un-check security updates check box and continue.

Oracle 12c on Oracle Linux 7 - Configure Security Updates

Oracle 12c on Oracle Linux 7 – Configure Security Updates

 

Select Installation Option, In my case i am creating as well as configuring database at a time:

Installation of Oracle 12c on Oracle Linux 7 - Select Installation Option

Installation of Oracle 12c on Oracle Linux 7 – Select Installation Option

 

Provide system class information, Select Server class if you are installing oracle on server, in my case I am using VM on my personal lappy.

Installation of Oracle 12c on Oracle Linux 7 - System Class

Installation of Oracle 12c on Oracle Linux 7 – System Class

 

Provide strong administrative password with pluggable database name, and continue.

Installation of Oracle 12c on Oracle Linux 7 - Typical Install Configuration

Installation of Oracle 12c on Oracle Linux 7 – Typical Install Configuration

Installation of Oracle 12c on Oracle Linux 7 - Create Inventory

Installation of Oracle 12c on Oracle Linux 7 – Create Inventory

Installation of Oracle 12c on Oracle Linux 7 - Perform Pre-requisite Check

Installation of Oracle 12c on Oracle Linux 7 – Perform Pre-requisite Check

 

All pre-requisite checks are succeeded in my case, It is recommended to satisfied all minimum requirements:

Installation of Oracle 12c on Oracle Linux 7 - Perform Pre-requisite Check

Installation of Oracle 12c on Oracle Linux 7 – Perform Pre-requisite Check

 

Ensure summary and continue with installation.

Installation of Oracle 12c on Oracle Linux 7 - summary

Installation of Oracle 12c on Oracle Linux 7 – summary

Installation of Oracle 12c on Oracle Linux 7 - Install Product

Installation of Oracle 12c on Oracle Linux 7 – Install Product

 

Execute following scripts as root users.

Installation of Oracle 12c on Oracle Linux 7 - Execute Configuration Script

Installation of Oracle 12c on Oracle Linux 7 – Execute Configuration Script

Oracle script

Oracle script

 

Oracle software installation is completed and now running database configuration assistant for database creation.

Installation of Oracle 12c on Oracle Linux 7 - Database Configuration Assistant

Installation of Oracle 12c on Oracle Linux 7 – Database Configuration Assistant

 

Unlock user by clicking password management button, or continue.

Installation of Oracle 12c on Oracle Linux 7 - configuration assistant

Installation of Oracle 12c on Oracle Linux 7 – configuration assistant

 

Oracle 12c installation on Oracle Linux 7 is successful.

Installation of Oracle 12c on Oracle Linux 7

Installation of Oracle 12c on Oracle Linux 7

 

Now, Export oracle environment variable by ‚ÄúOracel‚Ä̬†user to access sql prompt as below:

[oracle@OL712c ~]$ export ORACLE_SID=orcl
[oracle@OL712c ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1/
[oracle@OL712c ~]$ export PATH=$PATH:/u01/app/oracle/product/12.1.0/dbhome_1/bin/

Access SQL prompt as below:

[oracle@OL712c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 1 04:47:44 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 2923920 bytes
Variable Size 838861424 bytes
Database Buffers 402653184 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE

If you wish to start your databases on every reboot then make following changes in ‚Äú/etc/oratab‚ÄĚ file for all databases.

orcl:/u01/app/oracle/product/12.1.0/dbhome_1:Y

 

Congratulations!! You have successfully install oracle 12c on oracle Linux 7 64-bit OS.

 

Related article on Oracle 12c installation on Oracle Linux 5 & 6 as below:

Oracle 12c installation on Oracle Linux release-6 (64-bit)

Oracle 12c installation on Oracle Linux release-5 (64-bit)

 

System.Data.SqlClient.SqlError: The media loaded on X is formatted to support 1 media families, but 2 media families are expected according to the backup device specification

My one of the client wanted to full backup of his SQL Server database but getting failed with below mentioned error:

Error:

System.Data.SqlClient.SqlError: The media loaded on "H:\*******20141014.bak" is formatted to support 1 media families, but 2 media families are expected according to the backup device specification. (Microsoft.SqlServer.Smo)

Screen shot: You can see two backup media locations has been mentioned for full database backup.

System.Data.SqlClient.SqlError: The media loaded on X is formatted to support 1 media families, but 2 media families are expected according to the backup device specification. (Microsoft.SqlServer.Smo)

Root Cause:

When we add more than one backup location, than we are asking SQL Server to take backup on more than one backup location, means backup would be stripped/divide over the backup locations. You need both the stripped backup piece at the time of restore.

Solution:

To avoid above error, just replace your new backup location with listed backup locations. Means remove all old entry and add your new backup location. It will work.

Stay Tune. ūüôā

Oracle Linux logo

Oracle Linux 7: rpmdb open failed

While installation of oracle database 12c release 1 on oracle Linux 7, I tried installation of below mentioned packages as a part of oracle database installation pre-requisite but getting failed with the message “Another app is currently holding the yum lock;”.

[root@localhost ~]# yum install binutils* compat-libstdc++* gcc* gcc-c++* glibc* glibc.i686* glibc-devel* ksh* libgcc* libstdc++* libstdc++-devel* libaio* libaio-devel* libXext* libXtst* libX11* libXau* libxcb * libXi* make* sysstat* unixODBC* unixODBC-devel* zlib-devel* -y
Loaded plugins: langpacks
Existing lock /var/run/yum.pid: another copy is running as pid 2386.
Another app is currently holding the yum lock; waiting for it to exit...
 The other application is: PackageKit
 Memory : 63 M RSS (464 MB VSZ)
 Started: Fri Nov 28 06:31:22 2014 - 12:00 ago
 State : Running, pid: 2386
Another app is currently holding the yum lock; waiting for it to exit...
 The other application is: PackageKit
 Memory : 63 M RSS (463 MB VSZ)
 Started: Fri Nov 28 06:31:22 2014 - 12:02 ago
 State : Uninterruptible, pid: 2386

After waiting sufficient amount of time, I decided to kill precess id. ( i.e. 2386 )

[root@localhost ~]# kill -9 2386

Process ID successfully killed but again mentioned packages installation failed with new Error: rpmdb open failed.

[root@localhost ~]# yum install binutils* compat-libstdc++* gcc* gcc-c++* glibc* glibc.i686* glibc-devel* ksh* libgcc* libstdc++* libstdc++-devel* libaio* libaio-devel* libXext* libXtst* libX11* libXau* libxcb * libXi* make* sysstat* unixODBC* unixODBC-devel* zlib-devel* -y
error: rpmdb: BDB0113 Thread/process 2386/139857582520128 failed: BDB1507 Thread died in Berkeley DB library
error: db5 error(-30973) from dbenv->failchk: BDB0087 DB_RUNRECOVERY: Fatal error, run database recovery
error: cannot open Packages index using db5 - (-30973)
error: cannot open Packages database in /var/lib/rpm
CRITICAL:yum.main:
Error: rpmdb open failed

Root Cause:

I have issued wrong command at wrong time, I have forcefully killed/aborted/failed active package installation process with process id: 2386, resulting: RPM database corrupted.
Solution is simple, just need to delete and rebuild RPM database.

Solution:
Issue the following command as a root user:

Confirm that your RPM database corrupted with following RPM command, It will prompt you same error message mentioned above.

[root@localhost ~]# rpm -qa | sort

On safer side, backup your RPM database before rebuild it:

[root@localhost ~]# cp -r /var/lib/rpm /var/tmp/rpm-backup

Following remove command will help you to remove the RPM lock files:

[root@localhost ~]# rm -fr /var/lib/rpm/__db*

Rebuild RPM database with following RPM command:

[root@localhost ~]# rpm -vv --rebuilddb

Kindly confirm that your RPM database rebuild successfully with following:

[root@localhost ~]# rpm -qa | sort

Clean your yum:

[root@localhost ~]# yum clean all
Loaded plugins: langpacks
Cleaning repos: ol7_UEKR3 ol7_latest
Cleaning up everything

After following all above steps, yum have been installed all packages smoothly.

Stay Tune. ūüôā

Oracle 11g Logo

ORA-01149:cannot shutdown-file 1 has online backup set OR ORA-10873:file 1 needs to be either taken out of backup mode or media recovered

Oracle Error: While shutdown your database you may encounter following oracle error.

SQL> shutdown immediate;
ORA-01149: cannot shutdown – file 1 has online backup set
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/RTS/system01.dbf’

Cause:

If you try to shutdown database when database is in hot backup mode, you will encountered above mentioned error. Kindly verify with your team mates with hot backup activity, Oracle will not allow you to shutdown your database in above scenario, i.e. shutdown, shutdown immediate, shutdown transactional OR startup force will be failed with ORA-01149 oracle error.

Root cause detection:

Following query will help you to understand which oracle tablespace datafile is in hot backup mode.

SQL> select a.tablespace_name, b.status from dba_data_files a, v$backup b where a.file_id=b.file# order by tablespace_name;
TABLESPACE_NAME                STATUS
------------------------------ ------------------
SYSAUX                         ACTIVE
SYSTEM                         ACTIVE
UNDOTBS1                       ACTIVE
USERS                          ACTIVE

Status of the all datafile seems to be active. i.e. hot backup mode.

Solution:
Issue following SQL command in order to move out database from hot backup mode:

SQL> alter database end backup;
Database altered.

Now you can gracefully shutdown your database.

 

Cause: ORA-10873

In case you force fully trying to shutdown database with “shutdown abort” OR instance crashes for some reason OR database have not been shutdown gracefully than you would encountered “ORA-10873” error while next startup of your database.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 1040189872 bytes
Database Buffers 419430400 bytes
Redo Buffers 7958528 bytes
Database mounted.
ORA-10873: file 1 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 1: '/u01/app/oracle/oradata/RTS/system01.dbf'

Solution:

Bring out your database from hot backup mode with the help of following SQL:

SQL> alter database end backup;
Database altered.

OR, you can individually take out tablespaces from hot backup mode:

SQL> alter tablespace system end backup;
Tablespace altered.

Open database:

SQL> alter database open;
Database altered.

Your database opened in READ WRITE mode.

Stay Tune. ūüôā

Oracle 11g Logo

Oracle 11g Manual Online Hot Backup

In this article I am going to cover steps to perform online hot backup with database in open mode.

Note: It is assumed that your database already in ARCHIVELOG mode, If not than follow my one of the article to convert your database in archivelog mode.

For manual online hot backup, follow the steps mentioned below. I have simulated mentioned scenario on my test database, i.e. RTS.

Step 1>>

Verify your database is in ARCHIVELOG mode OR not.

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 88
 Next log sequence to archive 90
 Current log sequence 90
 Database is in ARCHIVELOG mode.

Step 2>>

List down the all oracle data files which you have to backup, with the help of following SQL.

 SQL> select file_name from dba_data_files;
 FILE_NAME
 --------------------------------------------------------------------------------
 /u01/app/oracle/oradata/RTS/users01.dbf
 /u01/app/oracle/oradata/RTS/undotbs01.dbf
 /u01/app/oracle/oradata/RTS/sysaux01.dbf
 /u01/app/oracle/oradata/RTS/system01.dbf

Step 3>>

We need to know current online log sequence number at this point, Plus all log sequence generated during backup. Because we need these archive logs in order to restore database.

 SQL> select group#, sequence#, status from v$log;
 GROUP# SEQUENCE# STATUS
 ---------- ---------- ----------------
 1 88 INACTIVE
 2 89 INACTIVE
 3 90 CURRENT

In my case, CURRENT log sequence number : 90, GROUP# : 3

Step 4>>

Put your database in hot backup mode with the help of following SQL:

 SQL> alter database begin backup;
 Database altered.

Step 5>>

Create backup directory in order to copy backup files to backup directory.

 [oracle@PR ~]$ mkdir -p /u01/bkup/manual_online_hot
 [oracle@PR ~]$ cd /u01/bkup/manual_online_hot
 [oracle@PR manual_online_hot]$ pwd
 /u01/bkup/manual_online_hot

Step 6>>

Copy all the files (i.e. .DBF) from the database directory “/u01/app/oracle/oradata/RTS” to backup directory “/u01/bkup/manual_online_hot”.

 [oracle@PR ~]$ cd /u01/app/oracle/oradata/RTS
 [oracle@PR RTS]$ cp * /u01/bkup/manual_online_hot

Step 7>>

Take your database out from hot backup mode:

 SQL> alter database end backup;
 Database altered.

Step 8>>

Verify current log sequence number with the help of same SQL mentioned in step-3.

 SQL> select group#, sequence#, status from v$log;
 GROUP# SEQUENCE# STATUS
 ---------- ---------- ----------------
 1 88 INACTIVE
 2 89 INACTIVE
 3 90 CURRENT

We need the earlier log file that we identified in step-3 & all log files generated during the database backup upto the current log file.

Note: In our case, As you know this is an test environment not production database. current log file before and after backup remain same, i.e. 90. But in case of production system, it may vary and generate more log files during backup.

Step 9>>

Forcefully we need to switch the logfile in order to archive current log sequence number. i.e. 90.

 SQL> alter system switch logfile;
 System altered.
 SQL> select group#, sequence#, status from v$log;
 GROUP# SEQUENCE# STATUS
 ---------- ---------- ----------------
 1 91 CURRENT
 2 89 INACTIVE
 3 90 ACTIVE

Now, Current log sequence number is 91, and log sequence number will be archive to archive log location. In my case it is FRA: Flash Recovery Area.

Step 10>>

Verify log sequence number have been archived to archive log location with the help of following SQL:

SQL> select SEQUENCE#,ARCHIVED,STATUS from v$archived_log where SEQUENCE#=90;
 SEQUENCE#  ARC S
 ---------- --- -
 90         YES A

Archive log sequence 90 has archived. Some time we need to wait for ARCH background process to complete copy the last online redo log file to the archive log directory.

Step 11>>

Now, Copy all archived logs (i.e. log sequence number noted in step-3 and all archived logs generated during backup) from archived log location (i.e. FRA) to the backup location.
In our case it was only log sequence 90.

 [oracle@PR ~]$ cd /u01/app/oracle/flash_recovery_area/RTS/archivelog/2014_11_16
 [oracle@PR 2014_11_16]$ cp o1_mf_1_90_b6jys239_.arc /u01/bkup/manual_online_hot

Cross verify all backup files are in place(i.e. datafiles and archive log file (sequence 90) @backup location in order restore database.

Congratulations!! Manual Online hot backup successfully completed.

Stay Tune. ūüôā

Oracle 11g Logo

Oracle 11g manual offline cold backup in Linux operating system

Executing offline cold backups in oracle is bit easy, Offline cold backup is nothing but bring your oracle database to shutdown and copy(backup) all database physical files to backup location manually. like data file, control file, log file, etc

Note: This article assumes you don’t use any tablespaces with ASM instance. For ASM instance, I strongly recommend¬†to kindly perform your database backup with RMAN utility. RMAN: Recovery Manager.

For manual offline backup, follow the steps mentioned below. I have simulated mentioned scenario on my test database, i.e. RTS.

Step 1>>

List down the oracle data files which you have to backup, following SQL command will help you to determine file name and its location:

SQL> select file_name from dba_data_files;
 FILE_NAME
 ----------------------------------------------------------------------
 /u01/app/oracle/oradata/RTS/users01.dbf
 /u01/app/oracle/oradata/RTS/undotbs01.dbf
 /u01/app/oracle/oradata/RTS/sysaux01.dbf
 /u01/app/oracle/oradata/RTS/system01.dbf

Step 2>>

List down all online redologs and its location with following SQL command:

 SQL> select member from v$logfile;
 MEMBER
 ----------------------------------------------------------------------
 /u01/app/oracle/oradata/RTS/redo03.log
 /u01/app/oracle/oradata/RTS/redo02.log
 /u01/app/oracle/oradata/RTS/redo01.log
 3 rows selected.

Step 3>>

List down all control files and its location by following:

SQL> select name from v$controlfile;
 NAME
 ----------------------------------------------------------------------
 /u01/app/oracle/oradata/RTS/control01.ctl
 /u01/app/oracle/flash_recovery_area/RTS/control02.ctl

We have gathered all the physical file list that we’ll need for backup.

Step 4>>

Create directory on your system, In this case I am using “manual_offline_cold” for backup location, as you know this is my test environment.
Note: It’s recommended to backup this files on Tape OR External hard drive, as per your convenience other than same server directory.

 [oracle@PR ~]$ mkdir -p /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cd /u01/bkup/manual_offline_cold
 [oracle@PR manual_offline_cold]$ pwd
 /u01/bkup/manual_offline_cold

Note: It’s recommended to backup this files on Tape OR External hard drive, as per your convenience other than same server directory.

Step 5>>

Grace fully shutdown your database with following command:

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

Step 6>>

Now copy all the physical file that you have found in step number 1,2 and 3.

 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/users01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/undotbs01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/sysaux01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/system01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/redo03.log /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/redo02.log /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/redo01.log /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/flash_recovery_area/RTS/control02.ctl /u01/bkup/manual_offline_cold

Once copy process complete, kindly verify the backup directory with all copied files.

 [oracle@PR ~]$ cd /u01/bkup/manual_offline_cold
 [oracle@PR manual_offline_cold]$ ll
 total 1376468
 -rw-r----- 1 oracle oinstall 10043392 Nov 15 23:02 control01.ctl
 -rw-r----- 1 oracle oinstall 10043392 Nov 15 23:02 control02.ctl
 -rw-r----- 1 oracle oinstall 52429312 Nov 15 23:02 redo01.log
 -rw-r----- 1 oracle oinstall 52429312 Nov 15 23:02 redo02.log
 -rw-r----- 1 oracle oinstall 52429312 Nov 15 23:01 redo03.log
 -rw-r----- 1 oracle oinstall 492838912 Nov 15 22:59 sysaux01.dbf
 -rw-r----- 1 oracle oinstall 702554112 Nov 15 23:00 system01.dbf
 -rw-r----- 1 oracle oinstall 31465472 Nov 15 22:59 undotbs01.dbf
 -rw-r----- 1 oracle oinstall 5251072 Nov 15 22:58 users01.dbf

There is no need to backup temporary datafile those are associated with temporary tablespace, because technically we don’t use to restore database.

You can compress backup files with any compression utility to manage your disk space well for log period of retention.

Step 7>>
Start your database:

 SQL> startup
 ORACLE instance started.
 Total System Global Area 1469792256 bytes
 Fixed Size 2213456 bytes
 Variable Size 1040189872 bytes
 Database Buffers 419430400 bytes
 Redo Buffers 7958528 bytes
 Database mounted.
 Database opened.

Congratulations!! Manual offline backup successfully completed.

Stay Tune. ūüôā

Oracle 11g Logo

Oracle 11g Physical standby data Guard Failover steps – Active Data Guard Part-V

Oracle 11g Physical standby data Guard Failover:

In case of worst situation with data guard primary database, or not available for production than we can activated standby database as a primary production database.

Previously, we have covered:¬†how to apply primary database redo information to standby database while standby database is in read only mode ‚Äď Active Data guard feature ‚Äď Data Guard Part-IV

Lets consider above scenario and bring standby database up as a primary database:
Verify database name its open mode and its role from following SQL command:

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

Following sort of SQL command will help to bring up standby as primary:

SQL> alter database recover managed standby database finish;
Database altered.
SQL> alter database activate standby database;
Database altered.

Managed recovery process has been stopped between primary and standby database and standby becomes primary database.

Bounce your database and verify database name its open mode and its role:

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 905972144 bytes
Database Buffers 553648128 bytes
Redo Buffers 7958528 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ WRITE PRIMARY

Note:

Now your old standby database is become primary database, it is highly recommended to consider immediate full backup of primary database.

After primary database available, you need to switch role between standby to primary database:

Stay Tune. ūüôā

Script to find out total number of oracle schema objects and its size

Following script help you to find out available schema’s along with total number of objects and its size ( in MB )

SQL> set pages 999
SQL> col "size MB" format 999,999,999
SQL> col "Objects" format 999,999,999
SQL> select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;

O/P:

Owner                          Objects      size MB
------------------------------ ------------ ------------
PRODAPP                        32,527       39,433
SYS                            30,924       5,521
SYSTEM                         604          4,468
AEX_030200                     2,406        157
XPRDB                          844          129
SYSMAN                         3,491        123
GHSYS                          1,019        29
TRXSYS                         366          19
...

Stay Tune. ūüôā

Script to find out datafiles with highest Input-Output activity

Following script will find out all datafiles OR first 5 datafiles with highest Input-Output in terms of physical reads and write on datafiles along with read/write time.

SQL> col name format a50
SQL> set linesize 200
SQL> select * from ( select name,phyrds, phywrts, readtim, writetim
from v$filestat a, v$datafile b where a.file#=b.file#
order by readtim desc) where rownum < 6;

O/P:

NAME                                    PHYRDS     PHYWRTS  READTIM   WRITETIM
--------------------------------------- ---------  -------- --------  ---------
/DATA/database/prod/prod_app_data.dbf   958846     231710   55197     12248
/DATA/database/prod/system01.dbf        1113495    69047    46798     1677
/DATA/database/prod/prod_app_lob.dbf    11226086   57018    44491     469
/DATA/database/prod/prod_app_index.dbf  389124     134121   44330     6719
/DATA/database/prod/sysaux01.dbf        396043     115628   29089     5735

Stay Tune ūüôā