Quick Tips File
Last Updated 11-Sep-19
Cron
Jobs
If you want to see all your cron jobs just type crontab
-l
If you want to edit a cron jobs just type crontab -e
Lines that begin with "#" are considered comments and are ignored.
An environment setting is of the form
NAME = VALUE
Cron will look at a special environment variable, MAILTO. Any
output generated by your cron jobs will be sent to the address
specified by MAILTO (if it is not specified it will be sent to the
owner of the crontab). If MAILTO is defined by empty (MAILTO=""),
no mail will be sent.
The format of the cron table entry includes five (5) time fields
followed by a command. Commands are executed when the time
specified by the date fields matches the current time. The five
time fields are as follows:
Field Allowed Values
minute 0-59
hour 0-23
day of month 0-31
month 1-12 (or names, see below)
day of week 0-7 (0 or 7 is Sun, or use names)
A field may be an asterisk (*), which indicates all values in
the range are acceptable. Ranges of numbers are allowed, i.e.
"2-5" or "8-11", and lists of numbers are allowed, i.e. "1,3,5" or
"1,3,8-11". Step values can be represented as a sequence, i.e.
"0-59/15", "1-31/3", or "*/2".
Names can be used for the "month" and "day of week" fields. Use
the first three letters (case-insensitive) of the particular month
or day. Ranges or lists of names are not allowed.
Examples:
MAILTO="someone@somewhere.com"
15 1 * * * [COMMAND]
Explanation: executes the command [COMMAND] at 1:15 AM every day
40 23 1 * * [COMMAND]
Explanation: executes the command [COMMAND] on the first of every
month at 11:40 PM
0-30/10 9,17 * * 1-5 [COMMAND]
Explanation: executes the command [COMMAND] on Monday-Friday (1-5)
every 10 minutes for the first half hour (0-30/10) of the 9 AM and
5 PM hours (9,17)
0 */4 * jan sun [COMMAND]
Explanation: executes the command [COMMAND] on each Sunday in
January at midnight, 4 AM, 8 AM, noon, 4 PM, and 8 PM
30 4 1,15 * fri [COMMAND]
Explanation: executes the command [COMMAND] at 4:30 AM on the 1st
and 15th of each month, plus every Friday
0 0 19 8 * [COMMAND] or
0 0 19 aug * [COMMAND]
esta es una linea de ejemplo:
00 20 * * * /usr/bin/find / -name core -exec rm -f {} \;
minute hour monthday month weekday command
VI EDITOR – VERY IMPORTANT TO LEARN
Start with vi <filename>
Edit modes: These keys enter editing modes and type in the text of your document.
i —Insert before current cursor position
I —Insert at beginning of current line
a —Insert (append) after current cursor position
A —Append to end of line
r —Replace 1 character
R —Replace mode
<ESC> —Terminate insertion or overwrite mode
Deletion of text
x —Delete single character
dd —Delete current line and put in buffer
ndd —Delete n lines (n is a number) and put them in buffer
J —Attaches the next line to the end of the current line (deletes carriage return).
Undo
u —Undo last command
Cut and Paste
yy —Yank current line into buffer
nyy —Yank n lines into buffer
p —Put the contents of the buffer after the current line
P —Put the contents of the buffer before the current line
Cursor Positioning
ctrl-d —Page down
ctrl-u —Page up
:n —Position cursor at line n
:$ —Position cursor at end of file
^g —Display current line number
h,j,k,l —Left,Down,Up, and Right respectivly. Your arrow keys should also work
String Substitution
:n1,n2:s/string1/string2/[g] —Substitute
string2 for string1 on lines n1 to n2. If g is included
(meaning global),
all instances of string1 on each line are substituted. If g
is not included, only the first instance per matching
line is substituted.
^ —matches start of line
. —matches any single character
$ —matches end of line
These and other “special characters” (like the forward slash) can be “escaped” with \
i.e to match the string “/usr/STRIM100/SOFT” say “\/usr\/STRIM100\/SOFT”
Examples:
:1,$:s/dog/cat/g —Substitute ‘cat’ for ‘dog’, every instance
for the entire file – lines 1 to $ (end of file)
:23,25:/frog/bird/ —Substitute
‘bird’ for ‘frog’ on lines 23 through 25. Only the first
instance on each line is substituted.
Saving and quitting commands
These commands are all prefixed by pressing colon (:) and then
entered in the lower left corner of the window.
Press <ESC> to exit from an editing mode.
:w —Write the current file.
:w new.file —Write the file to the name ‘new.file’.
:w! existing.file —Overwrite an existing file with the file currently being edited
:wq —Write the file and quit.
:q —Quit.
:q! —Quit with no changes.
:e filename —Open the file ‘filename’ for editing.
:set number —Turns on line numbering
:set nonumber —Turns off line numbering
Deleting Files by 'n'
Dates
There are three times associated with a file
atime - last access time
ctime - last status change time
mtime - last modify time
Remove all files from /home/dpafumi older than 5 days:
find /home/dpafumi -type f -mtime +5 -exec rm -f {} \;
Print out what files from /home/dpafumi older than 5 days:
find /home/dpafumi -type f -ctime +5 -print
Switching
ORACLE_HOME and ORACLE_SID in Unix.
Have you ever had the need to change your environment settings to
connect to another database on the same server? The following Unix
shell script will ask you which database you want to connect to,
and export your new environment variables. You may need to add
additional variables, depending on your system and application.
#!/bin/ksh
echo " "
echo "Enter the Oracle database you want: "
echo " "
echo " 1. PROD 8.0.5.2.1 "
echo " "
echo " 2. TEST 8.1.6.0.0 "
echo " "
echo " 3. DEV 7.3.4.1.0 "
echo " "
read TermType
case $TermType in
1) export ORACLE_SID=prod
export ORACLE_HOME=/u01/app/oracle/product/8.0.5
export LD_LIBRARY_PATH=/u01/app/oracle/product/8.0.5/lib;;
2) export ORACLE_SID=test
export ORACLE_HOME=/u01/app/oracle/product/8.1.6
export LD_LIBRARY_PATH=/u01/app/oracle/product/8.1.6/lib;;
3) export ORACLE_SID=dev
export ORACLE_HOME=/u01/app/oracle/product/7.3.4
export LD_LIBRARY_PATH=/u01/app/oracle/product/7.3.4/lib;;
esac
echo
echo Setting ORACLE_SID to $ORACLE_SID
echo Setting ORACLE_HOME to $ORACLE_HOME
echo Setting LD_LIBRARY_PATH to $LD_LIBRARY_PATH
echo
echo
- How to find O/S VERSION
or system name
uname -ap
- Different uses of 'ps
-ef'
ps -ef | grep
smon &nb sp; (Checks for databases)
ps -ef | grep ows
(Checks fo r web listeners)
ps -ef | grep f45
(Checks f or Form listeners)
- Finding process id for trace or other process
ps -fu username
- Zipping up a directory for backup
1. zip -r file
[Directory], or
2. zip -r file
*fresh* (This would zip up all files with fresh in the name, plus
any directories, and all dirs underneath with fresh in the name.
- How to tar a directory
tar -cvf
/tmp/filename *
do this from the
top direct ory that you want to bundle into the tar'd file
- using 'tar' to list contents of tar file, without
extracting
tar -tvf
[filename]
SETUP Display
If you have problems with the display, try the following in your
startup file:
if ( $?DISPLAY == 0 ) setenv DISPLAY `who -m | cut -f 2 -d
"(" | cut -f 1 -d ")"`:0.0
- To copy a directory structure
(Directory, sub-directories, and all their files)
cp -rp
- Checking space on a disk
df -tk
- Show size of directories (All files
under a specific directory)
du -sk
- Useful grep options
grep -c This
counts
the number of lines that contain the pattern
grep -i Ignore
upper/lower case
grep -l Print
only
the
name
of
the
files
that
contain
the
pattern.
Does
not
repeat file names.
- Count the number of files in a
directory
ls | wc -l
- How to send a message to everyone on a
UNIX machine
1. Create a file
that contains the message
2. $ wall <
[filename] ( Ex: wall < message )
3. You may need
to specify the directory 'wall' is in. /usr/sbin/wall < message
- Removing some files to
free up space
1. Go to the
destination directory
2. Run 'find .
-name "*.*O" -print'. Make sure this returns files you want to
delete
3. Run 'find .
-name "*.*O" -exec rm -f {} \;
WARNING! Make
sure you do this right or you can remove more files that you want.
Find Memory in CPU
/usr/sbin/prtconf
or
dmesg | more
- To find the printers available on a
server
lpstat -p
- rcp: Setting up UNIX
system to do a remote copy from another box
1. Create
or modify file '.rhosts' in your login's home directory
2. Add a
line such as...
machinename.us.oracle.com loginname
Example
apptest15.us.oracle.com applmgr
3. telnet
to the machine, and login as user specified in .rhosts
4. issue
following command:
rcp -r * login_to_your_machine@yourmachine:/directory_to_copy_to
example:
rcp -r * stuck@apptest9:/u01/stuck/
- How to do a search and
replace in vi
:%s,[string
to replace], [replacing string], g
example: :%s,oracle,stuck,gc {would replace oracle with stuck}
The above
command does this for the whole document without confirming, if
you would
like to confirm each change use the command:
:%s,[string to replace], [replacing string], gc
This will
stop after each search, type a y to confirm the change
- Stripping control character (^M) from an ascii
file
Sometimes,
in a patch, you will receive a file that was created in NT, so on
Solaris it
has a ^M at the end of each line. This can cause problems running
the script
sometimes. To strip out the ^M use the following
cat filename | tr -d ^M > newfilename
NOTE:
When
typing
the
^M
in
the
command
above
use
Cntl
V
Cntl M to type it
Performing Loop Actions with Files
- Deleting Trace Files
for FILE in *.trc
do
echo $FILE
rm $FILE
done
- Rename Files
OLDSUFFIX=aaa
NEWSUFFIX=bbb
for FILE in *."$OLDSUFFIX"
do
NEWNAME=´echo "$FILE" | sed -e
"s/${OLDSUFFIX}\$/$NEWSUFFIX/"´
mv "$FILE" "$NEWNAME"
done
- Finding the IP address for different
systems
grep -i finsun
/etc/hosts
- How to find the tcpip address for a machine
nslookup [machine
name]
- Different examples of the FIND command
find . -exec
grep -lis fdugpi.lpc \{\} \\;
(searches in this
directory down)
find / -exec
grep -lis fdugpi.lpc \{\} \\;
(searches from
root directory down)
find . -exec
grep -lis file {} \;
(searches this
directory down)
find . -follow
-name bug734234 -print
(use if there are
linked sub-directories, such as in tcpatch)
find . -name
"*.pls" -exec egrep -il ARP_STANDARD {} \;
(This searches
all .pls files for a file that contains the string ARP_STANDARD)
On Linux
$uname -a
64 Bits
Linux gaylord.stata.com 2.6.11-1.27_FC3 #1 Tue May 17 20:24:57 EDT
2005 x86_64 x86_64 x86_64
GNU/Linux
64 Bits
Linux caddo.stata.com 2.6.9-5.0.5.EL #1 SMP Fri Apr 8 14:20:58 EDT
2005 ia64 ia64 ia64
GNU/Linux
32 Bits
Linux tango.stata.com 2.6.10-1.771_FC2smp #1 SMP Mon Mar 28
01:10:51 EST 2005 i686 i686 i386
GNU/Linux
$uname
-m
It seems like the uname -m actually gives
* x86_64 when it is an kernel 64 bits
* i686 for 32 bits kernel
$getconf LONG_BIT
which returns either 32 or 64
On Solaris
isainfo -b -v
/usr/bin/isainfo -kv
On AIX
$ getconf -a | grep KERN
$ file /usr/lib/boot/unix*
On HP-UX
/usr/bin/ getconf KERNEL_BITS
/usr/bin/file /stand/vmunix
Change
HostName and IP Address on Linux
First IDENTIFY what you
have:
Display current IP address and setting for network interface
called eth0
# /sbin/ifconfig eth0
Output:
eth0 Link encap:Ethernet HWaddr 00:30:48:5A:BF:46You can change ip address using ifconfig command itself. To set IP address as 192.168.1.5, enter command:
inet addr:10.5.123.2 Bcast:10.5.123.63 Mask:255.255.255.192
inet6 addr: fe80::230:48ff:fe5a:bf46/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:728204 errors:0 dropped:0 overruns:0 frame:0
TX packets:1097451 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:62774749 (59.8 MiB) TX bytes:1584343634 (1.4 GiB)
Interrupt:177
Change
the IP Address
METHOD 1
Open network configuration file. In this example, it’ll configure
on interface eth0. Type
vi
/etc/sysconfig/network-scripts/ifcfg-eth0
As an example, I'm showing that the current configuration is DHCP:
DEVICE=eth0
BOOTPROTO=dhcp
HWADDR=00:D0:B7:08:09:BB
ONBOOT=yes
Modify the file and perform the following modifications/additions
(Change BOOTPROTO to static and add IP Address and Netmask as new
lines if they’re not existed yet)
DEVICE=eth0
IPADDR=192.168.1.100
NETMASK=255.255.255.0
BOOTPROTO=static
HWADDR=00:D0:B7:08:09:BB
ONBOOT=yes
# The following settings are optional
BROADCAST=192.168.1.255 #Always Finish with 255
NETWORK=192.168.1.0 #Always
Finish with 0
IPV6INIT=yes
IPV6_AUTOCONF=yes
Save and close the file. Define default gateway
(router IP) and hostname in /etc/sysconfig//network file:
vi /etc/sysconfig/network
Append/modify configuration as follows:
NETWORKING=yes
HOSTNAME=machinename.domain.com
TYPE=Ethernet
GATEWAY=10.10.29.65
Save and close the file. Restart networking::
# /etc/init.d/network restart
or
# service network
restart
Make sure you have correct DNS server defined in /etc/resolv.conf
file:
# vi /etc/resolv.conf
Setup DNS Server as follows:
nameserver 10.0.80.11
nameserver 10.0.80.12
nameserver 202.67.222.222
Save and close the file. Now you can ping the gateway/other hosts:
$ ping 10.0.80.12
You can also check for Internet connectivity with nslookup or
host command:
$ nslookup yahoo.com
Review the configuration. Type
ifconfig
METHOD 2
You can also execute the following command from the X
Window and it will show you the GUI Network Tool:
$ system-config-network &
METHOD 3
If you don’t have X windows GUI installed type the
following command at shell prompt:
# system-config-network-tui &
Change
the
HOST NAME
Make sure you are logged in as root:
vi /etc/sysconfig/network
Look for the HOSTNAME line and replace it with the new hostname
you want to use. In this example I want to replace localhost with
redhat9.
HOSTNAME=redhat9
When you are done, save your changes and exit vi.
Next we will edit the /etc/hosts file and set the new hostname.
vi /etc/hosts
Save your changes and exit vi.
The changes to /etc/hosts and /etc/sysconfig/network are necessary
to make your changes persistent (in the event of an unscheduled
reboot).
Now we use the hostname program to change the
hostname that is currently set.
hostname redhat9
And run it again without any parameters to see if the hostname
changed.
hostname
Finally we will restart the network to apply the changes we made
to /etc/hosts and /etc/sysconfig/network.
service network restart
This file was automatically generated by the
/lib/udev/write_net_rules program, run by the
persistent-net-generator.rules rules file. Simple delete it and
reboot the system to recreate the same. This should fix the
problem:
# /sbin/ifconfig | grep "^eth"
Sample outputs:
eth0 Link encap:Ethernet HWaddr 00:0C:29:F3:E4:14 eth1 Link encap:Ethernet HWaddr 00:0C:29:F3:E4:1E
You need to remove the MAC address in
/etc/sysconfig/networking-scripts/ifcfg-eth*. To find out current
MAC address, enter:
# ifconfig -a | grep eth1 | grep HWaddr | awk '{ print
$5}'
# ifconfig -a | grep eth2 | grep HWaddr | awk '{ print $5}'
Update your /etc/sysconfig/networking-scripts/ifcfg-eth0 with eth1
HWaddr (MAC address), enter:
# cat /etc/sysconfig/networking-scripts/ifcfg-eth0
Sample outputs:
DEVICE="eth0" BOOTPROTO="dhcp" NM_CONTROLLED="yes" ONBOOT="yes" HWADDR="00:0C:29:69:0C:4A"
Save and close the file. Restart the network service,
enter:
# service network restart
You can verify new settings with the following commands:
# /sbin/ifconfig -a
# route -n
- Checking OS block_size. Oracle
Block_Size must be equal or multiple of this one
df -g
- Pin Objects
1- As Internal Run:
@dbmspool.sql
@prvtpool.plb
2-Create the following Trigger
CREATE OR REPLACE TRIGGER db_startup_keep
AFTER STARTUP ON DATABASE
BEGIN
sys.dbms_shared_pool.keep('SYS.STANDARD');
sys.dbms_shared_pool.keep('SYS.DBMS_STANDARD');
sys.dbms_shared_pool.keep('SYS.DBMS_UTILITY');
sys.dbms_shared_pool.keep('SYS.DBMS_DESCRIBE');
sys.dbms_shared_pool.keep('SYS.DBMS_OUTPUT');
END;
3- The following Oracle core packages owned by user SYS should
be pinned in the shared PL/SQL area:
DUTIL
STANDARD
DIANA
DBMS_SYS_SQL
DBMS_SQL
DBMS_UTILITY
DBMS_DESCRIBE
DBMS_JOB
DBMS_STANDARD
DBMS_OUTPUT
PIDL
How to delete a database midway
through creation
1. Shutdown DB
2. delete lk.. and control files from $ORACLE_HOME/
3. delete log and dbf files
4. recreate db
- Umask and Permission
You need to change the "umask" to the required 022
and set the permissions on all relevant directories.
Example:
# umask 022
# cd $ORACLE_HOME
# chmod 755 (on all
sub-dirs)
# cd
$ORACLE_BASE/admin/(SID NAME)
# chmod 755 (on all
sub-dirs)
- TWO_TASK to another database
1. Find Values
from system you are connecting to...
echo $ORACLE_SID, echo $FNDNAM, echo $GWYUID
from the system you will be joining to.
2. Set
Environment Variables
TWO_TASK=[net_alias]; export TWO_TASK
FNDNAM=value from $FNDNAM; export FNDNAM
GWYUID=value from $GWYUID; export GWYUID
- To find a PORT that is available when
installing...
netstat -a | grep port#
ex.
netstat -a | grep 8068
Change
The Owner Of Oracle Database Software On Unix
1) Shutdown the database.
2) Shut down all processes running out of the ORACLE_HOME.
(Listener, EM etc.)
3) There are some files under Oracle Home which are owned by
“root” or other users. For Example :
The
file
‘nmo’,
’nmb’
,
‘oradism’
etc
...
in
the
ORACLE_HOME/bin
directory
are
owned by “root” user in 10g.In 9i the list may be different.
We can execute the following command to find the files, which are
not owned by oracle owner
$ cd $ORACLE_HOME
$ find . ! -user
<oracle_owner> | xargs ls -l | tee /tmp/list.txt
(Replace the
<oracle_owner> with the current owner of
installations)
For Example:
$ find . ! -user oracle |
xargs ls –l | tee /tmp/list.txt
-rwsr-x--- 1 root root 65319
Jan 8 19:00 ./bin/extjob
-rwsr-s--- 1 root root 18801
Jan 8 18:47 ./bin/nmb
-rwsr-s--- 1 root root 19987
Jan 8 18:47 ./bin/nmo
-r-sr-s--- 1 root root 14456
Feb 4 2006 ./bin/oradism
-rw-r----- 1 root root 1534
Dec 22 2005 ./rdbms/admin/externaljob.ora
Check and make a note of the ownership of the files, which are
listed as the result of the above command. The list will be also
redirected to the file /tmp/list.txt.
4) Change the ownership of oracle database software by using
recursive “chown” command.
Consider if the current owner of oracle software is "oracle92" who
is from "dba" group, you want to change it to a new user "oracle"
and your oracle software is installed in the directory
"/app/oracle/product/ora92" then you should do:
$ cd /app/oracle/product
$ chown -R oracle:dba ora92
(You require root access or help of your System administrator for
the same.)
The new owner should be also from the same group as like the
current owner. You can verify it by using the “id” command.
$ id <user_name >
Consider your current user is "oracle92" and the group is "dba"
then "id oracle92" will give output as uid=1003(oracle92)
gid=103(dba) where 1003, “oracle92” is the userid and the user
name respectively, 103 and “dba” is the groupid and group name.
The new owner “oracle” should be also from the same group “dba”
5) The recursive chown command will change the ownership of all
the files in the Oracle Home. So as “root” user ,change the
ownership of the files which are listed in the step 3 to the
respective users using a chown.
For example:
After recursive chown
$ ls –l
$ORACLE_HOME/bin/extjob
-rwsr-x--- 1
oracle dba 65319 Jan 8 19:00 ./bin/extjob
Change the ownership using chown.
$chown
root:root $ORACLE_HOME/bin/extjob
-rwsr-x--- 1
root root 65319 Jan 8 19:00 ./bin/extjob
6) All the folders that belong to oracle installation are found in
ORACLE_HOME except the Oracle Central Inventory.The location of
the Central Inventory usually found from the oraInst.loc file,
which contains the path as
"inventory_loc=< path
>/oraInventory"
You have to change the ownership of the oraInventory also to the
new owner.
(oraInst.loc file exists in /var/opt/oracle or /etc/oracle by
default)
In 9i Oracle Universal Installer (OUI) is also found outside the
ORACLE_HOME.The location of OUI is found in the oraInventory from
a file called "oui.loc" which contails the path for
"InstLoc=< path
>/oui".
You have to change the ownership of this also to the new owner.
(In 10g OUI is found inside Oracle Home by default.)
When a connection is attempted from the local database server, the OS username is passed to the Oracle server. If the username is recognized, the Oracle the connection is accepted, otherwise the connection is rejected.sqlplus /
sqlplus /@service
OS_AUTHENT_PREFIX
initialization parameter.
As you can see, the default value is "ops$". If this is not appropriate it can be changed using theSQL> SHOW PARAMETER os_authent_prefix
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
SQL>
ALTER SYSTEM
command, but for now we will use this default value.The situation is complicated slightly on Windows platforms as the domain or machine name forms part of the username presented to Oracle. On Windows platforms you would expect an Oracle username of "OPS$DOMAIN\TIM_HALL" for the Windows user "tim_hall".-- UNIX
CREATE USER ops$diego IDENTIFIED EXTERNALLY;
GRANT CONNECT TO ops$diego;
When using a Windows server, there is an additional consideration. The following option must be set in the "%ORACLE_HOME%\network\admin\sqlnet.ora" file.-- Windows
CREATE USER "OPS$ORACLE-BASE.COM\DIEGO" IDENTIFIED EXTERNALLY;
GRANT CONNECT TO "OPS$ORACLE-BASE.COM\DIEGO";
With the configuration complete, now you can connect as that "diego" user.SQLNET.AUTHENTICATION_SERVICES= (NTS)
Deleting
Files from Windows
Delete files more than X days old in Windows "forfiles" is part of
the resource kit, but ships with Windows 2003 Server and later.
forfiles -p d:\mssql_backup -s -m *.bak -d -2 -c "cmd /C del
@Path"
1. -p: Start in the directory d:\mssql_backup
2. -s: process subdirectories
3. -m: match files using *.bak
4. -d: Find files more than 2 days old
5. -c: Execute the del command to delete the file. @Path has
double-quotes around it already.
Load Balancing tnsnames.ora
First setup multiple listeners on listener.ora. Example:
LSNR1666 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)(HOST = jake)(PORT = 1666))
)
)
)
LSNR2666 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)(HOST = jake)(PORT = 2666))
)
)
)
SID_LIST_LSNR1666 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =
lx10r2.us)
(ORACLE_HOME =
/u01/app/oracle/product/10.2.0.1)
(SID_NAME = lx10r2)
)
)
SID_LIST_LSNR2666 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =
lx10r2.us)
(ORACLE_HOME =
/u01/app/oracle/product/10.2.0.1)
(SID_NAME = lx10r2)
)
)
lsnrctl start lsnr1666
Next, we configure the client. The client naming is controlled by
tnsnames.ora
LX10R2.US =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE=ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 1666))
(ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 2666))
)
(CONNECT_DATA = (SID
= lx10r2))
)
Here, the database alias is "lx10r2.us". Normally, when you
specify multiple address lines for an alias, Oracle will attempt
the first and if that fails will attempt the second. This might
actually work for the poster, but would still pound port 1666
until it couldn't handle any connections and THEN try port 2666.
The key to having a somewhat even distribution over the two
listeners is by using the (LOAD_BALANCE=ON) parameter.
Connect
to a system regardless of machine failure
You can place multiple address entries for a single connection
alias in the TNSNAMES.ORA file. This means that you can connect to
a database, even if some kind of physical failover occurred. Look
at the following example:
10gexpress =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST
= Machine01)(PORT
= 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = Machine02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
Suppose Machine01 is down, then every new SQL*NET connection using
service 10gexpress will
automatically
login to Machine02. However, there is one restriction, the SID
must be the same on both machines. This feature can provide
guaranteed login for application servers and for the Oracle
Parallel Server.
What
can
be done to increase SQL*Net performance?
# Number of packets to skip between checking for breaks (default=4)Prespawned server sessions. You can tell the listener to start up a pool of idle server processes. When a connection request is made, it doesn't have to start a server process; it just hands one of the idle processes to the client (and then starts a new connection in its own time). This is configured in LISTENER.ORA, in the SID_LIST_LISTENER section, as follows:
BREAK_POLL_SKIP=10000
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = yourSID)
(PRESPAWN_MAX = 50)
(PRESPAWN_LIST =
(PRESPAWN_DESC = (PROTOCOL = TCP) (POOL_SIZE = 5)
(TIMEOUT = 2))))
)
PRESPAWN_MAX: if there are over 50 sessions connected to the database, the listener won't prespawn any more.
POOL_SIZE: the listener will maintain an idle pool of 5 server processes.
TIMEOUT: after a client disconnects, the listener will keep the freed-up server process around for two minutes, waiting for a new connection request, before killing that process.
Multiple listeners with load balancing. You can start multiplelisteners on a server, and reference all of the listeners in the TNSNAMES.ORA file. When a client makes a connection request, the SQL*Net client will randomly pick one of the listeners to contact.# Define listener A...
STARTUP_WAIT_TIME_LISTENER_A = 0
CONNECT_TIMEOUT_LISTENER_A = 10
LISTENER_A=
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = yourHost.domain)
(PORT = 1521)))
SID_LIST_LISTENER_A =
(SID_LIST =
(SID_DESC =
(SID_NAME = yourSID)
(PRESPAWN_MAX = 50)))
# Define the second listener...
STARTUP_WAIT_TIME_LISTENER_B = 0
CONNECT_TIMEOUT_LISTENER_B = 10
LISTENER_B=
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = yourHost.domain)
(PORT = 1522)))
SID_LIST_LISTENER_B =
(SID_LIST =
(SID_DESC =
(SID_NAME = yourSID)
(PRESPAWN_MAX = 50)))
The TNSNAMES.ORA service for this database would be something like:
oradb1.world =
(description_list=
(description=
(address_list=
(address=
(protocol=tcp)
(host=yourHost.domain)
(port=1521)))
(connect_data =
(sid = yourSID)))
(description =
(address_list =
(address=
(protocol=tcp)
(host=yourHost.domain)
(port=1522)))
(connect_data =
(sid = yourSID))))
Changing
Oracle
UID and GID
In Unix, the file system only contains numeric UID and GID values,
they only get converted to a name via lookup in /etc/passwd and
/etc/group. (system calls getpwnam(), getpwuid(), getgrnam(), and
getgrgid() perform this task).
Oracle software does not know or care anything about the numeric
UID/GID, only the names. So the change is pretty easy, just like
changing the description for a unique ID in a lookup table in the
database.
Here is a sample scenario. Assumes new UID and GID are not already
in use, of course.
users:
oracle change 101 => 103
groups:
dba change 101 => 21
First, run pwck and grpck commands to clean up any problems with
the respective files. Optional, but recommended (you'd be
surprised what you might find).
# get "before" list of files
to be changed for logging purposes
find / -user oracle -exec ls
-ld {} \; > /tmp/ora_owned_files.lst
# find files which don't
have DBA group, if any (shouldn't be any)
find / -user oracle \!
-group dba -exec ls -ld {} \; >> \
/tmp/ora_owned_files.lst
Shut down all oracle software (confirm with "ps -fu oracle"
command).
# make the change
find / -user oracle -exec
chown 103:21 {} \;
# make backups using RCS or your favorite method
cd /etc
ci -l passwd
ci -l group
# change lookups
vipw [...change oracle UID
to 103, GID to 21]
vi /etc/group [change dba
GID to 21]
# re-run listing to check for consistency
# check output to see what's changed...should be the same as
"before"
# listing
find / -user oracle -exec ls
-ld {} \; > ora_owned_files.lst.new
find / -user oracle \!
-group dba -exec ls -ld {} \; >> /tmp/ora_owned_files.lst.new
&
It might be a little slow, you can experiment with the recursive
option of chown instead of using find. Or, instead of -exec option
of 'find', pipe output to xargs command. Just be sure you handle
symbolic links correctly. (Your SA should understand all of this,
in case you don't).
How to
use rlwrap to get a command history in sql*plus
SQL*Plus does not have a command history function under Linux and
Unix.
rlwrap is a readline wrapper for shell commands which uses input
from the controlling terminal.
It adds a persistent input history for each command and supports
user-defined completion.
You can download the sources for the rpm for rlwrap from HERE. The
most recent version I could find is version 0.26.
There you also find a README and the manpage for rlwrap.
After downloading and unpacking the tar.gz
gunzip rlwrap-0.26.tar.gz
tar -xvf rlwrap-0.26.tar
I ran as root (#)
./configure
make
make check
make install
and that was it.
Now I could call sqlplus this way:
$ rlwrap sqlplus user/password@sid.
Finally I created an alias
alias q="rlwrap sqlplus"
or
alias sqlplus="rlwrap sqlplus"
Database
Automatic Failover On Another Database
A-on SERVER1:
1-open tnsnames.ora file in this path
D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN
2-edit the tnsnames.ora file and modfiy your database service to
be like this
ORC1=
(DESCRIPTION=
(FAILOVER=on)
(ADDRESS=(PROTOCOL=tcp)(HOST=SERVER1)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=SERVER2)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORC1)))
and add another service for the the database ORC2 on SERVER2
ORC2 =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = SERVER2)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORC2)
)
)
B-On SERVER2
1-open tnsnames.ora file in this path
D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN
2-edit the tnsnames.ora file and modfiy your database service to
be like this
ORC2=
(DESCRIPTION=
(FAILOVER=on)
(ADDRESS=(PROTOCOL=tcp)(HOST=SERVER2)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=SERVER1)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORC2)))
and add another service for the the database ORC1 on SERVER1
ORC1 =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = SERVER1)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORC1)
)
)
C-at this point your databases can failover on each other
D-To test that your configuration is working try to connect an
application to or TOAD to the ORC1 database with schema
scott/tiger and then open SQLPLUS and shutdown this database
it will be automatically switched on the other database ORC2
Speed-Up Oracle on Your
Notebook PC
Details on how to make Oracle run as fast as possible on minimal
computer setup
1- Exclude Oracle data files directory(s) from all anti-virus and
anti-spyware automatic and manual scans. Myself, I exclude the
entire "C:\Oracle” directory - where I place all my Oracle files.
2- Eliminate all unnecessary background processes (demons) or
services. Here are some basic examples (note that your computing
environment needs and thus extraneous list will surely vary - so
check with your local administrators to be sure and thus
safe):
1. Windows XP
1. ATI Hot Key
Poller
2. Computer
Browser
3. Distributed
Link Tracking Client
4. Error
Reporting Service
5. Indexing
Service
6. Upload Manager
2. Redhat Linux(s)
1. anacron
/ cron
2.
httpd
3.
ISDN
4.
Net FS
5.
NFS Lock
6.
Send Mail
3- Clean up (i.e. purge) the Oracle logging directory tree. For 9i
and 10g, this was subdirectories such as BDUMP and UDUMP under
your "admin/SID” directory. Starting with 11g, you need to look at
the entire "DIAG" directory.
4- Exclude Oracle data files directory(s) from excessive or
unnecessary last update timestamp maintenance.
1. Windows
1.
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\FileSystem\NtfsDisableLastAccessUpdate
= 1
2. Redhat Linux(s)
1. chattr +A
file_name per Oracle control, data and log file
2. chattr -R +A
directory_name per Oracle SID data directory
3. Edit
/etc/fstab for the Oracle Home and/or data file systems
Example: /dev/sda6 /
home ext3
defaults,noatime 1 1
VNC -
Install Oracle Software on Remote Linux Server
If not already done so download and install a client version of
VNC so you can connect to the server. There are many out there,
for the purpose of this post I use UltraVNC.
You can download a free version from http://www.uvnc.com/download/index.html
1- By default VNC Server is installed in your RedHat and OEL OS,
but it is not configured. The way VNC works is when started it
creates a client instance for the user and binds it to a specific
port. So if have an account on the Linux box you can setup a VNC
Server session for that user, you don’t need to be root. For the
purpose of this document I’m going to use oracle as the user to
setup a VNC Session as this is the user I want use to install the
software. However to start the VNC Service you must be root.
As the root user run the following command:
service vncserver start
Starting VNC server: no
displays
configured
[ OK ]
2- Login to the Linux box as oracle to install the Oracle software
Run the command to create a new VNC server instance for the oracle
user:
vncserver :1
3- You will be ask to supply password information. This is what
you will enter when connecting from your desktop client. This
password is also independent of the actual Linux user password.
You will require a password
to access your desktops.
Password:
Verify:
xauth: creating new
authority file /home/oracle/.Xauthority
Creating default startup
script /home/oracle/.vnc/xstartup
Starting applications
specified in /home/oracle/.vnc/xstartup
Log file is
/home/oracle/.vnc/lisa.nz.oracle.com:1.log
4- As you can see a new instance server_name:1 has been created.
If you were to run the vncserver command again another instance
server_name:2 will be created.
If you are going through a firewall you will need to ensure that
the port 5901 (port 1) is open between your client desktop and the
Linux Server.
Depending on the options chosen at install time a firewall could
be in place. The simplest way to disable this is using the
command. You will need to be root.
service iptables stop
This will stop the firewall while you install. If you just want to
add a port to the accepted lists use the firewall UI. You will
need to be root.
system-config-security-level
Now you are ready to connect to the server via the VNC. Using the
software installed in step one start the VNC Client. You should be
prompted for the server and port.
Other Options:
Launch VNC with X
Desktop $HOME/.vnc/xstartup
#!/bin/sh
# Uncomment the following
two lines for normal
desktop:
unset SESSION_MANAGER
exec
/etc/X11/xinit/xinitrc
[ -x /etc/vnc/xstartup ]
&& exec /etc/vnc/xstartup
[ -r $HOME/.Xresources ]
&&xrdb $HOME/.Xresources
xsetroot -solid grey
vncconfig -iconic &
xterm -geometry 80x24+10+10
-ls -title "$VNCDESKTOP
Desktop" &
#twm&
Start VNC with larger
window
vncserver -geometry
1280x1024
Cleanly Kill VNC
vncserver
kill :1
To open a vncserver on a
specific port
vncserver -geometry
1280x1024 :64
Clone
Database Software Installation
Goto your Oracle home:
$ cd $ORACLE_HOME
Tar (and zip) the Oracle home (probably you encounter some
errors, some files in the bin directory are set to owner ‘root’,
this is alright):
$ tar czf
/path/to/tarfile.tgz --exclude "*.log" --exclude
"network/admin/*" --exclude "$(hostname)*" --exclude "/dbs/*" *
Create a directory for the new home:
$ mkdir -p /path/to/new/home
Untar the created tarball:
$ tar xzf
/path/to/tarfile.tgz -C /path/to/new/home
Cd to the new Oracle home:
$ cd /path/to/new/home/
Cd to the clone/bin directory in the home:
cd clone/bin
Run clone.pl:
./clone.pl
ORACLE_HOME="/path/to/new/home" ORACLE_BASE="/path/to/base"
-defaultHomeName
Here is the output of clone.pl on a testsystem: (see how clone.pl
calls runInstaller!)
./runInstaller -clone
-waitForCompletion "ORACLE_HOME=/oracle/db/11.2.0.1_test"
"ORACLE_BASE=/oracle" -defaultHomeName -defaultHomeName -silent
-noConfig -nowait
Starting Oracle Universal
Installer...
.
Checking swap space: must be
greater than 500 MB. Actual 1852 MB Passed
Preparing to launch Oracle
Universal Installer from /tmp/OraInstall2010-07-02_04-34-52PM.
Please wait ...Oracle Universal Installer, Version 11.2.0.1.0
Production
Copyright (C) 1999, 2009,
Oracle. All rights reserved.
.
You can find the log of this
install session at:
/oracle/oraInventory/logs/cloneActions2010-07-02_04-34-52PM.log
....................................................................................................
100% Done.
.
.
.
Installation in progress
(Friday, July 2, 2010 4:36:13 PM CEST)
.............................................................................
77% Done.
Install successful
.
Linking in progress (Friday,
July 2, 2010 4:36:40 PM CEST)
Link successful
.
Setup in progress (Friday,
July 2, 2010 4:41:23 PM CEST)
Setup successful
.
.
End of install
phases.(Friday, July 2, 2010 4:47:18 PM CEST)
Starting to execute
configuration assistants
The following configuration
assistants have not been run. This can happen because Oracle
Universal Installer was invoked with the -noConfig option.
--------------------------------------
The
"/oracle/db/11.2.0.1_test/cfgtoollogs/configToolFailedCommands"
script contains all commands that failed, were skipped or were
cancelled. This file may be used to run these configuration
assistants outside of OUI. Note that you may have to update this
script with passwords (if any) before executing the same.
The
"/oracle/db/11.2.0.1_test/cfgtoollogs/configToolAllCommands"
script contains all commands to be executed by the configuration
assistants. This file may be used to run the configuration
assistants outside of OUI. Note that you may have to update this
script with passwords (if any) before executing the same.
.
--------------------------------------
WARNING:
The following configuration
scripts need to be executed as the "root" user.
/oracle/db/11.2.0.1_test/root.sh
To execute the configuration
scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts
.
The cloning of OraHome1 was
successful.
Please check
'/oracle/oraInventory/logs/cloneActions2010-07-02_04-34-52PM.log'
for more details.
As the clone.pl output indicates, root.sh needs to be run after
the clone.pl command.
Uninstall Oracle
from Linux
As root perform:
* chkconfig oracle off
* chkconfig oracle -del
* $ORACLE_HOME/bin/localconfig delete
* rm -rf /etc/ora*
* rm -rf $ORACLE_HOME
* rm -rf $ORACLE_BASE
* rm -rf /tmp/.oracle
* rm -rf /var/tmp/.oracle
xhost and DISPLAY
The correct sequence is:
1.Log on to the computer that *owns* an XWindows screen.
This could be a Linux machine with gnome/KDE/twm, or perhaps a
Windows machine with cygwin/X, Xming, or perhaps Hummingbird (now
OpenText) eXceed ... these are known as XServers.
2.Tell that Xserver (screen) to accept remote requests, by issuing
"xhost +" (note that the + disables security)
3.Log on to the computer / user that is to use the XWindows - the
one that contains the runInstaller (or that will run Oracle)
4.Tell that to display on the computer that is to be used for
display using export DISPLAY=(host):display.screen
5.Run the program that needs a display
Many people, especially students, log in to root on the computer
and then su to oracle to do the installs. While this is
fundamentally a stupid thing to do, because using root
unnecessarily increases potential security risks and is it NOT
good practice and will it NOT be allowed in any self-respecting
business, the following would be the sequence
1.Log on to XWindows as root. Do NOT su to root! Do
NOT pass GO. Do NOT collect $200.
2.Open a terminal
3.Enter: xhost +
4.Find out which DISPLAY is used by entering: set | grep -i
DISPLAY
5.Enter: su - oracle
6.Enter: export DISPLAY=:0.0 # of course, base this on step 4
7.Enter: runInstaller # (or whatever is required, such as xeyes)
The most common mistake is running xhost after su. This will not
work.
The xhost must be issued by the first user - the one that owns the
screen.
Writing to the alert log
If you want to write a message to the alert log, you can use the
undocumented KSDWRT procedure of the DBMS_SYSTEM package.
This procedure has two parameters, the first one must be "2" to
write to the alert file, the second one is the message you want to
write.
Here is an example:
execute
sys.dbms_system.ksdwrt(2,to_char(sysdate)|| ' -- ');
Use 1 instead of 2 to write to the trace file
Use 3 to write to both.
Person
------
Person_id Name Address_id
--------- ---------------- ----------
00001 Fred Bloggs 00057
00002 Joe Smith 00092
00003 Jane Doe
00004 Sue Jones 00111
Address
-------
Address_id Address_Desc
---------- -------------------------
00057 1, Acacia Avenue, Anytown
00092 13, High Street, Anywhere
00113 52, Main Road, Sometown
Then the simple join:
SELECT PERSON.NAME, ADDRESS.ADDRESS_DESC
FROM PERSON, ADDRESS
WHERE PERSON.ADDRESS_ID = ADDRESS.ADDRESS_ID
returns:
NAME ADDRESS_DESC
---------- ------------
Fred Bloggs 1, Acacia Avenue, Anytown
Joe Smith 13, High Street, Anywhere
But the outer join:
SELECT PERSON.NAME, ADDRESS.ADDRESS_DESC
FROM PERSON, ADDRESS
WHERE PERSON.ADDRESS_ID = ADDRESS.ADDRESS_ID(+)
returns:
NAME ADDRESS_DESC
---------- ------------
Fred Bloggs 1, Acacia Avenue, Anytown
Joe Smith 13, High Street, Anywhere
Jane Doe
Sue Jones
Note the two new rows for Jane Doe and Sue Jones. These are the
people who do not have matching records on the ADDRESS table. Sue
Jones had an address_id on her PERSON record, but this didn't
match an address_id on the ADDRESS table. ( Probably a data
inconsistency ). Jane Doe had NULL in her PERSON.ADDRESS_ID
field, which obviously doesn't match any address_id on the ADDRESS
table.
Note that the outer join is created by including (+) on the WHERE
clause which joins the two tables. The (+) is put against the
column-name on the deficient table, ie. the one with the
missing rows. It is very important to put the (+) on the correct
table: putting it on the other table will give different results.
eg. the query:
SELECT PERSON.NAME, ADDRESS.ADDRESS_DESC
FROM PERSON, ADDRESS
WHERE PERSON.ADDRESS_ID(+) = ADDRESS.ADDRESS_ID
returns:
NAME ADDRESS_DESC
---------- ------------
Fred Bloggs 1, Acacia Avenue, Anytown
Joe Smith 13, High Street, Anywhere
52, Main Road, Someplace
Managing Dates
You can use fractions with sysdate (or any date column) to add
hours, minutes and/or seconds. For hours, use a denominator of 24;
for minutes use 1440; for seconds: 86400.
For example "sysdate + 3/24" will add 3 hours, "sysdate + 5/1440"
will add 5 minutes; "sysdate + 45/86400" will add 45 seconds. You
can use values like "sysdate + 30/24" to add one day +6
hours if you need to. Example:
SELECT to_char(sysdate,'dd-mon-yyyy hh:mi:ss'),
TO_CHAR(SYSDATE + 10/1440,'dd-mon-yyyy hh:mi:ss') FROM DUAL;
Using Oracle functions
for dates
An alternative to this method is to use the numtodsinterval function.
Example to add 2 hours:
SELECT to_char(sysdate,
'DD/MON/YY HH24:MI:SS'), to_char(sysdate + numtodsinterval(2, 'HOUR'),
'DD/MON/YY HH24:MI:SS')
FROM dual;
TO_CHAR(SYSDATE,'D
TO_CHAR(SYSDATE+NU
------------------ ------------------
17/JAN/06 12:50:53 17/JAN/06 14:50:53
Here the numtodsinterval function is doing the work of dividing
2/24 for hours.
Valid options for the numtodsinterval are ‘DAY’, ‘HOUR’, ‘MINUTE’,
or ‘SECOND’. Here is an example using ‘MINUTE’.
When working with minutes the numtodsinterval function is much
more readable.
SELECT to_char(sysdate,
'DD/MON/YY HH24:MI:SS'), to_char(sysdate + numtodsinterval(45,
'MINUTE'), 'DD/MON/YY HH24:MI:SS')
FROM
dual;
TO_CHAR(SYSDATE,'D
TO_CHAR(SYSDATE+NU
------------------
------------------
17/JAN/06 12:50:09 17/JAN/06
13:35:09
Adjusting Months and
Years
To work with months and years (either of which may have a varying
number of days) Oracle has provided the function numtoyminterval.
This works much like the numtodsinterval function mentioned above
by taking a number and a string. Valid options for the string are
‘YEAR’ or ‘MONTH’.
SELECT sysdate, sysdate +
numtoyminterval(5, 'MONTH')
FROM dual;
SYSDATE
SYSDATE+NUMTOYMINT
------------------
------------------
17/JAN/06 12:53:49 17/JUN/06
12:53:49
SELECT sysdate, sysdate +
numtoyminterval(2, 'YEAR')
FROM dual;
SYSDATE
SYSDATE+NUMTOYMINT
------------------
------------------
17/JAN/06 12:54:08 17/JAN/08
12:54:08
Truncate minutes and seconds
select to_char(TRUNC(sysdate,'HH24'),'dd-mon-yyyy hh:mi:ss') from
dual;
select to_char(TRUNC(sysdate,'MI'),'dd-mon-yyyy hh:mi:ss') from
dual;
How can I get the time difference between two date columns
Look at this example query:
define date1 = sysdate+3;
define date2 = sysdate ;
select
floor(((&date1-&date2)*24*60*60)/3600) || ' HOURS ' ||
floor((((&date1-&date2)*24*60*60)
- floor(((&date1-&date2)*24*60*60)/3600)*3600)/60) || '
MINUTES ' ||
round((((&date1-&date2)*24*60*60)
- floor(((&date1-&date2)*24*60*60)/3600)*3600
-(floor((((&date1-&date2)*24*60*60) -
floor(((&date1-&date2)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS ' time_difference
from DUAL;
undef date1;
undef date2;
Adding the Oracle SID to
the SQL Prompt
Just add the following statements to the GLOGIN.SQL to populate
the SQL command prompt with the Oracle SID:
-- Add any sqlplus
commands here that are to be executed when a user
-- starts SQL*Plus on
your system
-- Used by Trusted Oracle
column ROWLABEL format A15
-- OLD Added by me to show
the SID
-- column sid new_value osid
noprint;
-- select
upper(substr(global_name,1,(instr(global_name,'.')-1))) sid
-- from global_name;
-- set sqlprompt '&osid
SQL> '
set serveroutput on size
1000000
alter session set nls_date_format = 'DD/MON/YY hh24:mi:ss';
-- Added by me to show the
SID
set head off
set feed off
set term off
column C01 format A30
new_value DIA
column C02 format A8
new_value NOMBASE
column C03 format A8
new_value NOMUSER
column C04 format A10
new_value TERMINAL
column C05 format A5
new_value SESSION
select
decode(upper(substr(global_name,1,(instr(global_name,'.')-1))),
null,global_name,
upper(substr(global_name,1,(instr(global_name,'.')-1)))) C02,
USERENV('TERMINAL') C04,
USERENV('SESSIONID') C05
from global_name;
select USER C03,
to_char(sysdate,'DD Month YYYY - HH24:MI') C01 from dual;
set term on
set verify off
select
'****************************************'||CHR(10)||
'
&DIA
'||CHR(10)||CHR(10)||
' Connected to :
&NOMBASE'
||CHR(10)||
' As User :
&NOMUSER.'
||CHR(10)||
' Terminal : &TERMINAL
'
||CHR(10)||
' Session_ID :
&SESSION'
||CHR(10)||
'****************************************' from dual
/
undef DIA
set verify on
set feed on
set head on
set sqlprompt
"&NOMBASE/&NOMUSER> "
undef NOMUSER
undef NOMBASE
clear buffer
clear COLUMNS
-- Used for the SHOW ERRORS
command
column LINE/COL format A8
column
ERROR format A65 WORD_WRAPPED
-- Used for the SHOW SGA
command
column
name_col_plus_show_sga format a24
-- Defaults for SHOW
PARAMETERS
column
name_col_plus_show_param format a36 heading NAME
column
value_col_plus_show_param format a30 heading VALUE
-- For backward
compatibility
set pagesize 14
-- Defaults for SET
AUTOTRACE EXPLAIN report
column id_plus_exp format
990 heading i
column parent_id_plus_exp
format 990 heading p
column plan_plus_exp format
a60
column object_node_plus_exp
format a8
column other_tag_plus_exp
format a29
column other_plus_exp format
a44
Rebuilding Indexes
You can use the ALTER INDEX REBUILD command to change the storage
and tablespace parameters for an existing index without having to
drop it.
The following is an example of an index rebuild via this command.
It's storage parameters are changed to use an initial extent size
of 3MB and a next extent size of 500K. It is also being moved from
the USR7 tablespace to the IDX7 tablespace.
ALTER INDEX fuzzy_pink_slippers REBUILD
( STORAGE(INITIAL 3M NEXT 500K
PCTINCREASE 0)
TABLESPACE IDX7; )
alter index <index_name> REBUILD TABLESPACE
<new_tablespace>;
Rebuild? There is an alternative
You may wish to consider.
ALTER INDEX vmoore COALESCE;
Coalescing an index, de-fragments the leaf blocks for an index
as opposed to a full rebuild cycle. In many instances, this may be
sufficient remedial action.
Positives
- Less resource used - in fact no additional space is required
- Faster (typically)
- Good for databases with large block sizes (see the negative
point below on index height)
Negatives
- You aren't recreating anything, so obviously you cannot move the
index or adjust its storage parameters during a coalesce
- Since only the leaf blocks are coalesced, the height of the
index tree will not be changed. For databases with larger block
sizes, this should be less significant since the indexes will be
"flatter" anyway.
Remember that the default location for Archive Log Files is
USE_DB_RECOVERY_FILE_DEST. You can determine its path by looking
at the parameter RECOVERY_FILE_DEST:
SQL> show parameter recovery_file_dest
NAME
TYPE VALUE
------------------------------------
----------- ------------------------------
db_recovery_file_dest
string C:\app\flash_recovery_area
db_recovery_file_dest_size
big integer 6G
If you want to use the LOG_ARCHIVE_DEST_1, then you may use:
alter system Set
LOG_ARCHIVE_DEST_1='LOCATION=C:\app\oradata\DIE\ARCH'
SCOPE=both;
alter system set LOG_ARCHIVE_DEST_STATE_1=enable scope=both;
So after deciding the location, these are the steps:
archive log list;
select log_mode from gv$database;
shutdown immediate;
startup mount;
alter database
archivelog;
alter database open;
select log_mode from gv$database;
archive log list;
Archiving OFF
Steps Required To Take A Database In Archive Log Mode And Alter It
To No Archive Log Mode (for ever)
SELECT log_mode FROM
gv$database;
archive log list;
show parameter recovery; --To see where the
parameter DB_RECOVERY_FILE_DEST is pointing (if used)
or
show parameter LOG_ARCHIVE_DEST_1;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;
alter system set
LOG_ARCHIVE_DEST_STATE_1=DEFER scope=both;
alter system Set LOG_ARCHIVE_DEST_1='' SCOPE=both;
SELECT log_mode FROM gv$database;
archive log list;
Recompiling
Invalid Schema Objects
The DBA_OBJECTS view can be used to identify invalid objects using
the following query:
COLUMN object_name FORMAT
A30
SELECT substr(owner,1,18)
owner, object_type,
substr(object_name,1,30)
object_name, status
FROM dba_objects
WHERE status =
'INVALID'
ORDER BY owner, object_type,
object_name;
There are FOUR ways
to recompile invalid objects in schema.
1. DBMS_DDL
2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL
1-
DBMS_DDL.ALTER_COMPILE
Definition
This procedure is equivalent to the following SQL statement:
ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]
Syntax
Exec dbms_ddl.alter_compile ( type , schema, name);
Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY
or TRIGGER.
Schema : Database Username
Name : Objects name
Example
exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');
2-
DBMS_UTILITY.COMPILE_SCHEMA
The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles
all procedures, functions, packages, and triggers in the specified
schema. The example below shows how it is called from SQL*Plus:
EXEC
DBMS_UTILITY.compile_schema(schema => 'SCOTT');
If you are using 10g, you can use the following to recompile ONLY
the invalid ones:
EXEC
DBMS_UTILITY.compile_schema(schema => 'SCOTT', compile_all => 'FALSE');
3- UTL_RECOMP
The UTL_RECOMP package contains two procedures used to recompile
invalid objects. As the names suggest, the RECOMP_SERIAL procedure
recompiles all the invalid objects one at a time, while the
RECOMP_PARALLEL procedure performs the same task in parallel using
the specified number of threads. The following examples show
how these procedures are used:
-- Schema level.
EXEC
UTL_RECOMP.recomp_serial('SCOTT');
EXEC
UTL_RECOMP.recomp_parallel(4, 'SCOTT');
-- Database level.
EXEC
UTL_RECOMP.recomp_serial();
EXEC
UTL_RECOMP.recomp_parallel(4);
There are a number of restrictions associated with the use of this
package including:
4- utlrp.sql and
utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to
recompile all invalid objects in the database. They are typically
run after major database changes such as upgrades or patches. They
are located in the $ORACLE_HOME/rdbms/admin directory and provide
a wrapper on the UTL_RECOMP package. The utlrp.sql script simply
calls the utlprp.sql script with a command line parameter of "0".
The utlprp.sql accepts a single integer parameter that indicates
the level of parallelism as follows:
DBMS_XPLAN
In version 9, Oracle finally provides a utility that formats the
contents of the plan table. The plan table is one that is used to
hold the results of an "Explain Plan" for a particular SQL
statement. The output from the explain plan shows the anticipated
optimizer execution path, along with the estimated cost of the
statement without actually executing the statement against the
database. The DBA or developer first needs to create the plan
table. The DDL for this table is in the
$ORACLE_HOME/rdbms/admin/utllxplan.sql file. The next step in
using dbms_xplan is running Explain Plan for a statement.
explain plan for select * from flowdocument where amount >
100000;
The command above will populate the plan table with the data
returned from the optimizer. Next, the dbms_xplan utility can be
used to view the output
select * from table(dbms_xplan.display);
--Very Efficient, deleted 685 rows over a 5443932 rows table in 3
Min
DELETE
FROM
&&table_name
WHERE rowid NOT IN (SELECT max(rowid)
FROM &&table_name
GROUP BY &colums_with_duplicates);
Another way
SELECT a.rowid
FROM
&&table_name a
WHERE a.rowid
> (SELECT min(b.rowid)
FROM &&table_name b
WHERE a.&&column_name = b.&&column_name);
Then:
DELETE from
&&table_name
WHERE
a.rowid > (SELECT min(b.rowid)
FROM &&table_name b
WHERE a.&&column_name = b.&&column_name);
**** Most efficient way to remove duplicate rows
This script uses a hash join -- the most efficient way of joining
huge tables -- to find duplicate rows.
-- Set hash join enabled
DELETE FROM <table>
WHERE rowid IN (SELECT t1.rowid
FROM <table> t1, <same-table> t2
-- primary key is (a1,
a2)
WHERE
t1.a1 = t2.a1
AND t1.a2 = t2.a2
AND t1.rowid < t2.rowid);
****Another METHOD (by METALINK) to find duplicates for one
field****
To find duplicate keys from a table tx:
select key, count(key)
no_of_duplicates
from tx
group by key
having
count(key) > 1;
Shrinking Datafiles
For example, if a datafile is 100Meg in size, and 70Meg of the
datafile is currently in use. Then atleast 70Meg must be left in
the datafile. The RESIZE parameter of the ALTER DATABASE command
is used to reclaim the space.
ALTER DATABASE datafile '/db01/oracle/fix/data03.ora'
resize 80M;
Reduce UNDO Tablespace
The Undo tablespace file can grow quickly if you load data in the
database. To reduce the size of the file, you have to recreate the
default undo tablespace because the command
alter database datafile 'XXX' resize XM;
will not work.
Create a second undo tablespace
CREATE UNDO TABLESPACE
UNDOTBS2 DATAFILE
'/data/u01/app/oracle/oradata/CCOM/undotbs2.dbf' SIZE 50M
AUTOEXTEND OFF ;
Set the second undo tablespace as default undo
ALTER SYSTEM SET
undo_tablespace = UNDOTBS2 ;
Drop the first undo tablespace
DROP TABLESPACE undotbs1
INCLUDING CONTENTS AND DATAFILES ;
Create the first undo tablespace
CREATE UNDO TABLESPACE
UNDOTBS1 DATAFILE
'/data/u01/app/oracle/oradata/CCOM/undotbs01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 5M MAXSIZE 1000M ;
Set the first undo tablespace as default
ALTER SYSTEM SET
undo_tablespace = UNDOTBS1 ;
Drop the second undo tablespace
DROP TABLESPACE UNDOTBS2
INCLUDING CONTENTS AND DATAFILES ;
Reduce TEMP Tablespace
In many database configurations, the DBA will choose to allow
their temporary tablespace (actually the tempfile(s) for the
temporary tablespace) to autoextend. A runaway query or sort can
easily chew up valuable space on the disk as the tempfiles(s)
extends to accommodate the request for space. The obvious action
would
be to resize the tempfiles using the following statement:
SQL> alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M;
alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
The procedures above document how to drop a temporary tablespace that is not the default temporary tablespace for the database. You will know fairly quickly if the tablespace is a default temporary tablespace when you are greeted with the following exception:
SQL> DROP TABLESPACE temp;In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform, all documented below. The first step you need to perform is create another temporary tablespace (lets call it TEMP2). The next step would be to remove the temporary tablespace you want to resize from being the default temporary tablespace (in our example, this will be a tablespace named TEMP) by making TEMP2 the default. Drop / recreate the TEMP tablespace to the size you want. Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace. A full example session is provided below:
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/data/u01/app/oracle/oradata/CCOM/TEMP2.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/data/u01/app/oracle/oradata/CCOM/TEMP01.dbf' SIZE 300M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
Some good queries:
--Identify Who is using which Tablespace
select srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks, a.sid,
a.serial#, a.username, a.osuser, a.status
from v$session a, v$sort_usage srt
where a.saddr = srt.session_addr
order by srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks;
--Space used by TEMP Tablespace
select file_name, bytes,maxbytes,increment_by from dba_temp_files;
ALTER DATABASE TEMPFILE 'E:\ORACLE\ORADATA\ADWT\TEMP02.DBF' RESIZE 6000M;
Transportable
Tablespaces
1- Make the tablespace Read-Only = alter tablespace xxxx
read only;
2- Export it connecting as sys as sysdba = exp
file=tt.dmp log=tt.log tablespaces=xxxx
transportable_tablespaces=y
3- Copy the .dmp file and the data_files to the destination
4- Put the tablespace back in write mode = alter tablespace
xxxx read write;
5- In the destination offline and drop the tablespace if exists
6- Import = imp file=tt.dmp log=tt.log tablespaces=test
transportable_tablespace=y datafiles=(......., ........)
Orakill Utility
The orakill utility is provided with Oracle databases on
Windows platforms. The executable (orakill.exe) is
available to DBA's to kill Oracle sessions directly from the DOS
command line without requiring any connection to the database.
In the Unix world, a DBA can kill a shadow process by issuing the
kill -9 commands from the Unix prompt. Unix is able to provide
this capability given that the Unix operating system is based on
processes that fork other processes. All processes can be listed
by using the ps Unix command. The Oracle background processes will
be listed separately from all of the Oracle sessions since they
have their own process.
Unlike the Unix operating system, Windows systems are
thread-based. The background processes and sessions are all
contained within the ORACLE.EXE executable and are not listed in
the "Processes" tab of Windows Task Manager.
The orakill utility serves the same purpose as kill
-9 in Unix. The command requires the instance and the SPID
of the thread to kill. The following query will return the SPID
for each user connected to the database:
select a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr = b.addr
and a.username is not null;
USERNAME
OSUSER
SPID
---------------------------- ------------------------ -----
SCOTT
Scott
3116
AMOORE
Alex
4760
DMOORE
Dave
768
Given the SPID for each user listed above, the session for any
user can be killed with the orakill command.
C:\oracle9i\bin>orakill ORCL92 4760
Why does Oracle provide a utility to kill sessions from the DOS
prompt when a DBA could kill a user session from within Oracle?
The following command will also kill the user session:
alter system kill session(sid, serial#);
The sid and serial# used in the command above can be obtained
from the v$session view. There are a few reasons a DBA could use
orakill instead of the alter system kill session command.
1. The alter system statement will not clear the locks if any
exist. Instead, the session will remain connected until it times
out, then the session is killed and the locks are released. The
orakill command will kill the thread and the locks instantly.
2. A DBA may not be able to gain access to a SQL prompt due to a
runaway query consuming all system resources. In this case, the
session can be killed without ever logging in to the database.
The orakill utility should be used as a last resort only.
If the session cannot be killed more gracefully (via alter
system kill session), or the instance is inaccessible via
SQL, then orakill can be used to terminate the offending
session.
Background processes should not be terminated, only user sessions.
Killing a background process can cause serious Oracle errors and
can bring the database down.
The
PL/SQL WRAP Utility
The WRAP encrypts PL/SQL code displaying it in hexadecimal format.
Use the following command:
wrap iname=script.sql
The output file will be called script.plb. To rename the output file, use the oname option of Wrap (i.e., oname=output.sql).
COPY Command
It allows data to be copied between databases (or within the same
database) via SQL*PLUS. The greatest ability of this command is to
COMMIT alfter each array of data. Though the copy of an extremely
large table can tax the rollback segments, it is possible to break
the transaction into smaller entries. The syntax for this command
is:
COPY FROM
remote username/remote password@connect string
TO
username/password@connect string
{APPEND, CREATE, INSERT, REPLACE]
table name using subquery
To set the transaction entry size, use the SQL*PLUS SET command
to set a value for the ARRAYSIZE parameter. This determines the
number of records that will be retrieved in each "batch". The COPY
COMMIT parameter tells SQL*PLUS how many batches should be
committed at one time. In the following example, the data is
committed after every 1,000 records. This reduces the
transaction's rollback segment entry size.
SET COPYCOMMIT 1;
SET ARRAYSIZE 1000
COPY FROM batman/androbin @t:gotham:city CREATE batmobile USING
SELECT * FROM bat_mobile;
NOTE: The feedback from this command is not acqurate. After the
final commit is complete, the database reports the number of
records that were committed in the last batch. It does not report
the total number of records committed.
Authid Current_User vs Authid
Definer
A stored procedure is either run with the rights of the caller (authid current_user) or with
the rights of the procedure's owner (authid definer).
This authid clause immediatly follows the create procedure or
create function statement.
It can be ommited, in which case the default authid definer
is taken. Example:
create procedure pu
authid definer
as
v_d t.d%type;
begin
select d into v_d
from u;
dbms_output.put_line(v_d);
end;
/
or
CREATE OR REPLACE
- How
to
find what is locking a table.
1. Query from DBA_OBJECTS to
find the object_name of the table getting locked.
2. Query from V$LOCK where
id1 = 'table_name', get sid.
3. Query from v$PROCESS where
pid = sid. THis has info on what is locking the table.
Generate Random Data
In some cases you may need to generate random data, this is very
easy to perform by using the dbms_random package. Examples:
Generating a random
number (positive or negative)
select dbms_random.random
from dual;
RANDOM
----------
-744437494
Generating a random
number between 0 and 1.
select dbms_random.value
from dual;
VALUE
----------
.313051535
Generating a random
number from a range, between 1 to 1000.
select
dbms_random.value(1,1000) num from dual;
NUM
----------
182.888725
Generating a 12 digit
random number.
select
dbms_random.value(100000000000, 999999999999) num from dual;
NUM
----------
7.0770E+11
Generating an upper case
string of 20 characters
select
dbms_random.string('U', 20) str from dual;
STR
----------------------
KUKWEDHNTNUNVBFDOEBG
Generating a lower case
string of 20 characters
select
dbms_random.string('L', 20) str from dual;
STR
---------------------
zhddilwfvxqfftoktbyq
Generating an
alphanumeric string of 20 characters
select
dbms_random.string('A', 20) str from dual;
STR
---------------------
vWFNaoTvlSzPfQpkLPEB
Generating an upper case
alphanumeric string of 20 characters
select
dbms_random.string('X', 20) str from dual;
STR
---------------------
0XQYACWL35RHL5IH2I11
Generating a string of
printable 20 characters. This will output a string of all
characters that could possibly be printed.
select
dbms_random.string('P', 20) str from dual;
STR
--------------------
jqPJ3IxH%]Ij}_Y2j!p[
Select Randomly from Table
This feature allows you to randomly "sample" from a table. This
feature has many great uses. The syntax is as follows:
SELECT COUNT(*) * 100 FROM EMP SAMPLE (1);
This will randomly sample 1% of the rows, multiple the count of
them x 100 to get a rough estimate of the amount of rows in the
table.
You can also randomly sample by blocks for better performance but
possibly less random:
SELECT * FROM EMP SAMPLE BLOCK (1);
Again, this samples roughly 1% of the table by blocks, which may
not be 1% of the actual rows. But this will cause fewer blocks to
be visited and decrease the elapsed time, but if the data is
grouped in the table, it may not be very random.
This tool can be used to get a rough idea of the data in the
table, or give good estimates when using group functions. For
example, a great use of this would be on a 40 million row table:
SELECT AVG(number_of children) * 20 FROM dependants sample (5);
This will give you an average of the number of dependants by
only sampling 5% of the table by only visiting 2 million rows and
not 40 million.
Disk Space Needed for the Archive Files
The output of the above script tells you how many log switches are
occurring on your system on a daily basis. The actual disk
space that is required to serve the archiving is given as well in
MB. All you need to determine if the amount of log switches
are stable or difference a lot from day to day. First obtain
the size in Mb of your online redo log files, you can execute the
following query:
Then run:
column
ord noprint
column date_
heading 'Date'
format A15
column
no heading '#Arch files'
format 9999999
column no_size heading
'Size Mb' format 9999999
compute avg of
no on report
compute avg of no_size on
report
break on report
select MAX(first_time) ord,
to_char(first_time,'DD-MON-YYYY') date_,
count(recid) no,
count(recid) * &logfile_size no_size
from v$log_history
group by
to_char(first_time,'DD-MON-YYYY')
order by ord;
clear breaks
clear computes
clear columns
Unlocking Users
alter user &username account unlock;
Grab Execution
Time (DBMS_Utility.get_time)
In order to get the Execution time of a PL/SQL, you can use the
following:
set serveroutput on
declare
v_starttime
number;
begin
v_starttime :=
dbms_utility.get_time;
--your code
here
dbms_lock.sleep(10);
dbms_output.put_line('Elapsed Time: ' || (dbms_utility.get_time
- v_starttime)/100 || ' seconds....' );
end;
/
Elapsed Time: .10
seconds....
Using orapwd to Connect
Remotely as SYSDBA
The Oracle orapwd utility assists the DBA with granting
SYSDBA and SYSOPER privileges to other users. By default,
the user SYS is the only user that has these privileges.
Creating a password file via orapwd enables remote users to
connect with administrative privileges through SQL*Net. The
SYSOPER privilege allows instance startup, shutdown, mount, and
dismount. It allows the DBA to perform general database
maintenance without viewing user data. The SYSDBA privilege
is the same as connect internal was in prior versions. It
provides the ability to do everything, unrestricted. If
orapwd has not yet been executed, attempting to grant SYSDBA or
SYSOPER privileges will result in the following error:
SQL> grant sysdba to scott;
ORA-01994: GRANT failed: cannot add users to public password
file
The following steps can be performed to grant other users these
privileges:
1. Create the password file.
This is done by executing the following command:
$ orapwd file=filename password=password
entries=max_users
The filename is the name of the file that will hold the password
information. The file location will default to the current
directory unless the full path is specified. The contents
are encrypted and are unreadable. The password required is the one
for the SYS user of the database. The max_usersis the number
of database users that can be granted SYSDBA or SYSOPER.
This parameter should be set to a higher value than the number of
anticipated users to prevent having to delete and recreate the
password file.
2. Edit the init.ora parameter
remote_login_passwordfile. This parameter must be set to
either SHARED or EXCLUSIVE.When set to SHARED, the password file
can be used by multiple databases, yet only the SYS user is
recognized. When set to EXCLUSIVE, the file can be used by
only one database, yet multiple users can exist in the file.
The parameter setting can be confirmed by:
SQL> show parameter password
NAME
TYPE VALUE
----------------------------- ----------- ----------
remote_login_passwordfile
string EXCLUSIVE
3. Grant SYSDBA or SYSOPER to
users. When SYSDBA or SYSOPER privileges are granted to a
user, that user's name and privilege information are added to the
password file.
SQL> grant sysdba to scott;
Grant succeeded.
4. Confirm that the user is listed
in the password file.
SQL> select * from v$pwfile_users;
USERNAME
SYSDBA SYSOPER
------------------------------ ------ -------
SYS
TRUE TRUE
SCOTT
TRUE FALSE
Now the user SCOTT can connect as SYSDBA. Administrative
users can be connected and authenticated to a local or remote
database by using the SQL*Plus connect command. They must
connect using their username and password, and with the AS SYSDBA
or AS SYSOPER clause:
SQL> connect scott/tiger as sysdba;
The DBA utilizes the orapwd utility to grant SYSDBA and SYSOPER
privileges to other database users. The SYS password should
never be shared and should be highly classified.
Change
DB Name and File paths
Changing the DBID of a database is a serious procedure.
When the DBID of a database is changed, all previous backups and
archived logs of the database become unusable.
A common way to rename all the database and all data files is to
rebuild the control file. This can be very labor intensive,
however, and possibly dangerous if you have a large number of data
files. You could make a mistake and lose a data file or two in the
process because the database does not check the validity of the
data files when you do a control file rebuild. Here is a
low-stress, easy solution:
Changing the DBNAME without changing the DBID does not require you
to open with the RESETLOGS option, so database backups and
archived logs are not invalidated. However, changing the DBNAME
does have consequences. You must change the DB_NAME initialization
parameter after a database name change to reflect the new name.
Also, you may have to re-create the Oracle password file. If you
restore an old backup of the control file (before the name
change), then you should use the initialization parameter file and
password file from before the database name change.
For RAC Databases, ensure that you shutdown all instances, start
only one instance with CLUSTER_DATABASE=FALSE and perform below
activities. After the activity is performed, start all rac
instances normally.
Perform a FULL backup before you start this
process
Step 1
Run the scripts below (change directories for your server) with
the target database open. Spool out a file for each of your
available mount points.
Yes, we will be trying to move the data files more then once,
however, Oracle checks whether the data file exists before
accepting the command and returns errors if it does not exist.
This uses the Oracle Server to verify the validity of our paths
and is completely goof proof!
Data File Script
select 'alter database
rename file '''||file_name||''' to ', '''/ora01/oradata/sid/'||
substr(file_name,instr(file_name,'/',-1)+1) ||''';'
from dba_data_Files
Redo Log Script
select 'alter database rename
file '''||member||''' to ', '''/ora01/oradata/sid/'||
substr(member, instr(member,'/',-1)+1)||''';'
from v$logfile;
Step 2
Connect as SYS
shutdown immediate;
startup mount;
Run each of the scripts your have created.
SQL> alter database
open;
Check to make sure all your data files are pointing to the new
mount points.
SQL> select file_name
from dba_data_files;
If you just needed to rename the paths of your data files, you are
done. If you also need to rename the database, you have one more
step.
Step 3
After you have verified that all the datafiles and redo logs point
to the correct path, we will use the nid Oracle Command to rename
the Database.
Use "nid" command-line
(Example: testdb2 -> testdb)
shutdown immediate;
startup mount;
exit;
$ nid target=sys/password@service DBNAME=testdb
or
$ nid target=sys/password DBNAME=testdb
DBNEWID: Release 10.2.0.4.0 - Production on Mon Jul 27 18:29:06 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database <OLD DB Name>
(DBID=729333573)
Connected to server version 10.2.0
Control Files in database:
D<PATH>\CONTROL01.CTL
D<PATH>\CONTROL02.CTL
D<PATH>\CONTROL03.CTL
Change database ID and database name <OLD
DB Name> to <NEW DB
Name>? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 729333573 to 2473929266
Changing database name from <OLD DB
Name> to <NEW DB Name>
Control File D:<PATH>\CONTROL01.CTL - modified
Control File D:<PATH>\CONTROL02.CTL - modified
Control File D:<PATH>\CONTROL03.CTL - modified
Datafile D:<PATH>\SYSTEM01.DBF - dbid changed, wrote new name
Datafile D:<PATH>\UNDOTBS01.DBF - dbid changed, wrote new name
Datafile D:<PATH>\SYSAUX01.DBF - dbid changed, wrote new name
Datafile D:<PATH>\USERS01.DBF - dbid changed, wrote new name
Datafile D:<PATH>\EXAMPLE01.DBF - dbid changed, wrote new name
Datafile D:<PATH>\TEST.DBF - dbid changed, wrote new name
Datafile D:<PATH>\TEMP01.DBF - dbid changed, wrote new name
Datafile D:<PATH>\TEMP011.DBF - dbid changed, wrote new name
Control File D:<PATH>\CONTROL01.CTL - dbid changed, wrote new name
Control File D:<PATH>\CONTROL02.CTL - dbid changed, wrote new name
Control File D:<PATH>\CONTROL03.CTL - dbid changed, wrote new name
Instance shut down
Database name changed to <NEW DB Name>
Modify parameter file and generate a new password file before restarting.
Database ID for database <NEW DB Name>
changed to 2473929266.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
Get IP
and Name of Server
--Get Server Name and IP
Address
declare
v_host_name
v$instance.host_name%type;
v_ip_address
varchar2(50);
begin
select host_name into
v_host_name from v$instance;
dbms_output.put_line('the database server name is ' ||
v_host_name);
SELECT
UTL_INADDR.GET_HOST_ADDRESS(v_host_name) into v_ip_address FROM
DUAL;
dbms_output.put_line('the database server ip address is ' ||
v_ip_address);
end;
/
SELECT host_name,
UTL_INADDR.GET_HOST_ADDRESS(host_name) ip FROM v$instance;
Creating Scott Example Schema
If it is not already present create the SCOTT schema:
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlsampl.sql
Resetting a table's HWM in Oracle 10g
Traditionally, in order to reset the High Water Mark(HWM), the table will be truncated and then reloaded with the new set of data. But in Oracle 10g, with the help of alter table <xxx> shrink space command, we can reclaim the wasted space in our database as well as reset the HWM also. But keep in mind, prior to using this command on your table, there are some pre-requisites.
1. The tablespace in which the table is present must be of Auto Segment Space Management.
2. Row Movement must be enabled in the table. If it is not enabled at the time of creation, then you can use:
ALTER TABLE <TABLE-NAME> ENABLE ROW MOVEMENT;
Once the above mentioned pre-requisites are satisfied, then you can use the following command to shrink space and reset the HWM.
ALTER TABLE <TABLE-NAME> SHRINK SPACE CASCADE;
If you just want to only defrag the table and not reset the HWM or reclaim the space due to active DML transactions, you can use:
ALTER TABLE <TABLE-NAME> SHRINK SPACE CASCADE COMPACT;
In order to see how many empty blocks are still there in the table, first analyze the table and then you can use:
select table_name, blocks, empty_blocks, last_analyzed, global_stats from user_tables where table_name=<TABLE-NAME>;
select to_char(creation_time, 'YYYY Month')
"Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v$datafile
where creation_time >
SYSDATE-365*2
/*Number of Years: This can be changed according to your
requirement. */
group by to_char(creation_time, 'YYYY
Month');
EXPDP / IMPDP
create or replace directory MY_EXPORT as 'C:\Temp';
create or replace directory MY_EXPORT as '/home/oracle';
expdp userid=system dumpfile=FG10_EMPTY logfile=FG10_EMPTY directory=MY_EXPORT schemas=FG10
impdp userid=system@fguard dumpfile=FG10 logfile=imp_FG10 directory=MY_EXPORT
expdp userid=system dumpfile=FG logfile=FG directory=MY_EXPORT schemas=FG exclude=statistics,grant compression=all
impdp userid=system dumpfile=FG logfile=imp_FGUARD9X directory=MY_EXPORT remap_schema=FG9:FG9X version=LATEST remap_tablespace=FG_DATA:FG_DATA,FG_INDX:FG_INDX,FG_DATA_ARCH:FG_DATA_ARCH,FG_INDX_ARCH:FG_INDX_ARCH schemas=FG TABLE_EXISTS_ACTION=APPEND
Export - Import several Users
expdp userid=system/services12c@fguard dumpfile=FG10 logfile=FG10 directory=MY_EXPORT schemas=FG10,TD10 exclude=statistics compression=all
impdp userid=system dumpfile=FGUARD9X logfile=my_import directory=MY_EXPORT schemas=FGUARD9X,TD10 remap_tablespace=UMB_FG_DATA:FG_INDX
impdp userid=system dumpfile=FG10 logfile=impFG10 directory=MY_EXPORT schemas=FG10
impdp userid=system dumpfile=FG10HIST logfile=impFG10HIST directory=MY_EXPORT schemas=FG10HIST
Export no rows
expdp userid=system dumpfile=FGUARD9X_empty logfile=FGUARD9X_empty directory=MY_EXPORT schemas=FGUARD9X version=10.2 exclude=statistics,grant rows=n
Export Data Except some Tables
expdp FGUARD9x dumpfile=FGUARD9x_data logfile=FGUARD9x_data directory=MY_EXPORT exclude=TABLE:\"IN \(\'ACCOUNT\', \'SIGNATORY\' , \'SIGNATURES\'\)\" exclude=statistics,grant compression=all
expdp userid=system dumpfile=FGUARD9x_data logfile=FGUARD9x_data directory=MY_EXPORT schemas=BONY94 exclude=TABLE:\"IN \(\'ACCOUNT\', \'SIGNATORY\' , \'SIGNATURES\'\)\" exclude=statistics,grant compression=all
impdp userid=system dumpfile=FRAUDGUUARD.dmp logfile=imp directory=EXPORTPATH remap_schema=FRAUDUARD:FGUARD9X version=LATEST remap_tablespace=FGUARD_DATA:FG_INDX,FGUARD_INDX:FG_INDX,FGUARD_ARCH_INDX:FG_INDX,FGUARD_ARCH_DATA:FG_INDX
Export Data
Except Tables Like %
expdp userid=system dumpfile=ARP94FULL logfile=ARP94FULL directory=MY_EXPORT schemas=ARP94,BONY94 EXCLUDE=TABLE:\"LIKE \'%_ARCH\'\"
Check Dump File
impdp system directory=MY_EXPORT dumpfile=Some_atl_tables071801.dmp,Some_atl_tables071802.dmp logfile=import_log.txt sqlfile=ddl_dump.txt
CLEAN UP ORPHANED JOBS
There are cases were orphan Jobs are running "behind the scenes".
In order to detect that, you need to execute:
--Review Orphaned Jobs
select * from dba_datapump_jobs;
SET lines 200
SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%'
and attached_sessions=0
ORDER BY 4,2;
select 'drop table ' || o.owner||'.'|| object_name || ';'
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%'
and attached_sessions=0;
order by 1 ;
Put below value in .profile of user
HOSTNAME=$(uname -n)
PS1='(${?})${LOGNAME}@${HOSTNAME}:${ORACLE_SID:-“NO_SID”}: ${PWD}
$ ‘
PS2=’> ‘