Oracle Bullets

2017-01-07T19:09:00.003+05:30

2017-01-07T19:09:00.001+05:30

Change password using BRCONNECT

2015-09-11T15:19:00.000+05:30

Change password - BRCONNECT

To change the schema password using BRCONNECT an SAP utility to administer the oracle database,

Please execute the following command.

brconnect -u / -c -f chpass -o system -p manager

-u is user to connect to system (/ is root as sysdba)

-c is used to force the action

-f is function, chpass is change password

-o is the schema user

-p is the new password that needs to be set.

Cold backup

2015-09-11T14:48:00.003+05:30

How to take a cold backup

$> cd /oracle/backup/SID

$> mkdir -p /oracle/backup/SID/log

create the pfile from the current spfile.

$> sqlplus / as sysdba

SQL> alter system checkpoint;
SQL> shutdown immediate;
SQL> startup mount;
SQL> create pfile='/oracle/backup/SID/pfile`date +%d%m%Y`.ora' from spfile;

Create the backup script

Database needs to be put in mount state.

While backing up the database backup the current control file as well to disk seperately.

$> vi RMAN_cold_backup.sh

export ORACLE_HOME=/oracle/product/11.2.0
export ORACLE_SID=SID
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
rman target / log=/oracle/backup/SID/log/SID`date +%d%m%Y`.log < RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/oracle/backup/SID/SID_%U’;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT ‘/oracle/backup/SID/SID_%U’;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT ‘/oracle/backup/SID/SID_%U’;
BACKUP AS COMPRESSED BACKUPSET DATABASE;
BACKUP CURRENT CONTROLFILE FORMAT ‘/oracle/backup/SID/cntrl_%s_%p_%t’;
RELEASE CHANNEL disk1;
RELEASE CHANNEL disk2;
RELEASE CHANNEL disk3;
}

Change the permission of the file RMAN_cold_backup.sh to have execute permissions.

navigate to the directory which have the script

cd /oracle/backup/SID

./RMAN_cold_backup.sh

once the script completes sucessfully, you can open the database.

$> sqlplus / as sysdba

SQL> alter database open;

Database Opened.

SQL> exit

Drop oracle database

2015-09-11T14:48:00.002+05:30

How to Drop Oracle Database

If you want to drop your database it is wise to take an offline backup before you drop your database. if incase it is required to restore the database back we can use this backup.

Cold Backup

Check if database is up and running

$> ps -ef | grep pmon

if this returns a process with the database sid then the DB is running.

login to sqlplus as sysdba

$> sqlplus / as sysdba

shutdown the database to start it in restrict mode

SQL> shut immediate;

put the database in mount restrict mode

SQL> startup restrict mount;

ORACLE instance started.

Database mounted.

issues the following command to drop the database.

SQL> drop database;

Database dropped.

SQL> exit

Standby Roll forward

2015-09-08T12:05:00.000+05:30

When there is a big archivelog gap that has to be applied on the standby database, then it is wise to have to go ahead with a roll forward of the standby.

To get the standby sync with the primary please follow the steps below.

On standby database

SQL> alter database recover managed standby database cancel;

SQL> select current_scn from v$database;

-- If there are no READ ONLY Tablespaces/datafiles in database use the below query to find the current SCN number.
SQL> select min(fhscn) from x$kcvfh;

-- If there are any READ ONLY tablespaced/datafiles in database use the below query to find the current SCN number
SQL> select min(f.fhscn) from x$kcvfh f, v$datafile d
where f.hxfil =d.file#
and d.enabled != 'READ ONLY' ;

If the output from v$database view and x$kcvfh are different please proceed by taking the SCN number from X$KCVFH view.

Now open a new session of the primary database in a new window.

On Primary database

Go to RMAN to take an incremental backup of the database from the SCN number noted in the standby database.

rman target /

RMAN> run {
2> allocate channel C1 device type disk;
3> backup as compressed backupset incremental from SCN 1966714724 database format '/oracle/backup/tostdby_%U' filesperset=10 tag 'FOR STANDBY';
4> release channel C1;
5> }

Take the control file backup for standby database.

rman> backup current controlfile for standby format '/oracle/backup/cntrltostdby_%U' tag 'FOR STANDBY';

exit from RMAN

navigate to the folder in which the backups are taken

$> cd /oracle/backup

copy the files over to the standby database server using the scp command (or by any command that serves the purpose)

$> scp to* oracle@hostname:/oracle/backup
$> scp cntrl* oralce@hotname:/oracle/backup

once the files are copied over to the standby database server. close the primary session.

On standby database server

Remove the control files

rm /oracle/oradata/cntrl/cntrl*
rm /oracle/cntrl1/cntrl*
rm /oracle/cntrl2/cntrl*

login to sql prompt to shutdown the database and start in nomount to restore the control file in respective locations from the backup taken in primary database server.

sqlplus / as sysdba

SQL> shutdown immediate;

SQL> startup nomount

SQL> exit

$> rman target /

RMAN> restore standby controlfile from '/oracle/backup/cntrlforstandby_rdqffbhg_1_1';

RMAN> sql 'alter database mount standby database';
RMAN> sql 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL';

Catalog the backup pieces to the RMAN

RMAN> catalog backuppiece '/oracle/backup/forstandby_qnqff9e6_1_1';
RMAN> catalog backuppiece '/oracle/backup/forstandby_qoqff9tb_1_1';
RMAN> catalog backuppiece '/oracle/backup/forstandby_rbqffalj_1_1';
RMAN> catalog backuppiece '/oracle/backup/forstandby_rcqffb96_1_1';

