Oracle Database administration essentials
- Oracle Database administration essentials
- To startup the database
- To shutdown the database
- Starting the Enterprise Manager dbconsole Process
- Shutdown of other Oracle Background Processes
- To stop iSQL*Plus
- Database Management Processes
- Oracle Net Listener
- Cluster Synchronization Services (CSS)
- Tips and Hints for Oracle Database on Linux
- Oracle Enterprise Management Console
- sqlplus to connect to a remote host
- Check Instance (SID) parameters
- Change instance parameter
- Show parameters, data files, log files and control files
- Show DBA users
- Front-end GUI Tool
- Troubleshooting
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
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:
Log on to the operating system as the oracle user. Issue the following command to start the dbconsole process:
emctl start dbconsole
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:
Generate latest pfile with
create pfile from spfile;
Shutdown the instance
shutdown immediate;
- Modify the pfile (for example: processes, open_cursors)
Startup the instance with pfile
startup pfile=/path/filename.ora
For example: pfile=$ORACLE_HOME/dbs/initorcl01.ora
Create spfile from the new pfile
create spfile from pfile
- Shutdown instance
- 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
Troubleshooting
1. When starting up the database instance, the following error occurs
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
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!