Oracle cheat-sheet (1-day DBA) source: http://ss64.com/ora/
Feel free to look up this PDF. It’s a very good book for beginner DBAs.
LISTENER
lsnrctl status
lsnrctl start
tnsping [instance_name]
STARTUP
Startup a database instance. This is a SQL*Plus command (not part of standard SQL) Syntax:
STARTUP [FORCE] [RESTRICT] [PFILE=filename] NOMOUNT
STARTUP [FORCE] [RESTRICT] [PFILE=filename] MOUNT [dbname]
STARTUP [FORCE] [RESTRICT] [PFILE=filename] OPEN [Open_options] [dbname]
Open_options:
READ {ONLY | WRITE [RECOVER]} | RECOVER
Key:
FORCE
Shut down the current Oracle instance (if it is running) with
SHUTDOWN mode ABORT, before restarting it.
If the current instance is running and FORCE is not specified,
an error results. FORCE is useful while debugging and under
abnormal circumstances. It should not normally be used.
RESTRICT
Only allow Oracle users with the RESTRICTED SESSION system privilege
privilege to connect to the database. Later, you can use the
ALTER SYSTEM command to disable the restricted session feature.
PFILE=filename
The init.ora parameter file to be used while starting up the instance.
If PFILE is not specified, then the default STARTUP parameter file is
used. The default file used is platform specific.
For example, the default file is $ORACLE_HOME/dbs/init$ORACLE_SID.ora
on UNIX, and %ORACLE_HOME%\database\initORCL.ora on Windows.
MOUNT dbname
Mount a database but do not open it.
dbname is the name of the database to mount or open.
If no database name is specified, the database name is taken
from the initialization parameter DB_NAME.
OPEN
Mount and open the specified database.
NOMOUNT
Don't mount the database upon instance startup.
Cannot be used with MOUNT, or OPEN.
RECOVER
Specifies that media recovery should be performed, if necessary,
before starting the instance.
STARTUP RECOVER has the same effect as issuing the RECOVER DATABASE
command and starting an instance.
Only 'complete recovery' is possible with the RECOVER option.
Recovery proceeds, if necessary, as if AUTORECOVERY is set to ON,
regardless of whether or not AUTORECOVERY is enabled.
If a redo log file is not found in the expected location,
recovery will continue by prompting you with the suggested location
and name of the subsequent log files that need to be applied.
Startup NOMOUNT will run the background processes for the instance.
Startup MOUNT will open the Control Files.
Startup OPEN will open the Data Files.
On Windows, starting the Oracle services will also startup the instance.
Examples
Startup with a pfile or SPfile
When starting an Oracle instance with the simple command ‘startup’, Oracle will first look for a binary SPfile in the default location; if it can’t find one, it will look for a pfile(init.ora) in the default location.
If you start the oracle instance and specify pfile=xxx then Oracle will use the ‘old-fashioned’ pfile as you requested.
Startup pfile=”C:\oracle\admin\live\pfile\init.ora”
Strangely there is no option that will start an oracle instance with a binary spfile residing in a non-default location.
As a workaround for this, create a traditional init.ora containing one line:
SPfile=E:\pathtoyourSPfile\SPfileLive.ora
Then startup the instance calling the pfile which in turn will reference the SPfile that you want.
For this to work – make sure you don’t also have an SPfile in the default location.
ALTER DATABASE
Open an existing database, and /or modify associated files.
Syntax:
ALTER DATABASE database_name options
Options:
open/mount options: MOUNT MOUNT STANDBY DATABASE MOUNT CLONE DATABASE MOUNT PARALLEL MOUNT STANDBY DATABASE CONVERT OPEN [READ ONLY] OPEN [READ WRITE] RESETLOGS|NORESETLOGS [MIGRATE] ACTIVATE STANDBY DATABASE [NATIONAL] CHARACTER SET char_set archivelog options: ARCHIVELOG NOARCHIVELOG backup and recovery options: BACKUP CONTROLFILE TO 'filename' [REUSE] BACKUP CONTROLFILE TO TRACE [RESETLOGS] [AS 'filename' [REUSE]] CREATE STANDBY CONTROLFILE AS 'filename' [REUSE] RECOVER recover_clause RECOVER MANAGED STANDBY standby_recover_clause END BACKUP Datafile options: CREATE DATAFILE 'filename' AS filespec DATAFILE 'filename' ONLINE DATAFILE 'filename' OFFLINE [DROP] DATAFILE 'filename' RESIZE int K | M DATAFILE 'filename' AUTOEXTEND OFF DATAFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M | UNLIMITED] DATAFILE 'filename' END BACKUP RENAME FILE 'data_file_name' TO 'data_file_name' TEMPFILE 'filename' ONLINE TEMPFILE 'filename' OFFLINE TEMPFILE 'filename' DROP [INCLUDING DATAFILES] TEMPFILE 'filename' RESIZE int K | M TEMPFILE 'filename' AUTOEXTEND OFF TEMPFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M | UNLIMITED] redo log options: ADD LOGFILE [THREAD int] [GROUP int] filespec ADD LOGFILE MEMBER 'filename' [REUSE] TO GROUP int ADD LOGFILE MEMBER 'filename' [REUSE] TO 'filename' DROP LOGFILE GROUP int DROP LOGFILE ('filename') DROP LOGFILE MEMBER 'filename' RENAME FILE 'redolog_file_name' TO 'redolog_file_name' CLEAR [UNARCHIVED] LOGFILE GROUP int [UNRECOVERABLE DATAFILE] CLEAR [UNARCHIVED] LOGFILE ('filename') [UNRECOVERABLE DATAFILE] Parallel server options: CREATE STANDBY CONTROLFILE AS 'filename' [REUSE] SET DBLOW = 'text' SET DBHIGH = 'text' SET DBMAC = ON | OFF ENABLE [PUBLIC] THREAD int DISABLE THREAD int Backwards compatibility options: RENAME GLOBAL_NAME TO database [domain] RESET COMPATIBILITY
Any option above that includes a ‘filename’ can be extended to cover multiple files using the syntax: (‘filename1′,’filename2’)
‘filename’ [offline] DROP will only work on a tablespace consisting of a single datafile, for a tablespace comprising multiple datafiles you can only drop a datafile by dropping the entire tablespace.
Some of the commands above can only be used when the database is in a particular state:
MOUNT, CONVERT – Require that the db is Not Mounted.
ARCHIVELOG, NOARCHIVLOG, RECOVER – Require that the db is Mounted but not open (must be mount exclusive – not mount parallel).
ENABLE, DISABLE, RENAME GLOBAL_NAME, RESET, SET – Require that the db is Open.
All other options will work with the db mounted, open or closed as long as none of the files involved are ‘in use’
database_name is defined when the database is created – it is normally set to the same as the database SID.
Examples
Resize datafile:
SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' RESIZE 500m;
Take datafile offline:
SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' OFFLINE;
Database altered.
Take datafile online, when doing this, file recovery is needed to update the timestamp in the offline datafile header.:
SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE; ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE * ERROR at line 1: ORA-01113: file 16 needs media recovery ORA-01110: data file 16: 'F:\ORADATA\LIVE\Mydb02.ORA'
SQL> RECOVER DATAFILE 'F:\oradata\live\Mydb02.ora'; Media recovery complete.
SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE;
Database altered
Change the database character set to UTF8, this must be done with no users connected (restricted session)
SQL> SHUTDOWN IMMEDIATE; -- or NORMAL
<do a full backup>
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET UTF8;
SQL> SHUTDOWN IMMEDIATE; -- or NORMAL
SQL> STARTUP;
SHUTDOWN
Shutdown a database instance. This is a SQL*Plus command (not part of standard SQL) Syntax:SHUTDOWN ABORT SHUTDOWN IMMEDIATE SHUTDOWN TRANSACTIONAL [LOCAL] SHUTDOWN NORMAL key: ABORT The fastest possible shutdown of the database without waiting for calls to complete or users to disconnect. Uncommitted transactions are not rolled back. Client SQL statements currently being processed are terminated. All users currently connected to the database are implicitly disconnected and the next database startup will require instance recovery. You must use this option if a background process terminates abnormally. IMMEDIATE Does not wait for current calls to complete or users to disconnect from the database. Further connects are prohibited. The database is closed and dismounted. The instance is shutdown and no instance recovery is required on the next database startup. NORMAL NORMAL is the default option which waits for users to disconnect from the database. Further connects are prohibited. The database is closed and dismounted. The instance is shutdown and no instance recovery is required on the next database startup. TRANSACTIONAL [LOCAL] A planned shutdown of an instance, allowing active transactions to complete first. It prevents clients from losing work without requiring all users to log off. No client can start a new transaction on this instance. Attempting to start a new transaction results in disconnection. After completion of all transactions, any client still connected to the instance is disconnected. Now the instance shuts down (SHUTDOWN IMMEDIATE). The next startup of the database will not require any instance recovery procedures. The LOCAL mode specifies a transactional shutdown on the local instance only, so that it only waits on local transactions to complete, not all transactions. This is useful, for example, for scheduled outage maintenance.
You must log in to post a comment.