Terry : Oracle Database

Oracle Database administration essentials

Startup, shutdown the services, database, listener etc. Covering 10gR2, 11gR1 and 11gR2.

To startup the database

oracle$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup

startup mount;

Icon

Mount a database BUT DO NOT open it.
To open it

alter database open;

Equivalent to

sqlplus "/ as sysdba"

Or

$ORACLE_HOME/bin/dbstart

To shutdown the database

Graceful shutdown

oracle$ sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown

Force shutdown (immediately)

SQL> shutdown immediate

Equivalent to stop the SID service in Windows.

SQL> shutdown abort

Force shutdown

The slash connects you to the schema owned by SYS. In the above example you will be connected to the schema owned by SYS with the privilege SYSDBA. SYSDBA gives you the following privileges:

  • sysoper privileges WITH ADMIN OPTION
  • create database
  • recover database until

Or

$ORACLE_HOME/bin/dbshut

Starting the Enterprise Manager dbconsole Process

The dbconsole process must be started for you to access Oracle Enterprise Manager Database Control from a client browser. The dbconsole process is automatically started after installation.

If the process is not started, you can manually start it at the command line as follows:

  1. Log on to the operating system as the oracle user. Issue the following command to start the dbconsole process:

    emctl start dbconsole
  2. You can check the status of the dbconsole process by issuing the following command:

    emctl status dbconsole

Shutdown of other Oracle Background Processes

If you installed a preconfigured database using OUI, then several Oracle background processes are now running on your server. Execute the following command to see the background processes:

ps -ef

Or

ps -fael

To shutdown the Oracle background processes after an Oracle Database 10g installation, you can execute the following commands: iSQL*Plus

To stop iSQL*Plus

su - oracle
isqlplusctl stop

Database Management Processes

During the installation of Oracle 10g, OUI offered two Database Management Options:

If you selected "Database Control for Database Management", then the Oracle Enterprise Manager Database Control (Database Control) can be shutdown with the following command which stops both the agent and the Oracle Containers for Java (OC4J) management service:

su - oracle
emctl stop dbconsole

If you selected "Grid Control for Database Management" which is used for full "Grid Control" installations, then the Oracle Management Agent (standalone agent) for the Oracle Enterprise Manager Grid Control (Grid Control) can be stopped with the following command:

su - oracle
emctl stop agent

Oracle Net Listener

To start and stop the listener (under $ORACLE_HOME/bin), run:

su - oracle
lsnrctl start
lsnrctl status
lsnrctl stop

Listener definition

$ORACLE_HOME/network/admin/listen.ora

Cluster Synchronization Services (CSS)

To shutdown Oracle CSS daemon, run:

su - root
/etc/rc.d/init.d/init.cssd stop

Tips and Hints for Oracle Database on Linux

Check Oracle Database version

sqlplus -V

Set environment variables

oraenv

Example

oracle@fmw11g.vm.oracle.com $ oraenv
ORACLE_SID = [orcl01] ?
The Oracle base for ORACLE_HOME=/refresh/64bit/app/oracle/product/11.2.0/dbhome is /refresh/64bit/app/oracle

setenv.sh script sample and load it at login by sourcing it in ~/.bash_profile, ~/.profile (interactive login shell) or ~/.bashrc (interactive non-login shell, e.g. gnome-termnial)

export ORACLE_BASE=/refresh/64bit/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome
export ORACLE_SID=ORCL
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export PATH=$PATH:$ORACLE_HOME/bin;

Create Database via Database Configuration Assistant (dbca)

Run GUI

dbca

Run the following command to gain access to the X Server, otherwise connection refused may be encountered

xhost +
su oracle
xclock
dbca

Use SSH X11 Forwarding

# X11 Forwarding
ssh -X user@dbhost
# Trusted X11 Forwarding
ssh -Y user@dbhost

And run dbca on the SSH Client machine.

NOTE: The ssh client machine must have X Server.

To reinstall Oracle after a failed installation attempt, you might want to execute the following commands.

Make sure you first used the De-installation option in OUI.

