Saturday, April 30, 2016

How to Purge SQL Statement from the Shared Pool

SQL> select sql_id from v$sqlarea
where sql_text like '%FLOOR(((NVL(SODEODLOG_END_DATE,SYSDATE)-SODEODLOG_START_DATE)*24*60*60)/3600)%'  2
  3  ;

SQL_ID
-------------
73hyvwg8qfmv3
1175vqgptmgdg

SQL> select address, hash_value from v$sqlarea where sql_id like '73hyvwg8qfmv3';

ADDRESS          HASH_VALUE
---------------- ----------
0700000D2D1E6470 3513208675

SQL> select address, hash_value from v$sqlarea where sql_id like '1175vqgptmgdg';

ADDRESS          HASH_VALUE
---------------- ----------
0700000E4ED800E0 3952721327

SQL> exec dbms_shared_pool.purge('0700000D2D1E6470, 3513208675','C');

PL/SQL procedure successfully completed.

SQL> exec dbms_shared_pool.purge('0700000E4ED800E0, 3952721327','C');

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Saturday, April 23, 2016

RMAN Backup Strategy Implementation in Production Database

RMAN: Recovery Manager (RMAN) is simple and powerful utility of Oracle database to manage Oracle database backup, restore and recovery. Key parts of maintaining oracle database are ensuring backup, restore and recover database from any disaster.


Backup Strategy: To ensure high availability, perform easy backup and restore operation we will implement two types of backup, one is Full Backup (Level 0) and another is Incremental (Level 0) backup.

1.      Level 0 backup should run on holiday of each week (Saturday).
2.      Level 1 backup should run on each of week days (Sunday-Friday).

Implementation: To implement RMAN Backup strategy we will perform the following steps.

1.      Configuring RMAN.
2.      Prepare full backup and incremental backup scripts.
3.      Create cron job for performing RMAN Backup.

Configuring RMAN: To take RMAN backup we should run oracle database in archive mode. Perform the following to configure RMAN.

1.      Check database are run in archive mode or not if it is not perform the steps 2.

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     30
Next log sequence to archive   32
Current log sequence           32
SQL>

2.      Enabling archivelog

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2177456 bytes
Variable Size            1291847248 bytes
Database Buffers          838860800 bytes
Redo Buffers                5001216 bytes
Database mounted.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

3.      Define the location and size of FRA

SQL> alter system set DB_RECOVERY_FILE_DEST='+FRA' scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest_size=100G scope=spfile;

System altered.

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

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             822083824 bytes
Database Buffers          436207616 bytes
Redo Buffers                8847360 bytes
Database mounted.
Database opened.
SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 100G
SQL>

[NB: db_recovery_file_dest_size parameter value are depend on your system how much archive log file are generate per hour]

4.      Now take an RMAN backup using rman utility. In this document we are not using recovery catalog database so that control file will be used to contain catalog information.

[oracle@DBHOST01 ~]$ echo $ORACLE_SID
CBSDB
[oracle@DBHOST01 ~]$ rman target/

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Apr 23 19:11:05 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CBSDB (DBID=624768357)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ASMDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_CBSDB.f'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> backup database;

Starting backup at 23-APR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 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=+DATA/cbsdb/datafile/system.256.880569463
input datafile file number=00002 name=+DATA/cbsdb/datafile/sysaux.257.880569467
input datafile file number=00005 name=+DATA/cbsdb/datafile/example.265.880569629
input datafile file number=00006 name=+DATA/cbsdb/datafile/test_tbs.267.881349853
input datafile file number=00003 name=+DATA/cbsdb/datafile/undotbs1.258.880569467
input datafile file number=00004 name=+DATA/cbsdb/datafile/users.259.880569471
input datafile file number=00007 name=+DATA/cbsdb/datafile/tbs_user_data_1.268.882462007
input datafile file number=00008 name=+DATA/cbsdb/datafile/tbs_user_data_1.269.882458361
channel ORA_DISK_1: starting piece 1 at 23-APR-16
channel ORA_DISK_1: finished piece 1 at 23-APR-16
piece handle=+FRA/cbsdb/backupset/2016_04_23/nnndf0_tag20160423t191539_0.355.909947741 tag=TAG20160423T191539 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
Finished backup at 23-APR-16

