Cancel Oracle shutdown
You might want to cancel a shutdown if you issued the wrong command when there were connected users.
If you try to shutdown immediate from another session, you’ll get this:
SQL> shutdown IMMEDIATE ORA-24324: service handle NOT initialized ORA-24323: VALUE NOT allowed ORA-01089: IMMEDIATE shutdown IN progress - no operations are permitted
When you issue a shutdown, only the SYS/SYSTEM user will be able to connect, so open another SQL*Plus session and type this:
CONNECT /AS sysdba startup force
Backing up an Oracle database without RMAN
If you aren’t using RMAN for your backups (and are using something like Netapp’s snapshots, or the simple copy-command) (in Oracle 9i this can only be done when the Database is in mounted-mode, not opened), you can do this:
Note: Archivelog must be enabled for this to work.
ALTER DATABASE BEGIN BACKUP;
At this point, you can begin copying/taking snapshots. When you’re done:
ALTER DATABASE END BACKUP;
In older databases, you’ll have to use (run for every tablespace):
ALTER TABLESPACE tablespace_name BEGIN BACKUP; ALTER TABLESPACE tablespace_name END BACKUP;
Enabling Trace
The TRACE_ENABLED parameter allows you to trace the execution history of Oracle. It should be set to TRUE by default.
The information gathered is stored in the following locations:
#User: /usr/oracle/admin/sid/udump #Background: /usr/oracle/admin/sid/bdump #Core: /usr/oracle/admin/sid/cdump
These locations could be different depending on what’s set in your PFILE.
Check if trace is enabled:
SHOW parameter trace_enabled;
Sample output:
SQL> show parameter trace_enabled; NAME TYPE VALUE ------------------------- -------- -------------- trace_enabled BOOLEAN TRUE
And if it isn’t enabled, enable it using:
ALTER SYSTEM SET trace_enabled = TRUE;
Note: Prior to version Oracle 9i, this parameter was: _trace_enabled
Starting, stopping, and Status of Oracle Enterprise Manager (EM)
To start the Oracle Enterprise Manager Database Control (from the command-line):
emctl start dbconsole
To get the status:
emctl status dbconsole
To stop the dbconsole
emctl stop dbconsole
To access the EM (with default settings):
http://hostname:5500/em
Update statistics on all objects in a database
This helps Oracle choose the best execution plan for queries:
BEGIN dbms_stats.gather_database_stats(options=> 'GATHER AUTO'); END; /
Turning Archive-log mode on and off
You can check what mode the database is in with:
1 | SELECT LOG_MODE FROM SYS.V$DATABASE; |
If you do not specify an archive-log location, it will end up going to a directory like $ORACLE_HOME/dbs - which can be quite a mess.
Check where it's going:
1 | SHOW PARAMETER log_archive_dest; |
It would be good practice to set the location if it doesn’t show one.
To turn archive-log-mode on or off:
shutdown database:
1 2 3 | SHUTDOWN IMMEDIATE
STARTUP RESTRICT
SHUTDOWN |
Mount the database:
1 | ALTER DATABASE MOUNT; |
Then issue one of the following:
1 2 | ALTER DATABASE NOARCHIVELOG; ALTER DATABASE ARCHIVELOG; |
If the location hasn’t been set yet:
1 | ALTER SYSTEM SET LOG_ARCHIVE_DEST = "/ARC_LOCATION"; |
Then open the database:
1 | ALTER DATABASE OPEN; |
There might also be archivelog related entries in your PFILE, which you should also make changes to.
Listing key Oracle Database files
To get a list of all datafiles, redo-log files and control-files, run the following in SQL*Plus (database must be mounted):
1 2 3 | SELECT MEMBER FROM V$LOGFILE; SELECT NAME FROM V$DATAFILE; SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files'; |
An easier way of displaying your control-files is:
1 | SHOW PARAMETER control_files; |