Vi quick reference for DBAs

1 comments Wednesday, December 02, 2009 Posted by G.Rajeshkumar.B.E(CSE)


Here are a few useful commands for those who are new to vi.

esc :q!Just quit - don't save
esc :e!Revert to saved
esc :wqSave and exit
esc shift zzSave and exit
esc iEnter insert mode (edit mode)
esc aEnter append mode (edit mode)
escExit edit mode
esc rReplace a single character
esc xDelete a single character
esc ddDelete a single line
esc yyCopy a single line
esc pPaste a single line
.Repeat the last command
esc /String search
esc $Jump to end of line
esc ^Jump to begining of line
shift gJump to the end of the file
:1Jump to the begining of the file
:.=Display the current line number

tar command in linux

1 comments Thursday, July 30, 2009 Posted by G.Rajeshkumar.B.E(CSE)


To create an archive using tar, use a command like this, which bundles all the files in the current directory that end with .doc into the alldocs.tar file:

tar cvf alldocs.tar *.doc

Here's a second example, which creates a tar file named panda.tar containing all the files from the panda directory (and any of its subdirectories):

tar cvf panda.tar panda/

In these examples, the c, v, and f flags mean create a new archive, be verbose (list files being archived), and write the archive to a file. You can also create tar files on tape drives

or floppy disks, like this:

tar cvfM /dev/fd0 panda Archive the files in the panda directory to floppy disk(s).
tar cvf /dev/rmt0 panda Archive the files in the panda directory to the tape drive.

The /dev/fd0 entry is Linux-ese for "floppy drive zero" (your A drive under DOS), and /dev/rmt0 means "removable media tape zero," or your primary tape drive. The M flag means use multiple floppy disks--when one disk is full, tar prompts you to insert another.

To automatically compress the tar file as it is being created, add the z flag, like this:

tar cvzf alldocs.tar.gz *.doc

In this example, I added the .gz suffix to the archive file name, because the z flag tells tar to use the same compression as the gzip command.

To list the contents of a tar file, use the t (type) flag in a command, like this:

tar tvf alldocs.tar List all files in alldocs.tar.

To extract the contents of a tar file, use the x (extract) flag in a command, like this:

tar xvf panda.tar Extract files from panda.tar.

This will copy all the files from the panda.tar file into the current directory. When a tar file is created, it can bundle up all the files in a directory, as well as any subdirectories and the files in them. So when you're extracting a tar file, keep in mind that you might end up with some new subdirectories in the current directory.

We've used several different flags in the sample tar commands so far. Here's a list of the most common flags:

c Create a new archive.
t List the contents of an archive.
x Extract the contents of an archive.
f The archive file name is given on the command line (required whenever the tar output is going to a file)
M The archive can span multiple floppies.
v Print verbose output (list file names as they are processed).
u Add files to the archive if they are newer than the copy in the tar file.
z Compress or decompress files automatically.

For more information on the tar command, see the tar manual.

Identifying Your Oracle Database Software Release

1 comments Thursday, July 30, 2009 Posted by G.Rajeshkumar.B.E(CSE)

Because Oracle Database continues to evolve and can require maintenance, Oracle periodically produces new releases. Not all customers initially subscribe to a new release or require specific maintenance for their existing release. As a result, multiple releases of the product exist simultaneously.

As many as five numbers may be required to fully identify a release. The significance of these numbers is discussed in the sections that follow.

Release Number Format

To understand the release nomenclature used by Oracle, examine the following example of an Oracle Database server labeled "Release 10.1.0.1.0".

Figure 1-1 Example of an Oracle Database Release Number

Description of Figure 1-1 follows
Description of "Figure 1-1 Example of an Oracle Database Release Number"

Note:

Starting with release 9.2, maintenance releases of Oracle Database are denoted by a change to the second digit of a release number. In previous releases, the third digit indicated a particular maintenance release.

Major Database Release Number

The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.

Database Maintenance Release Number

The second digit represents a maintenance release level. Some new features may also be included.

Application Server Release Number

