Monthly Archives: August 2009

Oracle, Oracle 10g, oracle 9i, RMAN

Oracle RMAN recovery of files on a lost mount point

Leave a comment

Oracle’s Recovery Manger (RMAN), originally released in Oracle 8, is a proprietary utility used for the backup and recovery of Oracle databases. Given a good Oracle RMAN backup, A DBA can recover from any disaster which may occur to their database, to include: loss of controlfiles, loss of datafiles, corruption of datafiles, loss of the entire database and even provides the ability to clone a database. In this article we give detailed instructions on the recovery of a lost mount point in Oracle 9i, however these instructions will work in versions 10g and 11g.

1. Logon to the Oracle server as the Oracle software owner and set the ORACLE_SID.

[oracle@testdb02 ORCL]$ su – oracle
Password:
[oracle@testdb02 ~]$ export ORACLE_SID=ORCL
[oracle@testdb02 ~]$

2. Logon to SQL*PLUS and obtain the location of all datafiles from the table dba_data_files. Note: There exist two data files on /u05 that can be used for this test.

[oracle@testdb02 ~]$ SQL*PLUS ‘/ as sysdba’
SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 20:03:06 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL>

SQL> select tablespace_name, file_name from dba_data_files order by tablespace_name;
TABLESPACE_NA FILE_NAME
————- ————————————————–
SYSTEM /u01/oradata/ORCL/system01.dbf
TOOLS /u05/oradata/ORCL/tools01.dbf
UNDOTBS1 /u04/oradata/ORCL/undo01.dbf
USERS /u05/oradata/ORCL/users01.dbf

SQL>

3. Exit SQL*PLUS and navigate to /u05/oradata/ORCL, create temp directory and move all files located on the mount point /u05 to the new directory. This will simulate the loss of all Oracle related files on /u05. In a real situation, you would have your SA recreate the /u05 mount point.

[oracle@testdb02 ~]$ cd /u05/oradata/ORCL
[oracle@testdb02 ORCL]$ ls
sqlnet.log temp02.dbf tools01.dbf users01.dbf
[oracle@testdb02 ORCL]$ mkdir temp
[oracle@testdb02 ORCL]$ mv tools01.dbf temp/
[oracle@testdb02 ORCL]$ mv users01.dbf temp/
[oracle@testdb02 ORCL]$ ls
sqlnet.log temp temp02.dbf
[oracle@testdb02 ORCL]$

4. Log back into SQL*PLUS and attempt to shutdown the database. You will receive an indicating that the files on mount point /u05 cannot be located.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:27:47 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> shutdown
ORA-01116: error in opening database file 3
ORA-01110: data file 3: ‘/u05/oradata/ORCL/users01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>

5. Exit SQL*PLUS and force a shutdown of the database by killing the smon process.

[oracle@testdb02 ORCL]$ ps -ef|grep smon
oracle 12677 1 0 21:04 ? 00:00:00 ora_smon_ORCL
oracle 14115 13009 0 21:35 pts/4 00:00:00 grep smon
[oracle@testdb02 ORCL]$ kill -9 12677
[oracle@testdb02 ORCL]$ ps -ef|grep ORCL
oracle 12023 11973 0 20:51 pts/6 00:00:00 vim ORCL_RMAN.sh
oracle 14137 13009 0 21:36 pts/4 00:00:00 grep ORCL
[oracle@testdb02 ORCL]$

6. Logon to SQL*PLUS and perform open mount operation, so the recovery operation can be performed.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:37:20 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 144670496 bytes
Fixed Size 741152 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 1323008 bytes
Database mounted.
SQL>

7. Exit SQL*PLUS and enter the RMAN utility connecting to the target database and recovery catalog.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
[oracle@testdb02 ORCL]$ RMAN

Recovery Manager: Release 9.2.0.8.0 – 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN> connect target sys/h1g0alz

connected to target database: ORCL (DBID=1373796530)

RMAN> connect catalog RMAN/RMAN@RMANDB

connected to recovery catalog database

RMAN>

8. Issue the restore database command, which will restore all database components.

RMAN> restore database;

