Archive

Archive for November, 2010

Linux – Find Command

November 25, 2010 1 comment

The find command is used to find files and directories on a linux/unix system. It will search any set of directories that you specify for files/directories matching a given search criteria. You can search by name, owner, group, type, permission, date, and other criteria. Search is recursive and will search the specified directory and its subdirectories providing the user has permission to view the contents of that directory.

Find all files in current directory and it’s sub-directories

Find followed by a . will search for all files in the current directory and it’s sub-directories and print the output.

dgoh-data-01:oracle@OASLIVE1 > find .
.
./initOASLIVE.ora
./OASLIVEpostupgrade.ora
./spfileOASLIVE.ora
./OASSTBY.stby
./stats_gather.log
./updateusers.sql

Find all sub-directories within the current directory by given owner

If you want to find all sub-directories of the current directory with the owner ‘Oracle’ then you would execute the following command:

$  find . -type d -user oracle -ls

Advertisements
Categories: Commands, Linux Tags: , ,

Linux – Tunnel X windows Securely over SSH

November 25, 2010 Leave a comment

The X windows system is a network protocol that provides a graphical user interface for networked computers. In order to use graphical user interface applications it is sometimes necessary to use vncserver on Linux environments as they are command line based.

Output without X windows

When using vncserver and needing to connect to another server through ssh it would be necessary to connect using the X windows protocol, otherwise the following error occurs:

dgoh-data-02:oracle@OASLIVE2> ssh node11
Last login: Thu Nov 14:15:44 2010 from data-dgoh-02
dgoh-data-11:oracle@OASSTBY>dbca
DISPLAY not set
set DISPLAY environment variable, then re-run

Output with X Windows

dgoh-data-02:oracle@OASLIVE2> ssh -X node11
Last login: Thu Nov 14:27:16 2010 from data-dgoh-02

This time when DBCA is run the graphical user interface appears

Check X Windows is running

A simple ps grep command will let you know if X windows is currently running on your Linux system.

dgoh-data-01:oracle@OASLIVE1 > ps -ef|grep X
oracle    6776   421  0 14:39 pts/2    00:00:00 grep X
root      7091  7088  0 Nov11 tty7     00:00:00 /usr/bin/Xorg
:0 -br -audit 0 -auth /var/gdm/:0.Xauth -nolisten tcp vt7

Find which port X11 is running on

From the command line type the following where <server> is the server name, to discover which port x11 is running on:

# nmap <server>
PORT STATE SERVICE
21/tcp open ftp
22/tcp open ssh
23/tcp open telnet
6004/tcp open X11:4

Oracle – Archive Log Mode

November 22, 2010 Leave a comment

Archive log mode is necessary for hot backups and point-in-time recovery to take place. By default the database is created in noarchivelog mode, however you would need to shutdown the database in order to perform a backup. Archivelog mode allows for hot backups to be run with the archive logs being included in the backup to allow for consistency.

In archivelog mode the database will make copies of completed redo logs, archive logfiles. The disadvantage of this is that until the archivelog for a redo log has been written, the redo log cannot be reused.

Parameters

The following parameters need to be set in order to use archivelog mode:

log_archive_start = TRUE
log_archive_dest_1 = 'LOCATION=/arch_dir_name'
log_archive_dest_state_1 = ENABLE
log_archive_format = %d_%t_%s.arc

Enable Archivelog Mode

You must first shutdown all instances of the database. Then startup one instance, enable archivelog mode, the restart the remaining instances.

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

Check Archivelog Mode

You can easily check if a database is running in archivelog mode by running the following:

SQL> Archive log list;

Disable Archivelog Mode

To disable archivelog mode you once again need to shutdown the database, startup mount, then disable archivelog mode:

SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;

Oracle – Reconfigure Oracle Enterprise Manager

November 18, 2010 2 comments

If you have already created your database and not configured it with Enterprise Manager, or you have configured your database with Enterprise Manager but for some reason need to reconfigure, you can do this from the command line using EMCA.

If you have already configured Enterprise Manager before and wish to reconfigure then you need to drop a couple of public synonyms, roles and users on the database first.

Drop Users

SQL>  drop user mgmt_view cascade;
User dropped
SQL>  drop user sysman cascade;
User dropped

NB.  If you don’t drop the sysman user you receive the following error in the log file:

CONFIG: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17 oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17

Drop Public Synonyms

SQL>  drop public synonym setenviewusercontext;
Public synonym dropped
SQL>  drop public synonym mgmt_target_blackouts;
Public synonym dropped

Drop Roles

SQL>  drop role mgmt_user; Role dopped

Create the Enterprise Manager environment

The enterprise manager environment can then be created using EMCA –config

$  emca –config dbcontrol db –repos create

EMCA will prompt for the following information:

Database SID: Listener port number:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notification (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME:
ASM SID:
ASM port:
ASM user role:
ASM username:
ASM user password:

It is possible to use an input file for the EMCA parameters using the –respFile argument to specify an input file. The input file must be in a format similar to:

PORT=1510 SID=OASPRAC DBSNMP_PWD=password SYSMAN_PWD=password

An example of using an input file can be seen below:

./emca -config dbcontrol db -respFile input_file_path

EMCA and RAC Databases

If you have a RAC database then dbconsole needs to be configured on each instance of the cluster. By default Database Control Console is only configured on the local node, however the Enterprise Manager agent is started on all other nodes.

-cluster nodename

arguments should be used for RAC databases

Sample Output for Reconfiguring DBControl for a Single-Node RAC

merlin:oracle@OASPRAC1 > emca -config dbcontrol db -repos create
STARTED EMCA at Nov 18, 2010 4:02:25 PM EM Configuration Assistant, Version 10.2.0.1.0 Production Copyright (c) 2003, 2005, Oracle.
All rights reserved.
Enter the following information:
Database SID:
OASPRAC1 Database Control is already configured for the database OASPRAC1
You have chosen to configure Database Control for managing the database OASPRAC1
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: y
Listener port number: 1510
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /u01/oracle/ora102 ]: /u01/oracle/asm102
ASM SID [ +ASM ]:
ASM port [ 1510 ]:
ASM user role [ SYSDBA ]:
ASM username [ SYS ]:
ASM user password: -----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u01/oracle/ora102
Database hostname ................ merlin.smhsct.local
Listener port number ................ 1510
Database SID ................ OASPRAC1
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /u01/oracle/asm102
ASM SID ................ +ASM
ASM port ................ 1510
ASM user role ................ SYSDBA
ASM username ................ SYS -----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Nov 18, 2010 4:02:59 PM
oracle.sysman.emcp.EMConfig perform INFO:
This operation is being logged at /u01/oracle/ora102/cfgtoollogs/emca/OASPRAC/emca_2010-11-18_04-02-25-PM.log.
Nov 18, 2010 4:02:59 PM
oracle.sysman.emcp.util.DBControlUtil stopOMS INFO:
Stopping Database Control (this may take a while) ...
Nov 18, 2010 4:03:01 PM
oracle.sysman.emcp.EMReposConfig createRepository INFO:
Creating the EM repository (this may take a while) ...
Nov 18, 2010 4:04:33 PM oracle.sysman.emcp.EMReposConfig invoke INFO:
Repository successfully created Nov 18, 2010 4:04:36 PM
oracle.sysman.emcp.util.DBControlUtil secureDBConsole INFO:
Securing Database Control (this may take a while) ...
Nov 18, 2010 4:13:14 PM
oracle.sysman.emcp.util.DBControlUtil secureDBConsole INFO:
Database Control secured successfully.
Nov 18, 2010 4:13:14 PM
oracle.sysman.emcp.util.DBControlUtil startOMS INFO:
Starting Database Control (this may take a while) ...
Nov 18, 2010 4:14:49 PM
oracle.sysman.emcp.EMDBPostConfig performConfiguration INFO:
Database Control started successfully
Nov 18, 2010 4:14:49 PM
oracle.sysman.emcp.EMDBPostConfig performConfiguration INFO:
>>>>>>>>>>>
The Database Control URL is https://merlin.smhsct.local:5501/em
<<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Nov 18, 2010 4:14:49 PM

Oracle – Restricted Database Session

November 17, 2010 Leave a comment

Sometimes it is necessary to do work on a database without any other users being logged in. It is possible to restrict the database session in such a case. When the database starts in restricted mode only users with restricted session privileges can get access to the database even though it is technically in open mode.

Enable / Disable Restricted Session

SQL> startup restrict ORACLE instance started.
Total System Global Area 504366872 bytes
Fixed Size 743192 bytes
Variable Size 285212672 bytes
Database Buffers 218103808 bytes
Redo Buffers 307200 bytes
Database mounted.
Database opened.

Startup the database in restricted mode

The alter system command can be used to put the database in and out of restricted session once it is open:

SQL> alter system enable restricted session;
system altered
SQL> alter system disable restricted session;
system altered

Find and disconnect users connected during restricted session

Any users connected to the database when restricted session is enabled will remain connected and need to be manually disconnected
To check which users are connected to the database run the following:

SQL> SELECT username, logon_time, process from v$session;
USERNAME LOGON_TIM PROCESS
-------- --------- -------
17-NOV-10 606252
17-NOV-10 598054
17-NOV-10 540690
17-NOV-10 421948
17-NOV-10 561182
17-NOV-10 512046
17-NOV-10 1257542
SYS      17-NOV-10 1310796
8 rows selected.