The third digit reflects the release level of the Oracle Application Server (OracleAS).

Component-Specific Release Number

The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

Platform-Specific Release Number

The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.

Checking Your Current Release Number

To identify the release of Oracle Database that is currently installed and to see the release levels of other database components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION. A sample query follows. (You can also query the V$VERSION view to see component-level information.) Other product release levels may increment independent of the database server.

COL PRODUCT FORMAT A35
COL VERSION FORMAT A15
COL STATUS FORMAT A15
SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                  VERSION     STATUS
---------------------------------------- ----------- -----------
NLSRTL                                   10.2.0.1.0  Production
Oracle Database 10g Enterprise Edition   10.2.0.1.0  Prod
PL/SQL                                   10.2.0.1.0  Production
...

It is important to convey to Oracle the results of this query when you report problems with the software.

trouble shooting

1 comments Tuesday, May 26, 2009 Posted by G.Rajeshkumar.B.E(CSE)

ORA-28000: The account is lockedORA-28000: The account is locked
Another wild guess, did you create your user with a case-sensitive name?
SQL> create user "dbFan" identified by dbfan account lock;
User created.
SQL> select username, account_status from dba_users where username='dbFan';
USERNAME ACCOUNT_STATUS
------------- --------------------------------
dbFan LOCKED
SQL> alter user dbFan account unlock;
alter user dbFan account unlock*ERROR at line 1:
ORA-01918: user 'DBFAN' does not exist
SQL> alter user "dbFan" account unlock;
User altered.
SQL>
See what SQL users are running on the systemOnce we figure out who is on the system, we will probably want to know what they are doing. In this case, we will join the v$session view we just queried with another view, the V$SQL view. The V$SQL view will provide us with the SQL that is being executed on our system. Let’s see GRUMPY’s session details:
SQL> select a.sid, a.serial#, b.sql_text
2 from v$session a, v$sqlarea b
3 where a.sql_address=b.address
4 and a.username='GRUMPY';
SID SERIAL# SQL_TEXT
---- --------- -------------------------------------
122 61521 select count(*) from gen_person where gen_person_id=95000

FINDING WHICH INDEX BELONGS TO WHICH TABLE AND OWNER OF THE INDEX...
Select index_owner, table_name, index_name, column_nameFROM dba_ind_columns;
SQL> column table_owner format a15
SQL> column table_name format a20
SQL> column index_name format a20
SQL> column column_name format a20
SQL> Select owner, table_name, index_name, column_name
2 FROM dba_ind_columns
3 Order by owner, table_name, column_position
4 Where owner=’SCOTT’
5 AND table_name=’EMP’;
TABLE_OWNER TABLE_NAME INDEX_NAME COLUMN_NAME
------------------ --------------- ------ --------- ----------
SCOTT EMP

Monitoring Temporary Tablespaces and Sorting:
Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.
One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP 52428800 52428800

SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-01991: invalid password file 'C:\oracle\ora92\DATABASE\PWDjay.ORA'
to overcome this error
step one:
first delete the password file in the above destination
step two:
create a new password file using this query
orapwd file= orapwd file= (desination of pwdfile)\PWD(sid).ora password=oracleexampleC:\>orapwd file=c:\oracle\ora92\database\PWDjay.ora password=oracle;

Installing Oracle 9i in Linux RHEL4

0 comments Tuesday, May 12, 2009 Posted by G.Rajeshkumar.B.E(CSE)
Labels:

Installing Oracle 9i in Linux RHEL4
Part I: Installing Linux

Minimum hardware requirement:
* Pentium IV processor
* 512MB of RAM
* minimum 40GB HARD DISK