Starting restore at 12-JUN-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/ORCL/system01.dbf
restoring datafile 00002 to /u04/oradata/ORCL/undo01.dbf
restoring datafile 00003 to /u05/oradata/ORCL/users01.dbf
restoring datafile 00004 to /u05/oradata/ORCL/tools01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u07/RMAN_BACKUP/ORCL/ORCL_657220129_1_26_1_0qjiooh1.dbf tag=BACKUP_FULL_ORCL params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 12-JUN-08

RMAN>

9. Issue the recover database command, which will apply all archive redo logs since the last backup set.

RMAN> recover database;

Starting recover at 12-JUN-08
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 28 is already on disk as file /u01/oradata/ORCL/archive/ORCL_ARCH28.arc
archive log thread 1 sequence 29 is already on disk as file /u06/oradata/ORCL/archive/ORCL_ARCH29.arc
archive log filename=/u01/oradata/ORCL/archive/ORCL_ARCH28.arc thread=1 sequence=28
media recovery complete
Finished recover at 12-JUN-08

RMAN>

10. Exit out of RMAN, enter into SQL*PLUS and open the database. Once the database has successfully opened, you know that all database files are in a consistent state.

RMAN> exit

Recovery Manager complete.
[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:43:14 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> alter database open;

Database altered.

SQL>

This completes recovery of all files on a single mount point or directory.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, RMAN

RMAN recovery of lost or corrupted tablespace datafile

Leave a comment

Oracle’s Recovery Manger (RMAN), originally released in Oracle 8, is a proprietary utility used for the backup and recovery of Oracle databases. Given a good Oracle RMAN backup, A DBA can recover from any disaster which may occur to their database, to include: loss of controlfiles, loss of datafiles, corruption of datafiles, loss of the entire database and even provides the ability to clone a database. In this article we give detailed instructions on the recovery of a lost or corrupted tablespace datafile in Oracle 9i, however these instructions will work in versions 10g and 11g.

1. Logon to the oracle server as the oracle software owner.

2. Set the ORACLE_SID and logon to sqlplus as sysdba.

[oracle@testdb02 scripts]$ su – oracle
Password:
[oracle@testdb02 ~]$ export ORACLE_SID=ORCL
[oracle@testdb02 ~]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 20:03:06 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL>

3. Query the dba_data_files table for tablespaces and datafiles. We are looking for a datafile which can be moved out of the location specified in the controlfile, resulting in tablespace corruption

SQL> select tablespace_name, file_name from dba_data_files order by tablespace_name;
TABLESPACE_NA FILE_NAME
————- ————————————————–
SYSTEM /u01/oradata/ORCL/system01.dbf
TOOLS /u05/oradata/ORCL/tools01.dbf
UNDOTBS1 /u04/oradata/ORCL/undo01.dbf
USERS /u05/oradata/ORCL/users01.dbf

SQL>

4. Shutdown the database with immediate.

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

5. Exit SQL*PLUS and move to directory /u05/oradata/ORCL. Create a temp directory and move the datafile users01.dbf into the new temp directory.

[oracle@testdb02 ~]$ cd /u05/oradata/ORCL
[oracle@testdb02 ORCL]$ ls
temp02.dbf tools01.dbf users01.dbf
[oracle@testdb02 ORCL]$
[oracle@testdb02 ORCL]$ mkdir temp
[oracle@testdb02 ORCL]$
[oracle@testdb02 ORCL]$ mv users01.dbf temp/
[oracle@testdb02 ORCL]$ ls
temp temp02.dbf tools01.dbf
[oracle@testdb02 ORCL]$

6. Log back into sqlplus and startup your Oracle instance. Note: the error received referring to datafile users01.dbf, there exists a reference to this datafile in the controlfile, however Oracle could not locate the file. This results in the tablespace USERS being in an unusable inconsistent state.

[oracle@testdb02 ORCL]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 20:48:14 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 144670496 bytes
Fixed Size 741152 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 1323008 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 – see DBWR trace file
ORA-01110: data file 3: ‘/u05/oradata/ORCL/users01.dbf’

SQL>

7. To resolve the inconsistence, we must startup the RMAN utility and connect to both the target database and recovery catalog.

[oracle@testdb02 ORCL]$ rman

Recovery Manager: Release 9.2.0.8.0 – 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/h1g0alz

connected to target database: ORCL (DBID=1373796530)

RMAN> connect catalog rman/rman@RMANDB

connected to recovery catalog database

RMAN>

8. From within the RMAN utility, issue the command to ‘restore tablespace ’. In this example the tablespace was USERS. The restore command replaces the tablespace’s datafiles in your database with the copy in your backup set.

RMAN> restore tablespace users;

Starting restore at 12-JUN-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=15 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u05/oradata/ORCL/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u07/RMAN_BACKUP/ORCL/ORCL_657220129_1_26_1_0qjiooh1.dbf tag=BACKUP_FULL_ORCL params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 12-JUN-08

RMAN>

9. From within the RMAN utility, issue the command ‘recover tablespace ’. The recover command applies all archive redo logs which have occurred since the last backup set.

RMAN> recover tablespace users;

Starting recover at 12-JUN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK

starting media recovery

archive log thread 1 sequence 28 is already on disk as file /u01/oradata/ORCL/archive/ORCL_ARCH28.arc
archive log thread 1 sequence 29 is already on disk as file /u06/oradata/ORCL/archive/ORCL_ARCH29.arc
archive log filename=/u01/oradata/ORCL/archive/ORCL_ARCH28.arc thread=1 sequence=28
media recovery complete
Finished recover at 12-JUN-08

RMAN>

10. Exit the RMAN utility and enter SQL*PLUS, alter the database to an open status. You know that the tablespace USERS is now in a consistent state with the controlfiles when the database opens successfully

RMAN> exit

Recovery Manager complete.
[oracle@testdb02 ORCL]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:03:11 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> alter database open;

Database altered.

SQL>

This completes successful recovery of a tablespace.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

linux, Oracle, uncategorized, unix

Oracle RDBMS and UNIX/LINUX environmental variables.

Leave a comment

Oracle RDBMS installations on UNIX and LINUX require the use of operating system parameters to operate properly. Experienced DBA will take care in setting up the operation system parameter before movement, upgrade, or installation of an Oracle RDBMS system on UNIX and LINUX. This article will cover the necessary OS variables to setup a functioning ORACLE RDBMS system.

Required Variables:

ORACLE_HOME – The ORACLE_HOME variable defines the location of the oracle installation directory. Under the ORACLE_HOME you will find such directories as BIN, DBS, NETWORK, etc. These are all core components of the Oracle RDBMS system. Without proper definition of the ORACLE_HOME most of the Oracle binaries will not execute.

ORACLE_BASE – The ORACLE_BASE variable defines the location (normally one level above the ORACLE_HOME) where the Oracle inventory files are stored and is the root directory of multiple Oracle product installs. If you have several different RDBMS versions installed on your system, normally the varies ORACLE_HOMEs would be located directly under the ORACLE_BASE directory.

ORACLE_SID – The ORACLE_SID defines the name of the instance of your database. Without proper definition of the ORACLE_SID most of the Oracle binaries will not execute.

TNS_ADMIN – The TNS_ADMIN defines the directory where your Oracle networking components are located. This directory will usually include: TNSNAMES.ORA, LISTENER.ORA, and SQLNET.ORA. THE TNS_ADMIN is normally pointed at ORACLE_HOME/network/admin.

PATH – The PATH variable points to all binaries which you may need during
administration and operation of your Oracle Server. In the Oracle environment, it is important to include $ORACLE_HOME/bin in this parameter. NOTE: You will require other executables and the PATH variable will be considerable longer then $ORACLE_HOME/bin.

LD_LIBRARY_PATH – The LD_LIBRARY_PATH variable defines the directories which contain .lib files for your binary executables. The directory $ORACLE_HOME/lib and/or $ORACLE_HOME/lib32 must be defined within this variable. Without properly defining the LD_LIBRARY_PATH variable, errors will arise during execution of Oracle binaries.

NLS_LANG – The NLS_LANG variable defines the National Language Set used within your Oracle database.

PATH – The PATH variable defines the location of every executable required for proper operation of Oracle products. For a DBA, you should edit the PATH variable to include $ORACLE_HOME/bin directory.

OPATCH – The OPATCH variable defines the directory where Oracle OPatch utility is stored. Normally, this is located in $ORACLE_HOME/OPatch. This utility is the normally tool used to install Oracle RDBMS quartly CPU patches.

Nice to have Aliases:

udump – Provides the ability to change directories to your user dump
destination.

cdump – Provides the ability to change directories to your core dump
destination.

bdump – Provides the ability to change directories to your backround dump
destination.

alert – Provides for quick opening of your alert log.

oradata – Provides the ability to change directories to datafile destination.

pfile – Provides for quick opening of your parameter file.

Example .profile file of ORACLE UNIX or LINUX server.

umask 022

ORACLE_BASE = /app/oracle/products
export ORACLE_BASE

ORACLE_HOME = $ORACLE_BASE/10.2.0
export ORACLE_HOME

ORACLE_SID = orcl
export ORACLE_SID

TNS_ADMIN = $ORACLE_HOME/network/admin
export TNS_ADMIN

PATH = $PATH:$ORACLE_HOME/bin
export PATH

OPATCH = $ORACLE_HOME/OPatch
export OPATCH

alias udump=’cd /app/oracle/product/admin/orcl/udump’
alias cdump=’cd /app/oracle/product/admin/orcl/cdump’
alias bdump=’cd /app/oracle/product/admin/orcl/bdump’
alias alert=’vi /app/oracle/product/admin/orcl/bdump/alert_lufs.log’
alias oradata=’cd /app/oracle/product/oradata/orcl’
alias pfile=’cd /app/oracle/product/10.2.0/dbs’

Larry J Catt, 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, SQL

Gathering SQL statements in Oracle 10g

Leave a comment

Oracle 10g allows for increase amount of information to be extracted about SQL statements executed against the database since last instance startup. In Oracle 10g we have the new V$BIND_VALUE_CAPTURE, which allows you see the bind variables attached to most SQL statements executed in the database. Additionally, the view V$SQL has several improvements over it’s 9i counterpart to include: Full contents of the SQL statement; last execution time in date format; and actually username of parsing user. This article will cover how to extract previously and currently executed SQL statements with a few different definable variables from you Oracle 10g database.

1. Logon to your Oracle 10g database as sysdba.

MyLinux:>sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 16 13:30:13 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. To extract all SQL executed since the last system startup, execute the following PL/SQL block. NOTE: This may contain a lot of information, so you should spool the contents of this execution to an OS layer file with the command: spool ./

/********************
// Larry Catt
// 15 AUG 2009
// All SQL since instance startup.
//
// 10g version
/***********************/

set serveroutput on size 1000000

declare
v_ct number;
v_bind varchar2(4000);
begin

for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql order by LAST_ACTIVE_TIME)
loop

dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);

for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop

dbms_output.put_line(v_b.value_string);

end loop;
end loop;
end;
/

3. The following PL/SQL block will give you all the SQL for a particular user defined by the variable V_USER.

/********************
// Larry Catt
// 15 AUG 2009
// ALL SQL from a particular user defined as the variable V_USER.
//
// 10g version
/***********************/

set serveroutput on size 1000000
declare
v_ct number;
v_bind varchar2(4000);
v_user varchar2(30):=’SYS’;

begin

for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql where parsing_schema_name= v_user order by LAST_ACTIVE_TIME)
loop

dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);

for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop

dbms_output.put_line(v_b.value_string);
end loop;
end loop;
end;
/

4. The following PL/SQL block will give you all the SQL statements executed during the time range of variables V_START_TIME and V_END_TIME.

/********************
// Larry Catt
// 15 Aug 2009
// All SQL statements executed between the times V_START_TIME and V_END_TIME.
//
// 10g version
/***********************/

set serveroutput on size 1000000

declare

v_ct number;
v_bind varchar2(4000);
v_start_time date := to_date(’15-AUG-2009 13:15:00′,’DD-MON-YYYY HH24:MI:SS’);
v_end_time date:=to_date(’15-AUG-2009 15:15:00′,’DD-MON-YYYY HH24:MI:SS’);