Starting Control File and SPFILE Autobackup at 23-APR-16
piece handle=+FRA/cbsdb/autobackup/2016_04_23/s_909947845.356.909947847 comment=NONE
Finished Control File and SPFILE Autobackup at 23-APR-16

RMAN> LIST BACKUP;


List of Backup Sets
===================
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1       98.86M     DISK        00:00:11     28-MAY-15
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20150528T171856
Piece Name: +FRA/asmdb/backupset/2016_04_23/nnndf0_tag20160423t191539_0.355.909947741
……….

[NB: We did not configure rman default backup location for that rman send backup file to default location in FRA. We can change default backup location]

5.      This is optional steps for change default backup location.

Configure Control File Backup Destination
=============================================

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/rman_backup/cf_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/rman_backup/cf_%F';
new RMAN configuration parameters are successfully stored


Configure Datafile Backup Destination
=============================================

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u02/rman_backup/df_%d_%t_%s_%p';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   /u02/rman_backup/df_%d_%t_%s_%p';
new RMAN configuration parameters are successfully stored

---- End of RMAN Configuration ----

Prepare full backup and incremental backup scripts: In these steps we will create two shell file to perform full and incremental rman backup.

1.      Full RMAN Backup Scripts.

[oracle@DBHOST01 scripts]$ mkdir /u01/rman_backup/
[oracle@DBHOST01 scripts]$
[oracle@DBHOST01 scripts]$ cd /u01/rman_backup/
[oracle@DBHOST01 rman_backup]$
[oracle@DBHOST01 rman_backup]$ mkdir scripts
[oracle@DBHOST01 rman_backup]$ mkdir log
[oracle@DBHOST01 rman_backup]$ cd /u01/rman_backup/scripts/
[oracle@DBHOST01 scripts]$ vi disk_full_db_backup.rcv
[oracle@DBHOST01 scripts]$ cat disk_full_db_backup.rcv
RUN
{
ALLOCATE CHANNEL CH1 TYPE DISK FORMAT '+RMAN_BACKUP/rman_bkp_df1_%d_%t_%s_%p_%U';
ALLOCATE CHANNEL CH2 TYPE DISK FORMAT '+RMAN_BACKUP/rman_bkp_df2_%d_%t_%s_%p_%U';
BACKUP
INCREMENTAL LEVEL 0
DATABASE;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP ARCHIVELOG ALL FORMAT '+RMAN_BACKUP/rman_bkp_ar_%d_%t_%s_%p_%U.arch' DELETE INPUT;
BACKUP CURRENT CONTROLFILE FORMAT '+RMAN_BACKUP/rman_bkp_ctl_%d_%t_%s_%p_%U';
BACKUP SPFILE FORMAT '+RMAN_BACKUP/rman_bkp_spf_%d_%s_%T.bak';
}

[oracle@DBHOST01 scripts]$

[oracle@DBHOST01 scripts]$ vi disk_run_full_backup.sh
[oracle@DBHOST01 scripts]$
[oracle@DBHOST01 scripts]$ cat disk_run_full_backup.sh
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=cbsdb; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
cdate=`date '+%Y%m%d'`
ctime=`date '+%H%M%S'`
logfile=/u01/rman_backup/log/disk_full_db_backup-on-$cdate-at-$ctime.log

rman target sys/oracle  cmdfile '/u01/rman_backup/scripts/disk_full_db_backup.rcv' log $logfile
[oracle@DBHOST01 ~]$

2.      Incremental RMAN Backup Scripts.

[oracle@DBHOST01 scripts]$ vi disk_inc_db_backup.rcv
[oracle@DBHOST01 scripts]$ cat disk_inc _db_backup.rcv
RUN
{
ALLOCATE CHANNEL CH1 TYPE DISK FORMAT '+RMAN_BACKUP/rman_bkp_df1_%d_%t_%s_%p_%U';
ALLOCATE CHANNEL CH2 TYPE DISK FORMAT '+RMAN_BACKUP/rman_bkp_df2_%d_%t_%s_%p_%U';
BACKUP
INCREMENTAL LEVEL 1
DATABASE;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP ARCHIVELOG ALL FORMAT '+RMAN_BACKUP/rman_bkp_ar_%d_%t_%s_%p_%U.arch' DELETE INPUT;
BACKUP CURRENT CONTROLFILE FORMAT '+RMAN_BACKUP/rman_bkp_ctl_%d_%t_%s_%p_%U';
BACKUP SPFILE FORMAT '+RMAN_BACKUP/rman_bkp_spf_%d_%s_%T.bak';
}