1. Boot the server using the first CD.
* You may need to change your BIOS settings to allow booting from the CD.
2. The boot screen appears with the boot: prompt at the bottom of the screen.
* Select Enter to continue with a graphical install on the console. (For other installation methods and options, refer to the Red Hat Installation Guide.)
* The installer scans your hardware, briefly displays the Red Hat splash screen, and then begins a series of screen prompts.
3. Language Selection
* Accept the default.
4. Keyboard Configuration
* Accept the default.
5. Welcome Screen
* Click on Next.
6. Disk Partitioning Setup
* Do the disk partitioning as per the following
PARTITIONS MINIMUMSIZE
/ 12GB
/boot 100MB
/opt 4GB
/oracle 10GB
/tmp 3GB
swap double the size of RAM not more than 2GB

7. Boot Loader Configuration
* Accept the default.
8. Network Configuration
* It is usually best to configure database servers with a static IP address. To do so, click on Edit .
* A pop-up window appears. Uncheck the Configure using DHCP box, and enter the IP Address and Netmask for the server. Be sure that Activate on boot is checked, and click on OK .
* In the Hostname box, select manually and enter the hostname.
* In the Miscellaneous Settings box, enter the remaining network settings.
9. Firewall Configuration
* For the purposes of this walk-through, no firewall is configured. Select No firewall
* Select Disabled on the "Enable SELinux" drop down list.
* Click on Proceed when the "Warning - No Firewall" window appears.
10. Additional Language Support
* Accept the default.
11. Time Zone Selection
* Choose the time settings that are appropriate for your area. Setting the system clock to UTC is usually a good practice for servers. To do so, click on System clock uses UTC.
12. Set Root Password
* Enter a password for root, and enter it again to confirm.
13. Package Installation Defaults
* Select Customize software packages to be installed.
14. Package Group Selection
* Select only the package sets shown here and leave all others unselected.
* Desktop
o X Window System
o Gnome
* Applications
o Graphical Internet (optional)
* Servers
o Do not select anything in this group.
* Development
o Development Tools
* System
o Administration Tools
o System Tools
+ Add the package 'sysstat' by clicking on the Details link and selecting "sysstat - The sar an iostat system monitoring commands." from the Optional Packages list.
* Miscellaneous
o Do not select anything in this group.
* Click on Next to proceed.
15. Installing Packages
* Software will be copied to the hard disk and installed. Change disks as prompted.
16. Congratulations
* Remove the installation media from the system, and click on Reboot .
17. The system automatically reboots and presents a new welcome screen.
* Click on Next.
18. License Agreement
* Read the license agreement. If you agree to the terms, select Yes, I agree to the License Agreement and click on Next.
19. Date and Time
* Set the Date and Time.
* If you want to use an NTP server (recommended), select Enable Network Time Protocol and enter the name of the NTP server.
20. Display
* Accept the defaults or change as required.
21. Red Hat Login
* Enter your Red Hat Network login and password or create a new one.
22. System User
* Create an account for yourself.
* Do not create an account for oracle at this time. Creating the oracle account is covered later in this section.
23. Additional CDs
* Click on Next.
24. Finish Setup
* Click on Next.
25. A graphical login screen appears.
26. Congratulations! Your RHEL4 software is now installed.
27. Restart the system and logged in a root user, hence keeping the system ready to install Oracle 9i Release 2 on it.

Part 2:
Installation steps of oracle 9i:

Before Installing oracle, acquire the following softwares
1.Oracle 9i release 2(9.2.0.4.0) CD 1 OF 3.
2.Oracle 9i release 2(9.2.0.4.0) CD 2 of 3.
3.Oracle 9i release 2(9.2.0.4.0) CD 3 of 3.
4.Oracle 9i release 2(9.2.0.4.0) patch CD.
5.jdk1.5 update 6.

Download Oracle9i for Linux from the following web site:
http://otn.oracle.com/software/products/oracle9i/htdocs/linuxsoft.html

Download java for linux from the following website:
http://java.sun.com

Download oracle 9i patch cd from the following link:
http://metalink.oracle.com
http://www.idevelopment.info/data/Oracle/DBA_tips/Linux/LINUX_8.shtml#Downloading%20/%20Unpacking%20the%20Oracle9i%20Installation%20Files

