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
Startup, shutdown the services, database, listener etc. Covering 10gR2, 11gR1 and 11gR2.
To startup the database
To shutdown the database
Force shutdown (immediately)
Equivalent to stop the SID service in Windows.
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
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:
You can check the status of the dbconsole process by issuing the following command:
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:
To shutdown the Oracle background processes after an Oracle Database 10g installation, you can execute the following commands: iSQL*Plus
To stop iSQL*Plus
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:
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:
Oracle Net Listener
To start and stop the listener (under $ORACLE_HOME/bin), run:
Cluster Synchronization Services (CSS)
To shutdown Oracle CSS daemon, run:
Tips and Hints for Oracle Database on Linux
Check Oracle Database version
Set environment variables
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)
Create Database via Database Configuration Assistant (dbca)
Run the following command to gain access to the X Server, otherwise connection refused may be encountered
Use SSH X11 Forwarding
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.
Also make sure to unset and uncomment ORACLE_HOME from ~/.bash_profile.
Start Database at startup
Oracle Net Configuration Assistant
Oracle Enterprise Management Console
sqlplus to connect to a remote host
define service in file tnsnames.ora
Check Instance (SID) parameters
Change instance parameter
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
Shutdown the instance
- Modify the pfile (for example: processes, open_cursors)
Startup the instance with pfile
For example: pfile=$ORACLE_HOME/dbs/initorcl01.ora
Create spfile from the new pfile
- Shutdown instance
- Startup instance again with no parameter (using the spfile by default)
Show parameters, data files, log files and control files
Show DBA users
Front-end GUI Tool
Oracle SQL Developer
1. When starting up the database instance, the following error occurs
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.
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
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
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.
Database disk full - ORA-03113 Error when trying to start up the database
Disk full, DB archiver failed.
Figure out the user_dump_dest and db_recovery_file_dest and size
Find alert log location
Use ack or ag (faster) to scan the alert logs and found db_recovery_file_dest_size all used.
Analyze the user dumps. Find that the db_recovery_file_dest is 100% full.
Set a larger handle for db_recovery_file_dest
Shutdown database and do a clean startup. Fixed!