Oracle 11g Logo

How to kill Oracle RMAN backup job

We can kill RMAN backup job with the help of 2 methods, as below:

Method I:
Alter system kill session:

First, get the SID and SERIAL# from below query:

SQL> select b.sid, b.serial#, a.spid, b.client_info
from v$process a, v$session b
where a.addr=b.paddr and client_info like 'rman%';
SID        SERIAL#    SPID         CLIENT_INFO
---------- ---------- ------------ ---------------------------------
592        12         865          rman channel=full_chanel

OR

SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;
SID        SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
592        12         1          9115569    19258880   47.33

Use the following command to kill RMAN backup job:

SQL> alter system kill session '592,12' immediate;
system altered.

Backup job killed successfully, simultaneously you will get below mentioned error log in RMAN backup logs:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/01/2017 00:09:15
RMAN-10038: database session for channel full_chanel terminated unexpectedly

Method II:

Directly kill RMAN job from OS level with the help of “kill -9”

[oracle@PR ~]$ ps -ef | grep rman|grep -v grep
oracle 2348 3124 3 01:28 pts/1 00:00:00 rman target /
[oracle@PR ~]$ kill -9 2348

Thanks, Stay Tune. 🙂

Oracle 11g Logo

ORA-27047: unable to read the header block of file Linux-x86_64 Error: 25: Inappropriate ioctl for device ORA-27048: skgfifi: file header information is invalid ORA-27072: File I/O error

While recovering my manual physical standby data guard configuration, Every time oracle would apply all archive logs to standby database and end up with following mentioned errors.

As a part of troubleshooting, I have Verified:

  • Archive log sequence number on both the side but that was in place.
  • Block corruptions on datafiles level on both sides. It was clean.
  • Physical storage blocks, It was also clean.
  • Almost verify everything, that i can.

After scratching my head for several hours, I found non oracle files situated on FRA. After moving it from FRA, Recovery process successfully carried out. Non oracle file was only culprit to the following mentioned errors. It was very strange case I have faced in my life.

Solution:

Remove all non oracle files from FRA.

Error logs:

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 15198931 generated at 07/23/2015 17:55:02 needed for thread 1
ORA-00289: suggestion :
/u02/oradata/flash_recovery_area/DB1/archivelog/2015_07_23/o1_mf_1_5180_bv1qlby6_.arc
ORA-00280: change 15198931 for thread 1 is in sequence #5180
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 15199107 generated at 07/23/2015 18:00:02 needed for thread 1
ORA-00289: suggestion :
/u02/oradata/flash_recovery_area/DB1/archivelog/2015_07_23/o1_mf_1_5181_bv1qvq3n_.arc
ORA-00280: change 15199107 for thread 1 is in sequence #5181
ORA-00278: log file '/u02/oradata/flash_recovery_area/DB1/archivelog/2015_07_23/o1_mf_1_5180_bv1qlby6_.arc' no longer needed for this recovery
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-27048: skgfifi: file header information is invalid
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1
Additional information: 392
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-27048: skgfifi: file header information is invalid
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1
Additional information: 392
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropri

Thanks,

Stay Tune. 🙂

Oracle 12c Logo

ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use

ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use

ORA error message itself is self explanatory, When undo tablespace is in use and we will try to drop that tablespace than oracle pop up with ORA-30013 error.

Solution:
Wait until active transactions are COMMITED or ROLLED BACK. If so, you can easily drop mentioned tablespace.
OR
You can kill that transaction if it unnecessary.

Consider following example:

SQL> create tablespace newundotbs datafile '/u01/app/oracle/oradata/prod/newundotbs1.dbf' size 200M autoextend on maxsize 500M;
Tablespace created.
SQL> alter system set UNDO_TABLESPACE=NEWUNDOTBS scope=spfile;
System altered.

Following command will failed because there is active transactions.

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Query dba_rollback_segs to know list of online segments:

SQL> select tablespace_name, owner, segment_name, status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS1' and status='ONLINE';

Retrive SID and SERIAL# from below mentioned query in order to kill session with ‘alter system kill session’ command.

SQL> select SID, substr(username,1,10) username,serial#,segment_name from v$transaction,dba_rollback_segs,v$session where saddr=ses_addr and xidusn=segment_id;
SQL> alter system kill session 'SID,SERIAL#';
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.

Tablespace successfully dropped.

Thanks
Stay Tune 🙂

Oracle 12c Logo

ORA-03206: maximum file size of XXXX blocks in AUTOEXTEND clause is out of range

If you are creating SMALLFILE datafile (default) with 8k blocks with datafile size greater than 32 GB than oracle will pop up with ORA-03206 error.

Because oracle will allow only up to 32 GB of datafile size limit with 8k blocks, maximum data blocks per datafile : 2^22 -1 = 4194303. So oracle simply won’t allow you in this case.

If you wish more than 32 GB datafile with 8k blocks than your datafile should be create with BIGFILE keyword.

Calculations are as follows:

Oracle allows max number of datafile is 65533 and Maximum data blocks per datafile is 2^22 -1 = 4194303 blocks.

Formula to calculate max datafile size