The following steps are for installing oracle 9i on RHEL4.
1.Install JDK1.5 in /usr/java folder by using the following command.
* create folder named javasoft on the Desktop.
* open a new terminal using the following command:
right click on the Desktop and click Open terminal from the popup menu.
* copy jdk-1_5_0_06-linux-i586.bin from the CD to /root/Desktop/javasoft folder, using the following commands.
Insert the CD containing jdk1.5 software
#mount /media/cdrom
#cp /media/cdrom/jdk-1_5_0_06-linux-i586.bin /root/Desktop/javasoft
*create a folder named java in the /usr folder.
#mkdir -p /usr/java
*Use the following commands to install jdk1.5
#cd /usr/java
#/root/Desktop/javasoft/jdk-1_5_0_06-linux-i586.bin
follow the steps as prompted to install the software.
*Create a symbolic link "jdk1.5" for easy accessibility using the following command:
#ln -s jdk-1_5_0_06 jdk1.5
*Do the permanent path setting for writing and executing the java programs without going to this folder every time.
#vi /etc/profile
Insert the following lines at the end of the file.
JAVA_HOME=/usr/java/jdk1.5
export JAVA_HOME
PATH=$JAVA_HOME/bin:$PATH
export PATH
*save and relogin as root user.

2.Open the terminal window on the linux desktop

3.Use the following commands to go to the home directory.
#cd /home

4.Insert the oracle 9i release CD no.1/3 to the CD Drive.

5.Mount the CD by the following command:
#mount /media/cdrom

6.Confirm the availability of the .cpio file the CD using the following command:
#ls /media/cdrom

7.Use the following command to extract the software from CD 1 of oracle
#cpio -idmv < /media/cdrom/ship_9204_linux_disk1.cpio

note: once you do this a folder named Disk1 will be created in /home directory

8.Unmount the CD using the following command:
#umount /media/cdrom

9.Insert the oracle 9i release CD no.2/3 to the CD Drive.

10.Mount the CD by the following command:
#mount /media/cdrom

11.Confirm the availability of the .cpio file the CD using the following command:
#ls /media/cdrom

12.Use the following command to extract the software from CD 2 of oracle
#cpio -idmv < /media/cdrom/ship_9204_linux_disk2.cpio

note: once you do this a folder named Disk2 will be created in /home directory

13.Unmount the CD using the following command:
#umount /media/cdrom

14.Insert the oracle 9i release CD no.3/3 to the CD Drive.

15.Mount the CD by the following command:
#mount /media/cdrom

16.Confirm the availability of the .cpio file the CD using the following command:
#ls /media/cdrom

17.Use the following command to extract the software from CD 3 of oracle
#cpio -idmv < /media/cdrom/ship_9204_linux_disk3.cpio

note: once you do this a folder named Disk3 will be created in /home directory

18.Unmount the CD using the following command:
#umount /media/cdrom

19.For patch use the following commands:
#mkdir 92040

20.Insert the patch CD to the CD Drive.

21.Mount the CD using following command:
#mount /media/cdrom

22.Use the following command to unzip the files
#unzip -d /home/92040 /media/cdrom/p3006854_9204_LINUX.zip

23.After issuing the above command, it will create a folder named as 3006854 in the /home/92040 directory. please confirm the same.

24.Issue the following commands to extract the p4198954 patch.
#unzip -d /home/92040 /media/cdrom/p4198954_40_LINUX.zip

you will able to see the following rpms within the specified folder.
1) compact-orcle-rhel4-1-0-5.i386.rpm
2) compact-libcwait-2.1-1.i386.rpm

As soon as you try to install these RPM's then you will receive dependency failure.
Use the following steps to avoid dependency failure:
1)Insert the RHEL4 CD no.3 to the CD Drive.

2)Mount the CD using the following command:
#mount /media/cdrom
#cd /media/cdrom/RedHat/RPMS

3)Execute the following RPMs
#rpm -ivh --aid libaio-devel-0.3.102-1.i386.rpm
#rpm -ivh --aid openmotif21-2.1.30-11.RHEL4.2.i386.rpm