begin

for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql where LAST_ACTIVE_TIME between v_start_time and
v_end_time order by LAST_ACTIVE_TIME)
loop

dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);

for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop

dbms_output.put_line(v_b.value_string);

end loop;
end loop;
end;
/

5. The following PL/SQL block will give you all the SQL statements currently being executed in the system.

/********************
// Larry Catt
// 15 Aug 2009
// All SQL statements currently executing.
//
// 10g version
/***********************/

set serveroutput on size 1000000

declare

v_ct number;
v_bind varchar2(4000);

begin

for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql where address in
(select sql_address from v$session where status=’ACTIVE’) order by
LAST_ACTIVE_TIME)
loop

dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);

for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop

dbms_output.put_line(v_b.value_string);

end loop;
end loop;
end;
/

6. This completes extraction of Oracle SQL statements in Oracle 10g.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, oracle 9i, SQL

Gathering SQL statements in Oracle 9i

Leave a comment

Oracle 9i provides a way of gathering previously executed SQL in a system when tracing was not enabled. This feature is possible through the system view V$SQL, while not as robust as the newer Oracle releases, it still provides the DBA with the ability to view the exact user activities which are occurring in the database. Viewing previously executed statements during and after a system slowdown is highly valuable in determining the cause of certain system problems. In this article we will review the system view V$SQL and the columns in it which would allow you to regenerate previously executed statements.