RMAN> sql 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL';

RMAN> run{
2 allocate channel C1 device type disk;
3 recover database noredo;
4 release channel C1;
5 }

RMAN> sql 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION';

RMAN> exit

Once every thing is done successfully, please login to the DB and see if the current archivelogs are being applied.

$> sqlplus / as sysdba

SQL> select max(sequence#) from v$archived_log;
SQL> set lines 200;
SQL> select dest_id,status,error from v$archive_dest;
SQL> select PROCESS,PID,STATUS,THREAD#,SEQUENCE# from v$managed_standby;

Change DB ID in oracle

2015-08-19T16:13:00.002+05:30

There are two ways to change the DB ID.

The DB ID is the unique identifier of a DB. This is a randomly generated number that is assigned to the database at the time of creation. typically this would be a 10 digit number.

If there are scenarios to change the DB ID then please follow any of the two ways discussed below.

Case 1:
-------

Usage of nid utility which is in $ORACLE_HOME/bin location
-----------------------------------------------------------
DB Name - TEST

Login to sqlplus as sysdba

$> sqlplus / as sysdba

SQL>select dbid form v$database; -- to check the current DB ID of the database.

SQL>shutdown immediate

start the database in mount state so that the control file is read and all the other datafiles are read.

SQL> startup mount;

SQL> exit

on the OS command prompt execute the following command.

$>nid target=TEST

It will prompt for password. password is the sys account's password.

password - sys password

Once the password is accepted it will ask whether to change the DB ID or not. Press 'Y'

change database ID of database DBNAMR (Y/[N]) => Y

proceeding with operation
..........
.........
.
.

DBNEWID- completed successfully.

Here you will see that the DB ID is modified in control files and changed changed in datafiles.

Also, all the previous backups and archivelog backups will be unusable.

connect to sqlplus

sqlplus / as sysdba

open the database using reset logs as the DBID is changed the DB must be opened using resetlogs

SQL> alter database open resetlogs;

SQL> select dbid form v$database; -- now a new ID is assigned to the database.

SQL> shutdown immediate;

SQL> startup;

The DB ID is changed.

Take a full backup of the database and archivelogs if any.
-------------------------
Case 2:
------------------------

Using dbms_backup_restore pacakge.

Login to sqlplus

$> sqlplus / as sysdba

SQL> select dbid from v$database; -- To note the current DB ID

Take the control file to trace backup

sql> alrer database backup controlfile to trace as '/home/oracle/trace.sql' reuse; -- reuse will be used to replace the existing trace files if any on the location.

SQL> shutdown immediate;

SQL> exit

open the trace file and remove all the commented lines and -------------------------------------------------------

$> remove reuse instead put set also remove noresetlogs put resetlogs in create controlfile statement

sqlplus / as sysdba

SQL> startup mount;

SQL> select name,dbid form v$database;

SQL> execute dbms_backup_restore.zeroDbid(0);

PL/SQL procedure successfully completed.

SQL> select name,dbid form v$database; - output comes as is becuase its still in dynamic performance views

SQL> shutdown immediate;

SQL> exit

$> delete all the control files

sqlplus / as sysdba

SQL> @/home/oracle/trace.sql

controlfile created,

SQL> alter database open resetlogs;

database altered

SQL> select name,dbid from v$database;

DB is will be changed.

Take a full backup of the database and archivelogs if any.

Snapshot standby database Oracle 11g dataguard new feature.

2010-10-07T14:20:00.000+05:30

Snapshot Standby Database

Oracle 11g introduces a new Feature in Dataguard – the Snapshot Standby Database which can be in read-write mode. A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database.

As this database is in the read-write mode it is possible to process transactions apart from the primary database (used for Real Application Testing). It still maintains protection by archiving the logs that are shipped from the primary database.

Characteristics of snapshot standby database:

1. Snapshot standby database receives the logs but does not apply the redo data.

2. Redo data received from the primary database is applied automatically once the standby database is converted back into a physical standby database.

3. All updates that were made in the snapshot standby database will be discarded when it is converted back to physical standby database.

4. Snapshot standby database cannot be the candidate for a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition.

5. After a switchover or failover between the primary database and one of the physical or logical standby databases in a configuration, the snapshot standby database can receive redo data from the new primary database after the role transition.

6. Snapshot standby database cannot be the only standby database in a Maximum Protection Data Guard configuration.

Steps to Convert Physical Standby Database to the Snapshot Standby Database

The conversion from physical standby to snapshot standby database and vice versa can be done in three ways.

1. Dataguard Broker.

2. Enterprise Manager.

3. Manual method. This can be done by issuing the below SQL command in the physical standby database.

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

1. If flash recovery is not already configured, configure it as shown below:

first set the size of the recovery area

SQL> alter system set db_recovery_file_dest_size=’xxxx’;

2. Next, set the location

SQL> alter system set db_recovery_file_dest=’yyyy’;

3. Bring the physical standby database to mount stage:

4. Stop managed recovery if it is active and then convert the physical standby database to snapshot standby database

SQL> alter database convert to snapshot standby;

The database is dismounted during conversion and must be restarted after the conversion completes.

The database is now ready for transactions.

When a physical standby database is converted into a snapshot standby database, an implicit restore point is created and this restore point is used to flashback a snapshot standby database to its original state when it is converted back into a physical standby database from a snapshot standby database.

5. Steps to Convert the Snapshot Standby Database Back to the Physical Standby Database:

1. Shutdown the snapshot standby database

2. Bring the database to the mount state

3. Issue the following command

SQL> alter database convert to physical standby;

6. Shutdown the database and mount it again

SQL> select open_mode, database_role from v$database;

You are now ready to start the media recovery process.

Once a snapshot standby database has been converted back into a physical standby database and restarted – start the process of applying the redo logs and all of the redo received by the snapshot standby database will be applied to the physical standby database to roll it forward.

Manual cleanup of failed CRS installation (10gR2)

2010-08-25T09:49:00.005+05:30

Manual cleanup of CRS installation

At times we will have to perform a manual cleanup of failed CRS installation or some old CRS installation that was done.

Oracle provides two scripts to cleanup the CRS, The names and location of the scripts are given below.

1) Run rootdelete.sh script located at ‘$ORA_CRS_HOME/install’ directory