4)After successful execution of the above RPMs, execute the RPMs already unzipped from the 4198954 patch.
#cd /home/92040
#rpm -ivh compat-oracle-rhel4-1.0.5.i386.rpm
#rpm -ivh compat-libcwait-2.1-1.i386.rpm

5)unmount the 3rd CD of RHEL4 using the following command
#unmount /media/cdrom

25.Configuring the Linux Kernel Parameters, update the /etc/sysctl.conf file,by adding the following lines towards the end of the file.
#vi /etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=26214

save the file and relogin.

26.Create the Oracle Groups and User Account:
# groupadd oinstall
# groupadd dba
# groupadd oper
# useradd -g oinstall -G dba oracle
# id oracle

Set the password on the oracle account:
# passwd oracle
Changing password for user oracle.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.


27.Create Directories for oracle software.
# mkdir -p /oracle/app/oracle/product/9.2.0.4.0
# chown -R oracle:oinstall /oracle
# chmod -R 775 /oracle

28.Login as the oracle user and add the following lines at the end of the .bash_profile file:
#vi .bash_profile


ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0.4.0; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=$ORACLE_HOME/bin:$PATH:/usr/java/jdk1.5/bin; export PATH
ORACLE_OWNER=oracle; export ORACLE_OWNER
ORACLE_SID=jay; export ORACLE_SID

LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export CLASSPATH
ORACLE_OEM_JAVARUNTIME=/usr/java/jdk1.5; export ORACLE_OEM_JAVARUNTIME

LD_ASSUME_KERNEL=2.4.19; export LD_ASSUME_KERNEL
THREADS_FLAG=native; export THREADS_FLAG
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

29.Save the .bash_profile file and relogin as oracle user.

30.Make sure the .bash_profile file ran correctly by issuing the following command:
$set | more

31.Login to Root using this command:
$su -
password:rootpassword
#

32.Execute the 3006854 patch using the following command:
#sh /home/92040/3006854/rhel3_pre_install.sh

33.Exit from the Root.
#exit
$(After typing exit you will receive the oracle login $ prompt)

34.Start the universal installer using the following command:
$/home/Disk1/runInstaller

note: This will start the universal installer. Follow the steps in the wizard to install oracle software.


NOTE: During the installation enter the appropriate ORACLE_HOME and name then continue as normal

1. Welcome Screen:
Click Next

2.Inventory Location:
"/oracle/app/oracle/oraInventory"and Click Next

3.Unix Group Name:
Use "oinstall" and click Next
When asked to run /tmp/orainstRoot.sh, run it before you click Continue
open a new terminal,by click the desktop
$su -
password:root password
#cd /tmp
#./orainstRoot.sh
once this execution is over and you get back the prompt, switch to the dialog box in the Universal Installer and continue next.

5.File Locations:
name:use default value
path:/oracle/app/oracle/product/9.2.0.4.0

6.Available Products:
Select "Oracle9i Database 9.2.0.1.0"

7.Installation Types:
Select "Enterprise Edition(2.84GB)

8.Database configuration:
Select "General Purpose"

9.Database Identification
Global Database Name:jay
SID:jay
and then Click Next

10.Database File Locations:
Directory for Database Files:/oracle/app/oracle/oradata
and then click Next

11.Database Character Set:
Use the default character set
and then click Next

12.Summary:
Start the Install

13.Setup privileges:
before completing the installation the universal installer will prompt another file to be executed from the root login.
follow the sequence commands to overcome this:
open the terminal
$su -
password:root password
#cd /oracle/app/oracle/product/9.2.0.4.0
#./root.sh
it will prompt for a path, press enter to keep it default.
after that press ok in the dialog box.

14.Configuration tools:
if any error comes, Simply ignore it.

15.Database Configuration Assistant:
enter password for sys and system and click OK

16. At the end of the installation, exit runInstaller.

managing undo tablespace

1 comments Thursday, April 16, 2009 Posted by G.Rajeshkumar.B.E(CSE)
Labels:

managing undo tablespace in oracle 9i
Oracle Database keeps records of actions of transactiion, before they are committed and Oracle needs this information to rollback or Undo the Changes to the database. These records in Oracle are called Rollback or Undo Records. These records are used to Rollback transactions when a rollback statement is issued or during recovery of a database or to provide a read consistent view of data.
Until Oracle 8i and also in 9i, Oracle uses Rollback Segments to manage the Undo Data. Starting with Oracle 9i, DBA's are provided with some a new feature referred to as "Undo Tablespace" which allows the dba to exert more control on how long Undo information is retained and also eliminates the complexity of managing Rollback Segments in certain environments.
Starting with 9i, the rollback segment way is referred to as Manual Undo Management Mode and the new Undo Tablespaces method as the Automatic Undo Management Mode. Although both rollback Segments and Undo Tablespaces are supported in Oracle 9i, Both modes cannot be used. System Rollback segment exists in both the modes. Since we are all familiar with the manual mode, lets look at the features of the Automatic Undo Management (Undo Tablespaces )
Init.ora Parameters for Automatic Undo Management
UNDO_MANAGEMENT : This parameter sets the mode in which oracle manages the Undo Information.
The default value for this parameter is MANUAL so that all your old init.ora files can be used without any changes. To set the database in an automated mode, set this value to AUTO. ( UNDO_MANAGEMENT = AUTO)
UNDO_TABLESPACE : This parameter defines the tablespaces that are to be used as Undo Tablespaces. If no value is specified oracle grabs the first available Undo Tablespace or if there are none present, Oracle will use the system rollback segment to startup. This value is dynamic and can be changed online ( UNDO_TABLESPACE = undo_tbs1 )
UNDO_RETENTION : This value specifies the amount of time, Undo is kept in the tablespace. This applies to both committed and uncommitted transactions since the introduction of FlashBack Query feature in Oracle needs this information to create a read consistent copy of the data in the past. Default value is 900 Secs ( UNDO_RETENTION = 500)
UNDO_SUPRESS_ERRORS : This is a good thing to know about in case your code has the alter transaction commands that perform manual undo management operations. Set this to true to suppress the errors generated when manual management SQL operations are issued in an automated management mode.

Creating and Managing Undo Tablespaces :
Undo tablespaces use syntax that is similar to regular tablespaces except that they use the keyword UNDO. These tablespaces can be created during the database creation time or can be added to an existing database using the create UNDO Tablespace command
Create DATABASE jay controlfile ...........
UNDO Tablespace undo_tbs01
datafile '/oracle/app/oracle/oradata/jay/undotbs01.dbf' size 100m
Create UNDO Tablespace undo_tbs1datafile
'/oracle/app/oracle/oradata/jay/undotbs01.dbf' size 100m
autoextend on;
All operations like Drop,Renaming a data file, Adding a datafile, Online /Offline Swith or Start Backup / End Backup Switch can be made using the regular alter tablespace command. All other operations are managed by Oracle in the automated management mode.
NOTE:
the tablespace will only be dropped if it is not currently being used or contains undo information for a current transaction. It will however drop tablespaces where the undo information has not yet expired, thus affecting long running queries and flashback query.
Monitoring :
v$UNDOSTAT : This view contains statistics for monitoring the effects of transaction execution on Undo Space in the current instance. These are available for space usage, transaction concurrency and length of query operations. This view contains information that spans over a 24 hour period and each row in this view contains data for a 10 minute interval specified by the BEGIN_TIME and END_TIME.
Undo information can be queried using the following views:
V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
DBA_UNDO_EXTENTS
SHOW PARAMETER UNDO_
By default all users have unlimited access to undo tablespaces, although this can be limited using the Resource Manager directive UNDO_POOL.
for more information:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/undo.htm

Oracle Data Pump in Oracle Database 10g

0 comments Wednesday, April 15, 2009 Posted by G.Rajeshkumar.B.E(CSE)
Labels:

Oracle Data Pump in Oracle Database 10g
Oracle Data Pump is a newer, faster and more flexible alternative to the "exp" and "imp" utilities used in previous Oracle versions. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.

