Oracle DBA Daily Experiences

There are few commands one can use to save a file n
To save and quit: <esc>:wq
To save (if modified) and then quit, if not modified then does not save quit: <esc>:x (by jwood@opsource.net)
To save (if modified) and then quit, if not modified then does not save quit: <esc>ZZ (by boomslaang@hotmail.com)
To save and continue edititing: <esc>:w
To quit: <esc>:q (Note: You will be able to quit from vi if no changes were made in the file since opening the file or all changes have already been saved, if there changes in the file vi will display the following message “No write since last change..”)
To force quit without saving: <esc>:q!
To save with a different filename: <esc>:w <new filename path can be specified too> (It can be used incases when one doesn’t have permission to overwrite the file or directory is not writable by the user so using this option the changes made by the user can be saved with a different name in a different folder)

The view DBA_ROLES doesn’t have the created date column to display when the role was created, but the information is stored in sys.user$ the underlying table. So using the following query one can find the date the role was created.

SQL> select name, to_char(ctime, ‘DD-MON-YYYY HH24:MI:SS’) from sys.user$ where name = ‘RESOURCE’;

NAME TO_CHAR(CTIME,’DD-MON-YYYYHH24:MI:SS’)
——– —————————————–
RESOURCE 22-JUL-2005 00:42:50

This script will delete files modified more than 14 days ago and have file name that begin with daily_backup , it will do recursive search within directories that exists in the directory /work/dba/backup/logs/

find /work/dba/backup/logs/ -name "daily_backup*" -mtime +14 -exec rm -f {} ;

Useful commands in vi:
To delete lines
One at a time: <esc>dd
n lines at a time: <esc>ndd – where n is the number of lines to be deleted, eg: 3dd would delete 3 lines

To delete all blank lines (lines that don’t have spaces in between): <esc>:g/^$/d
To delete all blank lines that have spaces in them: <esc>:g/^ *$/d
To delete from current cursor position to end of line: <esc>d$ or <esc>D
To delete from current cursor position to beginning of line: <esc>d0
To delete word from current cursor position forward: <esc>dw
To delete word from current cursor position backward: <esc>db
To delete current character: <esc>dl or <esc>x
To delete one character backward: <esc>dh or <esc>X

This parameter controls the format of the archive log file name.  This parameter can’t be changed on the fly therefore requires a restart of the instance.  This parameter can be changed with scope=spfile if spfile is used.  

If the format defined in the parameter log_archive_format is invalid the database will startup but archiver will fail to archive logs which will cause database to hang and in the alert log the following message would be reported “ORA-00294: invalid archivelog format specifier..” or you will see an error message when you try to archive the current redo log by running “ALTER SYSTEM ARCHIVE LOG CURRENT;”, so if you change this parameter a quick test can be done by running the above SQL to make sure oracle is able to archive the redo log.

Format options available on 9i:

%s – log sequence number
%S – log sequence number, zero filled
%t – thread number, needed when running RAC as each node creates it’s own archivelog
%T thread number, zero filled, needed when running RAC as each node creates it’s own archivelog

Format options available on 10g

%s – log sequence number
%S – log sequence number, zero filled
%t – thread number, needed when running RAC as each node creates it’s own archivelog
%T – thread number, zero filled, needed when running RAC as each node creates it’s own archivelog
%a – activation ID
%d – database ID
%r – resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.

In 10g, %s, %t, %r are required to be present in the parameter, if it doesn’t the database fail to start with the error ORA-19905: log_archive_format must contain %s, %t and %r.  Using this format makes it the archive log filename unique for that instance.

The following article has information how to start database in archivelog mode

Using the following steps one can clone a database manually. In the following example PRD represents the source and DEV represents target/new database.

1) Get list of datafiles on the PRD database. In the following example this database has 3 datafiles.
SQL> SELECT name FROM v$datafile;
NAME
——————————————————————————–
/u03/oradata/PRD/system01.dbf
/u03/oradata/PRD/undotbs01.dbf
/u03/oradata/PRD/sysaux01.dbf