su - root
export ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1
. $ORACLE_HOME/bin/localconfig delete; # stops the Oracle CSS daemon and deletes configuration
rm -rf /u01/app/oracle/*
rm -f /etc/oraInst.loc /etc/oratab
rm -rf /etc/oracle
rm -f /etc/inittab.cssd

rm -f /usr/local/bin/coraenv /usr/local/bin/dbhome /usr/local/bin/oraenv

Also make sure to unset and uncomment ORACLE_HOME from ~/.bash_profile.

Start Database at startup

Edit /etc/oratab

ORCL:/oracle/database/product/11.2.0/dbhome_1:Y

Oracle Net Configuration Assistant

netca

Oracle Client

Oracle Enterprise Management Console

oemapp console

sqlplus to connect to a remote host

tnsping host/service name

define service in file tnsnames.ora

sqlplus username/password@host or service

For example:

sqlplus analyticsdbuser/analyticsdbuser@xe

Check Instance (SID) parameters

show parameter;
show parameter processes;
show parameter open_cursors;

or

select * from v$parameter;

Change instance parameter

alter system set processes=600 scope=spfile;

Better choice, using spfile.
pfile (pure text file)
spfile (text + binary) CANNOT be edited directly because it has a header and footer that contains binary values

Steps to modify sid parameters:

  1. Generate latest pfile with

    create pfile from spfile;
    Icon

    This will create a PFILE named initSID.ora in $ORACLE_HOME/dbs (Linux/Unix) and %ORACLE_HOME%\database (Windows)

  2. Shutdown the instance

    shutdown immediate;
  3. Modify the pfile (for example: processes, open_cursors)
  4. Startup the instance with pfile

    startup pfile=/path/filename.ora

    For example: pfile=$ORACLE_HOME/dbs/initorcl01.ora

  5. Create spfile from the new pfile

    create spfile from pfile
    Icon

    If your database is currently running using the SPFILE, be sure to shut down first so Oracle can replace the file. As your SPFILE is in use the entire time the database is running, you should never overwrite it during normal operations.

  6. Shutdown instance
  7. Startup instance again with no parameter (using the spfile by default)

Show parameters, data files, log files and control files

select * from v$parameter;
select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

Show DBA users

select username from dba_users

Front-end GUI Tool

Oracle SQL Developer

Oracle SQL Developer

Troubleshooting

1. When starting up the database instance, the following error occurs

Icon

ORA-00845: MEMORY_TARGET not supported on this system

Cause

The new Automatic Memory Management functionality uses /dev/shm on Linux for SGA and PGA management. The errors occur if either MEMORY_TARGET or MEMORY_MAX_TARGET is configured larger than the configured /dev/shm size, or if /dev/shm is mounted incorrectly.

Solution

Please confirm that ORACLE_HOME is set correctly. This error sometimes happens when it is not set correctly.

Make sure that the /dev/shm size is configured large enough, like in

mount -t tmpfs -o size=7g shmfs /dev/shm 

In this case, the size of the shared memory device is configured to be 7GB.

In order to make the same change persistent across system reboots, add an entry for this to the /etc/fstab mount table, as in

shmfs /dev/shm tmpfs size=7g 0

NOTE: you should check with your System Administrator what the "best" size for /dev/shm is based on what has been reported in the alert file. Also, importantly, many best practices now suggest disabling AMM especially in ExaLogic/Exadata Engineered boxes that have larger memory capability and can use Huge / Large pages: as these are mutually exclusive and overall performance will be better using Huge pages.

Make sure that the df -hT output shows the correct /dev/shm configuration when using Oracle on the system.

2. ORA-03113

Database disk full - ORA-03113 Error when trying to start up the database

Icon

ORA-03113: end-of-line on communication channel

Cause

Disk full, DB archiver failed.

Figure out the user_dump_dest and db_recovery_file_dest and size

SQL> show parameter user_dump_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /refresh/64bit/app/oracle/diag
                                                 /rdbms/orcl01/orcl01/trace
SQL> show parameter db_recovery;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /refresh/64bit/app/oracle/fast
                                                 _recovery_area
db_recovery_file_dest_size           big integer 60G

Find alert log location

select * from V$DIAG_INFO

Solution

Use ack or ag (faster) to scan the alert logs and found db_recovery_file_dest_size all used.

ORA-19815: WARNING: db_recovery_file_dest_size of 4322230272 bytes is 100.00% used, and has 0 remaining bytes available.

Analyze the user dumps. Find that the db_recovery_file_dest is 100% full.

Set a larger handle for db_recovery_file_dest

alter system set db_recovery_file_dest_size=60g;

Shutdown database and do a clean startup. Fixed!

Attachments:

sapatel_database.zip (application/x-zip-compressed)