max datafile size = db_block_size * maximum number of blocks

In our case:

max datafile size = 8 * 4194303 = 33554424/1024 = 32767.9921875 MB/1024 = 31.99999237060547 GB, i.e. 32 GB

Formula to calculate Max tablespace size:

Max tablespace size = 32 GB * max database file = 32 GB * 65533 = 2097056 GB/1024 = 2047.90625 TB

Error Log:

SQL> create temporary tablespace temp02
tempfile '/u02/oradata/db1/temp2_01.dbf' size 30G autoextend on maxsize 70G; 2
create temporary tablespace temp02
*
ERROR at line 1:
ORA-03206: maximum file size of (9175040) blocks in AUTOEXTEND clause is out of range

Solution:

Create tempfile with less than 32 GB size or use BIGFILE keyword.

In above example, I am facing ORA-03206 with tempfile creation, you may face the problem with datafile too. Same solution will be applicable.

Other area’s you may get same error:

If your existing datafile exceeds limit of 32 GB, than you will receive same error message, solution is to add new SMALLFILE datafile to the associated tablespace.

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

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)

 

Oracle 12c Logo

DBVERIFY: Offline Oracle Database Verification Utility For Datafile And segment

DBVERIFY is command-line utility ( external ) that used to performs physical data structure integrity check like data files, where it verifies data blocks corruption OR Segment corruption of data files.
We can use this utility to verify database backup file integrity check, i.e. Backup files are restorable or not.
This utility also called offline database verification utility, because we can verify/perform online as well as offline database integrity check. As we can perform it offline this utility is significantly faster.

Note:
DBVERIFY utility are limited to cache-managed blocks. ( data blocks )
Only used to verify data files, NOT with control files OR redo log files. Continue reading

Oracle 12c Logo

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

Prior to Oracle 12c, moving datafiles to another location needs downtime ( Need to Offline datafiles ) but from Oracle 12c we can accomplish same with ALTER DATABASE command.

Syntax:
ALTER DATABASE MOVE DATAFILE ( ‘filename’ | ‘ASM_filename’ | file_number )
[ TO ( ‘filename’ | ‘ASM_filename’ ) ] [ REUSE ] [ KEEP ]

Note:

  • Source file would be specified with either filename, ASM_filename or file_number.
  • Destination file should be specified by filename only.
  • REUSE: New file should create even if it already exists.
  • KEEP: Original copy of the datafile should be retained.

 

// Online move datafile with the help of ALTER DATABASE command + File name.
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME=’SYSTEM’;

FILE_NAME FILE_ID TABLESPACE_NAME
—————————————————- ———- ———————–
/u01/app/oracle/oradata/ORCL/datafile/system01.dbf 1 SYSTEM

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/ORCL/datafile/system01.dbf’ TO ‘/tmp/system01.dbf’;

Database altered.

// Ensure your changes by issuing same above query, In FILE_NAME column kindly observed the location of datafile has been changed to ‘/tmp/…’
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME=’SYSTEM’;

FILE_NAME FILE_ID TABLESPACE_NAME
—————————————————- ———- ———————–
/tmp/system01.dbf 1 SYSTEM

// Online move datafile with the help of ALTER DATABASE command + File number.
SQL> ALTER DATABASE MOVE DATAFILE 1 TO ‘/u01/app/oracle/oradata/ORCL/datafile/system01.dbf’;

Database altered.

// Ensure your changes by issuing same above query:
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME=’SYSTEM’;

FILE_NAME FILE_ID TABLESPACE_NAME
—————————————————- ———- ———————–
/u01/app/oracle/oradata/ORCL/datafile/system01.dbf 1 SYSTEM

 

Cheers!! Now we can move our datafile without downtime with Oracle 12c.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

How to Configure Flashback Database in oracle 12c

Issue the following steps to configure your database with flashback recovery:

// Ensure flashback recovery settings by querying ‘v$database’ dictionary view, as follows:
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
NO

// Set db_recovery_file_dest system parameter for flashback recovery file destination:
[oracle@12c ~]$ sqlplus “/ as sysdba”
SQL> alter system set db_recovery_file_dest=’/u01/flash_recovery’ scope=both; Continue reading

Oracle 12c Logo

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

Today, I came across the “ORA-00265: instance recovery required, cannot set ARCHIVELOG mode” while converting database into archive log mode.

This error usually caused when database crashed unfortunately or we shutdown database with the help of database shutdown command as: shutdown abort, startup force mount or shutdown abort. These types of command will required instance recovery in next startup. In short it need clean database startup.

In my case, i am trying to alter database in archive log mode with “startup force mount” command:
SQL> startup force mount
ORACLE instance started.

Total System Global Area 1503199232 bytes
Fixed Size 2288584 bytes
Variable Size 905970744 bytes
Database Buffers 587202560 bytes
Redo Buffers 7737344 bytes
Database mounted.
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

 

// Solution:

SQL> select open_mode from v$database;

OPEN_MODE
——————–
MOUNTED

// Open your database and shutdown it normally, as follow:

SQL> alter database open;

Database altered.

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