[oracle@DBHOST01 scripts]$

[oracle@DBHOST01 scripts]$ vi disk_run_inc_backup.sh
[oracle@DBHOST01 scripts]$
[oracle@DBHOST01 scripts]$ cat disk_run_inc_backup.sh
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=cbsdb; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
cdate=`date '+%Y%m%d'`
ctime=`date '+%H%M%S'`
logfile=/u01/rman_backup/log/disk_full_db_backup-on-$cdate-at-$ctime.log

rman target sys/oracle  cmdfile '/u01/rman_backup/scripts/disk_inc_db_backup.rcv' log $logfile
[oracle@DBHOST01 ~]$

3.      Create cron job for performing RMAN Backup: Cron is an operating system jobs which run at scheduled time and perform specific action. Now we will set cron job which will perform full back up at Saturday night and incremental backup at every night.

[oracle@DBHOST01 ~]$ crontab -l
no crontab for oracle
[oracle@DBHOST01 ~]$ crontab -e
no crontab for oracle - using an empty one
crontab: installing new crontab
[oracle@DBHOST01 ~]$
[oracle@DBHOST01 ~]$ crontab -l
* 01 * * 0 /u01/rman_backup/scripts/disk_run_full_backup.sh
* 01 * * 5 /u01/rman_backup/scripts/disk_run_inc_backup.sh
* 01 * * 4 /u01/rman_backup/scripts/disk_run_inc_backup.sh
* 01 * * 3 /u01/rman_backup/scripts/disk_run_inc_backup.sh
* 01 * * 2 /u01/rman_backup/scripts/disk_run_inc_backup.sh
* 01 * * 1 /u01/rman_backup/scripts/disk_run_inc_backup.sh
* 01 * * 6 /u01/rman_backup/scripts/disk_run_inc_backup.sh
[oracle@DBHOST01 ~]$


Tuesday, April 19, 2016

How to find out size of directory in AIX

NODE1$ du -ak /u01|sort -n -r|head
21140740        /u01
21137164        /u01/oracle
21123992        /u01/oracle/app
10759480        /u01/oracle/app/diag
10230288        /u01/oracle/app/product/11.2.0/db_1
10230288        /u01/oracle/app/product/11.2.0
10230288        /u01/oracle/app/product
9821492 /u01/oracle/app/diag/rdbms/cbsdb/cbsdb
9821492 /u01/oracle/app/diag/rdbms/cbsdb
9821492 /u01/oracle/app/diag/rdbms

Saturday, April 2, 2016

ORA-32000: write to SPFILE requested but SPFILE is not modifiable

You can see the spfile location are showing in your $ORACLE_HOME/dbs location but spfile are exist in ASM. Database by default use this location. Move the spfile from $ORACLE_HOME/dbs  to another destination.


SQL>  alter system set db_recovery_file_dest='+FRA' scope=spfile sid='*';
 alter system set db_recovery_file_dest='+FRA' scope=spfile sid='*'
*
ERROR at line 1:
ORA-32000: write to SPFILE requested but SPFILE is not modifiable
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.4
                                                 /db_1/dbs/spfileracdb2.ora
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@racn2 ~]$ mv /u01/app/oracle/product/11.2.4/db_1/dbs/spfileracdb2.ora /u01/app/oracle/product/11.2.4/db_1/dbs/bkpspfileracdb2.ora
[oracle@racn2 ~]$
[oracle@racn2 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Sat Apr 2 09:20:08 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

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

Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1073744936 bytes
Database Buffers          570425344 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL> alter system set db_recovery_file_dest='+FRA' scope=spfile sid='*';

System altered.

SQL>

Move spfile form disk to ASM


SQL> create spfile='+DATA/RACDB/spfileracdb.ora' from pfile='/u01/app/oracle/pfile.ora';

File created.

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

Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1073744936 bytes
Database Buffers          570425344 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL>