Implement Transparent Data Encryption(TDE) using fast offline conversion for Oracle Databases

Hi DBAs,

It is important we share what we do in DBAs life to help ourself and others. I've done many implementation projects recently related with Database Security. One of the them was TDE which was pending in my blog posts . I've decided to share the steps involve in implementing Transparent Data Encrption(TDE) using fast offline conversion method for entire Oracle Database. We can follow the similar steps in Oracle RAC or Standalone Database Except few additional things need to done for Oracle RAC Database.


Prerequisite:

Make sure you have applied the patch 23315889(fast offline conversion patch) if you are on Oracle 11g Database or latest CPU patches are applied which already include all the mandatory patches before proceeding with below steps.

Below steps can be used for Oracle 11g,12c , 18c, 19c Databases

Step 1: Take a Backup of  Database using RMAN.

Make sure you have full Database backup using RMAN and Validated.

Step 2: Take Backup of  $TNS_ADMIN ( $ORACLE_HOME/network/admin) Directory

Please take backup of network configuration files which includes backup of  $TNS_ADMIN Directory of $ORACLE_HOME in all the DB nodes.

Step 3: Make Sure You Inform Application owners or End User for Downtime

Please make sure you take sufficient downtime based on the size of your Database if it is Production Environment . If you have identical test environment as Production , we have very good opportunity to make a note of timings at each step of implementation phase. It will surely be useful.

Step 4:Shut down applications

If you are using Oracle ERP Applications make sure all the applications are stopped with adstpall.sh script or other applications it needs to be completed stoped.

Step 5:Create a specific wallet by specifying the wallet location in the  sqlnet.ora file(sqlnet_ifile.ora in Oracle ERP Environment).

$ORACLE_HOME/network/admin is the location where you find network configuration files( sqlnet.ora or sqlnet_ifile.ora(In Oracle ERP) needs to be modified).

sqlnet.or or sqlnet_ifile.ora contents :

ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /home/oracle/wallet/$ORACLE_SID)
)
)

Note: DIRECTORY location like above makes it easier to copy wallet files across multiple databases. However we are free to have any location of our choice.

Step 6: Restart Database instance and Listener for Oracle RAC Environment running on node1 and node2  or Listener and DB in Standalone DB using SQLPLUS

After completing step 1 to step5 perform step 6 mandatory. This is important to detect the wallet location by Database.

Make sure you bring down all the instances and databases services from all the other nodes if you are working in RAC Database Environment. All the steps need to be performed from DB node1 or Primary Database Instance and all the other instances and Database services are down in other servers.


DB node1:
=====================
[root@dbhost01 bin]# su - oracle
[oracle@dbhost01 ~]$ srvctl status database -d testdb
Instance testdb1 is running on node dbhost01
Instance testdb2 is not running on node dbhost02
[oracle@dbhost01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): dbhost01,dbhost02
[oracle@dbhost01 ~]$ srvctl stop instance -i testdb1 -d testdb
[oracle@dbhost01 ~]$ srvctl status database -d testdb
Instance testdb1 is not running on node dbhost01
Instance testdb2 is not running on node dbhost02
[oracle@dbhost01 ~]$ srvctl stop listener -n dbhost01
[oracle@dbhost01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): dbhost02
[oracle@dbhost01 ~]$ srvctl start listener -n dbhost01
[oracle@dbhost01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): dbhost01,dbhost02
[oracle@dbhost01 ~]$ srvctl start instance -i testdb1 -d testdb
[oracle@dbhost01 ~]$ srvctl status database -d testdb
Instance testdb1 is running on node dbhost01
Instance testdb2 is not running on node dbhost02
[oracle@dbhost01 ~]$


Step 7:Create the wallet and Set the Master Encryption Key.

Initiate a new SQL*Plus session.
This causes the changes to sqlnet.ora and the environment variable to be picked
up by the new session.

SQL> select name from v$database;

NAME
---------
testdb

SQL> select status from v$instance;

STATUS
------------
OPEN

Set the Master Encryption Key.

Check below:
==========

SQL> select instance_name,status,database_status from gv$instance;

INSTANCE_NAME    STATUS       DATABASE_STATUS
---------------- ------------ -----------------
testdb1        OPEN         ACTIVE


SQL> select * from gv$encryption_wallet order by inst_id;

   INST_ID WRL_TYPE
---------- --------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
         1 file
/home/oracle/wallet/$ORACLE_SID
CLOSED

(or)

select * from v$encryption_wallet;


SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "Mywork8t5_mydbacomp";

System altered.

NOTE: Ensure that the password string is contained in double quotation marks (" ").


Step 8: Shutdown & Startup  the database normally, ensuring that the wallet is open: 


[oracle@dbhost01 ~]$ sqlplus "/as sysdba"

SQL> select name from v$database;

NAME
---------
testdb

SQL>shut immediate

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 19 20:31:26 2019

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.2527E+10 bytes
Fixed Size                  2264856 bytes
Variable Size            7012876520 bytes
Database Buffers         5502926848 bytes
Redo Buffers                8658944 bytes
Database mounted.

SQL> alter system set encryption wallet open identified by "Mywork8t5_mydbacomp";

System altered.

SQL> alter database open;

Database altered.

SQL> select instance_name,status,database_status from gv$instance;

INSTANCE_NAME    STATUS       DATABASE_STATUS
---------------- ------------ -----------------
testdb1        OPEN         ACTIVE


SQL>

Step 9:To configure auto login for wallet (optional), do the following

Command: orapki wallet create -wallet "/home/oracle/wallet/$ORACLE_SID" -auto_login

[oracle@dbhost01 admin]$ orapki wallet create -wallet "/home/oracle/wallet/$ORACLE_SID" -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:     Mywork8t5_mydbacomp
                    
[oracle@dbhost01 admin]$

Verify autostart of Database is working as expected:
=======================================
[oracle@dbhost01 admin]$ srvctl status database -d testdb
Instance testdb1 is running on node dbhost01
Instance testdb2 is not running on node dbhost02
[oracle@dbhost01 admin]$ srvctl stop instance -i testdb1 -d testdb
[oracle@dbhost01 admin]$ srvctl status database -d testdb
Instance testdb1 is not running on node dbhost01
Instance testdb2 is not running on node dbhost02
[oracle@dbhost01 admin]$ srvctl start instance -i testdb1 -d testdb
[oracle@dbhost01 admin]$ srvctl status database -d testdb
Instance testdb1 is running on node dbhost01
Instance testdb2 is not running on node dbhost02

Step 10:Backup the wallet

Using 'cp' command copy the wallet files in separate directory and verify it.

[oracle@dbhost01 testdb1]$ pwd
/home/oracle/DBA/backup/wallet/testdb1
[oracle@dbhost01 testdb1]$ ls -ltr
total 8
-rw-------. 1 oracle oinstall 2917 Apr 19 21:00 cwallet.sso
-rw-r--r--. 1 oracle oinstall 2840 Apr 19 21:00 ewallet.p12
[oracle@dbhost01 testdb1]$


Step 11: Copy the wallet to DB node2 in the same location as DB node1.

Copy the wallet to DB node2 in the same location as DB node1 and make sure in the sqlnet.ora and sqlnet_ifile.ora file has the same location defined.

$ORACLE_HOME/network/admin is the location where you find network configuration files( sqlnet.ora or sqlnet_ifile.ora(In Oracle ERP) needs to be modified).

sqlnet.or or sqlnet_ifile.ora contents :

ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /home/oracle/wallet/$ORACLE_SID)
)
)

oracle@dbhost01 testdb1]$ pwd
/home/oracle/wallet/testdb1
[oracle@dbhost01 testdb1]$ ls -ltr
total 8
-rw-r--r--. 1 oracle asmadmin 2840 Apr 19 20:17 ewallet.p12
-rw-------. 1 oracle oinstall 2917 Apr 19 20:47 cwallet.sso
[oracle@dbhost01 testdb1]$ scp ewallet.p12 cwallet.sso oracle@dbhost02:/home/oracle/wallet/testdb2
ewallet.p12                                                                           100% 2840     2.8KB/s   00:00
cwallet.sso                                                                           100% 2917     2.9KB/s   00:00
[oracle@dbhost01 testdb1]$

Step 12:Verify DB Instances and Services on DB node1 & DB node2.

Now you can start the instance on Database Instance and Database services on DB node2 and verify it is running.

[oracle@dbhost01 admin]$ srvctl status database -d testdb
Instance testdb1 is running on node dbhost01
Instance testdb2 is running on node dbhost02

[oracle@dbhost01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): dbhost01,dbhost02

(OR)

For Testing purpose only

We can also verify DB node2 is started after copying ewallet.p12 and cwallet.sso:
===========================================================
[oracle@dbhost02 testdb2]$ ls -ltr
total 8
-rw-r--r--. 1 oracle oinstall 2840 Apr 19 21:08 ewallet.p12
-rw-------. 1 oracle oinstall 2917 Apr 19 21:08 cwallet.sso

[oracle@dbhost02 testdb2]$ srvctl stop instance -i testdb1 -d testdb
[oracle@dbhost02 testdb2]$ srvctl status database -d testdb
Instance testdb1 is not running on node dbhost01
Instance testdb2 is not running on node dbhost02
[oracle@dbhost02 testdb2]$ srvctl start instance -i testdb2 -d testdb
[oracle@dbhost02 testdb2]$ srvctl status database -d testdb
Instance testdb1 is not running on node dbhost01
Instance testdb2 is running on node dbhost02
[oracle@dbhost02 testdb2]$

Make sure all the Database Instances and Services are Up before proceeding next step.

Verify as below:
[oracle@dbhost01 admin]$ srvctl status database -d testdb
Instance testdb1 is running on node dbhost01
Instance testdb2 is running on node dbhost02

[oracle@dbhost01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): dbhost01,dbhost02



Step 13: Find out all the Temporary and Undo Tablespaces in the Database


SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY' and STATUS='ONLINE';

TABLESPACE_NAME
------------------------------
TEMP
PG_TEMP
MG_TEMP
TG_TEMP
TMP

SQL> select tablespace_name from dba_tablespaces where contents='UNDO' and STATUS='ONLINE';

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2


Step 14: Create a script called tbsp_offline.sql script to bring tablespaces 
other than system, sysaux, temp and undo offline: 

cd /home/oracle/DBA/scripts/tde_scripts

$ sqlplus / as sysdba
SQL>set heading off
 SQL>set linesize 150
 SQL>spool tbsp_offline.sql
SQL>select 'alter tablespace '||tablespace_name|| ' offline;' from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','TEMP','PG_TEMP','MG_TEMP','TG_TEMP','TMP','UNDOTBS1','UNDOTBS2','MY_TEMP');
SQL>exit
Now edit the tbsp_offline.sql script to remove all lines other than alter tablespace commands.

(testdb):
-------------------
Don't delete line just comment extra lines as done below:

[oracle@dbhost01 tde_scripts]$ ls -ltr
total 36
-rw-r--r--. 1 oracle oinstall  2181 Apr 17 15:00 tbsp_offline_orig_17Apr2019.sql
-rw-r--r--. 1 oracle oinstall 12025 Apr 17 15:07 datafiles_encrypt_orig_17Apr2019.sql
-rw-r--r--. 1 oracle oinstall  2175 Apr 17 15:21 tbsp_offline.sql
-rw-r--r--. 1 oracle oinstall  2150 Apr 17 15:21 tbsp_online.sql
-rw-r--r--. 1 oracle oinstall 12031 Apr 17 15:23 datafiles_encrypt.sql
[oracle@dbhost01 tde_scripts]$ cat tbsp_offline.sql
--SQL> select 'alter tablespace '||tablespace_name|| ' offline;' from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','TEMP','PG_TEMP','MG_TEMP','TG_TEMP','TMP','UNDOTBS1','UNDOTBS2','MY_TEMP');

alter tablespace USERS offline;
alter tablespace TEST1_TS offline;
alter tablespace TEST2_TS offline;
alter tablespace TEST3_TS offline;
alter tablespace TEST4_TS offline;
alter tablespace TEST5_TS offline;
alter tablespace TEST6_TS offline;


--7 rows selected.

Step 15: Create script for to Encrypt datafiles of Tablespaces

Create a script called datafiles_encrypt.sql containing the commands to encrypt your datafiles,
except system, sysaux, temp and undo.Include all TEMP and UNDO tablespace names from the database in your TESTDB instance, in the exclusion list.

$ sqlplus / as sysdba
SQL>set heading off
 SQL>set linesize 150
 SQL>spool datafiles_encrypt.sql
 SQL>select 'alter database datafile ''' || file_name ||''' encrypt;' from dba_data_files where tablespace_name not in (('SYSTEM','SYSAUX','TEMP','PG_TEMP','MG_TEMP','TG_TEMP','TMP','UNDOTBS1','UNDOTBS2','MY_TEMP');

SQL>exit

Verify the Script.

[oracle@dbhost01 tde_scripts]$ cat datafiles_encrypt.sql
--SQL> select 'alter database datafile ''' || file_name ||''' encrypt;' from dba_data_files where tablespace_name not in --('SYSTEM','SYSAUX','TEMP','PG_TEMP','MG_TEMP','TG_TEMP','TMP','UNDOTBS1','UNDOTBS2','MY_TEMP');
alter database datafile '+DATA/testdb/datafile/user_ts_ts01.dbf' encrypt;                                     
alter database datafile '+DATA/testdb/datafile/test_01_ts.dbf' encrypt;                                           
alter database datafile '+DATA/testdb/datafile/test_02_ts.dbf' encrypt;                                         
alter database datafile '+DATA/testdb/datafile/test_03_ts.dbf' encrypt;                                           
alter database datafile '+DATA/testdb/datafile/test_04_ts.dbf' encrypt;                                           
alter database datafile '+DATA/testdb/datafile/test_05_ts.dbf' encrypt;                                         
alter database datafile '+DATA/testdb/datafile/test_06_ts.dbf' encrypt;   

--7 rows selected.                                   
                                     
                                             
Step 16: Create a script called tbsp_online.sql script to bring Encrypted tablespaces online now 

$ sqlplus / as sysdba
SQL>set heading off
 SQL>set linesize 150
 SQL>spool  tbsp_online.sql 
SQL> select 'alter tablespace '||tablespace_name|| ' online;' from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','TEMP','PG_TEMP','MG_TEMP','TG_TEMP','TMP','UNDOTBS1','UNDOTBS2','MY_TEMP');
--SQL> spool off


alter tablespace USERS online;
alter tablespace TEST1_TS online;
alter tablespace TEST2_TS online;
alter tablespace TEST3_TS online;
alter tablespace TEST4_TS online;
alter tablespace TEST5_TS online;
alter tablespace TEST6_TS online;


--7 rows selected.

[oracle@dbhost01 tde_scripts]$


Step 17: Execute the script for bringing the tablespaces offline

cd /home/oracle/DBA/scripts/tde_scripts

sqlplus "/as sysdba"

SQL> select name from v$database;

NAME
---------
testdb

SQL> @tbsp_offline.sql

Tablespace altered.

Step 18: Execute the script for Encrypting the Datafiles of the tablespaces.

cd /home/oracle/DBA/scripts/tde_scripts

sqlplus "/as sysdba"

SQL> select name from v$database;

NAME
---------
testdb

SQL> @datafiles_encrypt.sql

Database altered.

Note: If we have a large number of datafiles , we can parallelize their encryption by creating sub-scripts  and running the sub-scripts from parallel SQL*Plus sessions.

Step 19: Execute the script for bringing the tablespaces online

cd /home/oracle/DBA/scripts/tde_scripts

sqlplus "/as sysdba"

SQL> select name from v$database;

NAME
---------
testdb

SQL> @tbsp_online.sql

Tablespace altered.


Note: Some tablespaces may take time to show as online. These are probably tablespaces that are encrypted.

*Check the status of tablespace encryption by connecting to SQL*Plus / as sysdba
and running the query shown:

$ sqlplus / as sysdba

SQL>select tablespace_name, encrypted from dba_tablespaces;

Note: Be aware that unless an auto login keystore is created, every time the database is started up the wallet will need to be opened.


Note: In Oracle 12c we can use below command to create auto login

$ sqlplus / as sysdba
$ administer key management create AUTO_LOGIN keystore from keystore "<Wallet Path>" identified by "<Wallet Password>";

Please refer below link for more details on it:

https://oracle-base.com/articles/12c/multitenant-transparent-data-encryption-tde-12cr1


Step 20: Bounce database and listener , verify auto login is working .

Verify the Database , Listener and Do Complete Health check of the Database.
Start the Applications.
Validate Database connectivity with TOAD, SQL Developer and every component is working as expected.
Make sure all the instances and Database services are up & running. If you have DR Database copy the wallet to same location as Primary Database and Sync it and Verify the DR DB is in sync with Primary.

sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 5 17:12:22 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 5167382528 bytes
Fixed Size                  3056376 bytes
Variable Size            1056967944 bytes
Database Buffers         4093640704 bytes
Redo Buffers               13717504 bytes
Database mounted.
Database opened.
SQL> select tablespace_name, encrypted from dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          YES

SQL> SET LINESIZE 200
SQL> COLUMN wrl_parameter FORMAT A50
SQL> SELECT * FROM gv$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE                 /home/oracle/wallet/testdb1 OPEN                                    SINGLE    NO                 0
FILE                 /home/oracle/wallet/testdb2 OPEN 
                   
SQL> select active_state,instance_Name,status from gv$instance;

ACTIVE_ST INSTANCE_NAME    STATUS
--------- ---------------- ------------
NORMAL    testdb1          OPEN
NORMAL    testdb2          OPEN

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE


Enjoy DBA learning and doing tasks....


Thanks,

Rafi

crsctl command to start and stop crs in Oracle RAC Database

Hi DBAs,

We basically have the below command to stop/start crs in Oracle RAC Database.

crsctl command to stop and start the cluster on a specific node noderac1 & noderac2:


We need to login with 'root' or specific sudo user having permissions to run the crs commands.

/u01/app/11.2.0.3/grid/bin/crsctl stop crs
/u01/app/11.2.0.3/grid/bin/crsctl disable crs

/u01/app/11.2.0.3/grid/bin/crsctl enable crs
/u01/app/11.2.0.3/grid/bin/crsctl start crs

where /u01/app/11.2.0.3/grid =$GRID_HOME
 
crsctl command to stop and start on the Clusterware(noderac1 &noderac2) will be

/u01/app/11.2.0.3/grid/bin/crsctl stop cluster -all
 /u01/app/11.2.0.3/grid/bin/crsctl disable crs

/u01/app/11.2.0.3/grid/bin/crsctl enable crs
/u01/app/11.2.0.3/grid/bin/crsctl start resource -all

where /u01/app/11.2.0.3/grid =$GRID_HOME


Enjoy doing RAC tasks...

Thanks,

Rafi

How to check Pre-requistics Patches while applying Database Patch using opatch

Hi DBAs,

Lot of time we apply Database patches using the opatch,It is mandatory to know the Pre-requistics patches for applying the Patch.We can find it out with the below steps.


Step 1:Go to the Directory where patch is copied(PATCH TOP)
>cd /home/oracle/patch/18308717

Step 2:Execute below command
>$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/11.2.0.3/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-07-24_17-52-50PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
node1(TESTDB1)  /home/oracle/patch/18308717
>ls -altr
total 24
-rw-rw-r-- 1 oracle oinstall 5905 May 14 03:35 README.txt
drwxr-xr-x 3 oracle oinstall 4096 May 14 03:35 files
drwxr-xr-x 4 oracle oinstall 4096 May 14 03:35 etc
drwxrw-rw- 3 oracle oinstall 4096 Jul 24 11:30 ..
drwxrwxr-x 4 oracle oinstall 4096 Jul 24 11:30 .
node1(TESTDB1)  /home/oracle/patch/18308717


Step 3:Verify the Log

>vi /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-07-24_17-52-50PM_1.log

[Jul 24, 2014 5:52:50 PM]    PREREQ session
[Jul 24, 2014 5:52:50 PM]    OPatch invoked as follows: 'prereq CheckConflictAgainstOHWithDetail -ph ./ -invPtrLoc /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc '
[Jul 24, 2014 5:52:50 PM]    OUI-67077:
                             Oracle Home       : /u01/app/oracle/product/11.2.0.3/dbhome_1
                             Central Inventory : /u01/app/oraInventory
                                from           : /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc
                             OPatch version    : 11.2.0.3.6
                             OUI version       : 11.2.0.3.0
                             OUI location      : /u01/app/oracle/product/11.2.0.3/dbhome_1/oui
                             Log file location : /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-07-24_17-52-50PM_1.log
[Jul 24, 2014 5:52:50 PM]    Patch history file: /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch_history.txt
[Jul 24, 2014 5:52:50 PM]    Invoking prereq "checkconflictagainstohwithdetail"
[Jul 24, 2014 5:52:51 PM]    Patch 18308717 is not subset of any other patch processed till now
[Jul 24, 2014 5:52:51 PM]    Patch ID  18308717
[Jul 24, 2014 5:52:51 PM]    Patches that conflict: [  ]
[Jul 24, 2014 5:52:51 PM]    Checking conflicts for patch: 18308717
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:18308717
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:17076717
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:17296419
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:16475860
[Jul 24, 2014 5:52:51 PM]    Prereq "checkConflictAgainstOHWithDetail" passed.
[Jul 24, 2014 5:52:51 PM]    Finishing PrereqSession at Thu Jul 24 17:52:51 IST 2014 

Message:Patch 18308717 is not subset of any other patch processed till now 
From the above logs we can confirm that there are no-prerequistics patches to be applied before applying 18308717.


Enjoy doing DBA tasks...

Happy DBA tasks..

Thanks,

Rafi

Killing multiple sessions in Oracle Database

Hi,

We often get task to kill sessions which are active,inactive and the session related with a particular program.When we have one or two sessions we can kill it easily,if we have multiple sessions we can do it as follows:


Step 1:Check the Name of the Database
sqlplus "/as sysdba"

SQL>Select name from v$database;

Step 2:How to kill all inactive session in Database

sqlplus "/as sysdba"

SQL>set heading off

SQL>spool kill12.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
 FROM v$session
 WHERE status ='INACTIVE' and type != 'BACKGROUND';

SQL>spool off

then execute the sql script

SQL> @kill12.sql

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.

How to kill all active session in Database:
sqlplus "/as sysdba"

SQL>set heading off
SQL>spool kill_active.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='ACTIVE' and type != 'BACKGROUND';

SQL>spool off

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.


How to kill all ODI sessions in Database:
sqlplus "/as sysdba"

SQL>set heading off
SQL>set lines 1000
SQL>set linesize 2000
SQL>spool kill_active.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='ACTIVE' AND USERNAME LIKE '%ODI%';

SQL>spool off


Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.


How to get the list of Users and Processes running ODI sessions:

SQL> SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,s.sid,s.serial#,p.spid, s.username, s.program
 FROM   gv$session s
 JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND' AND S.USERNAME LIKE '%ODI%'; 

 How to kill a particular object blocking session:

 1.Find the tables(objects) which are locked:

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id and o.object_name='XX_OBJECT';

2.Killing the session holding the lock:

--Find the serial# for the sessions holding the lock:

SQL> select SERIAL# from v$session where SID=667;

SERIAL#
----------
21091

SQL> alter system kill session '667,21091';

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.

Enjoy DBA tasks...

Happy DBA learning..


Best regards,

Rafi

Reorganization of table in Oracle Database

Hi All,


As we all know reorganization help in better disk space management and performance management in oracle Database,so this task is often done in companies for Tables,Tablespaces and Databases.It helps to reduce the fragmentation( which affects the performance of the Oracle).

Below are the Steps I follow for doing reorganization:

Step 1:Check the last analyzed, tables size before Reorganization:


-- Query 1:Check the last Analyzed and number of rows:
SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 06-JUN-14 NO 71374990


--Query 2 :Check the size of the table

SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 41643 5330304


Note: top -c enter 1 to get the list of cpus =>8 we can give parallel upto 16


ALTER INDEX APPS.XHL_ORDER_TAB_NU5 REBUILD NOLOGGING PARALLEL 12


Note:CUSTOM_SCHEMA,APPS and ORD_SCHEMA are the important schemas used.

Step 2: Get the list of all the INDEXES associated with the tables used for Reorganization.

set lines 2000
set linesize 2000
select 'alter index ORD_SCHEMA.'||index_name||' rebuild NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';


If db is up:
=================

select 'alter index ORD_SCHEMA.'||index_name||' rebuild online NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';


INDEXES NEEDS TO BE REBUILDED:
=================================

--alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild online parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;



Step 3:Make sure to make it noparallel and  logging after indexes are rebuild:

Make it no parallel to release the CPU channels

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';


Step 3:Move the tables to reduce fragmentation.

SQL> spool move_table_10June2014.out
SQL> ALTER TABLE ORD_SCHEMA.ORDER_TAB MOVE;
SQL> spool off

Table altered.



Step 4: Rebuild indexes online using VNC session(vncserver)

We need to rebuild indexes using VNC session or run it in the form of shell scripts using nohup as this operation takes time.

spool rebuild_10Jun2014.out
alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;
spool off


Step 5:Check index status again after rebuild

Check index status again after rebuild,if they are in unusable status for some reason,please rebuild them again and make sure
it comes to VALID Status.



SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

OWNER                          INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
APPS                           ORDER_TAB_NU17      UNUSABLE
ORD_SCHEMA                            ORDER_TAB_F2        UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU3   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N10           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N11           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N12           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_U1            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N6            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_U2            UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU1   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N9            UNUSABLE

OWNER                          INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
CUSTOM_SCHEMA                           XHL_ORDER_TAB_NU4   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N1            UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU2   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N7            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N8            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N2            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N3            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N4            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N5            UNUSABLE
APPS                           XHL_ORDER_TAB_NU5   UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_R1    UNUSABLE

22 rows selected.


To check unusable indexes for table:
==========================================

SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

no rows selected

SQL>




Step 6:Make it no parallel and release the channels:

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';





Step 7:Check the status of INDEX after rebuild
Check the status of INDEX after rebuild,make sure it is in valid state or else rebuild the INDEX again.
SELECT INDEX_NAME||' '||INDEX_TYPE||' '||STATUS
FROM USER_INDEXES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');


Step 8:Run the gather stats for the tables in VNC session:

We run gather statistics to choose the best posible execution plan.

spool gather_stats_tables_TESTDB.out



sqlplus "/as sysdba"

SQL>

Begin DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'ORD_SCHEMA',
tabname => 'ORDER_TAB',
degree => 10,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/


Step 9:Verify the number of rows,statistics ,tables size after reorganization

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 11-JUN-14 NO 71348700

SQL> SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 42534 5444352




Hope it helps.Enjoy DBA tasks...



Thanks,

Rafi

Reorganization of Tables and Indexes in Oracle Database

Hi DBAs,

Reorganization is very useful and important tasks DBAs perform in order to reduce space used by blocks and it also helps in improving the performance of the Oracle Database.It also helps to reduce the fragmentation.

 There are 3 ways to do:

1.Export/Import
2.Alter table Move
3.CTAS method(Create table table_name2 as Select *from table_name1)

 I followed Step 2 of the above ways.

 Below are the Steps I follow for doing reorganization:

Step 1:Check the last analyzed, tables size before Reorganization
Query 1:Check the last Analyzed and number of rows
SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');
Query 2:Check the size of the table before Reorganization
SQL>  SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024 FROM DBA_SEGMENTS
     WHERE SEGMENT_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');

Step 2: Get the list of all the INDEXes associated with the tables used for Reorganization.
SET LINES 2000
SET LINESIZE 2000
SQL> select  OWNER||' '||table_name||' '||INDEX_name from DBA_INDEXES where table_name in ('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');

OWNER||''||TABLE_NAME||''||INDEX_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSADM PS_TEST_WL PSATEST_WL
SYSADM PS_TEST_WL PSBTEST_WL
SYSADM PS_TEST_WL PS_TEST_WL
SYSADM PS_TEST_USERINST PSATEST_USERINST
SYSADM PS_TEST_USERINST PSBTEST_USERINST
SYSADM PS_TEST_USERINST PSCTEST_USERINST
SYSADM PS_TEST_USERINST PSDTEST_USERINST
SYSADM PS_TEST_USERINST PSETEST_USERINST
SYSADM PS_TEST_USERINST PS_TEST_USERINST
SYSADM PS_TEST_STEPINST PSBTEST_STEPINST
SYSADM PS_TEST_STEPINST PSCTEST_STEPINST

OWNER||''||TABLE_NAME||''||INDEX_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSADM PS_TEST_STEPINST PSDTEST_STEPINST
SYSADM PS_TEST_STEPINST PSETEST_STEPINST
SYSADM PS_TEST_STEPINST PSFTEST_STEPINST
SYSADM PS_TEST_STEPINST PSATEST_STEPINST
SYSADM PS_TEST_STEPINST PS_TEST_STEPINST
SYSADM PSWORKLIST PS_PSWORKLIST
SYSADM PSWORKLIST PS0PSWORKLIST
SYSADM PSWORKLIST PSAPSWORKLIST
SYSADM PSWORKLIST PSBPSWORKLIST
SYSADM PS_TEST_TAUTH_AW PS_TEST_TAUTH_AW
SYSADM PS_TEST_TAUTH_AW SYS_IL0000025105C00012$$

OWNER||''||TABLE_NAME||''||INDEX_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSADM PS_TEST_SHEET_AW PSATEST_SHEET_AW
SYSADM PS_TEST_SHEET_AW SYS_IL0000024994C00012$$
SYSADM PS_TEST_SHEET_AW PS_TEST_SHEET_AW
SYSADM PS_TEST_ADV_AW SYS_IL0000024456C00012$$
SYSADM PS_TEST_ADV_AW PS_TEST_ADV_AW

27 rows selected.


Step 3:Move the tables to reduce fragmentation.

SQL> spool move_tables.out
SQL> ALTER TABLE PS_TEST_WL MOVE;

Table altered.

SQL> ALTER TABLE PS_TEST_USERINST MOVE;

Table altered.

SQL> ALTER TABLE PS_TEST_STEPINST MOVE;

Table altered.

SQL> ALTER TABLE PSWORKLIST MOVE;

Table altered.

SQL> ALTER TABLE PS_TEST_TAUTH_AW MOVE;

Table altered.

SQL> ALTER TABLE PS_TEST_SHEET_AW MOVE;

Table altered.

SQL> ALTER TABLE PS_TEST_ADV_AW MOVE;

Table altered.

SQL> spool off


Step 4: Rebuild indexes online

spool rebuild.out

ALTER INDEX PSATEST_WL REBUILD ONLINE;
ALTER INDEX PSBTEST_WL REBUILD ONLINE;
ALTER INDEX PS_TEST_WL REBUILD ONLINE;
ALTER INDEX PSATEST_USERINST REBUILD ONLINE;
ALTER INDEX PSBTEST_USERINST REBUILD ONLINE;
ALTER INDEX PSCTEST_USERINST REBUILD ONLINE;
ALTER INDEX PSDTEST_USERINST REBUILD ONLINE;
ALTER INDEX PSETEST_USERINST REBUILD ONLINE;
ALTER INDEX PS_TEST_USERINST REBUILD ONLINE;
ALTER INDEX PSBTEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSCTEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSDTEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSETEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSFTEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSATEST_STEPINST REBUILD ONLINE;
ALTER INDEX PS_TEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSBPSWORKLIST REBUILD ONLINE;
ALTER INDEX PS0PSWORKLIST REBUILD ONLINE;
ALTER INDEX PSAPSWORKLIST REBUILD ONLINE;
ALTER INDEX PS_TEST_TAUTH_AW REBUILD ONLINE;
ALTER INDEX SYS_IL0000025105C00012$$ REBUILD ONLINE;
ALTER INDEX PSATEST_SHEET_AW REBUILD ONLINE;
ALTER INDEX SYS_IL0000024994C00012$$ REBUILD ONLINE;
ALTER INDEX PSATEST_SHEET_AW REBUILD ONLINE;
ALTER INDEX SYS_IL0000024994C00012$$ REBUILD ONLINE;
ALTER INDEX PS_TEST_ADV_AW REBUILD ONLINE;
spool off

Step 5:Check the status of INDEX after rebuild
Check the status of INDEX after rebuild,make sure it is in valid state or else rebuild the INDEX again.
SELECT INDEX_NAME||' '||INDEX_TYPE||' '||STATUS
FROM USER_INDEXES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');

Step 6:Run the gather stats for the tables.
spool gather_stats_tables_TESTDB.out

BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PS_TEST_WL',
    method_opt =>'for all columns size auto');
END;
/

BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PSWORKLIST',
    method_opt => 'for all columns size auto');
END;
/

BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PS_TEST_USERINST',
    method_opt => 'for all columns size auto');
END;
/

BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PS_TEST_STEPINST',
    method_opt => 'for all columns size auto');
END;
/

BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PS_TEST_TAUTH_AW',
    method_opt => 'for all columns size auto');
END;
/

BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PS_TEST_ADV_AW',
    method_opt => 'for all columns size auto');
END;
/
BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PS_TEST_SHEET_AW',
    method_opt => 'for all columns size auto');
END;
/

spool off

Step 7:Verify the statistics ,tables size after reorganization

SQL>  SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES
  2  WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
----------------------------------------------------------------------------------------------------
SYSADM PS_TEST_ADV_AW 28-NOV-13 NO 1716
SYSADM PS_TEST_SHEET_AW 28-NOV-13 NO 197699
SYSADM PS_TEST_TAUTH_AW 28-NOV-13 NO 128592
SYSADM PSWORKLIST 28-NOV-13 NO 936531
SYSADM PS_TEST_STEPINST 28-NOV-13 NO 292227
SYSADM PS_TEST_USERINST 28-NOV-13 NO 5111123
SYSADM PS_TEST_WL 28-NOV-13 NO 652895

7 rows selected.

SQL>  SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024 FROM DBA_SEGMENTS
  2   WHERE SEGMENT_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024
----------------------------------------------------------------------------------------------------
SYSADM PS_TEST_ADV_AW .25
SYSADM PS_TEST_ADV_AW .125
SYSADM PS_TEST_SHEET_AW 46
SYSADM PS_TEST_SHEET_AW 31
SYSADM PS_TEST_TAUTH_AW 18
SYSADM PS_TEST_TAUTH_AW 4
SYSADM PSWORKLIST 168
SYSADM PS_TEST_STEPINST 18
SYSADM PS_TEST_STEPINST 19
SYSADM PS_TEST_USERINST 365
SYSADM PS_TEST_USERINST 93

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024
----------------------------------------------------------------------------------------------------
SYSADM PS_TEST_WL 88
SYSADM PS_TEST_WL 64

13 rows selected.

Enjoy DBA tasks and Performance tuning.


Best regards,

Rafi.

WARNING: inbound connection timed out ORA-3136

Hi,

Recently I was troubleshootiong for this error,finally resolved.First,let me explain what exactly it mean, the cause of this error,possible method of diagnosis and finally the resolution.

INBOUND_CONNECT_TIMEOUT:
 The INBOUND_CONNECT_TIMEOUT is the parameter used to specify the time, in seconds, for the client to complete its connect request to the listener after the network connection had been established.
i.e The default timeout for a new connection to be established. This setting is called SQLNET.INBOUND_CONNECT_TIMEOUT.

 If the listener does not receive the client request in the time specified, then it terminates the connection.Check the listener.log for details.

In Oracle 9i,the value is set to unlimited and the new value in 10g is 60 seconds.In Oracle 11g also the default value is 60 seconds.

Causes:

1) Some Malicious client connections. 
2)  Connection takes a long time.
3)Database is heavily loaded and cannot process request in allotted time.

Diagnosis:

1)Check the alert log file and check from where the connection comes.
2)Check the listener is up & running.
3)Ping the server,make sure tnsping is working.

Resolution:

To identify the listener name and ORACLE_HOME,we can use the below command.

$ ps -eaf|grep tns
oracle    1643     1  0  2012 ?        04:51:49 /data01/home/oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit
oracle   31682 16935  0 04:54 pts/0    00:00:00 grep tns


Check if the sqlnet.ora file is existing in $ORACLE_HOME/network/admin path,if not create one.

Adjusted the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora and reloaded the the listener configuration:

sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=60
listener.ora: INBOUND_CONNECT_TIMEOUT_LISTENER=60
where:Listener_name=>LISTENER
Default value for these is 60 seconds.

Below are the details:

LSNRCTL>set  INBOUND_CONNECT_TIMEOUT_LISTENER=60
[oracle@hostname admin]$ cat sqlnet.ora
#SQLNET.ORA Network Configuration File: /u01/home/oracle/product/10.2.0/network/admin/sqlnet.ora
#To eliminate inbound connection timeout
SQLNET.INBOUND_CONNECT_TIMEOUT=60

#sqlnet.authentication_services = (NONE)

#SQLNET.EXPIRE_TIME = 0

#SQLNET.ENCRYPTION_SERVER = requested

#SQLNET.ENCRYPTION_CLIENT = requested

NAMES.DIRECTORY_PATH= (TNSNAMES,hostname)
[oracle@s259722ch3el19 admin]$

[oracle@s259722ch3el19 admin]$ lsnrctl reload LISTENER

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-JAN-2013 05:01:25

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.48.1.511)(PORT=1521)))
The command completed successfully

Keep  monitoring - alerts have stopped for now.

If the alerts continous to come,than you have to increase the value of  INBOUND_CONNECT_TIMEOUT as follows:

sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=300
listener.ora: INBOUND_CONNECT_TIMEOUT_LISTENER=300
 where:Listener_name=>LISTENER

 Hope it help.

Happy DBA tasks and troubleshooting.


Best regards,

Rafi.

Oracle RAC 11gR2 crsctl all resource start and stop

Hi DBAs,

Oracle RAC 11gR2 came up with new crsctl command which can be use to start particular resource like asm,listener
grid services or at a time we can start all resource and stop all resources.

Below are the commands to do:


crsctl start resource
   

crsctl start resource -all

crsctl stop resource
   

crsctl stop resource -all


Reference:http://docs.oracle.com/cd/E11882_01/rac.112/e16794/crsref.htm#CHDFEEEG



Hope it helps ...

Enjoy RAC DBA learning....


Best regards,


Rafi.

Killing session in Oracle RAC Database

Hi DBAs,
          Whenever we find some locking issue in RAC Database and Developers requested to kill the session which is blocking and creating lock.,please make sure you are killing the correct session.Below steps can be very much useful.

Step 1:Find the Blocking sessionSQL> SET LINES 1000

SQL> select process,sid,blocking_session,BLOCKING_INSTANCE from gv$session
where blocking_session is not null;  2

PROCESS                         SID BLOCKING_SESSION BLOCKING_INSTANCE
------------------------ ---------- ---------------- -----------------
24822                           1139             5366                 1


1 rows selected.


Step 2:Check the Program which is blocking

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where s.sid=1139;

So ,1139 is the CALC program

Step 3:Find the Session details

SQL> select sid,serial#, INST_ID from gv$session where sid=' 1139';

       SID    SERIAL#    INST_ID
---------- ---------- ----------
      1139      56959          3
     
Step 4:Kill the session immediately

SQL> alter system kill session '1139,56959,@3'  immediate;

System altered.



Hope it helps...

Enjoy Oracle RAC DBA learning...


Best regards,

Rafi.

Creating Oracle 11g Database manually in 11 steps in Unix-based Operating system

Hi,
This is one of the most basic tasks for Oracle DBA,it also helps us to understand how Oracle Database works.Creating Database in 11g is very simple.In 10g you need to create additional directories bdump,cdump,udump instead of diagnostic dump directory.Below are the steps:


Step 1:Create Directory structure with Oracle ownership and permission as below:

[oracle@localhost ~]$ cd /u03

[oracle@localhost u03]$ mkdir testdb

[oracle@localhost u03]$ chmod -R 777 /u03/testdb/*

[oracle@localhost u03]$ chown -R oracle:oinstall /u03/testdb/*

[oracle@localhost testdb]$ mkdir adump diag flash_recovery_area

[oracle@localhost u03]$ chmod -R 777 /u03/testdb/*

[oracle@localhost u03]$ chown -R oracle:oinstall /u03/testdb/*


Step 2:Create Parameter file in $ORACLE_HOME/dbs location:

[oracle@localhost testdb]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ vi init_testdb.ora

db_name='testdb'
memory_target=1G
processes = 150
audit_file_dest='/u03/testdb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u03/testdb/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u03/testdb/diag'
dispatchers='(PROTOCOL=TCP) (SERVICE=testdb)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u03/testdb/ora_control1.ctl,/u03/testdb/ora_control2.ctl)
compatible ='11.2.0'


Step 3:Prepare Create Database script :

[oracle@localhost u03]$ cd /u03/testdb/

[oracle@localhost testdb]$ vi createdb_shaik.sql

CREATE DATABASE testdb
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
LOGFILE
GROUP 1 '/u03/testdb/redo1.log' SIZE 10M,
GROUP 2 '/u03/testdb/redo2.log' SIZE 10M,
GROUP 3 '/u03/testdb/redo3.log' SIZE 10M
DATAFILE
'/u03/testdb/system.dbf' size 200M REUSE
sysaux datafile '/u03/testdb/sysaux.dbf' size 100m
undo tablespace UNDOTBS1
datafile '/u03/testdb/undo1.dbf' size 100m
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE '/u03/testdb/temp01.dbf'
SIZE 100M REUSE
CHARACTER SET AL32UTF8
;
:wq

Step 4:Set the Oracle ENVIRONMENT and SID of Database in the Operating System:

[root@localhost testdb]# su - oracle

[oracle@localhost ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=orcl
export TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

:wq
[oracle@localhost ~]$ . .bash_profile
     This will set the Oracle Environment variables in Unix-based operating system.

[oracle@localhost ~]$export ORACLE_SID=testdb
     This will set the SID of the current Database in Unix-based operating system.


Step 5:Create the Password file.

[oracle@localhost ~]$orapwd file=$ORACLE_HOME/dbs/orapwtestdb password=Oracle entries=10


Step 6:Create server parameter file.

[oracle@localhost dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 28 14:08:02 2012

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

Connected to an idle instance.

SQL>create spfile from pfile='$ORACLE_HOME/dbs/init_testdb.ora';


step 7:Start the Database in nomount State.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
SQL> select status from v$instance;

STATUS
------------
STARTED

Note:Common issue memory_target not supported,refer the below link for resolving:

http://rafioracledba.blogspot.in/2011/06/ora-00845-memorytarget-not-supported-on.html

Step 8:Execute Create Database script created in Step 3

SQL> @/u03/testdb/createdb_shaik.sql

Database created

Step 9:Execute the catalog.sql,catproc.sql and pupbld.sql scripts:
a)catalog.sql =>Creates dictionary tables and views
b)catproc.sql =>Creates PL/SQL procedures,functions and packages necessary.
c)pupbld.sql  =>Creates user profiles.


So our database is created. Now just run the catalog.sql,catproc.sql and pupbld.sql scripts.
WE will find catalog.sql and catproc.sql in $ORACLE_HOME/rdbms/admin path and pupbld.sql in $ORACLE_HOME/sqlplus/admin path.

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
conn system/manager

SQL>@$ORACLE_HOME/sqlplus/admin/pupbld.sql

SQL> alter user system identified by manager;

User altered.

SQL> conn system
Enter password:
Connected.
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


  DATE_VALUE FROM PRODUCT_USER_PROFILE
                  *
ERROR at line 3:
ORA-00942: table or view does not exist


DROP TABLE PRODUCT_USER_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

DROP TABLE PRODUCT_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist



View created.


Grant succeeded.

DROP PUBLIC SYNONYM PRODUCT_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist



Synonym created.

DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist



Synonym created.

DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.


Step 10:Verify the Dictionary views created.


SQL> select name from v$database;


Step 11:Change the Database mode from noarchive log to archive log mode
Changing to archive log mode:

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


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           3
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
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   3
Current log sequence           3

This completes the manual Database creation on Unix based Operating system....

Enjoy learning Oracle DBA....


Best regards,

Rafi.

Working with FLASHBACK in oracle Database

FLASHBACK in Oracle DATABASE:
=============================

  Flashback is nothing but going to previous state,same case with Oracle
Database also,here we go to a previous state of Database.

Advantages and Applications of Flashback:
--------------------------------------------

1)Useful in recovering the lost data.
2)Useful in saving time and system resources.
2)Usefull in performing Data testing.

Disadvantages:
---------------

1)Additional Overhead on Database


prerequisites:
---------------

prerequisite 1: Configure FRA(Flash Recovery Area)

    SQL> alter system set db_recovery_file_dest_size=1g;

    System altered.

    SQL> alter system set db_recovery_file_dest=’/oracle/test_flash’;

    System altered.

Note: If it is a RAC database the FRA should point to the shared storage.
Ensure you estimate appropriately db_recovery_file_dest_size and have enough space.
 STORAGE_SIZE column from V$RESTORE_POINT can help you for that.

prerequisite 2:The Oracle database should be in archivelog

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

Note:Below steps can be use to switch Database to archive log mode.

    [oracle@orclbox ~]$ sqlplus /nolog

    SQL*Plus: Release 11.2.0.2.0 Production

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

    SQL> conn / as sysdba
    Connected.
    SQL> alter system set log_archive_dest_1=’location=/oracle/test_flash’;

    System altered.

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

    Total System Global Area 1653518336 bytes
    Fixed Size                  2227032 bytes
    Variable Size            1275069608 bytes
    Database Buffers          369098752 bytes
    Redo Buffers                7122944 bytes
    Database mounted.
    SQL> alter database archivelog;

    Database altered.

    SQL> alter database open;

    Database altered.

    SQL> archive log list
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /oracle/test_flash
    Oldest online log sequence     2
    Next log sequence to archive   4
    Current log sequence           4
    SQL>


prerequisite 2:On the Flashback in Database

SQL> alter database flashback on;

Database altered.

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON

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

ARCHIVELOG   YES

Note:To off the flashback use the below statement.

SQL> alter database flashback off;

Database altered.

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON

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

ARCHIVELOG   NO



Example 1: Let us consider one eg.of restoring a table to a particular scn(system change number):

Below i'm creating one 'test' user with some test data,than I will check the scn after committing the changes,than I will delete the data and finally I will restore that table using flashback scn technique.

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded


 SQL> insert into test.flashback_test values(1);

    1 row created.

    SQL> insert into test.flashback_test values(2);

    1 row created.

    SQL> insert into test.flashback_test values(3);

    1 row created.

    SQL> commit;

    SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
----------- ---------------------------------------------------------------------------
     931892 28-JUL-12 09.49.40.000000000 AM


    SQL> delete from test.flashback_test;

    3 rows deleted.

    SQL> commit;

    Commit complete.

    SQL> select * from test.flashback_test;

    no rows selected


Restore of table flashback_test using flashback database:
=====================================================

 step 1:Shut down the instances of Database

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

Step 2:Bring the Database in mount state
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.

Step 3:Use flashback database command to go to scn when table was dropped.

If  you want to revert the database to SCN=930717 where flashback_test table has 3 rows

SQL> flashback database to scn 930717;

Flashback complete.

    SQL> alter database open resetlogs;
--set log sequence no. and the database will be synchonized.

Verify the Data:

    SQL> select * from flashback_test;

    COL1
    ———-
    1
    2
    3

Example 2:Flashback to timestamp

Lets say you have to drop some table on a particular day and the user know the time when he had dropped that table,than we can use the below steps to recover that table using flashback technique.

flashshback to timestamp:
======================

step 1:Shut down the instances of Database

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

Step 2:Bring the Database in mount state
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.

step 3: flashback to timestamp 28-JUL-12 09.49.40

SQL>flashback database to timestamp TO_TIMESTAMP('2012-07-28 09:49:40','YYYY-MM-DD HH24:MI:SS');

    Flashback complete.

    SQL> select * from test.flashback_test;

    COL1
    ———-
    1
    2
    3

Example 3: Enable recyclebin and get back the lost data.

Step 1:Enable recycle bin when the Database is in open state.

SQL>alter session enable recyclebin=on;

Step 2:Recover lost table using below command.
 
    SQL> FLASHBACK TABLE test.flashback_test TO BEFORE DROP;

    Flashback complete.

    SQL> select * from test.flashback_test;

    COL1
    ———-
    1
    2
    3

Example 4:Create guaranteed restore point and restore that restore point:
========


How we can restore the entire data which is lost in a particular period,this can be use to test data also.
 This is very vital feature of Oracle flashback.

Create Restore point:
=====================

Step 1:Create restore point 'test_rest1':

    SQL> create restore point test_rest1 guarantee flashback database;

    Restore point created.

    * To view the restore points:

    SQL> select NAME,SCN,TIME,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
         from V$RESTORE_POINT
         where GUARANTEE_FLASHBACK_DATABASE='YES';


SQL> select NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
         from V$RESTORE_POINT
         where GUARANTEE_FLASHBACK_DATABASE='YES';  2    3

NAME
--------------------------------------------------------------------------------
       SCN
----------
TIME                                                                        GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST_REST1
    930537
28-JUL-12 08.57.51.000000000 AM                                             YES
           0


NAME
--------------------------------------------------------------------------------
       SCN
----------
TIME                                                                        GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST_REST2
    930660
28-JUL-12 09.02.54.000000000 AM                                             YES
    30203904


NAME
--------------------------------------------------------------------------------
       SCN
----------
TIME                                                                        GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST1
    932549
28-JUL-12 10.30.01.000000000 AM                                             YES
     8192000

Restore Restore point:
=======================

step 1:shut the database

SQL> shut immediate
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down

step 2:Startup in mount state

SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.


step 3:flashback Database to restore point 'test1':

SQL> FLASHBACK database TO RESTORE POINT test1;

Flashback complete

Note:To restore a table below command can be used.
FLASHBACK TABLE emp TO RESTORE POINT test1;


step 4:Open the Database with resetlogs

SQL> alter database open resetlogs;

Database altered

step 6:Verify the data

SQL> select *from test.flashback_test;

      COL1
----------
         1
         2
         3

Step 7:Monitor flashback  v$flashback_database_log

    SQL> select oldest_flashback_scn from v$flashback_database_log;

OLDEST_FLASHBACK_SCN
--------------------
              930314

Note: Dropping restore point.

SQL> DROP RESTORE POINT TEST_REST1;

Restore point dropped.

Note:
Possible flashback options available are:

SQL>flashback table test.flashback_test to (SYSDATE-1/24);

-- be sure to substitute your SCN
FLASHBACK DATABASE TO SCN 19513917;
or
FLASHBACK DATABASE TO RESTORE POINT bef_damage;

/*
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);

FLASHBACK DATABASE TO TIMESTAMP timestamp'2002-11-05 14:00:00';

FLASHBACK DATABASE
TO TIMESTAMP to_timestamp('2002-11-11 16:00:00','YYYY-MM-DD HH24:MI:SS');
*/


Offcourse,we do have flashback query,flashback transaction query,but above are very useful.


Hope it helps....

Enjoy DBA tasks and practice...



Best regards,

Rafi.

Manually Installing Oracle Data Mining in Oracle 11g

Hi,
If you compare Oracle 11g and previous versions there are lot of changes in terms of installing Data Mining component,
For installing check the MY ORACLE SUPPORT note :
How To Manually Install Data Mining In Oracle 11g? [ID 818314.1].

Information about data mining:
The brief information about Oracle Data mining can be obtained from the below link:

http://docs.oracle.com/html/B14339_01/1intro.htm

In short,If you do the Oracle installation with Enterprise Edition installation type ,it selects Oracle Data Mining option by default.In Oracle Database 11g,the Data Mining metadata is created with 'SYS' metadata when you select the Create Database option.

For verifying the installation of Oracle Data Mining:
Check if the parameter DATA MINING is set to TRUE,if it is than Oracle Data Mining component is already installed in the Database.The V$OPTION is very useful for checking the various functionality exist in your Database or not as seen from the below results:

SQL>SET LINESIZE 1000
SQL>SELECT *FROM V$OPTION;

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
Bit-mapped indexes TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Parallel execution TRUE
Parallel load TRUE
Point-in-time tablespace recovery TRUE
Fine-grained access control TRUE
Proxy authentication/authorization TRUE
Change Data Capture TRUE
Plan Stability TRUE
Online Index Build TRUE
Coalesce Index TRUE
Managed Standby TRUE
Materialized view rewrite TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Materialized view warehouse refresh TRUE
Database resource manager TRUE
Spatial TRUE
Automatic Storage Management FALSE
Export transportable tablespaces TRUE
Transparent Application Failover TRUE
Fast-Start Fault Recovery TRUE
Sample Scan TRUE
Duplexed backups TRUE
Java TRUE
OLAP Window Functions TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Block Media Recovery TRUE
Fine-grained Auditing TRUE
Application Role TRUE
Enterprise User Security TRUE
Oracle Data Guard TRUE
Oracle Label Security FALSE
OLAP TRUE
Basic Compression TRUE
Join index TRUE
Trial Recovery TRUE
Data Mining TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Online Redefinition TRUE
Streams Capture TRUE
File Mapping TRUE
Block Change Tracking TRUE
Flashback Table TRUE
Flashback Database TRUE
Transparent Data Encryption TRUE
Backup Encryption TRUE
Unused Block Compression TRUE
Oracle Database Vault FALSE
Result Cache TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
SQL Plan Management TRUE
SecureFiles Encryption TRUE
Real Application Testing TRUE
Flashback Data Archive TRUE
DICOM TRUE
Active Data Guard TRUE
Server Flash Cache TRUE
Advanced Compression TRUE
XStream TRUE
Deferred Segment Creation TRUE


PRIVILEGE REQUIRED FOR DATA MINING SCHEMA:

Let us say,If you want to use Data Mining feature to a specific user,than we have to grant CREATE MINING MODEL privilege to that user.

SQL> conn dmuser
Enter password:
Connected.
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
CREATE MINING MODEL

11 rows selected.



Happy Oracle DBA tasks...


Best regards,

Rafi.