2) Run rootdeinstall.sh script located at ‘$ORA_CRS_HOME/install’ directory

If the CRSHOME is deleted before running the above mentioned scripts then, one can follow the following steps to cleanup the CRS manually.

Note: The locations used in the following cleanup process are based on the LINUX platform, the locations may slightly differ with the corresponding flavor of the OS.

Manual Cleanup of CRS on LINUX

1. Remove below mentioned files as root user on ALL NODES that form a CLUSTER

rm /etc/oracle/*

rm -f /etc/init.d/init.cssd

rm -f /etc/init.d/init.crs

rm -f /etc/init.d/init.crsd

rm -f /etc/init.d/init.evmd

rm -f /etc/rc2.d/K96init.crs

rm -f /etc/rc2.d/S96init.crs

rm -f /etc/rc3.d/K96init.crs

rm -f /etc/rc3.d/S96init.crs

rm -f /etc/rc5.d/K96init.crs

rm -f /etc/rc5.d/S96init.crs

rm -Rf /etc/oracle/scls_scr

rm -f /etc/inittab.crs

cp /etc/inittab.orig /etc/inittab

2. If the EVM, CRS and CSS processes are not already down then kill them off as root user on ALL NODES

ps -ef | grep evm

kill 'evm_pid'

ps -ef | grep crs

kill 'crs_pid'

ps -ef | grep css

kill 'css_pid'

3. Remove the files in /var/tmp/.oracle or /tmp/.oracle on ALL NODES

rm -f /var/tmp/.oracle/*

or

rm -f /tmp/.oracle/*

4. Make sure that you remove the ocr.loc at /etc/oracle on ALL NODES

5. De-install the CRS home using the Oracle Universal Installer; this is to remove the CRS home from inventory. Installer may not be able to remove all the files, because CRS home is already deleted, but this will help in cleaning up the inventory.

6. Remove the CRS install location on ALL NODES

7. Remove OCR and Voting disk from shared location from any 1 node

Once these steps are performed then the hosts are ready for the next CRS installation.

Data guard broker

2010-08-18T14:49:00.000+05:30

Data Guard Broker

Data guard broker is an assistance utility given along with the Oralce Software, which can be used to create and manage the data guard configurations. Data guard broker consists of server side and client side components.

The server side components include dataguard monitor (DMON) and configuration files. The client side components include Data guard manager and Command line interface (CLI).

Data guard broker manages a data guard configuration using a broker management model. The broker management model is a hierarchial structure comprised of configuration,site and database resources. Broker can manage all three layers of the management model.

Dataguard broker can manage the proimary database and upto nine (9) standby databases in one configuration (in Oacle 10g). These nine standby database can be a mixture of physical and logical standby databases. Standby databases can be created using data guard broker. In addition, it can be used to add an existing standby database to a configuration.

When a standby database is created using Data guard broker, broker takes care of the entire set of supportinf files such as SPFILE, oracle net configuration files, etc., if in case an existing standby database is to be added to the data guard configuration, the SPFILE and oracle net files must be explicitly configured manually before the standby database is added to a configuration. If data guard broker is to be used for the management of standby databases, SPFILE must be used on all the databases.

Some of the advantages of using dataguard broker are listed:

1. It is a centralized management tool that can be used to manage the data guard configuration using a CLI / GUI interface.

2. It reduces the complexity of role management services (Switchover and Failover).

3. It provides an extensive health check mechanism for both the primary and standby databases.

4. It can be used to gather useful statistics for fine tuning the log transfer and log appky services.

In oracle 9i Dataguard broker cannot be used with Oracle Real Application Cluster (RAC). RAC support is provided with Oracle 10g.

Obsolete Parameters for 9i

2010-08-17T11:20:00.000+05:30

The following are the obsolete parameters for 9i

*1 )* _average_dirties_half_life	*2 )* _lm_statistics
*3 )* allow_partial_sn_results	*4 )* always_anti_join
*5 )* always_semi_join	*6 )* arch_io_slaves
*7 )* b_tree_bitmap_plans	*8 )* backup_disk_io_slaves
*9 )* cache_size_tdreshold	*10 )* cleanup_rollback_entries
*11 )* close_cached_open_cursors	*12 )* compatible_no_recovery
*13 )* complex_view_merging	*14 )* cpu_count
*15 )* db_block_checkpoint_batch	*16 )* db_block_lru_extended_statisti
*17 )* db_block_lru_latches	*18 )* db_block_lru_statistics
*19 )* db_block_max_dirty_target	*20 )* db_file_simultaneous_writes
*21 )* delayed_logging_block_cleanout	*22 )* discrete_transactions_enabled
*23 )* distributed_lock_timeout	*24 )* distributed_recovery_connectio
*25 )* distributed_transactions	*26 )* dblink_encrypt_login
*27 )* fast_full_scan_enabled	*28 )* freeze_DB_for_fast_instance_re
*29 )* gc_defer_time	*30 )* gc_latches
*31 )* gc_lck_procs	*32 )* gc_releasable_locks
*33 )* gc_rollback_locks	*34 )* hash_multiblock_io_count
*35 )* instance_nodeset	*36 )* job_queue_interval
*37 )* job_queue_keep_connections	*38 )* large_pool_min_alloc
*39 )* lgwr_io_slaves	*40 )* lm_locks
*41 )* lm_procs	*42 )* lm_procs
*43 )* lm_ress	*44 )* lock_sga_areas
*45 )* log_block_checksum	*46 )* log_files
*47 )* log_simultaneous_copies	*48 )* log_small_entry_max_size
*49 )* log_archive_buffers	*50 )* log_archive_buffer_size
*51 )* ogms_home	*52 )* ops_admin_group
*53 )* ops_interconnects	*54 )* optimizer_percent_parallel
*55 )* optimizer_search_limit	*56 )* parallel_default_max_instances
*57 )* parallel_min_message_pool	*58 )* parallel_server_idle_time
*59 )* parallel_transaction_resource_	*60 )* push_join_predicate
*61 )* row_cache_cursors	*62 )* sequence_cache_entries
*63 )* sequence_cache_hash_buckets	*64 )* shared_pool_reserved_min_alloc
*65 )* snapshot_refresh_interval	*66 )* snapshot_refresh_keep_connecti
*67 )* snapshot_refresh_processes	*68 )* sort_direct_writes
*69 )* sort_multiblock_read_count	*70 )* sort_read_fac
*71 )* sort_spacemap_size	*72 )* sort_write_buffer_size
*73 )* sort_write_buffers	*74 )* spin_count
*75 )* temporary_table_locks	*76 )* text_enable
*77 )* use_ism

RAC User Equivalence - RSA and DSA

2010-07-21T22:07:00.000+05:30

Why to use both the RSA and DSA while configuring SSH for User Equivalence?

There are two versions of the SSH protocol; version 1 uses RSA and version 2 uses DSA, so we will create both types of keys to ensure that SSH can use either version. The ssh-keygen program will generate public and private keys of either type depending upon the parameters passed to it.

Most SSH clients support both SSH protocols (SSH1 and SSH2). SSH2 is regarded as a more secure protocol, so many users like to make certain it is used first, before any connection using SSH1 is attempted (this is in case the remote server doesn't support your primary protocol selection, it can fall back to the other). It is highly recommended that users should configure their clients for the SSH2 protocol for the first connection attempt.

* The SSH1 protocol supports RSA keys.
* The SSH2 protocol supports RSA and DSA keys.

In order to make sure that the connection is success, we use both RSA and DSA while configuring SSH for user equivalence.

Usage for RSA:

ssh-keygen -t rsa

Usage for DSA:

ssh-keygen -t dsa

Basic Tips for Tuning SQL Statement

2009-07-29T15:32:00.000+05:30

Subquery versus Joins:

The main difference between subquery and join is:

• subquery is faster when we have to retrieve data from large number of tables. Because it becomes tedious to join more tables. Join is faster to retrieve data from database when we have less number of tables
• subquery retrieve the data depending on certain condition or manipulation in inner query. Where as joins will join the entire data depending on the conditions.

While using Order by Operator:

• Don't use ORDER BY in your SELECT statements unless you really need to, as it adds a lot of extra overhead.

While Using AND operator:

If you have a WHERE clause that includes expressions connected by two or more AND operators, Oracle Server will evaluate them from left to right in the order they are written.
Locate the least likely true AND expression first. This way, if the AND expression is false, the clause will end immediately, saving time. If both parts of an AND expression are equally likely being false, put the least complex AND expression first.

Using Where Clause:

• When you have a choice of using the IN or the EXISTS clause in your SQL, Use the EXISTS clause, as it is usually more efficient and its performance is good.
• If you use LIKE in your WHERE clause, try to use one or more leading character in the clause, if at all possible For example, use: LIKE 'm%' not: LIKE '%m‘.
• If you use a leading character in your LIKE clause, then the Query Optimizer has the ability to potentially use an index to perform the query.
• If the leading character in a LIKE clause is a wildcard, the Query Optimizer will not be able to use an index, and a table scan must be run, which will reduce performance and will take more time.
• When you have a choice of using the IN or the BETWEEN clauses in your SQL, use the BETWEEN clause, as it is much more efficient.

• For example:

SELECT emp_id, emp_name FROM employee WHERE emp_id IN (1000, 1001, 1002, 1003, 1004);

is much less efficient than this:

SELECT emp_id, emp_name FROM employee WHERE emp_id BETWEEN 1000 and 1004;

If your SELECT statement includes an IN operator along with a list of values to be tested in the query :-

• Order the list of values so that the most frequently found values are placed at the first of the list, and the less frequently found values are placed at the end of the list.
• This can speed performance because the IN option returns true as soon as any of the values in the list produce a match. The sooner the match is made, the faster the query completes.

Returning data from Select Statement:

• Do not return the column data that you do no need in you queries.
• You should avoid using SELECT * when returning data from tables.
• Select only the columns that you need. For example, you should not use SELECT * to return all the columns from a table if you don't need all the data from each column.
• In addition, using SELECT * prevents the use of covered indexes, further potentially hurting query performance.

Using the UNION statement:

• The UNION statement performs equivalent of a SELECT DISTINCT on the final result set.
• UNION takes the results of two recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. Example :-

SELECT column_name1, column_name2 FROM table_name1 WHERE column_name1 = some_value
UNION
SELECT column_name1, column_name2 FROM table_name1 WHERE column_name2 = some_value;

• You can write the same query as:

SELECT DISTINCT column_name1, column_name2 FROM table_name1 WHERE column_name1 = some_value OR column_name2 = some_value

It will boost the performance of your sql queries.

ORACLE Recyclebin

2009-07-15T15:07:00.000+05:30

There are two recyclebin views present in ORACLE :
1. USER_RECYCLEBIN and
2. DBA_RECYCLEBIN.
For convenience, when we are firing queries at RECYCLEBIN which is a public synonym that points to USER_RECYCLEBIN. The RECYCLEBIN is enabled by default in 10g, but one can turn it on or off with the RECYCLEBIN init parameter, at the session level or system level.

Let’s say RECYCLEBIN is enabled, any tables that we drop are not actually getting deleted. Instead, Oracle just renames that particular table which has been dropped and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.

For example, consider this simple table:

SQL> Create table test (num number);
Table created.
SQL> Insert into test values (1);
1 row created.
SQL> Insert into test values (2);
1 row created.
SQL> Insert into test values (3);
1 row created.
SQL> Select * from test;
NUM
----------
1
2
3

If the RECYCLEBIN init parameter is set to ON (the default in 10g), then the dropped table will placed in the RECYCLEBIN:

SQL> drop table test;
Table dropped.
SQL> Select object_name, original_name, type, can_undrop, can_purge, droptime from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_DROP CAN_PURGE DROPTIME
------------------------------ -------------------------------- ------------------------- --- --- ------------------------
BIN$brwK19W3F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:50:33

What has happened to the table is that when we dropped it that has got renamed. The table data is still there and can be queried just like a normal table:

Note: the table name must be in quotes (“ “)

SQL> select * from "BIN$brwK19W3F/7gRAADuhyf0w==$0";
NUM
----------
1
2
3

Since the table data is still there, we can "undrop" the table. This operation is known as a "flashback drop". This operation will simply rename the BIN$... table to its original name:

SQL> Flashback table "BIN$brwK19W3F/7gRAADuhyf0w==$0" to before drop;
Flashback complete.
SQL> select * from test;
NUM
----------
1
2
3

It's important to know that after you've dropped a table, it has only been renamed; the table segments still exist there in your tablespace, unchanged, occupying space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it, or by purging it from the recyclebin.

SQL> select object_name, original_name, type, can_undrop, can_purge, droptime from recyclebin;
Object_name original_name type can_undrop can_purge droptime
--------------------------------------------------------------------------------
BIN$brwK19W3F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-09:12:26:22
SQL> purge table “BIN$brwK19W3F/7gRAADuhyf0w==$0” ;
Table purged.
SQL> select * from recyclebin;
No rows returned.

You have several purge options. You can also purge everything from the USER_RECYCLEBIN using PURGE RECYCLEBIN; a user with DBA privileges can purge everything from all recyclebins using DBA_RECYCLEBIN; and finally, you can purge recyclebin objects by schema and user with PURGE TABLESPACE USER.
Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space, or until you hit your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.

Dealing with dropped table versions

Just as you can wind up with several versions of a file with the same name in the Windows recycle bin, you can wind up with several versions of a table in the Oracle recyclebin also. For example, if we create and drop the TEST table twice, we'll have two versions in the recyclebin:

SQL> create table test (test_col varchar2(10));
Table created.
SQL> insert into test values ('Version 1');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> create table test (test_col varchar2(10));
Table created.
SQL> insert into test values('Version 2');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime fro
m recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brrGkpUUF7vgRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:08:48:05
BIN$brrGkpUVF7vgRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:08:48:31

Now query the two dropped tables to verify that whether they are different:

SQL> select * from "BIN$brrGkpUUF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 1
SQL> select * from "BIN$brrGkpUVF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 2

Now we are going to issue a FLASHBACK DROP command for TEST, which version will Oracle restore?

SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from test;
TEST_COL
----------
Version 2

Oracle will always restore the most recent version of the dropped object. If we want the previous version then we can select the new BIN$... name of the TEST from the recyclebin and we can perform flashback query operation on the BIN$... table as follows. This will restore the required version of the TEST table.

SQL> select * from "BIN$brrGkpUUF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 1
SQL> select * from "BIN$brrGkpUVF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 2

Now we want to restore the Version 1 table then we need to capture the BIN$... name of the TEST table i.e.,
BIN$brrGkpUUF7vgRAADuhyf0w==$0

Now issue the flash back query as follows it will restore the required version of the TEST table.

SQL> flashback table "BIN$brrGkpUUF7vgRAADuhyf0w==$0" to before drop;
Flashback complete.
SQL> select * from test;
TEST_COL
----------
Version 1

Going with DEPENDENT OBJECTS

In a modern RDBMS, Most will have indexes, constraints, and/or triggers some stand alone. When a table is dropped ORALCE drops the dependent objects associated with it. When you drop a table with the recyclebin enabled, the table and its dependent objects get renamed, but still have the same structure as before. The triggers and indexes get modified to point to the new BIN$ table name. (Any stored procedures that referenced the original object, will be invalidated.)

For example:

SQL> create table test (test_col varchar2(10));
Table created.
SQL> create index ind_test_col on test(test_col);
Index created.
SQL> insert into test values ('Version 1');
1 row created.
SQL> commit;
Commit complete.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_UNDROP CAN_PURGE DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brwK19WxF/7gRAADuhyf0w==$0 IND_TEST_COL INDEX NO YES 2009-07-15:10:18:46 53649 53650
BIN$brwK19WyF/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:18:46 53649 53649
SQL> flashback table test to before drop;
Flashback complete.

SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
no rows selected

The RECYCLEBIN views have a few other columns that make the relationship between TEST and IND_TEST_COL clear:
The PURGE_OBJECT column is the object number of the object itself; eg. the object number of IND_TEST_COL is 53650. Note the value of the BASE_OBJECT column for IND_TEST_COL: 53649, the object number of the associated version of the TEST table.
If we FLASHBACK DROP the TEST table, its index will be restored - but Oracle will not rename it to its original name. It will retain its BIN$.. name:

SQL> select index_name from user_indexes where table_name='TEST';
INDEX_NAME
------------------------------
BIN$brwK19WxF/7gRAADuhyf0w==$0

SQL> drop table test;
Table dropped.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_UNDROP CAN_PURGE DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brwK19WzF/7gRAADuhyf0w==$1 BIN$brwK19WxF/7gRAADuhyf0w==$0 INDEX NO YES 2009-07-15:10:43:33
BIN$brwK19W0F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:43:33 53649 53649

Note the values in the CAN_UNDROP and CAN_PURGE columns for the index. An index cannot be undropped without the table - so CAN_UNDROP is set to NO. It can, however, be purged without purging the table:

SQL> purge index "BIN$brwK19WzF/7gRAADuhyf0w==$1";
Index purged.

Now, if we restore the table, it will be restored without the index:

SQL> select index_name from user_indexes where table_name='TEST';
no rows selected

LIMITATIONS

A few types of dependent objects are not handled like the simple index above.

o Bitmap join indexes are not put in the recyclebin when their base table is dropped, and not retrieved when the table is restored with FLASHBACK DROP.

o The same applies for materialized view logs; when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.

o Referential integrity constraints that reference another table are lost when the table is put in the recyclebin and then restored.

If space limitations force Oracle to start purging objects from the recyclebin, it purges indexes first. If you FLASHBACK DROP a table whose associated indexes have already been purged, it will be restored without the indexes.

Finding the “Floating” calendar dates:

2009-07-09T11:49:00.000+05:30

Using the SQL we can find the floating calendar dates, such as the second Tuesday of the month, with modular arithmetic and brute force reasoning.

To calculate the second Tuesday of the month, this depends on the day of the week of the first day of the month. But we need to know which month is this? Obviously, all we need to know are the month and year, but it’s easier if we start with a date as such. For testing purposes, the following table can be used.

Create table month_date (month_date date);
Insert into month_date values(date ‘2009-04-01’);
Insert into month_date values(date ‘2009-05-01’);
Insert into month_date values(date ‘2009-06-01’);
Insert into month_date values(date ‘2009-07-01’);

So, given a date, the first day of a month, what date is the second Tuesday of that month? The process begins by calculating the day of the week for the first day of the month.

Day-of-week function:

Even though standard SQL doesn’t provide any function to give the day of the week for any date, most of the database systems do.

ORACLE provides a function:

TO_CHAR(DATE,’D’);

The essence of this function is that it will return a number between 0 and 6 or 1 and 7.Sometimes 0 or 1 is Sunday and 6 or 7 is Saturday, and sometime 0 or 1 is Monday and 6 or 7 is Sunday. This depends on how the NLS_TERRITORY parameter is set.

Note: We follow that 1 is Sunday and 7 is Saturday in our example.
-----

The Formula:

To convert the first day of the month into second Tuesday of the month simply involves manipulating the day of the week of the first day with in an arithmetic formula. Before we see the formula, we should know what happens when the first day of the month falls on each day of the week from Sunday through Saturday.

Lets see this:

1. A Sunday, the third is a Tuesday, so the tenth is the second Tuesday
2. A Monday, the second is Tuesday, so the ninth is the second Tuesday
3. A Tuesday, the eighth is the second Tuesday
4. A Wednesday, the seventh is the next Tuesday and fourteenth is the second Tuesday.
5. A Thursday, the sixth is the next Tuesday and thirteenth is the second Tuesday
6. A Friday, the fifth is the next Tuesday and the twelfth is the second Tuesday
7. A Saturday, the fourth is the next Tuesday and the eleventh is the second Tuesday

This gives us all the possibilities. So now we have to reduce these facts to formula.

A	B	C	D	E
1ST	WEEK DAY	10-B	C MOD 7	D+7
SUN	1	9	2	9
MON	2	8	1	8
TUE	3	7	0	7
WED	4	6	6	13
THU	5	5	5	12
FRI	6	4	4	11
SAT	7	3	3	10

The first column (A) is the day of the week of the first day of the month; and the second column is the numerical equivalent of this day using the range from 1 to 7.
The important data in the above table is the last column; which is the number of days to add to the date of the first day of the month.

So, the formula can be derived as follows.

1. Find B, the day of the week of the first day of the month, using
1-Sunday……7-Saturday.

2. Subtract this number from 10 to get C:
With Sunday -1 …. Saturday – 7, Tuesday would be 3.
The number 3-B is the offset (relative to the first of the month) for a Tuesday, but it might be in the current month or the previous month; 10-B is also a Tuesday, and so are 17-B and 24-B.
You should choose to subtract 10 because you want C to be positive for all inputs. Because you need D to be positive in the next step, but a negative value for C would result in a negative value for D. this is because -1%7 gives -1 on most systems.

3. Divide by 7 and keep remainder to get D value.
D is also the offset for a Tuesday, and D in the range of 0 to 6. every day in the first week has an offset between 0 to 6. So D is the first Tuesday of the month.

4. Add 7 to get E.
That takes the range of E from 7 to 13. Every day in the second week has an offset in the range of 7-13.

5. Take the result and add that number of days to the date of the first day of the month.

Implementing this on ORACLE database:

SELECT TO_CHAR (month_date, 'dd-mon-yyyy') AS first_day_of_month,
TO_CHAR (( (month_date)
+ MOD ((10 - TO_CHAR ((month_date), 'd')), 7)
+ 7
),
'dd-mon-yyyy'
) AS second_tuesday_of_month
FROM month_date

OUTPUT
*******

FIRST_DAY_OF_MONTH	SECOND_TUESDAY_OF_MONTH
01-apr-2009	14-apr-2009
01-may-2009	12-may-2009
01-jun-2009	09-jun-2009
01-jul-2009	14-jul-2009

Hope this is useful for you guys.

Dedicated server Vs Shared Server:

2009-06-16T11:11:00.000+05:30

Dedicated server:

--> The user process and the server process are separate all together.
--> In a dedicated server environment each user process has its own server process.
--> To take the advantage of distributed processing the user process and server process can run on different machines.
--> The dedicated server exists even when a user process is not making any database requests, at this particular point of time the server process remains idle.
--> The ratio between the user process and server process in a dedicated server environment will be one – to – one.
If the user and server process are on the same machine then the interface in use will be IPC (Inter Process Communication) mechanism.

Shared server:

--> Generally we go to shared server in order to increase the server efficiency.
--> In an OLTP (On Line Transaction Processing) environment, at the time of order entry the server process will be in the idle state for almost 90 % of the time. Due to this we are not able to actually utilize the system resources which are getting wasted.
--> Unlike dedicated server processes, a shared server process can process can process any request which comes, instead waiting for a particular request.
--> In a shared server the client (User) process hits the dispatcher which in turn assigns the request to any one of the server processes which is free at that particular point of time.
--> The PMON process registers the location and load of the dispatcher with the listener, enabling the listener to forward the requests to least utilized dispatcher.
--> A dispatched can support multiple client connection concurrently.

Benefits of Shared server:

--> Reduces the number of server process which are idle.
--> Reduces the umber of processes against an instance.
--> Load balancing is achieved.
--> The number of possible users can be increased.

Note:

A dedicated server can be used at –
--> The time of connecting as a SYSDBA to start up / shutdown / at the time of performing recovery.
--> When batch jobs are executed as there will be minimal / no idle time as such.

Subscribe to Oracle Bullets by Email

Gist of Materialized views:

2009-06-16T10:40:00.000+05:30

People might be aware of Snapshots in ORACLE, now in recent versions of ORACLE the place of snapshots has been occupied by MATERIALIZED VIEWS. These materialized views have many uses:

1. We can copy the data from one instance to other with the same format, and we can query against the materialized view directly.
2. We can copy the aggregated (Summarized) data from one instance to other instance and we can fire queries against the materialized view directly.
3. We can create a summarized (aggregated) version of a table, the interesting point here is that if we query the base table, then the query is automatically be rewritten to use the materialized view.

Let’s have examples for each of the above uses of the materialized views.

1. Here all the code is in our local database LOCALDB. We have a remote database RMDB, where there is a table named employee. However, one time per day our materialized view wakes up, dips into the RMDB and grabs the data and brings the same to LOCALDB.

Because of this we can fire the required queries to the materialized view even though we don’t have access to the RMDB.

In our LOCALDB:

Create materialized view emp_mview_ex1
Refresh complete
Start with sysdate
Next sysdate+1 as
Select emp_name, dept_name from employee@RMDB_dblink;

Now we can query the Materialized view as such

Select * from emp_mview_ex1;

2. Now let’s discuss about the summary (aggregated) table example. Even her all the code is in the local database LOCALDB. We have a employee table in the RMDB, now this data is summarized daily – getting the number of employees per department, as well as the average salary in that particular department.

Even, here we can fire queries against our local database LOCALDB with no access to the RMDB, aside from the refreshing the materialized view daily.

In our LOCALDB:

Create materialized view emp_mview_ex2
Refresh complete
Start with sysdate
Next sysdate+1 as
Select dept_name,count(*), avg(salary) from employee@RMDB_dblink; group by dept_name;

Now we can query the Materialized view,

Select * from emp_mview_ex2;

3. Now the interesting use of the materialized view query rewrite. A materialized view is built with the help of a base table and data is summarized, The ORACLE optimizer is smart enough in such a way that it can realize that when a query is fired on a base table, the data is already been aggregated in a materialized view, and will go with the data in the materialized view.

In our LOCALDB:

Create materialized view emp_mview_ex3
Refresh complete
Start with sysdate
Next sysdate+1 as
Select dept_name, count(*) from employee group by dept_name;

Now we query the LOCALDB with the base table n the from clause

Select dept_name, count(*) from employee group by dept_name;

Even though the query is fired against the base table, at the time of execution the optimizer will switch to the existing materialized view with the information already aggregated (summarized) and it will use the emp_mview_ex3 instead.

To make optimizer to think of using the existing materialized views there are some init.ora parameters to be set they are:

1. query_rewrite_enabled needs to be set to a value TRUE and we should have the ‘query rewrite’ system privilege.
2. If the materialized view has to be refreshed based on a schedule the it is going to use dbms_job to do so, so job_queue_processes needs to be some value > 0.

Subscribe to Oracle Bullets by Email

Generating random numbers and strings in Oracle is easier

2009-06-12T10:14:00.000+05:30

For testing purposes at times we may need some test data, for this we might need to generate some random numbers or strings. People may write their own Random number generation scripts, but even ORACLE is providing a package for generating the random numbers and strings as well. This may be useful to in creating the auto generated passwords in a web page.

The fact is that Oracle provides us with a random number generation package. When this package is used to generate the random numbers it is faster than writing our own random generation scripts in PL/SQL. This Random generator package is not only used to generate the numbers and strings but this can be used to generate the alpha numeric strings also with the desired size (number of characters).

Bullet:

DBMS_RANDOM package

The DBMS_RANDOM package which is provided by ORACLE will generate random data in character, numeric or alphanumeric formats. The size of the random values which are to be generated can also be specified.
This package can be created using the script dbmsrand.sql available in the /RDBMS/ADMIN directory.

The following are the functions that are present in the package which can be used to serve the purpose of generating random numbers and strings.
RANDOM – This function is used to generate the random numbers.

VALUE – This function is used to generate random numbers from the range provided. The range will be taken as 0-1 if nothing is provided.

STRING – This function is used to generate the strings either in upper case or lower case or alphanumeric format.

• The first argument of the functions takes the following values which will define the string type to be generated.
• U - Upper case
• L - Lower case
• A - Alphanumeric
• X - Alphanumeric with upper case alphabets.
• P - Printable characters only.

If we use any character other than these characters the output will be in Upper case.
The second argument will be the size of the random number / string to be generated.
Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.

INITIALIZE - Initialize the package to proceed with the number generation.
Provide a number (seed) as input to the routine.

SEED - Used to change the seed value. It is used in the internal algorithm to generate values. Setting this will generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.

TERMINATE - Close the process of random number generation.

Examples:

Below are some of the examples of using the package.

Example 1: To generate a random number (positive or negative)
Select dbms_random.random from dual;

RANDOM
_____________
1254544778

Example 2: To generating a random number between 0 and 1.
SQL> select dbms_random.value from dual;

VALUE
_____________
0.98121484036063

Example 3: To generating a random number from a range, between 1 to 1000.
SQL> select dbms_random.value(1,1000) num from dual;

NUM
_____________
979.825208337552

Example 4: To generating a 12 digit random number.
SQL> select dbms_random.value(100000000000, 999999999999) num from dual;

NUM
_____________
886832250758.021

Example 5: To generating an upper case string of 20 characters
SQL> select dbms_random.string('U', 20) str from dual;

STR
_______________________
TSKZJBAGRQREGUEBRXIO

Example 6: To generating a lower case string of 20 characters
SQL> select dbms_random.string('L', 20) str from dual;

STR
____________________
thfrkllkuhxqeybkcykg

Example 7: To generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.
SQL> select dbms_random.string('A', 20) str from dual;

STR
__________________
xWPvLfHYuPWDNFEIHtUI

Example 8: To generating an upper case alphanumeric string of 20 characters
SQL> select dbms_random.string('X', 20) str from dual;

STR
________________________
CWWULRKBQLAMCLGYWC4A

Example 9: To generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.
SQL> select dbms_random.string('P', 20) str from dual;

STR
___________________
dsL&B}l?mMUIr?'T1r6{

Subscribe to Oracle Bullets by Email

Running out of Disk Space:

2009-06-09T13:52:00.000+05:30

A Query which is badly written may even fill up the disk drive even if the disk is large enough. We need to get back the system onto its feet as quickly as possible.
Temporary Tablespace:
Temporary tablespace generally it holds SQL Execution workspace, intermediate indexes, sub-query information, sorts etc… Generally it is released automatically, but there are situations where this may lead to problems. Issues such as fragmentation, DBMS bugs, badly written queries may cause temporary space to grow till you run out of disk space.

To housekeep the temporary space in oracle is a common activity. The temporary space is flexible and powerful but it comes with some administration overhead.

Bullet:

Let us assume our database is in /u01/oradata/Chaitanya/ and our temporary space is named as temp01.dbf. We have some free space in u02 to use some space for a specified period of time. A SYSDBA user can do this.

Find the name of the current default temporary tablespace

SELECT property_value FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
-----------------------------
TEMP

Create a new temporary tablespace to allow the system to operate while we are making the changes:

CREATE temporary tablespace t1
Tempfile ‘/u02/Chaitanya_temp/t01.dbf’
Size 512M reuse autoextend on next 10M maxsize unlimited
Extent management local uniform size 256K;

Bring the new temporary tablespace online

ALTER DATABASE
Tempfile ‘u02/Chaitanya_temp/t01.dbf’
ONLINE ;

Change the New temporary tablespce as the default temporary tablespace

Alter database default temporary tablespace t1;

Drop the old temporary tablespace which is using the disk space

Drop tablespace temp including the contents and datafiles;

We are running with the new temporary tablespace but what we really wanted to do is recreate the old space in old partition and use the same. While we are doing so, we can restrict the size of the new space as 0.5 GB / 1 Gb, and allow it to grow till some extent let it be 4 GB.

Create temporary tablespace temp
Tempfile ‘u01/oradata/chaitanya/temp01.dbf’
Size 512M reuse autoextend on next 10M maxsize 4096M
Extent management local uniform size 256K;

Now we have to switch every thing back to using this final temp tablespace, and delete the temporary tablespce which we have created for the temporary use (/u02/Chaitanya_temp/t1)

Alter database
Tempfile ‘u01/oradata/Chaitanya/temp01.dbf’
ONLINE;

Alter database default temporary tablespace temp;

Drop tablespace t1 including contents and datafiles;

Please post your comments on this Bullet.

Subscribe to Oracle Bullets by Email