4). Locate Files that Contain Certain Strings

$ find . -print | xargs grep -i v\$dba_objects

5) To Delete top 10 huge files in directory,

Use,

$du -sk | sort -nr | head | awk ‘{print $2}’  | xargs rm -r

6) To list hidden files
ls -al
7)command is used to create a new empty file with the default permissions
touch filename
8.Command to used to delete a directory
rm -R /dir

9) command is used to move or rename files and directories
mv file1 file2
mv * /dirname
mv /dirname/* .
10) command is used to copy files and directories

cp file1 file2
cp * /dirname
cp /dirname/* .

11)command is used to find the location of an executable you are using
which executable filename

File permissions

12) command can be used to read or set default file permissions for the current user
umask 022
The umask value is subtracted from the default permissions (666) to give the final permission

13)command is used to alter file permissions after the file has been created
chmod ( example chmod 777 filename or xyz*.*)
14)command is used to reset the ownership of files after creation
chown
eaxmple chown -R oinstall.dba *

OS user management

15)command is used to add OS users
useradd -G oinstall -g dba -d /usr/users/my_user -m -s /bin/ksh my_user
where
The “-G” flag specifies the primary group.
The “-g” flag specifies the secondary group.
The “-d” flag specifies the default directory.
The “-m” flag creates the default directory.
The “-s” flag specifies the default shell.
16)command is used to modify the user settings after a user has been created
usermod -s /bin/csh my_user
17)command is used to delete existing users
userdel -r my_user(The “-r” flag removes the default directory)
18) command is used to set, or reset, the users login password
passwd my_user
19)command can be used to list all users who have OS connections
who
root> who | head -5
root> who | tail -5
root> who | grep -i ora
root> who | wc -l

The “head -5″ command restricts the output to the first 5 lines of the who command.
The “tail -5″ command restricts the output to the last 5 lines of the who command.
The “grep -i ora” command restricts the output to lines containing “ora”.
The “wc -l” command returns the number of lines from “who”, and hence the number of connected users

Process management

20) commands can be used to get information about the host
The “uname” and “hostname” commands can be used to get information about the host:

root> uname -a
OSF1 oradb01.lynx.co.uk V5.1 2650 alpha

root> uname -a | awk ‘{ print $2 }’
oradb01.lynx.co.uk

root> hostname
oradb01.lynx.co.uk

Error lines in Files
cat alert_LIN1.log | grep -i ORA- ( i- case insensitive)
cat alert_LIN1.log | grep -i ORA- | wc -l

Remove Old Files

The find command can be used to supply a list of files to the rm command:

find /backup/logs/ -name daily_backup* -mtime +21 -exec rm -f {} ;
Remove DOS CR/LFs (^M)
Remove DOS style CR/LF characters (^M) from UNIX files using:

sed -e ‘s/^M$//’ filename > tempfile
The newly created tempfile should have the ^M character removed.

Compress Files
In order to save space on the filesystem you may wish to compress files such as archived redo logs. This can be using either the gzip or the compress commands. The gzip command results in a compressed copy of the original file with a “.gz” extension. The gunzip command reverses this process:

gzip myfile
gunzip myfile.gz
The compress command results in a compressed copy of the original file with a “.Z” extension. The uncompress command reverses this process:

compress myfile
uncompress myfile

                        

Related posts:

1. Kill Oracle process from UNIX server In some scenarios , my database may be hung and...
2. Configuring Kernel Parameters for Oracle database server Impacts of  kernel parameter on oracle server ? On SGA(Shared...
3. Oracle Installation One of the most important duties of an Oracle DBA...

Related posts brought to you by Yet Another Related Posts Plugin.

Enable at the database level

SQL>ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY; – to enable
SQL>ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY; – to disable

Alternately  we can configure the  SQL_TRACE  parameter  in  the init.ora file. This will enable trace for all sessions and the background
sql_trace = TRUE — to enable
sql_trace = FALSE — to disable

Enable SQL trace for current session

SQL>ALTER SESSION SET sql_trace = TRUE; — to enable
SQL>ALTER SESSION SET sql_trace = FALSE; — to disable

Enable SQL trace for another session

Oracle DBA can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procedure to enable or  disable SQL tracing for a particular session.

Before we can checked the session ID and session serial# for the perticur session  by using the following query

SQL>SELECT  sid,serial#  FROM v$session WHERE  osuser = OSUSER;

After getting the sid and serial# , we can user the following command to enable the session SQl tracing

SQL>EXEC dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
SQL>EXEC dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);
SQL>EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>SID, serial#=>SERIAL, sql_trace=>TRUE);
SQL>EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>SID, serial#=>SERIAL, sql_trace=>FALSE);

In Oracle 10g the SQL tracing options have been centralized and extended using the DBMS_MONITOR package. Following is  a few possible variations for enabling and disabling SQL trace in Oracle 10g.

SQL> EXEC DBMS_MONITOR.session_trace_enable;
SQL> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable;
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>sid, serial_num=>serial#);
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>sid, serial_num=>serial#, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234);
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>’client’);
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>’client’, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.client_id_trace_disable(client_id=>’client’);

10046 trace event

The 10046 trace event allows to capture tracing at a very fine level, the execution of a given set of SQL statements. This can be enable at session level or database level. This event can be set in the parameter file, or by dynamically for the database session or even for another session.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Identify Oracle trace files

Trace output is written to the database’s UDUMP/BDUMP directory.The default naming convention for the trace files is  “DB_NAME”_ORA_”SID”.trc where:
* DB_NAME  is the name of the Oracle SID,
* SID is the session id

Formating trace file
Trace output is not easily readable. TKProf oracle provided utility is used to format trace output.

Check the Environment

Before tracing enabled ,the environment must  be configured by performing the following steps:

Enable Timed Statistics – TIMED_STATISTICS parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times.The trace output is more meaningful with setting of this parameter.

The command to enable timed statistics is:

SQL> ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

Check the User Dump Destination Directory – The trace files is generated at the place configured as “user_dump_dest” parameter in the init.ora file .Before activating tracing make sure that enough space exists on the device to support the number of trace files that you expect to generate.

Trace Level

                        

Related posts:

1. Killing Oracle Sessions There are different ways to kill oracle sessions From  SQL*PLUS...

Related posts brought to you by Yet Another Related Posts Plugin.

• On SGA(Shared Global area) on oracle server
• Impacts on the total number of users and files in the database
• Impacts the number of users and the ability to use some optional features.

To see all kernel parameters value on your system . execute the following command.

su – root
sysctl -a

List of Kernel Parameters :

shmmax
shmall
shmmni
sem
fs.file-max
fs.aio-max-nr
net.ipv4.ip_local_port_range
net.core.rmem_default
net.core.rmem_max
net.core.wmem_default
net.core.wmem_max

How we can change the Kernel parameters?

If the value of any kernel parameter is different from the recommended value, then change the value to for better performance of the system.
In Linux kernel parameters are stores in the file /etc/sysctl.conf file.
To change the parameters follow the following steps.

1. Edit the file /etc/sysctl.conf
2. Add or edit lines similar to the following

Note :
Include lines only for the kernel parameter values that you want to change.
For the semaphore parameters (kernel.sem), you must specify all four values. However, if any of the current system parameter values are greater than the recommended values, then keep using the larger values.

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
fs.aio-max-nr=1048576
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 4194304

Setting kernel parameters value in the  /etc/sysctl.conf file  persist when you restart the system.

Execute following command to effect Kernel changes immediately to server

su – root
sysctl -p

                        

Related posts:

1. Oracle Initialization Parameters On startup of Oracle database instance,Oracle reads Initialization Parameters stored...
2. Initialization Parameters in Oracle On startup of Oracle database instance,Oracle reads Initialization Parameters stored...

Related posts brought to you by Yet Another Related Posts Plugin.

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250452 bytes
Variable Size             197135212 bytes
Database Buffers          411041792 bytes
Redo Buffers                2940928 bytes
Database mounted.
Database opened.
SQL>

                        

Related posts:

1. ORA-12638: Credential retrieval failed Today while I am going to startup my database ,...
2. Recover database when all control file and Redo log files are lost As an Oracle DBA , Recover database and maintain high...

Related posts brought to you by Yet Another Related Posts Plugin.

SQL> startup
ORA-12638: Credential retrieval failed

I have edited the sqlnet.ora file and modified the same as follows

Before – SQLNET.AUTHENTICATION_SERVICES= (NTS)

After – SQLNET.AUTHENTICATION_SERVICES= (NONE)

After modification , I perform the following operation to bring the database open

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250452 bytes
Variable Size             192940908 bytes
Database Buffers          415236096 bytes
Redo Buffers                2940928 bytes
Database mounted.
Database opened.
SQL>

                        

Related posts:

1. ORA-01078: failure in processing system parameters SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could...
2. Recover database when all control file and Redo log files are lost As an Oracle DBA , Recover database and maintain high...
3. The Oracle Instance When Oracle database started, a memory structure, called the System...

Related posts brought to you by Yet Another Related Posts Plugin.

Physical Backups

Oracle database physical backup includes of backing up the  physical files as

• Datafiles
• Archived redo logs
• Control files
• Network files (tnsnames.ora,listener.ora,sqlnet.ora)
• Parameter file

Physical backups are made on the disk or on the tape devices.

Logical Backups

Oracle database logical backup made using export utility (using exp or expdb command ).

Logical backup contain logical objects like tables  and procedures .
These backups are in binary form and can be extracted only using import(imp or impdp command)  utility. Logical backup is an optional way to protect database.

                        

Related posts:

1. Oracle database backup Database backup is a primary goal of an Oracle DBA...
2. Oracle database Logical structures Oracle logical structures includes oracle data blocks, extents, segments and...
3. RMAN Backup Oracle Recovery manager commonly known as RMAN is a very...

Related posts brought to you by Yet Another Related Posts Plugin.

• jobs
• ps

jobs command

jobs command will list all the jobs running in the background as follows:
[1]  – 12427 Running              test.pl
[2]  + 12540 Stopped            myprog.pl

The value within “[]” is the job number. By this number you can control our jobs, like you can kill job or  bring the job to foreground.

ps command

ps    command will display a list of processes as follows

PID TTY      TIME CMD
12427  pts/10   1:07  test.pl

Where

• PID – Process identification number. By this number you can kill the process.
• TTY- terminal name where the process is executing
• TIME – time elapsed
• CMS- the process name, your script or program name

Bringing Job to the Foreground

Please remember, a background job may be brought to the foreground if it was initiated during the current login session. Jobs which were initiated during other login sessions and which are still running cannot be brought to the foreground. However, it is possible to send signals to such jobs using the “kill” command. A background job or a stopped job may be brought to the foreground with ” fg” command.

fg  %1 , 1 is the job number

Terminating Background Jobs

Jobs from the current session can usually be terminated by bringing them to the foreground and interrupting them with control-c key.

Another way for terminating jobs running under your user id  with  “kill” command.

Different way to terminate a job

By job id number as follows :

kill -9 %1

By Process Id (PID) as follows :

kill -9 PID

Please remember with kill you can not only terminate jobs for current session, you can terminate jobs initiated by other user login session also.

                        

Related posts:

1. Controling jobs on UNIX systems UNIX  job monitoring and control facilities allows you to monitor...
2. Background jobs on Unix impact on System Performance Is your background jobs on Unix impact on System Performance?...
3. Kill Oracle process from UNIX server In some scenarios , my database may be hung and...

Related posts brought to you by Yet Another Related Posts Plugin.

Commands to control your jobs on UNIX systems

jobs -  This command check the status of the jobs in the current session. It shows the jobs executing on background also.

ps –u – This command check the status of processes including those from other sessions.

kill -9 %1 -  kill a job ( 1 is job number, preceding % sign) by job number

kill -9 PID – kill a process by PID (Process ID ) number

bg – Run the most recently stopped  job on the background

fg- Bring the most recently backgrounder job to foreground

fg %1 –Bring a job to foreground by specifying job number

Control-z – Stop foreground job and return you to shell prompt. Remember this will not kill the job

Difference between Foreground and Background jobs

Foreground job can interrupt using  keyboard input and signals such as Control-C from the controlling terminal, background jobs cannot. If your  are disconnected from your session, foreground jobs are terminated by a hang-up signal, while backgrounds jobs are not. Both foreground and background jobs can write to the controlling terminal.

How you can put a Job in the Background

To initiate a job in the background, you have to put an ampersand (&) at the end of the command line. For example if your script name is test.sh and want to execute on the background, then use the following command at the command line.

sh test.sh &

If you want to put a job in background, which is already running on foreground, then first stop the job by pressing control-Z and type bg command.

Read about monitoring background jobs on UNIX

                        

Related posts:

1. Monitoring background Jobs on UNIX systems To monitor background jobs on UNIX, you can use the...
2. Background jobs on Unix impact on System Performance Is your background jobs on Unix impact on System Performance?...
3. Killing Oracle Sessions There are different ways to kill oracle sessions From  SQL*PLUS...

Related posts brought to you by Yet Another Related Posts Plugin.

Yes, but it depends upon the priority of the job and how much RAM they require to run. Large jobs like  you are downloading a huge file from the web, can consume immense amount of RAM which can be very painful for your system performance . If your job requires too much RAM, the system CPU will be wasting a lot of time swapping memory to and from disk rather than executing the jobs.

Secondly ,other variable affects your system performance is job priority. If your jobs run at low priority, they will be executed at the system’s leisure time and your system performance  will not affected . If they run at normal priority, they will increase the competition for resources and finally performance of your may be degraded. On a heavily loaded system, jobs should only be put into the background at low priority, using the “nice” (Which is very nice to balance  your system performance) command. Very simple to use nice command. Just put “nice” before your regular command.

For example, if you normally execute your program with the following

myscript  < inputfile > outfile &

where myscript reads from ” inputfile” and writes to ” outfile ” . To run the same in low priority  you should use as follows :

nice -19  myscript  < inputfile > outfile &

There is a command to determine current load of your system , which is “uptime” command .

The output of the “uptime” command looks like as follows

14:49:07 up 192 days,4:15,105 users,load average: 0.16,0.52,0.34

Have you noticed three numbers at the right of the resulting report ? These three numbers indicate the load of your system . If the first number of the three number is more than 3, then background jobs only initiated with low priority (niced)

“renice” command

To change the priority of a job , which is already running, you can use  “renice” command.

For example,If one job is already running, you can check the process id of the job using “ps”  or “ps -u ” command.  suppose the process id of the job is 12337. Run the job in low priority using the following command

renice -19 12337

Read about job Control on UNIX systems

                        

Related posts:

1. Monitoring background Jobs on UNIX systems To monitor background jobs on UNIX, you can use the...
2. Controling jobs on UNIX systems UNIX  job monitoring and control facilities allows you to monitor...

Related posts brought to you by Yet Another Related Posts Plugin.

Following is the steps to clone oracle database.

1. Take a copy of the production database’s initialization file .
2. Take a backup of the production database’s control file using the following command on SQL prompt( Sign in as SYSDBA)
SQL>alter database backup controlfile to trace;
This command will create trace file in udump folder.

3. FTP the new initialization file and  trace file  created on step 2 to destination server into temp folder.
4. Shutdown the production database
5. Copy all datafiles from production server  to new server using rcp command
rcp /u01/oradata/oldsid/* newhost:/u01/oradata/newsid
rcp /u01/oradata/oldsid/* newhost:/u01/oradata/newsid
rcp /u03/oradata/oldsid/* newhost:/u03/oradata/newsid
rcp /u04/oradata/oldsid/* newhost:/u04/oradata/newsid
Please note that you must edit the control file created at sept 2 and modify the new datafile name.

6.Edit control file  on new server and make the modifications as follows

a) CREATE CONTROLFILE REUSE DATABASE “<old db name>” NORESETLOGS
TO
CREATE CONTROLFILE SET DATABASE “<new db name>” RESETLOGS ARCHIVELOG

b) Remove the “recover database” and “alter database open statement” from the control file.
c) Rename the datafile name as stored on the new server.
7. Save the control file as clone_dbname_server.sql
8. Create the required directories like bdump ,cdump,udump,pfile etc.
cd /u01/app/oracle/
mkdir newsid
cd newsid
mkdir bdump
mkdir cdump
mkdir udump
mkdir pfile
9. Copy the initialization file from temp folder which was  copied from production at step 3  to pfile folder.
10. Edit the initialization file make the necessary modification of parameters like log_archieve_dest_1 ,bdump,cdump etc.
11. Start the new instance on nomount mode with the following command.
sqlplus
startup nomount pfile=<complete path of filename>

12. On SQL prompt execute the command as @<controlfile with complete path>
13. Alter database mount
14. alter database archievelog
15. Alter database open

                        

Related posts:

1. Create Oracle Database Manually As being an Oracle DBA , we can create database...
2. Recover database when all control file and Redo log files are lost As an Oracle DBA , Recover database and maintain high...
3. Oracle Installation One of the most important duties of an Oracle DBA...

Related posts brought to you by Yet Another Related Posts Plugin.