Oracle Database Administration: August 2017

Tuesday, 22 August 2017

Taking Cold Backup of Oracle Database

Introduction

Cold backup is consistent backup, as the backup is run when the database is shutdown. Its exact image copy of the database that can be used to clone database on other server. We can take Oracle database cold backup in two ways

Cold backup can be taken using OS command such as cp
Using RMAN utility also we can take cold backups

Taking Cold Backup of Oracle Database - Manual

Step 1:- Check the list of data files, control files and redolog files in the database

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/GGDB01/users01.dbf

/u01/app/oracle/oradata/GGDB01/undotbs01.dbf

/u01/app/oracle/oradata/GGDB01/sysaux01.dbf

/u01/app/oracle/oradata/GGDB01/system01.dbf

/u01/app/oracle/oradata/GGDB01/ggdata01.dbf

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/GGDB01/redo03.log

/u01/app/oracle/oradata/GGDB01/redo02.log

/u01/app/oracle/oradata/GGDB01/redo01.log

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/GGDB01/control01.ctl

/u01/app/oracle/flash_recovery_area/GGDB01/control02.ctl

Step 2:- Shutdown the database using graceful shutdown

[oracle@prim backups]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 22 19:35:45 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

Step 3:- Now manually copy all the data files, control files and redolog files

$ cp /u01/app/oracle/oradata/GGDB01/users01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/undotbs01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/system01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/sysaux01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/ggdata01.dbf /u01/app/backups/
--------------------------------------------------------------------------------

$ cp /u01/app/oracle/oradata/GGDB01/redo01.log /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/redo02.log /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/redo03.log /u01/app/backups/

--------------------------------------------------------------------------------
$ cp /u01/app/oracle/oradata/GGDB01/control01.ctl /u01/app/backups/

$ cp /u01/app/oracle/flash_recovery_area/GGDB01/control02.ctl /u01/app/backups/

Step 4:- Go to backup location and list the files. This step is to verify all the files are present

[oracle@prim backups]$ ls -ltr

total 1898144

-rw-r----- 1 oracle oinstall 5251072 Aug 22 19:36 users01.dbf

-rw-r----- 1 oracle oinstall 31465472 Aug 22 19:36 undotbs01.dbf

-rw-r----- 1 oracle oinstall 702554112 Aug 22 19:37 system01.dbf

-rw-r----- 1 oracle oinstall 503324672 Aug 22 19:38 sysaux01.dbf

-rw-r----- 1 oracle oinstall 524296192 Aug 22 19:38 ggdata01.dbf

-rw-r----- 1 oracle oinstall 52429312 Aug 22 19:39 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Aug 22 19:39 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Aug 22 19:39 redo03.log

-rw-r----- 1 oracle oinstall 9748480 Aug 22 19:40 control01.ctl

-rw-r----- 1 oracle oinstall 9748480 Aug 22 19:40 control02.ctl

[oracle@prim backups]$

Now we have completed the cold backup for Oracle database :)

Step 5:- Once the backup is complete, start the database

Taking Cold Backup of Oracle Database using RMAN

Here we will demonstrate the procedure of taking RMAN cold backup for Oracle database

Step 1:- Shutdown the database and bring it to mount state

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes

Variable Size 532678648 bytes

Database Buffers 301989888 bytes

Redo Buffers 2396160 bytes

Database mounted.

SQL>

Step 2:- Connect to RMAN then run full database backup and control file backup

[oracle@prim ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Aug 22 22:52:34 2017

connected to target database: GGDB01 (DBID=1637703886, not open)

RMAN> backup database;

Starting backup at 22-AUG-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/GGDB01/system01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/GGDB01/ggdata01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/GGDB01/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/GGDB01/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/GGDB01/users01.dbf

channel ORA_DISK_1: starting piece 1 at 22-AUG-17

channel ORA_DISK_1: finished piece 1 at 22-AUG-17

piece handle=/u01/app/oracle/flash_recovery_area/GGDB01/backupset/2017_08_22/o1_mf_nnndf_TAG20170822T225245_dsrt35w5_.bkp tag=TAG20170822T225245 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 22-AUG-17

channel ORA_DISK_1: finished piece 1 at 22-AUG-17

piece handle=/u01/app/oracle/flash_recovery_area/GGDB01/backupset/2017_08_22/o1_mf_ncsnf_TAG20170822T225245_dsrt5lvt_.bkp tag=TAG20170822T225245 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 22-AUG-17

RMAN> backup current controlfile;

Starting backup at 22-AUG-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 22-AUG-17

channel ORA_DISK_1: finished piece 1 at 22-AUG-17

piece handle=/u01/app/oracle/flash_recovery_area/GGDB01/backupset/2017_08_22/o1_mf_ncnnf_TAG20170822T225457_dsrt7boz_.bkp tag=TAG20170822T225457 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 22-AUG-17

RMAN>

Once the database is complete, open the database

SQL> alter database open;

Now we can successfully completed full :)