1. Logon to SQL*PLUS as sysdba.

mylinux$ sqlplus ‘/ as sysdba’
SQL*Plus: Release 9.2.0.8.0 – Production on Tue Aug 16 10:53:45 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
SQL>

2. Describe the system view V$SQL and you will find three columns of interest for extraction of SQL are: SQL_TEXT, PARSING_USER_ID, and LAST_LOAD_TIME. SQL_TEXT provides the first 1000 characters of the SQL statement executed. NOTE: this may not be the entire SQL text. PARSING_USER_ID provides the User ID which last successfully parsed the statement. NOTE: This may not be the last person to execute the statement. Finally, LAST_LOAD_TIME provides you the time of last execution.

SQL> desc v$sql
Name Null? Type
—————————————– ——– —————
SQL_TEXT VARCHAR2(1000)
PARSING_USER_ID NUMBER
LAST_LOAD_TIME VARCHAR2(19)
…
…
…

3. Due to the limitation of DBMS_OUTPUT package in 9i we will create a dummy table to temporarily hold our SQL extracts. Execute the collowing DDL to create the table SQL_LONG.

SQL> create table sql_long(sql_text long, exec_time varchar2(19));

Table created.

SQL>

4. To extract all SQL in your database since instance startup, execute the following PL/SQL Block to populate your dummy table SQL_LONG.

set serveroutput on size 1000000
declare

v_ct number;

begin

for v_sql in(select SQL_TEXT, LAST_LOAD_TIME from v$sql order by last_load_time)
loop

insert into sql_long(sql_text, exec_time) values(v_sql.sql_text,
v_sql.last_load_time);

end loop;
end;
/

5. Now the only thing left is to extract the SQL from your dummy table. NOTE: You do not have to order by time of execution, because the SQL was inserted in ordered fashion.

set long 10000
select * from sql_long;

6. To retrieve the currently executing SQL statements issue the following PL/SQL block. NOTE: truncate the table SQL_LONG before every execution to ensure that you only received the information from the previous block.

Truncate table sql_long;

set serveroutput on size 1000000
declare

v_ct number;

begin

for v_sql in(select SQL_TEXT, LAST_LOAD_TIME from v$sql where address in
(select sql_address from v$session where status=’ACTIVE’) order by
last_load_time)
loop

insert into sql_long(sql_text, exec_time) values(v_sql.sql_text,
v_sql.last_load_time);

end loop;
end;
/

set long 10000
select * from sql_long;

Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g

Oracle Temporary Tablespace Movement

Leave a comment

Temporary tablespace’s in the Oracle environment are used for sorting and other non-permanent data operations. Although the space in a temporary tablespace is not permanent, a database of any size cannot properly function without the use of very large amounts of temporary tablespace. This procedure covers the movement of a temporary tablespace after space on the original storage device becomes insufficient. We will cover the movement of the TEMPORARY tablespace from the mount point data1 to mount point data3.