Getting Started
Table Exports/Imports
Schema Exports/Imports
Database Exports/Imports
Miscellaneous Information
Data Pump API
External Tables
Help
Getting Started
For the examples to work we must first unlock the SCOTT account and create a directory object it can access:

CONN sys/password@db10g AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;
GRANT CREATE ANY DIRECTORY TO scott;

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
Table Exports/Imports
The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
For example output files see expdpEMP_DEPT.log and impdpEMP_DEPT.log.

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema Exports/Imports
The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log
For example output files see expdpSCOTT.log and impdpSCOTT.log.

Database Exports/Imports
The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log
For an example output file see expdpDB10G.log.

Miscellaneous Information
Unlike the original exp and imp utilities all data pump ".dmp" and ".log" files are created on the Oracle server, not the client machine.

All data pump actions are performed by multiple jobs (server processes not DBMS_JOB jobs). These jobs are controlled by a master control process which uses Advanced Queuing. At runtime an advanced queue table, named after the job name, is created and used by the master control process. The table is dropped on completion of the data pump job. The job and the advanced queue can be named using the JOB_NAME parameter. Cancelling the client process does not stop the associated data pump job. Issuing "ctrl+c" on the client during a job stops the client output and presents a command prompt. Typing "status" at this prompt allows you to monitor the current job:

Export> status

Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: D:TEMPDB10G.DMP
bytes written: 4,096

Worker 1 Status:
State: EXECUTING
Object Schema: SYSMAN
Object Name: MGMT_CONTAINER_CRED_ARRAY
Object Type: DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Completed Objects: 261
Total Objects: 261
Data pump performance can be improved by using the PARALLEL parameter. This should be used in conjunction with the "%U" wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=expdpSCOTT.log
The DBA_DATAPUMP_JOBS view can be used to monitor the current jobs:

system@db10g> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION
------------------------------ ------------------------------ ------------------------------
JOB_MODE STATE DEGREE ATTACHED_SESSIONS
------------------------------ ------------------------------ ---------- -----------------
SYSTEM SYS_EXPORT_FULL_01 EXPORT
FULL EXECUTING 1 1
The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export. When the EXCLUDE parameter is used all objects except those specified by it will be included in the export:

expdp scott/tiger@db10g schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
Data Pump API
Along with the data pump utilities Oracle provide an PL/SQL API. The following is an example of how this API can be used to perform a schema export:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_dp_handle NUMBER;
l_last_job_state VARCHAR2(30) := 'UNDEFINED';
l_job_state VARCHAR2(30) := 'UNDEFINED';
l_sts KU$_STATUS;
BEGIN
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
job_name => 'EMP_EXPORT',
version => 'LATEST');

DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'SCOTT.dmp',
directory => 'TEST_DIR');

DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'SCOTT.log',
directory => 'TEST_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'SCHEMA_EXPR',
value => '= ''SCOTT''');

DBMS_DATAPUMP.start_job(l_dp_handle);

DBMS_DATAPUMP.detach(l_dp_handle);
END;
/
Once the job has started the status can be checked using:

system@db10g> select * from dba_datapump_jobs;
External Tables
Oracle have incorporated support for data pump technology into external tables. The ORACLE_DATAPUMP access driver can be used to unload data to data pump export files and subsequently reload it. The unload of data occurs when the external table is created using the "AS" clause:

CREATE TABLE emp_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY test_dir
LOCATION ('emp_xt.dmp')
)
AS SELECT * FROM emp;
The data can then be queried using:

SELECT * FROM emp_xt;
The syntax to create the external table pointing to an existing file is similar, but without the "AS" clause:

DROP TABLE emp_xt;

CREATE TABLE emp_xt (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2))
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY test_dir
LOCATION ('emp_xt.dmp')
);

SELECT * FROM emp_xt;
Help
The HELP=Y option displays the available parameters:

expdp help=y