By querying the process id you can then issue a kill -9 <process_id> at the operating system level to disconnect the connected user. The blank usernames in v$session refer to background database processes.

Check if database in restricted mode

If you are unsure whether the database is in restricted session or not you can run the following query to check:

SQL> SELECT logins from v$instance;
LOGINS
----------
RESTRICTED

Linux – Find Linux Directory Space Usage

November 17, 2010 Leave a comment

Sometimes it is necessary to find the size of a given directory on a unix/linux environment, this helps to see where the most amount of space is being consumed and where directories can be trimmed to free up space.

$ du

– This command tells you a list of sub-directories and their current sizes for the directory you are currently in. The last line of this command tells you the size of the current directory including all sub-directories. By default the sizes are in Kb

$ du /home/oracle

– This command gives you the size of the specified directory.

$ du -h

– This gives you an output of directory sizes in human readable format. The directory sizes are suffixed with a K for Kb, M for Mb, and G for Gb.

$du -ah

– This provides the sizes of all directories and files for the current directory and its subdirectories in human readable format.

$du - c

– This provides a list of sub-directories and a grand total of the directory and its sub-directories.

$du -ch | grep total

– This provides only the grand total amount for the directory and its sub-directories. Only one line is displayed.

$du -s

– Another way of finding the total size of the directory and its sub-directories is to issue this command. You can append the command to make it human readable also.

$du -S

– This provides a list of the sizes of all files in the current directory only. The total at the end is the sum total of the files in the current directory excluding the sub-directory.

$du --exclude=oracle

– This provides a list of the sizes for the current directory and its sub-directories, but excludes all files with the given pattern in their filenames.

Categories: Commands, Linux Tags: , ,

Linux – Backup to Tape

November 3, 2010 Leave a comment

One task of a database administrator is to perform backups for recovery purposes. One method of performing backups is to backup files to tape. Tape backups are cheaper for the amount of storage available, and more trustworthy for longer term retention that hard drives. Tapes are also useful for the fact that they can be moved offsite, therefore providing more security. The downside to tape backups is that they are time consuming, however combined with short-term hard-drive backups can provide a good backup regime.

Backup to Remote Tape Device Script

The below script will print the database SID and the current date, before changing to the root directory. The file is then compressed, stored on a remote tape device, and an error log is produced.

#################################################
#  Backup to Remote Tape Device Script
#################################################
(
ps –ef | grep pmon | grep $ORACLE_SID
echo  Backup started at `date`
cd /
#  On Linux gnu tar is run by default so it is not necessary to declare the path explicitly.
#  On Solaris and HP, GNU tar needs to be copied to the path below and referenced
GZIP=--fast
Export GZIP
/usr/local/bin/tar zcvbf 128 [remote server]:[device name] ./[directories]
echo Backup finished at `date`
) > "[error log filename]" 2>&1 &
# End of the script

GZIP=–fast

Sets the speed at which files are compressed. The faster a file is compressed, the lower the amount of compression performed. With the -# option, # can be any number between 1 and 9 where 1 is the fastest and 9 is the slowest. The –fast option is the equivalent to -1 whereas –best is the equivalent to -9. By default gzip performs compression as thought the -6 option was specified.

tar zcvbf

tar compresses files into archive files.
-z :- filter the archive through gzip
-c :- create a new archive
-v :- verbosely list files created
-b :- use record size of Nx512 bytes (default N=20)
-f :- use archive file or device

2>&1 &

The incantation 2>&1 means “Send errors (output stream number 2) to the same place ordinary output (output stream number 1) is going to”.

Backup to Local Tape Script

######################################
#  Backup to script for “$ORACLE_SID”
######################################
mt –t [device name] rewind
(
ps –fe | grep pmon | grep $ORACLE_SID
echo Backup started at `date`
cd /
tar cvbf 128 [device name] ./[directories]
echo Backup finished at `date`
) > “[error log filename]” 2>&1 &
# End of the script

Note about Rewind devices and Norewind devices

The tape device drivers on Linux and Unix allow a tape device to be opened for “rewind on close” or “norewind on close”. You use a slightly different device name to choose one from another, for example:

DLT rewind device: /dev/rmt/0u
DLT norewind device: /dev/rmt/0un

If you choose the rewind device, the driver will place two EOT markers at the end of the tape, and rewind the tape. You cannot get any more backups on the tape.

If you choose the norewind device, the driver will place one EOT marker and then one BOT marker at the end of the tape, and will not rewind the tape. It is possible to add another backup onto the tape. The “mt” command can be used to move the tape forwards and backward between savesets