Perl – Perl Version

Sometimes it is necessary to find which version of perl you are running on your Linux server.

Find Perl Version

$ perl -v

Oracle – Opatch

Opatch is an Oracle utility for the application and rolling back of interim patches to the Oracle RDBMS.  In order to use opatch it is recommended to set its location in $PATH.

Location

OPatch can be found under $ORACLE_HOME/OPatch

Current Patch History

To find the current patch history run the following:

$ opatch lsinventory [ -all ] [ -i[nvPtrLoc] <Path to oraInst.loc> ][ -oh <ORACLE_HOME>]
-all

– list all patches currently applied.

OPatch Version

$ sh opatch version
Invoking OPatch 10.2.0.4.2
OPatch Version: 10.2.0.4.2
OPatch succeeded.

OPatch Apply

$ opatch apply [-f[orce]] [-i[nvPtrLoc]<path to oraInst.log] [-m[inimize_downtime]] [-n[o_inventory]] [-oh<ORACLE_HOME>] [<patch_location>]
-force

– used to remove any conflicting patches and force the patch to be applied

-invPtrLoc

– used to locate the oraInst.loc file

-minimize_downtime

– used only in RAC instances, allowing for the remaining instances to remain up during patching. It is the responsibility of the user to shutdown each instance before applying the patches, after the last instance is patched then all instances can be brought back up.

-no_inventory

– This only works if the inventory is unavailable and allows for bypassing the inventory for reading and updates.

-oh

– Directory to be used instead of the default $ORACLE_HOME

patch_location

– Where to install the patch from

OPatch Rollback

Sometimes it is necessary to rollback a patch once it has been applied.

$ opatch rollback -id patch_id [-i[nvPtrLoc]<Path to oraInst.loc>] [-oh <ORACLE_HOME>] -pj patch_dir
-id

– The id of the patch that requires rollback

-ph

– The directory that is a valid patch area.

Oracle – Check tablespace sizes

One of the tasks a DBA undertakes is to monitor the amount of space consumed by the database. If a tablespace becomes spacebound than the database will freeze up as data cannot be written down to disk.

Check Tablespace Free Space

The below script will identify the available free space for each tablespace.