3 rows selected.

2) On the PRD (source) database, run the following query to find the last archived logs, the archive logs created after backup begin is run needs to be copied, so make a note of sequence # shown needs to be copied on the DEV (target) database to restore.
SQL> SELECT sequence#, TO_CHAR(next_time, ‘DD-MON-YYYY HH24:MI:SS’)
FROM (SELECT sequence#, next_time FROM V$archived_log ORDER BY next_time DESC)
WHERE rownum < 2;

SEQUENCE# TO_CHAR(NEXT_TIME,’D
———- ——————–
6 12-JAN-2009 20:40:15

1 row selected.

3) Execute the following SQL to make the datafiles in backup mode.  This command is supported in 10g for 9i, each tablespace would need to placed in backup mode by using “ALTER TABLESPACE name BEGIN BACKUP;”
SQL> ALTER DATABASE BEGIN BACKUP;

Database altered.

4) Now the datafiles can be copied from PRD (source) to DEV (target) machine from the list created in step (1)
$ cd /u03/oradata/DEV
$ cp -pi /u03/oradata/PRD/system01.dbf .
$ cp -pi /u03/oradata/PRD/undotbs01.dbf .
$ cp -pi /u03/oradata/PRD/sysaux01.dbf .

4) Execute the following SQL to make the datafiles out of backup mode.
SQL> ALTER DATABASE END BACKUP;

Database altered.

5) Perform some logswitches on the PRD (source) database, this step will create archive logs on the source database.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

6) On the PRD (source) database, run the SQL in step (2) again to get the current sequence# of archived logs. The archived log files created for the sequence# between step (2) and step (6) would need to copied to the DEV (target) machine. In this example archive logs with sequence# 6-12 would need to be copied on the target host.

SEQUENCE# TO_CHAR(NEXT_TIME,’D
———- ——————–
14 12-JAN-2009 20:43:05

1 row selected.

7) Modify the pfile by copying the pfile from PRD (source) to target or if the source is using spfile, run the following SQL to create the pfile “CREATE PFILE=’/tmp/initDEV.ora’ TO SPFILE;” The parameters in the pfile needs to be modified for the target database. In the following example the following parameters where modified where “PRD” was replaced with “DEV”.

*.audit_file_dest=’/u01/app/oracle/admin/DEV/adump’
*.background_dump_dest=’/u01/app/oracle/admin/DEV/bdump’
*.user_dump_dest=’/u01/app/oracle/admin/DEV/udump’
*.core_dump_dest=’/u01/app/oracle/admin/DEV/cdump’
*.control_files=’/u03/oradata/DEV/control01.ctl’,'/u03/oradata/DEV/control02.ctl’,'/u03/oradata/DEV/control03.ctl’
*.db_name=’SMOXY’
*.log_archive_format=’DEV_%t_%s_%r.arc’
*.log_archive_dest_1=’LOCATION=/u03/oradata/DEV/arch’

8 ) On the PRD (source) instance create the backup control file to trace using the following SQL
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database Altered

Modify the control file creation script in udump folder, section where the resetlogs and change REUSE TO SET, modify the name PRD to DEV, change the path of the datafiles if needed, only the following section would needs to be run.
CREATE CONTROLFILE SET DATABASE “DEV” RESETLOGS ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 500
MAXINSTANCES 1
MAXLOGHISTORY 2045
LOGFILE
GROUP 1 (
‘/u03/oradata/DEV/redo01a.log’,
‘/u03/oradata/DEV/redo01b.log’
) SIZE 100M,
GROUP 2 (
‘/u03/oradata/DEV/redo02a.log’,
‘/u03/oradata/DEV/redo02b.log’
) SIZE 100M,
GROUP 3 (
‘/u03/oradata/DEV/redo03a.log’,
‘/u03/oradata/DEV/redo03b.log’
) SIZE 100M,
GROUP 4 (
‘/u03/oradata/DEV/redo04a.log’,
‘/u03/oradata/DEV/redo04b.log’
) SIZE 100M
DATAFILE
‘/u03/oradata/DEV/system01.dbf’,
‘/u03/oradata/DEV/undotbs01.dbf’,
‘/u03/oradata/DEV/sysaux01.dbf’
CHARACTER SET WE8ISO8859P1;