1. Find all tablespaces defined as TEMPORARY

SQL> select tablespace_name from dba_tablespaces where contents=’TEMPORARY’;

TABLESPACE_NAME
——————————
TEMP
TEMPORARY

SQL>

2. Locate the temp_files in the temporary tablespace.

SQL> select file_name from dba_temp_files where tablespace_name = ‘TEMPORARY’;

FILE_NAME
/data1/temporary01.dbf
/data1/temporary02.dbf
/data1/temporary03.dbf

SQL>

3. Find size of current files

SQL> select file_name, bytes/1024/1024 from dba_temp_files where tablespace_name = ‘TEMPORARY’;
FILE_NAME BYTES/1024/1024
—————— —————
/data1/temporary01.dbf 4096
/data1/temporary02.dbf 2048
/data1/temporary03.dbf 2048

SQL>

4. Add new temp files on your new mount point to the tablespace temporary with the command ‘alter tablespace’.

SQL> alter tablespace temporary add tempfile ‘/data3/temporary01.dbf’
size 4096m;
Tablespace altered.

SQL> alter tablespace temp add tempfile ‘/data3/temprary02.dbf’
size 2048m;
Tablespace altered.

SQL> alter tablespace temp add tempfile ‘/data3/temprary03.dbf’
size 2048m;
Tablespace altered.

5. Shutdown database to free all in-use segments of the temporary tablespace TEMPORARY with the command ‘shutdown immediate’.

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

6. Startup the database in restricted mode using the command ‘startup restict’ so no user activity can pickup segments from the tablespace TEMPORARY.

SQL> startup restrict
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 159386500 bytes
Database Buffers 444596224 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

7. Drop the old datafiles using the command ‘alter database’

SQL> alter database tempfile ‘/data1/temprary01.dbf’
drop including datafiles;
Database altered.

SQL> alter database tempfile ‘/data1/temprary02.dbf’
drop including datafiles;
Database altered.

SQL> alter database tempfile ‘/data1/temprary03.dbf’
drop including datafiles;
Database altered.

8. View the new temp files with the following command.

SQL> select file_name from dba_temp_files where tablespace_name=’TEMPORARY’;
FILE_NAME
———————–
/data3/temprary01.dbf
/data3/temprary02.dbf
/data3/temprary03.dbf

SQL>

9. Open the database for normal use with ‘alter system’ command.

SQL> alter system disable restricted session;

System altered.

SQL>

This completes the movement of temporary tablespaces tempfiles.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g

Movement of Oracle datafiles

Leave a comment

Oracle allows for the movement of datafiles from one storage device to another when you exhaust space on the original device without having to recreate your database. In this procedure we will move the datafiles located on LUN data4 to a new LUN on data1.

1. Find all datafiles located on mount point /data4 and view size in MB.

SQL> select tablespace_name, bytes/1024/1024, file_name from dba_data_files where
file_name like ‘%data3%’;

SYSTEM /data4/oradata/system01.dbf
DRSYS /data4/oradata/drsys01.dbf
TOOLS /data4/oradata/tools01.dbf
OEM /data4/oradata/oem01.dbf
USERDATA /data4/oradata/userdata_01.dbf
USERDATA /data4/oradata/userdata_02.dbf
USERDATA /data4/oradata/userdata_03.dbf
USERDATA /data4/oradata/userdata_04.dbf

SQL> select SUM(bytes)/1024/1024 from dba_data_files
13:15:46 2 where file_name like ‘%data4%’;

SUM(BYTES)/1024/1024
——————–
32546

2. The data files in tablespace USERDATA amount to 32GB of space,
we will move these data files to the new mount point /data1.

3. Shutdown the oracle database with the command ‘shutdown immediate’.

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

4. Startup the oracle database in restricted mode, this will prevent non-DBA users from accessing the database while you moving the data files. Use the command ‘startup restrict’.

SQL> startup restrict
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 155192196 bytes
Database Buffers 448790528 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

5. Take the tablespace USERDATA offline.

SQL> alter tablespace USERDATA offline;

Tablespace altered.

