Linux oracle basics

Published on Author bloggereight1 Comment

Basics linux commands for oracle

How to check the oracle instances running on linux server

[root@rh5x64-test ~]# ps -ef|grep smon
root     12521 12237  0 08:38 pts/1    00:00:00 grep smon
oracle   15036     1  0 Jun01 ?        00:00:08 ora_smon_ENG11R1 

Oracle main configuration file

/etc/oratab

contains three data items separated by colons:
Eg:

ASG920xr:/usr/oracle/9.2.0:Y
ASG817xr:/usr/oracle/8.1.7:Y
TEST920xr:/usr/oracle/9.2.0:N
PROD920xr:/usr/oracle/9.2.0:N

. The first field is the Oracle SID like “ASG817xr” 
. The second field is the home directory for that Oracle SID like “/usr/oracle/”
. The Y or N instructs Oracle whether to start or stop the particular database when either the dbstart or dbshut command is issued. 
. The dbstart command simply parses the oratab file and starts those databases that have a Y in the third field.  It also uses the ORACLE_HOME specified in the file to connect internally to the database and issue the startup command.

dbstart
dbshut

Login into Oracle

as root,

[root@rh5x64-test ~]# su - oracle

then,

[oracle@rh5x64-test dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 10 09:34:25 2010
Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

to get out from oracle,

SQL> exit;

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rh5x64-test dbs]$

Start and Stop Database

a. Start a database,

while starting the oracle database,it will check for SPFILE.
The default SPFILE (server parameter file) is located under $ORACLE_HOME/dbs. Oracle will use this SPFILE during startup, if you don’t specify PFILE.

Oracle will look for the parameter file in the following order under $ORACLE_HOME/dbs. If any one of them exist, it will use that particular parameter file.

1. spfile$ORACLE_SID.ora
2. spfile.ora
3. init$ORACLE_SID.ora

SQL> startup

b. Oracle provides two  UNIX  scripts that assist DBAs with starting and stopping the database: dbstart and dbshut.

as the user oracle, use dbstart to start the databse.

You can also start the database in restricted mode. Restricted mode will only allow users with special privileges (we will discuss user privileges in a later chapter) to access the database (typically DBA’s), even though the database is technically open. We use the startup restrict command to open the database in restricted mode as seen in this example.

SQL> startup restrict

Stop or shutdown a database.
——————————————

1. Normal Shutdown
2. Shutdown Immediate
3. Shutdown Abort

During normal shutdown, before the oracle database is shut down, oracle will wait for all active users to disconnect their sessions. As the parameter name (normal) suggest, use this option to shutdown the database under normal conditions.

SQL> shutdown

During immediate shutdown, before the oracle database is shut down, oracle will rollback active transaction and disconnect all active users. Use this option when there is a problem with your database and you don’t have enough time to request users to log-off.

SQL> shutdown immediate;

During shutdown abort, before the oracle database is shutdown, all user sessions will be terminated immediately. Uncomitted transactions will not be rolled back. Use this option only during emergency situations when the “shutdown” and “shutdown immediate” doesn’t work

SQL> shutdown abort

How to check Oracle Listener

As oracle user

[oracle@dbaserver ~]$ lsnrctl status

[oracle@dbaserver ~]$ lsnrctl start

[oracle@dbaserver ~]$ lsnrctl stop

This command will use all the listeners. If you want to start/stop a specific listener, specify the listener name next to stop. i.e lsnrctl stop [listener-name]

[oracle@dbaserver ~]$ lsnrctl reload

realod will read the listener.ora file for new setting without stop and start of the Oracle listener.

How to change oracle user password

alter user sys identified by oracle;
alter user system identified by oracle;

In this example we are setting password for the users ‘sys’ and ‘system’ as ‘oracle’

Oracle port

1521/tcp

Exp and Imp

exp and imp are utilities present in the $ORACLE_HOME/bin directory and are installed when Oracle is installed. Their prime purpose is to move logical objects out of and into the database respectively – for example dumping all of the tables owned by a user to a single file is achieved using the exp utility. It is important to distinguish between dumping data in this manner and backing up the database which is normally achieved using the rman utility.

Logical backups are backups in which the export utility (for example exp) uses SQL to read database data and then export it into a binary file at the operating system level. You can then import the data back into a database using the import utility (imp).
Backups taken with the export utility differ in the following ways from RMAN backups:

* Database logical objects are exported independently of the files that contain those objects.
* Logical backups can be imported into a different database, even on a different platform. RMAN backups are not portable between databases or platforms.

Usually, physical backups are made. However, if a backup needs to copy a database to a different OS, a logical backup needs be made (Not always true anymore in 10g.)

exp/imp can be used in four modes:

Full export
The EXP_FULL_DATABASE and IMP_FULL_DATABASE, respectively, are needed to perform a full export.
Use the full export parameter for a full export.

Tablespace
Use the tablespaces export parameter for a tablespace export.

User
This mode can be used to export and import all objects that belong to a user.
Use the owner export parameter and the fromuser import parameter for a user (owner) export-import.

Table
Specific tables (and partitions) can be exported/imported with table export mode.
Use the tables export parameter for a table export.

We are using exp in “user” mode.

eg:

exp system/$ORA_PASS file=$EXPORT_DIR/bbdata_ls.dmp log=$EXPORT_DIR/bbdata_ls.log owner='(bbadmin, bb_bb60, bb_bb60_report, bb_bb60_stats, bb_jreport, bblearn, bblearn_admin, bblearn_stats, bblearn_report)’ buffer=2048000″

Eg:

dbca -silent -createDatabase -templateName webct_linux_10g_ee.dbt -gdbname VISTA10R2 -sid VISTA10R2 -datafileJarLocation /usr/local/oracle/10gR2/assistants/dbca/templates -datafileDestination /usr/local/oracle/10gR2/oradata -responseFile NO_VALUE

To list all users in oracle

select * from all_users;
select username from dba_users

Comments

comments

One Response to Linux oracle basics

Leave a Reply

Your email address will not be published. Required fields are marked *