Export: Release 10.1.0.2.0 - Production on Tuesday, 23 March, 2004 8:33

Copyright (c) 2003, Oracle. All rights reserved.


The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to unload where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dumpfiles and logfiles.
DUMPFILE List of destination dump files (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
ESTIMATE_ONLY Calculate job estimates without performing the export.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Export entire database (N).
HELP Display Help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of export job to create.
LOGFILE Log file name (export.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile (N).
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to export a subset of a table.
SCHEMAS List of schemas to export (login schema).
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
TABLES Identifies a list of tables to export - one schema only.
TABLESPACES Identifies a list of tablespaces to export.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command Description
------------------------------------------------------------------------------
ADD_FILE Add dumpfile to dumpfile set.
ADD_FILE=dumpfile-name
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS=[interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.



impdp help=y

Import: Release 10.1.0.2.0 - Production on Saturday, 11 September, 2004 17:22

Copyright (c) 2003, Oracle. All rights reserved.


The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

Format: impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to load where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dump, log, and sql files.
DUMPFILE List of dumpfiles to import from (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Import everything from source (Y).
HELP Display help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of import job to create.
LOGFILE Log file name (import.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile.
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to import a subset of a table.
REMAP_DATAFILE Redefine datafile references in all DDL statements.
REMAP_SCHEMA Objects from one schema are loaded into another schema.
REMAP_TABLESPACE Tablespace object are remapped to another tablespace.
REUSE_DATAFILES Tablespace will be initialized if it already exists (N).
SCHEMAS List of schemas to import.
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE Write all the SQL DDL to a specified file.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION Action to take if imported object already exists.
Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES Identifies a list of tables to import.
TABLESPACES Identifies a list of tablespaces to import.
TRANSFORM Metadata transform to apply (Y/N) to specific objects.
Valid transform keywords: SEGMENT_ATTRIBUTES and STORAGE.
ex. TRANSFORM=SEGMENT_ATTRIBUTES:N:TABLE.
TRANSPORT_DATAFILES List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command Description (Default)
------------------------------------------------------------------------------
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
START_JOB=SKIP_CURRENT will start the job after skipping
any action which was in progress when job was stopped.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS=[interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.
For more information see:

Oracle Database Utilities 10g Release 1 (10.1)

ARCHIVELOG MODE ENABLE AND DISABLE

1 comments Monday, April 13, 2009 Posted by G.Rajeshkumar.B.E(CSE)
Labels:

Archive log files are the offline copies of the online redolog files to enable recovery incase of failures and media recovery.

An Oracle database can run in one of two modes. By default, the database is created in NOARCHIVELOG mode. When in NOARCHIVELOG mode the database runs normally, but there is no capacity to perform any type of point in time recovery operations or online backups. Thus, you have to shutdown the database to back it up, and when you recover the database you can only recover it to the point of the last backup. While this might be fine for a development environment, the big corporate types tend to frown when a weeks worth of current production accounting data is lost forever.

So, if you wish to avoid the wrath of the CEO and angry end-users, you will want to run Oracle in ARCHIVELOG mode. In ARCHIVELOG mode, the database will make copies of all online redo logs after they are filled. These copies are called archived redo logs. The archived redo logs are created via the ARCH process. The ARCH process copies the archived redo log files to one or more archive log destination directories
Enabling ARCHIVELOG Mode
Lets start by checking the current archive mode.
SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;
LOG_MODE
------------
NOARCHIVELOG
else
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination D:\oracle\ora92\RDBMS
Oldest online log sequence 0
Current log sequence 1
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL>
SQL> alter database archivelog;

Database altered.

SQL>
SQL> alter database open;

Database altered.

SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Disabled
Archive destination D:\oracle\ora92\RDBMS
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> alter system set log_archive_start=true scope=spfile;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Disabled
Archive destination D:\oracle\ora92\RDBMS
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\ora92\RDBMS
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL>
Learn more about managing archive redo logs in the Oracle Database Administrator's Guide:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10739/archredo.htm

Subscribe to: Posts (Atom)