SQL>

6. View the status of the tablespace USERDATA to ensure that it is in an offline state.

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name =
‘USERDATA’;

TABLESPACE_NAME STATUS
—————————— ———
USERDATA OFFLINE

SQL>

7. Shutdown the database with the command ‘shutdown immediate’.

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

8. Move the data files of tablespace USERDATA to their new locations with OS command ‘cp’. One could use the command mv, which is normally faster, however if anything goes wrong you do not have a backup of your datafile.

cp /data4/oradata/userdata_01.dbf /data1/oradata/userdata_01.dbf
cp /data4/oradata/userdata_02.dbf /data1/oradata/userdata_02.dbf
cp /data4/oradata/userdata_03.dbf /data1/oradata/userdata_03.dbf
cp /data4/oradata/userdata_04.dbf /data1/oradata/userdata_04.dbf

9. Open the database in a nomount state with ‘startup nomount’.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 155192196 bytes
Database Buffers 448790528 bytes
Redo Buffers 7135232 bytes
SQL>

10. Change the location of the data files moved in the control file with the command ‘alter database rename datafile’.

SQL> alter database rename datafile ‘/data4/oradata/userdata_01.dbf’ to
‘/data1/oradata/userdata_01.dbf’;
Database alerted.

SQL> alter database rename datafile ‘/data4/oradata/userdata_02.dbf’ to
‘/data1/oradata/userdata_02.dbf’;
Database alerted.

SQL> alter database rename datafile ‘/data4/oradata/userdata_03.dbf’ to
‘/data1/oradata/userdata_03.dbf’;
Database alerted.

SQL> alter database rename datafile ‘/data4/oradata/userdata_04.dbf’ to
‘/data1/oradata/userdata_04.dbf’;
Database alerted.

11. Mount the database with the command ‘alter database mount’.

SQL> alter database mount;

Database altered.

SQL>

12. Open the database with command ‘alter database open’.

SQL> alter database open;

Database altered.

SQL>

13. Bring the tablespaces back online.

SQL> alter tablespace USERDATA online;

Tablespace altered.

SQL>

14. Validate that the tablespace is online with the following SQL.

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name
2 ‘USERDATA’;

TABLESPACE_NAME STATUS
—————————— ———
USERDATA ONLINE

SQL>

Completes movement of Oracle data files.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle

Setup Automatic Oracle STATSPACK jobs

Leave a comment

Oracle provides the package DBMS_JOB(Pre 10g) and DBMS_SCHEDULER(Post 10g) to allow for the automation of pre-scheduled routine tasks in the Oracle database, just as they are performed by CRONTAB on UNIX and the Scheduler on Windows. This article will explain the use of DBMS_JOB and DBMS_SCHEDULER to take automatic snapshots for the STATSPACK utility every two hours. It is important to realize that STATSPACK stores information related to system performance in the database, thus the more often you take snapshots, the great resources you will be consuming.

1. The DBMS_JOB package contains several procedures and functions for creation, modification and deletion of scheduled jobs for Oracle 9i and earlier versions. For Oracle 10g and higher versions use DBMS_SCHEDULER package, which provides for greater flexibility and control over job creation. A complete listing of the procedures and functions can be retrieved in SQL*PLUS with the command desc for both packages.

2. Use the SUBMIT procedure to create a new job to execute. The below statement will execute once every two hours.

For 9i or earlier use dbms_job.submit.

variable jobno number;

exec dbms_job.submit(job=>:jobno, what=>’begin statspack.snap;
end;’,next_date=>sysdate,interval=>’sysdate+1/12′);