Sunday, 20 August 2017

DDL - Create, Alter, Drop, Truncate and Rename SQL statements in Oracle

DDL - Data Definition Language

Its used to create and modify database objects such as tables etc. DDL are used to define metadata in the database. By default DDL is auto commit, when ever you a execute a DDL commit is triggered

CREATE : Its used to create the table, schema etc
ALTER : Modifying existing table by adding column, dropping column
DROP : Delete table or any other database objects
TRUNCATE : It clears all the data inside a table
RENAME : Change the name of the table or any other object

Create SQL Statement

Create statement is used to create the objects. Here we will create the table to demonstrate an example

Create table without primary key or constraints

SQL> CREATE TABLE hr.emp (

eno NUMBER(10),

ename VARCHAR2(15),

ssn NUMBER(10),

dept VARCHAR2(10),

mgr_id NUMBER(5),

hiredate DATE DEFAULT (sysdate));

Create table with primary key and other constraints

SQL> CREATE TABLE hr.emp (

eno NUMBER(10) PRIMARY KEY,

ename VARCHAR2(15) NOT NULL,

ssn NUMBER(10),

dept VARCHAR2(10),

mgr_id NUMBER(5),

hiredate DATE DEFAULT (sysdate));

Alter SQL Statement

Alter command is used to modify database objects such as tables etc. Its used to add, drop and rename the columns of the table

Alter table add column

This statement adds column to existing table

SQL> alter table hr.emp add (phone number, email varchar2(35));

Alter table rename column

This statement renames existing column to new column name

SQL> alter table hr.emp rename column phone to mobile_number;

Alter table modify column data type

This statement changes the datatype of a column

SQL> alter table hr.emp modify email char(50);

Alter table drop column

This statement drops existing column from the table

SQL> alter table hr.emp drop (email, mobile_number);

Drop SQL Statement

Drop SQL statements are used to drop or delete the database objects

SQL> drop table emp;

Rename SQL Statement

This SQL statement renames an existing table with new table name

SQL> rename emp to employee;

Truncate SQL Statement

This SQL statement deletes all the rows that are present in the table

SQL> truncate table emp;

Saturday, 19 August 2017

ORA-01031: insufficient privileges when creating table

Issue

Create table command fails with below mentioned error

ERROR at line 1:
ORA-01031: insufficient privileges

Here are the list of privileges granted

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

Resolution

Grant the below mentioned privileges to the user then re login to database. Now execute the create table statement, it must run successfully. In case you face any issue, please write the comment below

SQL> grant resource to hr;

Grant succeeded.

SQL Language in Oracle: DDL, DML, DRL, DCL and TCL Statements

Overview

SQL stands for Structured Query Language and its basically divided into 5 types of language statements i.e. DDL, DML, DRL, DCL and TCL. These languages are used to create, modify, load, control and retrieve the data from the database

Types of SQL Statements

DDL - Data Definition Language

Its used to create and modify database objects such as tables etc. DDL are used to define metadata in the database. By default DDL is auto commit, when ever you a execute a DDL commit is triggered

CREATE : Its used to create the table, schema etc
ALTER : Modifying existing table such as adding column, dropping column
DROP : Delete table or any other database objects
TRUNCATE : It clears all the data inside a table
RENAME : Change the name of the table or any other object

DML - Data Manipulation Language

Its used to insert, update and delete the data from the tables, hence its called as Data manipulation language