9) On the target machine, and start the instance in nomount, make sure it will use the pfile created in step (7)
export ORACLE_SID=DEV
SQL> startup nomount

10) Using the script create the control file created in step ( 8 )

11) Now database can be recovered, at this step Oracle will prompt for the archive logs copied in step (2) and (6) or a specific point in time can be specified. When all the archive logs are applied, type CANCEL
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

ORA-00279: change 9603 generated at 01/12/2009 20:40:43 needed for thread 1
ORA-00289: suggestion : /u03/oradata/DEV/arch/DEV_1_9_675981354.arc
ORA-00280: change 9603 for thread 1 is in sequence #9

21:07:48 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u03/oradata/DEV/arch/DEV_1_9_675981354.arc
ORA-00279: change 9614 generated at 01/12/2009 20:40:56 needed for thread 1
ORA-00289: suggestion : /u03/oradata/DEV/arch/DEV_1_10_675981354.arc
ORA-00280: change 9614 for thread 1 is in sequence #10
ORA-00278: log file ‘/u03/oradata/DEV/arch/DEV_1_9_675981354.arc’ no longer needed for this recovery

……..

21:11:25 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL

Media recovery cancelled.

Or (for point in time recovery)
SQL> RECOVER DATABASE UNTIL TIME ’2009-01-11:15:14:30′ USING BACKUP CONTROLFILE;
…
Media recovery complete

12) Now the database can be opened with the reset logs option.
ALTER DATABASE OPEN RESETLOGS;

Database altered.

13) Add datafile to temporary tablespace, the size of the datafile can be adjusted as needed.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u03/oradata/DEV/temp01.dbf’ SIZE 100M REUSE AUTOEXTEND OFF;

14) Change global name of the cloned database
SQL> ALTER DATABASE RENAME global_name TO DEV;

SQL> SELECT * FROM global_name;

GLOBAL_NAME
——————————————————————————–
DEV

—– Connect.java —-
—- To compile c:> javac Connect.java
—- Note: Make sure the CLASSPATH env is set. MySQL Connnector/J can be downloaded from http://dev.mysql.com/downloads/connector/
—- Example: CLASSPATH=mysql-connector-java-5.1.7\mysql-connector-java-5.1.7-bin.jar;.
—- To run c:> java Connect

— Connect.java code
import java.sql.*;

public class Connect {
public static void main(String argv[]) throws Exception {
Connection conn = null;
try {
String url = “jdbc:mysql://localhost:3306/test?user=root&password=root”;
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
conn = DriverManager.getConnection(url);
if ( conn != null )
System.out.println(“A connection to DB has been made”);

}
catch(Exception e)
{
throw e;
}
finally {
if ( conn != null )
conn.close();
conn = null;
}
}
}

If you use SSH to login using your own user account and su to oracle or su directly to oracle then different users would share the same history file. If you want each user to have it’s own history file using the snippet of script given below one can create different shell history file for each user.

The output may vary for tty and who from different flavors of Unix/Linux but can easily to customized to the environment.

Note: For KSH (Korn shell) the env HISTFILE needs to set in the beginning of the script else it doesn’t take into effect and default .sh_history is used even though value set for HISTFILE.

For KSH(Korn):

# find TTY connected to and remove /dev/
mytty=`tty | sed ‘s/\/dev\///’`

# this will work if you login with your own user and su to oracle
# find the username based on the tty connected
shuser=`who | grep “$mytty” | awk ‘{print $1}’`

