Oracle – Find consuming sql from process id

If you find an oracle process is consuming a high amount of CPU, you can find the sql_text that is related to that process id using the following code:

SELECT SQL_TEXT
FROM v$sql
WHERE sql_id = (SELECT sql_id FROM v$session WHERE paddr = (sELECT addr
FROM v$process
WHERE spid = '&process_id'));

You will need to find the process id using either top (linux), topas -P (aix), or task manager (windows). Equally you can use Enterprise Manager to find this information, by looking under the performance monitor and any alerts regarding tuning / high CPU consumption.

Oracle – Clone Database without RMAN

If you need to do a clone of a database, say from production to test, then you can do this by backing up the production control file to trace, copying the datafiles and pfile across to the new test environment, then running the script created by the control file backup.

Prereqs:

The copy of the datafiles must be done with the source database shutdown, or in ARCHIVELOG mode.

Copy parameter file:

Copy the init.ora or spfile from the source database $ORACLE_HOME/dbs to the $ORACLE_HOME/dbs directory of the target database.

Vi the parameter file and change any directory names that need amending. I.e. logfile locations, control file locations, etc…

EITHER

A…………….>

A. Source database in archivelog mode

If the source database is open then you need to put the tablespaces into backup mode before starting the copy:

SQL> ALTER TABLESPACE <tablespace_name> BEGIN BACKUP;

Copy all datafiles

SQL> ALTER TABLESPACE <tablespace_name> END BACKUP;
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;


<……………A

OR

B…………….>

B. Source database not in archivelog mode

On source database:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
SQL> SHUTDOWN IMMEDIATE;


<…………….B

Copy control files, datafiles, all archivelogs to target destination

It may be necessary to create the directory structure for these files if it does not already exist. However, it is best to SCP -rp the entire $ORACLE_HOME/dbs directory to ensure permissions and ownership are correctly assigned and no files are missed.

Set Oracle environment

$ set ORACLE_SID = <target_oracle_sid>
$ set ORACLE_HOME = <target_oracle_home>

Setup password file

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=
$ sqlplus '/as sysdba'

EITHER

A…………….>

A. Clone to same name database

If you are cloning to a database of the same name, then after checking the control file has the correct location for the datafiles, you should be able to startup the database with the startup command:

Check datafile location:

SQL> STARTUP MOUNT
SQL> SELECT file#, name FROM v$datafile;

Rename any incorrect datafile locations:

SQL> ALTER DATABASE RENAME FILE '/old_full_directory_path/datafile_name.dbf' TO '/new_full_directory_path/datafile_name.dbf';

Check logfile location:

SQL> SELECT member FROM v$logfile;

Rename any incorrect logfile locations:

SQL> ALTER DATABASE RENAME FILE '/old_full_directory_path/logfile_name.log' TO '/new_full_directory_path/logfile_name.log';

EITHER

A…………….>

A. Database without archive logs

Open database

SQL> ALTER DATABASE OPEN
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 2072056 bytes
Variable Size 88080904 bytes
Database Buffers 167772160 bytes
Redo Buffers 6316032 bytes
Database mounted.
Database opened.
SQL>

<……………A

OR

B…………….>

B. Database with archive logs

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

Wait for all archivelogs to apply then:

SQL> ALTER DATABASE CANCEL;

Open database with resetlogs if necessary

SQL> ALTER DATABASE OPEN RESETLOGS;

<…………….B

<……………A

OR

B…………….>

B. Clone to new database name

• Rename clone pfile to new db name
• Change directory names within pfile if necessary
• Find the trace file containing the backup controlfile script (normally in $ORACLE_ADMIN/udump)
• Edit the create script from the trace file such as below, remember to change directory names from source to target, save as “create_db.sql”:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "TST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 (
'/tst/redologs1/redo01_1.log',
'/tst/redologs2/redo01_2.log'
) SIZE 25M,
GROUP 2 (
'/tst/redologs1/redo02_1.log',
'/tst/redologs2/redo02_2.2og'
) SIZE 25M,
GROUP 3 (
'/tst/redologs1/redo03_1.log',
'/tst/redologs2/redo03_2.log'
) SIZE 25M
DATAFILE
'/tst/system/system_01.dbf',
'/tst/undo/undotbs_01.dbf',
'/tst/system/sysaux_01.dbf',
'/tst/oradata/users_01.dbf',
'/tst/oradata/data_01.dbf',
'/tst/oradata/data_02.dbf',
'/tst/oradata/data_03.dbf',
'/tst/oradata/indexes_01.dbf',
'/tst/oradata/indexes_02.dbf',
'/tst/oradata/csmig_01.dbf',
'/tst/oradata/cm_rem_ts_01.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER TABLESPACE TEMP ADD TEMPFILE '/tst/temp/temp_01.dbf'
SIZE 1048576000 REUSE AUTOEXTEND OFF;

• create udump, adump, bdump, cdump directories
• run create script

$ sqlplus '/as sysdba'
SQL> @create_db.sql

• check database is open

<…………….B

Change DBID if required using NID

$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 29 11:36:36 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 2072056 bytes
Variable Size 88080904 bytes
Database Buffers 167772160 bytes
Redo Buffers 6316032 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64
With the Partitioning and Data Mining options

$ nid TARGET=/
DBNEWID: Release 10.2.0.3.0 - Production on Wed Jun 29 11:37:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database SAWTST1 (DBID=1067814800)
Connected to server version 10.2.0
Control Files in database:
/sawtst1/ctrl1/sawtst1_01.ctl
/sawtst1/ctrl2/sawtst1_02.ctl
Change database ID of database SAWTST1? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1067814800 to 2196809383
Control File /sawtst1/ctrl1/sawtst1_01.ctl - modified
Control File /sawtst1/ctrl2/sawtst1_02.ctl - modified
Datafile /sawtst1/system/system_01.dbf - dbid changed
Datafile /sawtst1/undo/undotbs_01.dbf - dbid changed
Datafile /sawtst1/system/sysaux_01.dbf - dbid changed
Datafile /sawtst1/oradata/users_01.dbf - dbid changed
Datafile /sawtst1/oradata/data_01.dbf - dbid changed
Datafile /sawtst1/oradata/data_02.dbf - dbid changed
Datafile /sawtst1/oradata/data_03.dbf - dbid changed
Datafile /sawtst1/oradata/indexes_01.dbf - dbid changed
Datafile /sawtst1/oradata/indexes_02.dbf - dbid changed
Datafile /sawtst1/oradata/csmig_01.dbf - dbid changed
Datafile /sawtst1/oradata/cm_rem_ts_01.dbf - dbid changed
Datafile /sawtst1/temp/temp_01.dbf - dbid changed
Control File /sawtst1/ctrl1/sawtst1_01.ctl - dbid changed
Control File /sawtst1/ctrl2/sawtst1_02.ctl - dbid changed
Instance shut down
Database ID for database SAWTST1 changed to 2196809383.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 29 11:37:40 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 2072056 bytes
Variable Size 88080904 bytes
Database Buffers 167772160 bytes
Redo Buffers 6316032 bytes
Database mounted.

SQL> alter database open resetlogs;
Database altered.

SQL>