INSERT : It adds rows or records to the table
UPDATE : Its used to modify information of rows in table
DELETE : It delete records or rows from the table
MERGE : It allows to join data source and perform action on target table

DRL - Data Retreival Language

This language is used to fetch the records from the table

SELECT : Retrieves the data from the tables

DCL - Data Control Language

This language is used to control the level of access to users on the database objects

GRANT : Allows users to access particular object in the database
REVOKE : Prohibits user from accessing particular object in the database

TCL - Transaction Control Language

This language is used to manage transactions within the database

COMMIT : Used to save the data
ROLLBACK : Used to revert the changes made
SAVEPOINT : Its used to identify point in transaction, where we can rollback

Tuesday, 15 August 2017

Configure Oracle 11gR2 Data Guard Physical Standby

Synopsis

In this exercise we will configure Oracle Data Guard for Oracle 11gR2 database. We have two virtual machines configured where we have built Linux machines and installed Oracle 11gR2 database. Please refer below mentioned link to build Linux virtual machine and install Oracle 11gR2 database software

Primary Database

hostname : prim.localdomain
db_name : PRODPRIM
db_unique : PRODPRIM

Standby Database

hostname : stby.localdomain
db_name : PRODPRIM
db_unique : PRODSTBY

Configuration on the Primary Database :-

Step 1 :- Enable archivelog mode

Click on this link to see, how to enable archivelog mode

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 2

Current log sequence 2

Step 2 :- Enable force logging

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

Step 3 :- Set database parameters to configure the data guard

Here are the list of parameters to be set to configure the dataguard

db_unique_name
log_archive_config
log_archive_dest_2
remote_login_passwordfile
fal_server
fal_client
standby_file_management

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRODPRIM,PRODSTBY)';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=PRODSTBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSTBY';

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

System altered.

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

SQL> alter system set fal_server=PRODSTBY;

System altered.

SQL> alter system set fal_client='PRODPRIM';

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

Step 4 :- Create and start the Listener

We need to create and start the listener on both primary and standby servers

Click Here to Configure and Start Listener

Step 5 :- Add tnsnames for services of primary and standby database

This step has to be performed on both primary and standby database servers. You can update the tnsnames.ora file to add service or service can be added using netca utility. Go to $ORACLE_HOME/network/admin/tnsnames.ora add the below mentioned line to the file

In case you want to add service using netca utility refer this link

Click here to add service using netca utility

PRODPRIM =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = prim.localdomain)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PRODPRIM)

)

PRODSTBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = stby.localdomain)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PRODSTBY)

)

Step 6 :- Check the services if its been configured properly using tnsping on both the servers

[oracle@prim dbhome_1]$ tnsping PRODPRIM

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 15-AUG-2017 14:17:41

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.localdomain)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PRODPRIM)))

OK (0 msec)

[oracle@prim dbhome_1]$ tnsping PRODSTBY

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 15-AUG-2017 14:18:51

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stby.localdomain)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PRODSTBY)))

OK (20 msec)

Step 7 :- Backup primary database using RMAN utility

[oracle@prim dbhome_1]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Aug 15 14:20:28 2017

connected to target database: PRODPRIM (DBID=881986728)

RMAN> backup database plus archivelog;

Step 8 :- Create standby control file

SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/PRODPRIM/stbycontrol1.ctl';

Database altered.

Step 9 :- Create pfile of primary database

SQL> create pfile from spfile;

File created.

Step 10 :- Update the newly created pfile with below mentioned changes

log_archive_dest_2 = 'SERVICE=PRODPRIM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODPRIM'
fal_client = PRODSTBY
fal_server = PRODPRIM
db_unique_name = PRODSTBY

Step 11 :- Copy newly created pfile, standby controlfile, password file and RMAN backup pieces to standby database server

[oracle@prim dbs]$ scp initPRODPRIM.ora oracle@stby.localdomain:/u01/app/oracle/product/11.2.0/dbhome_1/dbs

The authenticity of host 'stby.localdomain (192.168.10.2)' can't be established.

RSA key fingerprint is a3:a6:ed:66:82:ad:bf:9b:6b:a7:59:4d:27:f6:f0:a0.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added 'stby.localdomain,192.168.10.2' (RSA) to the list of known hosts.