Solaris
$ swap -s
total: 8669792k bytes allocated + 911696k reserved = 9581488k used, 89182544k available
$ swap -l
swapfile dev swaplo blocks free
/dev/vx/dsk/bootdg/swapvol 240,104002 16 16380608 16380608
Unix: command to check physical memory (RAM) in Solaris & AIX
Solaris: prtconf |grep -i mem
Aix: Determine the memory device name: lsdev -C |grep mem
$ lsdev -C |grep mem
mem0 Available Memory
lsattr -El mem0 (where mem0 is the memory device name)
Unix: Command to Find out Most CPU Consuming Processes
Linux Command
ps -eo pid,cmd,%cpu,%mem --sort=-%cpu | head -n 100
Sample Output
Unix: Command to kill multiple processes (pid) in single command
Most of the time DBAs has to perform their tasks quickly and with 100% accuracy. If we wanted to kill multiple oracle processes at time we can use below mentioned command. This command will filter the idle oracle sessions(local=no) and terminate.
Windows ORAKILL utility usage-Syntax to Kill Oracle thread
Oracle Utility orakill for windows
There are two ways to kill an oracle process (UNIX) or thread (windows). First, kill the session from database with sys as sysdba privilege using the command alter system kill session ‘sid, serial#’. Secondly, kill the process or thread from OS level. The first one has some limitations as it needs oracle database access.
Data Pump (expdp/impdp) Tuning features - PARALLEL option for faster performance behavior
Some Facts about DATA PUMP Parallel=n option
- By default the value of the parallel option is 1
- Export job creates those many threads specified in the parallel option.
- The value of the parallel option can be modified in interactive mode.
Solaris psrinfo to check number of CPU/processors SunOS
psrinfo command
In Solaris, psrinfo is the command to see the status of the CPUs/Processors. Below mentioned command will give the total number of CPUs in a Solaris server.
In Solaris, psrinfo is the command to see the status of the CPUs/Processors. Below mentioned command will give the total number of CPUs in a Solaris server.
Troubleshoot Fix Resolve ORA-01555:snapshot too old: rollback segment number %s
ORA-01555 Snapshot Too Old
This error is related to rollback (Oracle 8i and below) / UNDO (9i and above) segments. Oracle uses UNDO segments for maintaining the read consistency of the database by recording the block level changes to the UNDO. UNDO is used for reconstruct the read-consistent snapshot of the data. Features and configuration of Oracle Automatic UNDO Management AUM
Overview
Oracle 8i and below, the rollback segments were used for maintaining the read consistency and for the transaction rollback. Oracle 9i onwards the UNDO replaced the rollback segments with some automatic features. This feature includes like creation, monitoring, performance, sizing of the rollback segments. It is easier for the DBAs to minimize the issues with UNDO when using the automatic undo management.
Functions and Features of Oracle UNDO
Overview:
UNDO is an oracle method to rollback or undo the changes to the database. UNDO keeps track of the oracle transactions, mainly before the commit of the transaction. Block level changes are recorded ‘in time’ to provide the read consistency.
Troubleshoot Fix Resolve Ora-00020: Maximum Number Of Processes (num) Exceeded
Overview
This is a database resource error. The error is because of the number of oracle processes reached its maximum limit. The maximum limit is mentioned in your database parameter file (spfile/pfile). Corresponding init parameter is PROCESSES.
DBMS_STATS.export import:How to transfer oracle statistics between databases
Overview:
Oracle database statistics are very import in Cost Based optimizer (CBO). Sometimes the execution plan differs from production database to test databases even though the table, index structures are same. The execution plan might be good in production database compare to the test database. DBAs might get the complaints from the testers. In this situation DBAs can export the schema or table statistics to the test database.
How to rename Oracle Database? Recreate oracle controlfile: Steps to rename the database
Scenario: suppose we wanted to rename a database with name SOURCE to TARGET
Pre requisites
· Take the backup of the database. This backup can be used to restore the database if something wrong happens to the rename procedure.
· Prepare a new init.ora file for TARGET database with db_name as TARGET.
Oracle Imp show=y & Indexfile: How to generate sql scripts from Oracle dump file?
imp SHOW=Y
Import (imp) is the oracle utility to read export dump files and insert them into database. Export dump files can only be read by imp utility. Using SHOW=Y option, the content of the dump file can be generated into readable ASCII format. If we use this option with Y as the value the content of the dump will not be inserted into the database whereas it will spool the sql statements to the log file. SHOW option can be used only with FULL=Y, FROMUSER, TOUSER and TABLES mode.
vi editor mark to create a sub file from main file: explained
1. Open the main in vi (vi created.sql)
2. Go to the line number from where you want to copy. Suppose if you wanted to create a new file using lines from 10 to 20 of main file. Go to 10th line
How to open a crashed oracle database during HOT BACKUP
Different methods to END BACKUP
If the instance crash happens during the oracle hot backup due to the power failure or shutdown abort the database will not open in normal way. It will ask for the media recovery. It throws error like ORA-01113: file xx needs media recovery, ORA-01110 during the database startup.
How to compress export (exp) dump files using UNIX pipes?
Overview:
One of the biggest challenges for DBAs is the lack of disk space especially for the backups. Most often DBAs have to move the backup files from one location to other location to get room for new backups. Here we explained about a common solution for taking the export dump with compressed file size. Compression happens parallel with the export.
Oracle exp compress=Y Compress option in export (EXP) utility
Overview:
Export is the oracle utility to take the export the logical data as backup. There are many options with this tool and among them Compress=[y]/n is very useful. This is not for compressing export dump file. If you take export of a table with compress=Y option, during the import the table the total number of current extents will be consolidated into one single extent. It means there would be only one extent after the import. The default value for the compress option is Y.
UNIX: How to find out the total size of files from multiple directories
ls -ltr |awk '{ sum+=$5 } END {print "Bytes In MB:" sum/1024/1024}'
ls -ltr |awk '{ sum+=$5 } END {print "Bytes In GB:" sum/1024/1024/1024}'
ls -ltr /orabkup0[1-3]/PROD/physical/pool_??/*bkp |awk '{ sum+=$5 } END {print "Bytes In Gb:" sum/1024/1024/1024}'
Oracle Data Pump (expdp/impdp) Benefits / Advantages & Disadvantages
Data Pump Overview
Data pump introduced in oracle 10g and it is entirely different from the normal export/import. Similar to export/import using data pump we can migrate the data from one database to another database running on different operating system. DBMS_DATAPUMP package can be used to implement API and you can access the data pump utility programmatically.
How to schedule a Unix shell script in crontab to run every 2nd Saturday(nth Day of the month)
There is no direct way to schedule the script in crontab for nth day of every month. Which means suppose if you wanted to execute the shell script for every 2nd Saturday or every 3rd Monday, etc. (i.e: The execution occurrence will be one in a month; nth Sunday, Monday, Teusday, Wednesday, Thursday, Friday or Saturday) Crontab will not support this. You have to write your own code to execute this. A sample code is given below.
How to search Unix files pattern recursively (subdirectory) with file size
find . -name *stg -exec ls -ltr {} \;
$ find . -name *stg -exec ls -ltr {} \;
-rw-r----- 1 oracle dba 4440 Mar 10 16:30 ./stage/stg_212977.stg
-rw-r----- 1 oracle dba 3826 May 5 17:05 ./stage/stg_316906.stg
-rw-r----- 1 oracle dba 4440 May 5 19:53 ./stage/stg_332970.stg
5 Different Methods to Partition a non partitioned Oracle table, PARTITION EXCHANGE, SPLIT, Redefinition
Method#1: Partition Exchange
What is Partition Exchange?
ALTER TABLE EXCHANGE PARTITION can convert a partition into a non-partitioned table and vice versa
a. Create duplicate table of non-partitioned table with required partitions
b. Alter table EXCHANGE partition <partition_name>
with <non_partition_table_name>;
with <non_partition_table_name>;
Different types of Oracle Partitioning Range, Hash, List, Interval
Introduction
Partitioning is a technique which allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Oracle provides different varieties of partitioning strategies applicable to different kinds of business requirement. Partitioning is entirely transparent and can be applied to almost any kind of applications
What are the Oracle Advanced Data Compression methodology Tips?
Introduction: The data volume keep on increasing in the databases day by day which result the need of more storage for the databases. Oracle 11g provides advance compression feature to reduce the storage on an average of 3X actual data volume. It is kind of go green feature which can be applied on structured data and unstructured data like documents, images and multimedia. Using this feature database can store more data, faster queries executions, etc.
Trace an Oracle session? DBMS_SYSTEM, DBMS_SUPPORT
1. Using DBMS_SYSTEM package:
Identify the session details (SID & Serial#) which we need to trace from v$session using the available information like Machine/osuser/username.
Query: Select sid, serial#, machine, osuser, program from v$session;