For 10g or higher use dbms_scheduler.create_job (NOTE: start_date is a timestamp not a date value, so use the following query to determine the timestamp format for your database (

Oracle

Taking STATSPACK Snapshots and producing reports

Leave a comment

Oracle’s STATSPACK performance diagnostic tool allows you to
analyze the performance of your Oracle RDBMS. This Analysis can be used to adjust parameters, determine high wait time events, and determine the SQL which causes the highest load on your system.

1. Logon to your Oracle database as sysdba.

mylunix:mydb:/mydb:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 2 07:38:57 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. The STATSPACK package is used to take snapshots, by calling the procedure snap. Take your first snapshot at the beginning of your peak usage and at evenly spaced intervals in between. IE: If your peak usage is between 8AM and 4PM, start taking your snapshots at 8AM and every 2 hours ( 10AM, 12PM, 2PM, and 4PM). This will allow you to take reports during the entire span of your peak usage and at intervals in between.

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

SQL>

3. Once you are ready to take a report from you snapshots, you will have to find the snapshot ids which identify a specific snapshot range. This can be done by querying the table PERFSTAT.STATS$SNAPSHOT.

SQL> select snap_id, to_char(snap_time, ‘DD-MON-YY HH:MI:SS’) from
perfstat.stats$snapshot
where snap_time between to_date(’02-AUG-09 07:00:00′,’DD-MON-YY HH24:MI:SS’)
and to_date(’02-AUG-09 17:00:00′,’DD-MON-YY HH24:MI:SS’); 2 3

SNAP_ID TO_CHAR(SNAP_TIME,
———- ——————
1 02-AUG-09 08:22:29
11 02-AUG-09 10:00:07
21 02-AUG-09 12:00:03
31 02-AUG-09 14:00:12
41 02-AUG-09 15:00:48
42 02-AUG-09 14:00:47

6 rows selected.

SQL>

4. Once you have your high and low snap ids, call the Oracle script
$ORACLE_HOME/rdbms/admin/spreport.sql to generate an external trace file of your database performance. This script requires three input variables:
beginning snap_id, ending snap_id, and OS file name.

SQL> @?/rdbms/admin/spreport.sql

Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
———— ———— ——— —————– —– ——————–
mydb mydb 1 02 Aug 2009 08:22 5
11 02 Aug 2009 10:00 5
21 02 Aug 2009 12:00 5
31 02 Aug 2009 14:00 5
41 02 Aug 2009 15:00 5
42 02 Aug 2009 16:00 5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 42

5. Your report will be generated in the current working directory.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Dart:mylinux:/mydb>ls -lrt
-rw-r–r– 1 oracle dba 197698 Aug 2 16:38 sp_1_42.lst

This completes execution of STATSPACK SNAPSHOTS and generation of
STATSPACK reports.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle

Setup of Oracle STATSPACK Diagnostics tool

Leave a comment

Oracle STATSPACK is a tool that allows you to analyze the performance of your Oracle RDBMS over a specific period of time. Normally the analysis of how your database is performing should be based over a period of the highest usage, but over a length of time that would avoid short term peaks to skew your results. STATSPACK has been in use since Oracle 8i with continual enhancements with every new version release. In this article, we will show how to install and prepare your database for the use of STATSPACK.

Also see:
Taking STATSPACK snap shots and producing reports.
Setup of STATSPACK scheduled jobs.

1. Logon to Oracle as sys dba

MyLinux:mydb:/mydb:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Aug 3 09:03:30 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. The STATSPACK tool is owned by the user PERFSTAT and specific storage should be created to avoid interference with your database. In this step will create specific storage for PERFSTAT user.

SQL> create tablespace perfstat datafile ‘/mydb/ordata/perfstat01.dbf’ size 2096m;

Tablespace created.

SQL> alter tablespace perfstat add datafile ‘/mydb/ordata/perfstat02.dbf’ size 2096m;

Tablespace altered.

3. The user PERFSTAT and all components of the STATSPACK tool are created with the ORACLE provided script SPCREATE.SQL. Execute the script $ORACLE_HOME/rdbms/admin/spcreate.sql to create the perfstat user and data structures.

SQL> @?/rdbms/admin/spcreate.sql

4. The SPCREATE.SQL script accept three parameters before execution: Password; Default Tablespace; and Temp Tablespace. Enter the following when prompted:
PERFSTAT Password
Default Tablespace
Temp Tablespace

5. After execution has occurred, check the following list files to determine that no errors occurred.

spcusr.lis – Creates the perfstat user account.
spctab.lis – Creates all data structure under the perfstat user account.
spcpkg.lis – Creates the perfstat packages.

6. This completes the build of Oracle’s STATSPACK diagnostic tool in your database.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com