# if you directly su to oracle then and if you always connect from the same host you can use the hostname to postfix the history file name instead
# example: calora6db01q:/home/oracle $ who
# ajaffer pts/1 Jan 08 12:36 myhostname
# shuser=`who | grep “$mytty” | awk ‘{print $6}’`

# if tty found then set HISTFILE
if [[ "$shuser" != "" ]]; then
export HISTFILE=$HOME/.sh_history.$shuser
fi

HISTSIZE=100 # sets to number of commands to remember in the command history
HISTFILESIZE=1000 # sets maximum number of lines to store in the history file

History commands
$ fc -l 10 20 # display commands 10 through 20
$ fc -l -5 # display last 5 commands
$ fc -l ls # shows the last command beginning with ls
$ fc -e vi 5 10 # starts vi with commands from 5 – 10

Tom Kyte, Oracle DBA and founder of the Ask Tom blog, gave the Oracle Database Insider his resolutions for 2009 in hopes that other DBAs will follow suit. “In the coming year,” says Kyte, “I resolve to…

• Practice a restore at least once a month under different scenarios. DBAs are allowed to make mistakes—with one exception: They cannot make mistakes in recovery. We can fix any other mistake easily, but not recovery mistakes.

• Learn something new. When was the last time you read the “what’s new in” chapters in the documentation? Take a couple of minutes to see what’s new.

• Participate in user group discussion forums. If I just do the same thing day in, day out, never experiencing what others are doing, I’ll stagnate. The easiest way to stay current, to network, and to learn something new is to participate.

• Become a mentor for someone more junior. I’ll get two things out of this: First, I will learn a ton of new stuff because they will ask me things I don’t fully know. Second, I’ll have put in place the next generation of DBAs so that I can move up or move over in the future. Training your replacement is one sure way to be able to progress yourself.

• Be more thoughtful, don’t jump to conclusions, take my time. As the old saying goes, haste makes waste. And that is never more true than when doing something that will take hours or longer to accomplish, as many DBA tasks may take. I will study the options and pick the one that is best suited for the problem at hand—realizing that the approach I take in 2009 might be very different from the one I took in 2008, 2000, 1995, or any other time.

Depending on the platform different command may be available, below are the commands for AIX and SUN to find the shared libraries needed to run a binary. This may not show all the libraries needed as the application may be referenced in the code and dynamically loading them during different phases of execution of a program.

For AIX
# if the binary is 64bit one can use -X32_64 or -X64
/u01/app/oracle/bin $ dump -X32_64 -H oracle

oracle:

***Loader Section***
Loader Header Information
VERSION# #SYMtableENT #RELOCent LENidSTR
0×00000001 0x0000071e 0×00040902 0x000000e2

#IMPfilID OFFidSTR LENstrTBL OFFstrTBL
0×00000008 0x00413b28 0x000056a5 0x00413c0a

***Import File Strings***
INDEX PATH BASE MEMBER
0 /u01/app/oracle/rdbms/lib/:/u01/app/oracle/lib/:/usr/lib:/lib
1 libc.a shr_64.o
2 libpthreads.a shr_xpg5_64.o
3 libjox10.a shr.o
4 libdl.a shr_64.o
5 libperfstat.a shr_64.o
6 libodm.a shr_64.o
7 libc.a aio_64.o

For SUN (may be AIX depends on version).
$ cd $ORACLE_HOME
$ ldd oracle
oracle needs:
/usr/lib/libc.a(shr_64.o)
/usr/lib/libpthreads.a(shr_xpg5_64.o)
/u01/app/oracle/lib/libjox10.a(shr.o)
/usr/lib/libdl.a(shr_64.o)
/usr/lib/libperfstat.a(shr_64.o)
/usr/lib/libodm.a(shr_64.o)
/usr/lib/libc.a(aio_64.o)
/unix
/usr/lib/libcrypt.a(shr_64.o)
/usr/lib/libcfg.a(shr_64.o)
/usr/lib/liblvm.a(shr_64.o)