SELECT a.tablespace_name
, a.fileCount
, a.MaxTSBytes
, b.TSDataBytes
, a.MaxTSBytes - b.TSDataBytes FreeSpace
, ROUND((a.TotalFilesizeMB/a.MaxTSBytes)*100,2) DFAlloc
, ROUND((b.TSDataBytes/a.MaxTSBytes)*100,2) AS DATAAlloc
FROM
(SELECT tablespace_name
, COUNT(*) filecount
, SUM(CASE WHEN maxbytes > bytes THEN maxbytes/1048576 ELSE bytes/1048576 end) AS MaxTSBytes
, SUM(bytes)/1048576 TotalFilesizeMB
FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name
, SUM(bytes)/(1048576) AS TSDataBytes
FROM dba_extents GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY 1
/
TABLESPACE_NAME FILECOUNT MAXTSBYTES TSDATABYTES FREESPACE  DFALLOC DATAALLOC
--------------- --------- ---------- ----------- ---------  ------- ---------
INTERFACE       13        22250      21906.4375  343.5625   100      98.46
LOAD            6         8550       7629.125    920.875    100      89.23
MLOG            1         1250       617.5625    632.4375   100      49.41
NOTES           3         4200       3728.6875   471.3125   100      88.78
SYSTEM          1         500        406.625     93.375     100      81.33
UNDO            3         4500       323.085938  4176.91406 100       7.18
XDB             1         100        44.9375     55.0625    100      44.94
6 rows selected.

Query Datafile Sizes

The below script queries how much free space is available per datafile. In this example only the system tablespace has been queried:

SELECT a.file_id,
SUBSTR(a.tablespace_name,1,10) tablespace,
a.autoextensible,
(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 ELSE bytes/1048576 END) MaxFileSize,
a.bytes/1048576 filesize,
(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 ELSE bytes/1048576 END) - nvl(b.usedbytes,0) free_Mb,
round(100*(1-(nvl(b.Usedbytes,0))/(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 else bytes/1048576 END)),2) "%_FREE",
a.file_name
FROM  dba_data_files a,
(SELECT file_id
, sum(bytes)/1048576 Usedbytes
FROM dba_extents GROUP BY file_id) b
WHERE a.file_id = b.file_id(+)
AND a.tablespace_name = '&tablespace_name'
ORDER BY
a.tablespace_name,
a.file_id
/
FILE_ID TABLESPACE AUT MAXFILESIZE FILESIZE FREE_MB %_FREE FILE_NAME
------- ---------- --- ----------- -------- ------- ------ ---------
1       SYSTEM     NO  500         500      93.375  18.68 /u05/oracle/oradata/SYSTM_01.dbf

Once you have found the file_id for the datafile in the tablespace that needs expanding then you can increase the size of the tablespace.

Linux – Check uptime

Use

Normally used for diagnostic purposes to check how long a system has been running

Description

Gives a one line display of the current time, how long a server has been running for, how many users are currently logged on, and the system load averages for the past 1, 5, and 15 minutes.

Command

$ uptime
17:40:09 up 34 min,  1 user,  load average: 0.00, 0.01, 0.01

Oracle – Migrate database from Oracle 9i to Oracle 10g

If you are migrating from Oracle 9i to Oracle 10g you must use the Oracle export/import tools.  The process for migrating from Oracle 9i to Oracle 10g is to export the data from the Oracle 9i system, the transfer the files across to the 10g system. On the 10g system you must then create a 10g database. Once the 10g database has been created you must then import the data from the 9i system. After the import has completed statistics can then be gathered.

9i Preparation

1. Check how much disk space is available, either by checking the file system, or checking how much space can be created when backups have been cleared down
2. Check how big the datafiles are to see how big the export is likely to be, remember the export will be compressed but it is better to have more space than necessary than not enough
3. So as not to run out of undo tablespace create the following script and store it on the 9i system:
   

   create table tmp_users as select * from dba_users;
delete tmp_users;

   Do not close the session or commit the transaction whilst the export is completing. If the database export is large then there is a possibility you will run out of undo tablespace during export. If this happens you will receive the following error:

   ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

4. For ease of use we can create an export parameter file. This file can be reused for further exports. Create the export parameter file on the 9i system:
   

   userid=<user>/<password>@<oracle_sid>
log=exp.log
full=y
consistent=y
filesize=5G
file=/u02/oracle/oraback/<oracle_sid>/export/exp1.dmp

5. Create a shell script for running the export, this should include a nohupto allow the script to run in the background. If the script runs in the foreground then the export will fail if the server window times out.
   

   nohup exp parfile=export.par &

6. Query tablespaces and sizes. Take a note of the sizes of the tablespaces and datafiles in order to recreate them for use on the 10g system.

10g Preparation

1. On the 10g system create an import shell script. This will look similar to that of the export on the 9i system:
   

   nohup imp parfile=import.par &

2. Create the export parameter file on the 10g system:
   

   userid=<user>/<password>@<oracle_sid>
log=import.log
full=y
commit=y
feedback=50000
buffer=10000000
file=exp1.dmp

3. Create a transactional database using DBCA in a VNC session
4. Create a sql script for the creation of tablespaces and datafiles

Export Data from 9i

1. Check backup to tape has been successful
2. Cleardown any old backups if space is required before export
3. Run the sql script for undo tablespace retention
4. In a separate session start the export using the previously created export shell script
5. Tail nohup.out to see when the export has completed
   

   $  tail -f nohup.out

FTP the files to the 10g server

1. FTP the files across to the 10g server either using WinSCP (linux to linux) or using mget (windows to linux/windows)

Import to 10g

1. In a VNC session run the import shell script created previously

Oracle – Unable to access ASMCMD (DBD ERROR: OCISessionBegin)

Error:

$  asmcmd
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory (DBD ERROR: OCISessionBegin)
ASMCMD-08103: failed to connect to ASM; ASMCMD running in non-clustered mode

Cause:

The ASM database has not been started

Resolution:

Restart the ASM database then log into asmcmd to make sure it has started.

Check that the ORACLE_SID and ORACLE_HOME are pointing towards ASM, if not then set them correctly:

$ echo $ORACLE_SID
+ASM
$ echo $ORACLE_HOME
/u01/oracle/asm102

Check that the ASM database is running:

$ sqlplus ‘/as sysdba’
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 18 11:08:39 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to an idle instance.
SQL>

Startup ASM database

SQL> startup

Log into ASMCMD

$  asmcmd
ASMCMD>