// Now startup database in mount state:
SQL> startup mount
ORACLE instance started.

Total System Global Area 1503199232 bytes
Fixed Size 2288584 bytes
Variable Size 905970744 bytes
Database Buffers 587202560 bytes
Redo Buffers 7737344 bytes
Database mounted.

// Now issue following to convert database into archivelog mode:
SQL> alter database archivelog;

Database altered.

// Now open database:
SQL> alter database open;

Database altered.

Database has been configured in archivelog without oracle error:ORA-00265, because this time we shutdown our database in normal way.

 

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

How to configure oracle 12c database in archivelog mode

Connect database as sys user to configure it in archivelog mode.

[oracle@12c ~]$ sqlplus “/ as sysdba”

// Archive log list: Database is in ‘No Archive Mode’.

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 140
Current log sequence 142

// Shutdown database:

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

// Startup your database in mount state:

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1503199232 bytes
Fixed Size 2288584 bytes
Variable Size 905970744 bytes
Database Buffers 587202560 bytes
Redo Buffers 7737344 bytes
Database mounted.

// Configure your database in archivelog mode by issue the following:

SQL> alter database archivelog;

Database altered.

// Alter database in open mode:

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

// Database has been successfully configured in archive log mode. Issue the following:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 142
Next log sequence to archive 144
Current log sequence 144

 

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

Managing Pluggable Databases from Pluggable as well as Container Databases

Following SQL* Plus are used to start/stop a pluggable databases.

Note:

  • Priviledged user must be use to connect pluggable database.
  • I will simulate whole pluggable database management with the help of HR & SALES pluggable databases.

 

[oracle@12c ~]$ sqlplus “/ as sysdba”
SQL> show con_name

CON_NAME Continue reading

Oracle 12c Logo

Delete Pluggable Database Manually using SQL Prompt

In previous article we have seen how to clone pluggable database manually from exisiting pluggable database ( Non seed )

In this article we will see how to delete pluggable database manually using SQL Prompt.

If required keep associated datafiles, in case of future use.

Note:

  • Pluggable database should be closed before being dropped.
  • I will simulate scenario with the help of PLUGDB & NEWPDB pluggable databases.

// Ensure your pluggable database mode by issuing the following query:
SQL> select name,open_mode from v$pdbs; Continue reading

Oracle 12c Logo

Clone a Pluggable Database Manually using SQL Prompt

To make a clone of existing pluggable database is exactly similar to creating a new pluggable database from the seed pluggable database.

Note:

  • We are using local pluggable database (non-seed) as the source to clone new pluggable database.
  • Source pluggable database should open in READ ONLY mode.
  • I will simulate scenario with the help of pluggable database: NEWPDB

// Ensure your pluggable database is in READ ONLY mode, if not then convert it into READ ONLY.

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE Continue reading

Oracle 12c Logo

Create Pluggable Database Manually using SQL Prompt

In earlier article, we have seen the method of creating pluggable databases using configuration assistant.

In this article we will have a look & hands-on on creating a pluggable database manually with the help of SQL Prompt.

We can create PDB using PDB_FILE_NAME_CONVERT initialization parameter, In following example i am going to create new Pluggable Database, name: ‘newpdb’

[oracle@OL6 ~]$ sqlplus “/ as sysdba”
// Ensure database globale name by following SQL query:
SQL> select global_name from global_name;

GLOBAL_NAME Continue reading

Oracle 12c Logo

Audit Commands IN ORACLE 12C DATA PUMP

In Oracle 12c, Now we can also audit oracle data pump backups job by creating an audit policy just like a user/schema auditing.
With the help of this feature keeping eye on your data pump backups is easy now.

Syntax:
CREATE AUDIT POLICY Data_Pump_Policy_name
ACTIONS COMPONENT=DATAPUMP [EXPORT | IMPORT | ALL];

When this policy is applied to a user, their data pump jobs will appear in the audit trail.
The following policy audits all data pump operations. The policy is applied to the SYSTEM user. Continue reading

Oracle 12c Logo

Pluggable Database Automatic Startup

Unfortunately, There is no default mechanism to start all Pluggable Databases.
While startup of Container Databases, all the Pluggable Databases will be in mounted state.
But we can accomplished same with the system trigger on Container Database to start PDB[s].

Following trigger will automatically open all PDB’s while starting up CDB.

CREATE OR REPLACE TRIGGER opn_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;
END opn_pdbs;
/

We can customise it if we don’t want to start all PDB’s

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

In Database Archiving – Oracle 12c New Feature

Lets consider following points to understand “In Database Archiving” in depth:

  1. What is “In Database Archiving”?
  2. Benefits of “In Database Archiving”.
  3. Implementation & Hands-on on In Database Archiving.

What is “In Database Archiving”?

Most of the time, oracle enterprise databases(tables) are very large in size which has years & years of historical data. Very difficult in identifying current & old data, if any how we can manage to identify it, we can’t move it to Tape or Storage because we don’t know when these historical data will be needed for any reasons. If we managed to separate these as current and old data on tape or storage, In case of need, getting that data back in database will be very time-consuming  Continue reading