My name is Mark Tiger, creator of this blog. I am an Oracle Certified Professional (OCP DBA 11g).
Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly. I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.
In this blog, I will give you the details of how to carry out those tasks, that typically need a lot of research, before you can do them. I will try to present the information in an easy to understand way. My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA. The illustrations are primarily meant for Linux, since this is a good choice of platform. However they are easily adaptable for versions of UNIX/AIX and windows etc.
11g R2 - Starting up and Shutting Down
Starting up a Database
When you start a database you define the state of the database. Typically you would mount and open the database. Once the database is open and available, valid users can connect to the database; and perform DML and DDL against the database.
If your database is being manage by “Oracle Restart”, then you should start the database with SVRCTL. Otherwise SQL*Plus, RMAN or Enterprise manager should be used.
Connect to SQL*Plus with administrator privileges or as sysdba and issue the startup command. This is many times the most convenient and easiest to illustrate method for starting the database up.
You can also issue startup and shutdown commands from RMAN. This is useful if you are already in an RMAN environment or logged into RMAN. Then you don’t have to log out and log into SQL*Plus, you can just issue the commands from the RMAN> prompt.
If you are using Enterprise Manager Database Console and logged in as an administrator, then you can shutdown and startup the database from within the GUI. This is an easy way to administer the database. However for purposes of illustration I will demonstrate most scenarios from the SQL*Plus command line, so that an understanding of what is happening beneath the GUI can also be cultivated.
When Oracle restart is installed and configured, Oracle recommends that you use srvctl to start the database. The reasons for this is:
· There may be components that the database depends on, for example: ASM(Automatic storage management), Oracle Net Listener. Srvctl will ensure that these dependent components are started in the right order, before the Oracle database is started.
· The settings in the Oracle Restart configuration are followed, when the database is started, for example, the location of the spfile.
· There are environment variables stored in the Oracle Restart Configuration. They are set before trying to start the instance.
Initialization Parameters at Startup
To start the instance the database must either read the initialization parameters from a spfile or from the text format pfile. Oracle looks for these files in the default locations, when you start the database with SQL*Plus, and Oracle Restart is not in use. When the database is started with srvctl, then the location for these initialization parameter file may be defined in a non-default location.
If Oracle Restart is not configured, then Oracle attempts to read the parameter files in the default location in this way:
If for example DBCA created the spfile in an ASM disk group, then the Oracle Database will search for the spfile in the disk group.
If you want to override the spfile at startup, then you can do it like this:
SQL> startup pfile=’/u01/app/oracle/dbs/initORACLE_SID.ora’
If you are using ASM, then you have a non-default location for your spfile, because the spfile is in your ASM disk group.
Starting with a non-default spfile
You can use the pfile clause to start an instance with a non-default spfile.
· Create a pfile in the default location, that contains the SPFILE parameter.
Note that you cannot use the IFILE initialization parameter within a pfile to point to the spfile.
· Startup the instance pointing to this pfile if non-standard.
SQL> startup pfile=$ORACLE_HOME/dbs/non_standard_init.ora
The spfile must reside on the host computer. You can have a client-side pfile, that points to the server side spfile. This means that you don’t have to maintain a client side pfile, but just the pointer to the spfile inside the client side pfile.
Start the database with srvctl and a non-default spfile
If your database is being managed by Oracle Restart then, you can set the SPFILE parameter within the Oracle Restart configuration for the database.
· srvctl modify database –d db_unique_name –p spfile_path
db_unique_name must match the DB_UNIQUE_NAME parameter
spfile_path is the fully qualified path for the non-default spfile.
· srvctl start database –d db_unique_name [options]
When a database is managed by Automatic Restart, you can configure startup options for each database service. The default management policy for a service is set to AUTOMATIC. This means that the service starts when the database is started up with srvctl. If you set a service to MANUAL, then you will need to manually start this service with srvctl.
Once a service is running Oracle Restart will restart a service when it fails regardless of whether it was configured to AUTOMATIC or MANUAL. In a Data Guard environment, you can additionally assign Data Guard roles to the services in their Oracle Restart configurations.
A service starts when you manually startup the database, if the management policy of the service is AUTOMATIC, and one of its assigned roles matches the current role of the database.
If Oracle Restart is not in Use
If you are using SQL*Plus
· Make sure that the components on which the database depends have been started. For example, the Oracle NET listener. Also if you are running ASM, make sure that the ASM instance is running, and the disk groups are mounted.
· If you are using OS authentication, then log into the database host computer with a user that is a member of the OSDBA group.
· Ensure that the appropriate environment variables are set, for example ORACLE_HOME and ORACLE_SID, and that these variables map to the correct instance that you want to start.
· Start SQL*Plus without connecting to the database.
$ Sqlplus /nolog
· Connect to the database as sysdba
SQL> connect / as sysdba
SQL> connect username as sysdba
· Now finally you are ready to issue the startup command.
You can start the instance in various modes
If you are connected to the database through a shared server process, then you can’t start the database.
· NOMOUNT – This does not allow access to the database. This mode is useful for database creation or for the re-creation of the control files.
· MOUNT – Mounts the database, but leaves it closed. This state prevents general access to the database. However this state is useful for DBA maintenance activities.
· OPEN – The database is mounted and open. In RESTRICTED mode it will allow access to database administrators only. In UNRESTRICTED mode, it will allow access to all users.
· FORCE – Force the instance to start, after a startup or shutdown problem.
· OPEN RECOVER – Start the instance, and let complete media recovery begin immediately.
When you start an instance you can potentially encounter problems with the control files, database files or redo log files. If you are missing a control file, then you will not be able to mount the database. The error conditions will be recorded in the alert log.
If you are missing a datafile or redo log file, then you may be able to mount the database, but you can’t open the database. The details of the error condition will be recorded in the alert log.
To start the instance, read the initialization parameters, and then mount and open the database, so that users can access the database.
$ srvctl start database -d db_unique_name
To start the database without mounting it:
SQL> startup nomount
$ srvctl start database -d db_unique_name -o nomount
To start the instance and mount the database:
This will allow you to enable and disable archive log mode. Also you can perform full database recovery in this mode.
SQL> startup mount
$ srvctl start database -d db_unique_name -o mount
Restricting Access to an Instance at startup
You can start an instance, and mount or open it in restricted mode. In restricted mode the database is only available to administrators. Restricted mode is useful for maintenance tasks, when you want to sure that ordinary users, don’t make any changes. For example:
· Backup and recovery operations, exports and imports
· Ideal for performing a data load using SQL*Loader
· This will prevent typical users from using the data, while the database is in restricted mode
· Migration and upgrade operations
When the database is open in normal mode, then anybody with the CREATE SESSION system privilege can connect to the database.
When the database is open in restricted mode, then you need the CREATE SESSION, and RESTRICTED SESSION system privilege to connect to the database. Only database administrators, should have the RESTRICTED SESSION system privilege.
When an instance is in restricted mode, then a DBA can only access the database from the host that the instance is running on. You can’t access a database in restricted mode remotely through the Oracle NET listener.
SQL> startup restrict
$ srvctl start database -d db_unique_name -o restrict
The restricted mode can be used in combination with nomount, mount and open modes.
You use the ALTER SYSTEM to disable the RESTRICTED SESSION feature.
SQL> alter system disable restricted session;
If the database is already running you can also place it in restricted mode.
SQL> alter system enable restricted session;
Forcing an instance to start
Depending on circumstances, you may be required to force a database to start. Leave this option as a last resort. You may need to do it if you have problems like:
· You cannot shutdown the current instance with SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL
· You are experiencing problems when you try to start the instance
SQL> startup force;
$ srvctl startup database -d db_unique_name –o force
The FORCE keyword, causes the database to do a shutdown abort, followed by a startup normal. The details of this are carried in the alert file.
Open a database and start complete media recovery
SQL> startup open recover;
$ srvctl start database -d db_uniqe_name -o “open,recover”
If you issue this command and there is no recovery required, then you will get an error message.
Automatic Database start at Operating system Start
Most sites would want their database to startup, immediately after a system or host restart. The ways to accomplish this depend on the operating system.
From Oracle 11gR2 and onwards, the preferred way to configure the automatic restart of the database is with “Oracle Restart”
Mounting a database to an instance
For administrative operations you will want the database open and mounted, but closed to database users.
You can achieve this by starting the database in mount mode or by altering the mode from nomount to mount mode.
SQL> startup mount
SQL> alter database mount;
Opening a closed or mounted database
You can render a mounted database as an open database by running this command.
SQL> alter database open;
After running this command, any valid user with the CREATE SESSION system privilege, will be able to connect to the database.
Open a database in read only mode
You can use read only mode, so that you can query the database, without making any changes to the database. The datafiles and redo logs are not written to in this mode.
Read only mode does not restrict operations, that do not generate redo. For example recovery operations may work. You can take datafles offline and bring them online again.
Queries that run against a read-only database will use the temporary tablespace for sort segments. The temporary tablespace must be a locally managed tablespace, otherwise the query will fail.
SQL> alter database open read only;
The default mode is:
SQL> alter database open read write;
You can’t use the RESETLOGS clause when you open the database in read only mode.
There are limitations to what you can do with a read only database.
· You can’t do inserts, updates, deletes, or merges in tables. You can’t manipulate sequences, lock rows, or run an explain plan. You can’t execute any DDL. Most oracle supplied PL/SQL packages will write to the database objects, and so you can’t use them.
· You must commit or rollback all in-progress transactions, that involve a DB Link, before yo attempt to reference another DB link. This includes generic selects on a database link from a database in READ ONLY mode.
· You cannot compile / recompile any stored procedures.
Use REMOTE_DEPENDENCIES_MODE=SIGNATURE, in sessions that do remote procedure calls on a READ ONLY database.
· You can’t invoke a remote procedure call from a READ ONLY database, if the procedure has never been called from the read only database before. To work around this you can call the procedure, before putting the database in read only mode. You can also put the remote procedure call in a stored procedure, before you invoke it.
Restricting access to an open database
SQL> alter system enable restricted session;
When the statement successfully completes, then users with the RESTRICTED SESSION system privilege can initiate new sessions. For example users with the DBA role, or users connecting AS SYSDBA.
Altering a running instance to restricted mode, has a few considerations to keep in mind.
· In a “Single Instance” configuration, where “Oracle Restart” has not been configured. None of the current user sessions are killed or terminated. It may be a good idea to manually kill the user session, before starting with the maintenance tasks.
· In a “Single Instance” configuration, where “Oracle Restart” has been configured. Database services that are managed by Oracle Restart are terminated. The standard Database service is not managed by Oracle Restart: DB_UNIQUE_NAME.DB_DOMAIN, therefore it does not go offline.
· In a RAC environment, all the database services that are managed by Oracle CLusterware will go offline, for the specific instance involved. Any sessions connected to that instance, and the services that went offline will be terminated. The standard database service for the instance does not go offline: DB_UNIQUE_NAME.DB_DOMAIN
End the maintenance session with:
SQL> alter system disable restricted session;
Shutting down a database
Should be connected as sysdba
The control will not return to the SQL*Plus session again, until the shutdown is complete. If any users attempt to connect to the database while it is busy shutting down, then they will get an error:
ORA-01090: shutdown in progress
If your database is managed by Oracle Restart, then you should shut the database down using srvctl
$ srvctl stop database
The normal mode is the default when a SQL> SHUTDOWN is issued.
With “$ srvctl stop database”, the –o immediate is the default.
· No new connections are allowed, after the statement is issued
· Before the database is shut down, the database will wait for all currently connected users to disconnect from the database.
· When you do a startup after a shutdown normal, then no instance recovery procedures are required.
SQL> shutdown [normal]
$ srvctl stop database -d db_unique_name -o normal
Use this mode in the following situations:
· To start an automated or unattended backup
· When a power shutdown/test is going to occur soon
· When the database needs maintenance, and you can’t contact all of the users to tell them to log-off, or they are unable to logoff
The immediate mode has these considerations:
· No new connections or statements are allowed, after the statement is issued
· All uncommitted transactions are rolled back. Long uncommitted transactions, may take a while to roll back
· The database implicitly rolls back active transactions and disconnects all connected users
· When you restart the database, you do not need any instance recovery procedures
SQL> shutdown immediate
$ srvctl stop database -d db_unique_name -o immedate
This is for a planned shutdown of the instance , this mode allows active transaction to complete
The transactional has some considerations:
· No new connections or transactions are allowed after the statement is issued
· After all the transactions have finished, any clients still connected to the instance are disconnected
· Then the database shuts down in the same way as it would for a shutdown immediate
· A restart will not require any instance recovery procedures
· The transactional shutdown, prevents users from losing work, and does not require users to log off, until all the transactions have been completed
SQL> shutdown transactional
$ srvctl stop database -d db_unique_name -o transactional
This mode will take the database down instantly. Using this mode should be only as a last resort:
· The database needs maintenance, and none of the other types of shutdown are working
· A power cut is about to happen in less than a minute, so there is no time left
· You are having problems when trying to start the instance
The abort mode has some considerations:
· No new connections or statements are allowed after the statement is issued
· Current client SQL is immediately terminated
· Uncommitted transactions are not rolled back
· The database implicitly disconnects all users
· When you restart the database will require “Automatic Instance Recovery” procedures. This means that it may take a while to startup
SQL> shutdown abort
$ srvctl stop database –d db_unique_name –o abort
The shutdown aborts after an hour, f it is still waiting for users to disconnect or transactions to complete, or rollbacks to complete.
You will receive this error at your command prompt:
ORA-01013: user requested cancel of current operation
If you cancel the shutdown by pressing CTRL-C, you will also get the ORA-01013 error. Oracle recommends that you do not cancel, the shutdown process. It is better to wait for the instance to shutdown, and then to restart the instance.
After receiving an ORA-01013, you must consider the instance to be in an unpredictable state. You should there continue with the shutdown by issuing the shutdown commands again. If you still can’t shut the database down, then you will need to issue the “SQL> shutdown abort” statement. You should then be able to restart the instance.
Quiescing a database
For maintenance purposes you may require a database to be quiesed, so that the only connections allowed are as sys or system. Even users with the DBA role will be excluded.
This mode is useful for:
Transactions that would otherwise fail if user transactions access the same object or set of objects. Things like changing the schema of a database table, or adding a column to an existing table where a no-wait lock is required.
Multistep procedures, that could confuse application users, if they were still logged on. For example exporting dropping an re-creating a table.
Quiescing the database will have a similar effect to shutting the database down, and restarting it in restricted mode. However on a high availability system, you may not be able to shut the database down. Quiescing would have a smaller impact then the shutdown and restart.
To place a database into quiesced mode:
SQL> alter system quiesce restricted;
Non-DBA active session will continue until they become inactive. An active session is a session that is inside a transaction or a session that is currently holding shared resources. No inactive sessions are allowed to become active. When a connected user issues a query, the query will seem to hang until the database is taken out of quiesced state, and the query will then continue.
Once all of the Non-DBA active sessions have become inactive, then the “SQL> alster system quiesce restricted;” completes, and the database is then in a quiesced state. In a RAC environment, this statement will affect all the instances, and not just the instance where the statement was issued from.
While waiting for this statement to process you can query from another session SID(session ID) from v$blocking_quiesce view. You can join this view with v$session to get more information about the session.
SQL> select bq.sid, ses.user, ses.osuser, ses.type, ses.program
From v$blocking_quiesce bq,
Where bq.sid = ses.sid;
If your session terminates, or you cancel the request to quiesce the database, then the Oracle Database automatically reverses any partial effects of the statement.
For queries that are carried out by successive OCI(Oracle Call Interface) fetches, the Oracle database only waits for the first fetch to complete.
The mechanism works the same for dedicated and shared server connections. The user trying to login after the quiesce statement has been issued, will experience it as though the login as hung. The login will continue again when the database is unquiesced.
The database will remain in this state, regardless of whether the session that issued the statement is still active or not. In order to take the database out of this mode a DBA(sys,system) will need to log in and unquiesce the database. In a RAC environment, the unquiesce statement can even be issued from a different instance.
SQL> alter system unquiesce;
A quiesced database is not in the correct state for a cold backup, because the file headers are still being updated. However an online backup in this state is possible.
To view the QUIESCE state of an instance, you can:
SQL> select active_state from v$instance;
The possible values returned are:
· NORMAL - Normal unquiesced state
· QUIESCING - Being quieced, but some non-DBA sessions are still active
· QUESCED - Quiesced, non non-DBA sessions are active or allowed
Suspending and Resuming a Database
“ALTER SYSTEM SUSPEND” will halt all I/O to datafiles(file header, and file data), and control files.
The suspended state lets you backup a database without any I/O interference. When the database is suspended, then all current or pre-existing I/O operations are allowed to complete. Any new database accesses are placed in a queue.
In a RAC environment, the suspend is propagated from the one instance, until all the active instances are quiesced in a given cluster. However if a new instance is started, while another instance is suspended, then the newly started instance is not suspended.
When you issue the RESUME statement in a RAC environment, you can issue it from any of the instances, and the resume will propagate to all the instances.
The SUSPEND / RESUME is useful in systems where you want to make a split mirror image of your disks. You may have a system, that can’t take a split while updates are being written to the mirror disks. So you can SUSPEND the database and split the mirror. When this operations is complete, then you can simply RESUME the database again.
Don’t substitute the SUSPEND / RESUME functionality for shutdown operations, because copies of a suspended database can contain uncommitted updates. Do not use SUSPEND in place of having a tablespace in backup mode. Rather issue SQL> alter tablespace begin backup; and then the SUSPEND command.
SQL> alter system suspend;
SQL> select database_status from v$instance;
SQL> alter system resume;
SQL> select database_status from v$instance;
Need a database health check, remote monitoring, support & maintenance, or a security Audit?
P.S. I am busy preparing Nine books on preparing for the Oracle 11g R2 OCM DBA exam. Watch this spot for details on the books, as they start becoming available.