Create the database

Note: Practice performed on 11g Rel 1 (APPS DB)

Considerations Before Creating the Database
Database Planning Tasks
- Plan the database tables and indexes and estimate the amount of space they will require.
- Plan the layout of the underlying operating system files your database will comprise. To greatly simplify this planning task, consider using Oracle
Managed Files and Automatic Storage Management to create and manage the operating system files that comprise your database storage.

- Select the global database name, which is the name and location of the database within the network structure. Create the global database name by setting both the DB_NAME and DB_DOMAIN
initialization parameters.
- Familiarize yourself with the initialization parameters contained in the initialization parameter file.
- Select the database character set.
- Consider what time zones your database must support. Oracle Database uses one of two time zone files as the source of valid time zones. The default time zone file is timezlrg_11.dat. It
contains more time zones than the smaller time zone file, timezone_11.dat
- Select the standard database block size. The SYSTEM tablespace and most other tablespaces use the standard block size. Additionally, you can specify up to four nonstandard block sizes when creating tablespaces.
- Determine the appropriate initial sizing for the SYSAUX tablespace.
- Plan to use a default tablespace for non-SYSTEM users to prevent inadvertently saving database objects in the SYSTEM tablespace.
- Plan to use an undo tablespace to manage your undo data.
- Develop a backup and recovery strategy to protect the database from failure. It

Character Set
Oracle recommends AL32UTF8 as the database character set which supports most of the currently spoken languages of the world.

Creating a Database with the CREATE DATABASE Statement
If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the
data dictionary tables and installing standard PL/SQL packages. You perform these actions by running the supplied scripts.
Step 1: Specify an Instance Identifier (SID)
Decide on a unique Oracle system identifier (SID) for your instance, open a command window, and set the ORACLE_SID environment variable.
■ Bourne, Bash, or Korn shell:
ORACLE_SID=mynewdb
export ORACLE_SID
■ C shell:
setenv ORACLE_SID mynewdb
for the Windows operating system:
set ORACLE_SID=mynewdb

C:\oracle\PROD\OCM>dir
 Volume in drive C has no label.
 Volume Serial Number is 8C5C-16E4

 Directory of C:\oracle\PROD\OCM

10/11/2011  01:30 PM   






          .
10/11/2011  01:30 PM              ..
               0 File(s)              0 bytes
               2 Dir(s)  60,492,808,192 bytes free

C:\oracle\PROD\OCM>set ORACLE_SID=OCM1

Step 2: Ensure That the Required Environment Variables Are Set
Depending on your platform, you may have to set environment variables, on most platforms, ORACLE_SID and ORACLE_HOME must be set.
It is advisable to set the PATH variable to include the ORACLE_HOME/bin directory
Step 3: Choose a Database Administrator Authentication Method
■ With a password file
■ With operating system authentication
To be authenticated with a password file, create the password file.  The file name required for the password file is operating system specific.

 CC:\oracle\PROD\OCM>orapwd FILE=PWDOCM1 ENTRIES=30
Enter password for SYS:
C:\oracle\PROD\OCM>
The contents of this file are encrypted, and the file cannot be read directly.
This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.
Sharing and Disabling the Password File
initialization parameter REMOTE_LOGIN_PASSWORDFILE to control whether a password file is shared among multiple Oracle Database instances.
The values recognized for
REMOTE_LOGIN_PASSWORDFILE are:
■ NONE: Setting this parameter to NONE causes Oracle Database to behave as if the
password file does not exist. That is, no privileged connections are allowed over
nonsecure connections.
■ EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.
■ SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (Oracle RAC) database. A SHARED password file cannot be modified. Therefore, you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an
error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.

Note: You cannot change the password for SYS if REMOTE_LOGIN_ PASSWORDFILE is set to SHARED. An error message is issued if you attempt to do so.

Step 4: Create the Initialization Parameter File
If you create the initialization parameter file manually, ensure that it contains at least the parameters below.
DB_NAME
CONTROL_FILES
MEMORY_TARGET (Sets the total amount of memory used by the instance and enables automatic memory management.)
Example: initocm1.ora

DB_NAME=OCM1
CONTROL_FILES=(C:\oracle\PROD\OCM\oradata\ocm1ctl1.ora,C:\oracle\PROD\OCM\oradata\ocm1ctl2.ora,C:\oracle\PROD\OCM\oradata\ocm1ctl3.ora)
MEMORY_TARGET=838860800

Step 5: (Windows Only) Create an Instance
The ORADIM command creates an Oracle instance by creating a new Windows service.

Do not set the -STARTMODE argument to AUTO at this point, because this causes the new instance to start and attempt to mount the database, which does not exist yet.

Step 6: Connect to the Instance

C:\oracle\PROD\OCM>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Oct 11 14:39:46 2011
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
Connected to an idle instance.
SQL>

Step 7: Create a Server Parameter File

SQL> CREATE SPFILE='C:\oracle\PROD\OCM\spfileOCM1.ora' from PFILE='C:\oracle\PROD\OCM\initocm1.ora';
File created.
SQL>

Better to create the spfile in the default location so that you don't need to do extra work for using it and avoid the errors like below.
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\ORACLE\PROD\DB\TECH_ST\11.1.0\DATABASE\INITOCM1.ORA'
Step 8: Start the Instance
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1348508 bytes
Variable Size             176163940 bytes
Database Buffers          352321536 bytes
Redo Buffers                5828608 bytes
SQL>
Step 9: Issue the CREATE DATABASE Statement Example 1:  
SQL> CREATE DATABASE OCM1
  2  USER SYS IDENTIFIED BY ocm1
  3  USER SYSTEM IDENTIFIED BY manager
  4  LOGFILE
  5  GROUP 1 ('C:\oracle\PROD\OCM\oradata\redo01a.log','C:\oracle\PROD\OCM\oradata\redo01b.log') SIZE 100M BLOCKSIZE 512
,
  6  GROUP 2 ('C:\oracle\PROD\OCM\oradata\redo02a.log','C:\oracle\PROD\OCM\oradata\redo02b.log') SIZE 100M BLOCKSIZE 512
,
  7  GROUP 3 ('C:\oracle\PROD\OCM\oradata\redo03a.log','C:\oracle\PROD\OCM\oradata\redo03b.log') SIZE 100M BLOCKSIZE 512

  8  MAXLOGFILES 5
  9  MAXLOGMEMBERS 5
 10  MAXLOGHISTORY 1
 11  MAXDATAFILES 100
 12  CHARACTER SET US7ASCII
 13  NATIONAL CHARACTER SET AL16UTF16
 14  EXTENT MANAGEMENT LOCAL
 15  DATAFILE 'C:\oracle\PROD\OCM\oradata\system01.dbf' SIZE 325M REUSE
 16  SYSAUX DATAFILE 'C:\oracle\PROD\OCM\oradata\sysaux01.dbf' SIZE 325M REUSE
 17  DEFAULT TABLESPACE users
 18  DATAFILE 'C:\oracle\PROD\OCM\oradata\users01.dbf'
 19  SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
 20  DEFAULT TEMPORARY TABLESPACE tempts1
 21  TEMPFILE 'C:\oracle\PROD\OCM\oradata\temp01.dbf'
 22  SIZE 20M REUSE
 23  UNDO TABLESPACE undotbs
 24  DATAFILE 'C:\oracle\PROD\OCM\oradata\undotbs01.dbf'
 25  SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

A database is created with the following characteristics:
■ The database is named mynewdb. Its global database name is OCM1
■ Three control files are created as specified by the CONTROL_FILES initialization parameter
■ The passwords for user accounts SYS and SYSTEM are set to the values that you specified.
■ The new database has three redo log file groups, each with two members, as specified in the LOGFILE clause.
■ MAXDATAFILES specifies the maximum number of datafiles that can be open in the database. This number affects the initial sizing of the control file.
■ The US7ASCII character set is used to store data in this database.
■ The AL16UTF16 character set is specified as the NATIONAL CHARACTER SET, used to
store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2.
■ The SYSTEM tablespace, consisting of the operating system file system01.dbf is created as specified by the DATAFILE clause.
■ The SYSTEM tablespace is created as a locally managed tablespace.
■ A SYSAUX tablespace is created, consisting of the operating system file sysaux01.dbf as specified in the SYSAUX DATAFILE clause.
■ The DEFAULT TABLESPACE clause creates and names a default permanent tablespace for this database.
■ The DEFAULT TEMPORARY TABLESPACE clause creates and names a default temporary tablespace for this database.
■ The UNDO TABLESPACE clause creates and names an undo tablespace that is used to store undo data for this database if you have specified UNDO_MANAGEMENT=AUTO in the initialization parameter file.
■ Redo log files will not initially be archived, because the ARCHIVELOG clause is not specified in this CREATE DATABASE statement. 

NOTE: with 11g Rel 11.1.0 you will get the following errors for create database statement (as above)

GROUP 1 ('C:\oracle\PROD\OCM\oradata\redo01a.log','C:\oracle\PROD\OCM\oradata\redo01b.log') SIZE 100M BLOCKSIZE 512,                                                                                                    *
ERROR at line 5:
ORA-02165: invalid option for CREATE DATABASE

*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01222: MAXINSTANCES of 16 requires MAXLOGFILES be at least 32, not 5
 
Dropping a Database
Dropping a database involves removing its datafiles, redo log files, control files, and initialization parameter files. To drop a database:
■ Submit the following statement:
DROP DATABASE;
The DROP DATABASE statement first deletes all control files and all other database files listed in the control file. It then shuts down the database instance. To use the DROP DATABASE statement successfully, the database must be mounted in exclusive and restricted mode.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict mount;
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1348508 bytes
Variable Size             176163940 bytes
Database Buffers          352321536 bytes
Redo Buffers                5828608 bytes
Database mounted.
SQL>
SQL> drop database;
Database dropped.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>


Example 2:
This example illustrates creating a database with Oracle Managed Files, which enables you to use a much simpler CREATE DATABASE statement. To use Oracle Managed Files, the initialization parameter DB_CREATE_FILE_DEST must be set. This parameter defines the base directory for the various database files that the database creates and automatically names.
Sample below for initOCM1.ora

DB_NAME=OCM1
DB_CREATE_FILE_DEST='C:\oracle\PROD\OCM\oradata'
MEMORY_TARGET=838860800

SET ORACLE_SID=OCM1

C:\oracle\PROD\OCM>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Oct 12 10:05:28 2011
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
Connected to an idle instance.
SQL>
SQL> startup nomount pfile=C:\oracle\PROD\OCM\initocm1.ora
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1348508 bytes
Variable Size             176163940 bytes
Database Buffers          352321536 bytes
Redo Buffers                5828608 bytes
SQL>

SQL> create spfile from pfile='C:\oracle\PROD\OCM\initocm1.ora';
File created.
SQL>

SQL> CREATE DATABASE OCM1
  2  USER SYS IDENTIFIED BY ocm1
  3  USER SYSTEM IDENTIFIED BY manager
  4  EXTENT MANAGEMENT LOCAL
  5  DEFAULT TEMPORARY TABLESPACE temp
  6  UNDO TABLESPACE undotbs1
  7  DEFAULT TABLESPACE users;
Database created.
SQL>

Files created with Oracle Managed File option in Create Database Statement

 Step 10: Create Additional Tablespaces
To make the database functional, you must create additional tablespaces for your application data. 

Step 11: Run Scripts to Build Data Dictionary Views
Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus:
@?/rdbms/admin/catalog.sql  -- C:\oracle\PROD\db\tech_st\11.1.0\RDBMS\ADMIN (for my enviornment)
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql -- Enables SQL*Plus to disable commands by user.


SQL> @C:\oracle\PROD\db\tech_st\11.1.0\RDBMS\ADMIN\catalog.sql
SQL> @C:\oracle\PROD\db\tech_st\11.1.0\RDBMS\ADMIN\catproc.sql
SQL> @C:\oracle\PROD\db\tech_st\11.1.0\RDBMS\ADMIN\pubbld.sql

Step 12: (Optional) Run Scripts to Install Additional Options
You may want to run other scripts. The scripts that you run are determined by the features and options you choose to use or install.
Step 13: Back Up the Database.
Take a full backup of the database to ensure that you have a complete set of files from which to recover if a media failure occurs.
Step 14: (Optional) Enable Automatic Instance Startup
You might want to configure the Oracle instance to start automatically when its host computer restarts. See your operating system documentation for instructions.

ORADIM -EDIT -SID OCM1 -STARTMODE AUTO -SRVCSTART SYSTEM -SPFILE

You must use the -SPFILE argument if you want the instance to read an SPFILE upon automatic restart.

Error Starting the Instance after using Oracle Managed File:

After I created the database using Oracle Managed Files, everything went smooth, but upon next time starting with the instance threw the following error.

SQL> STARTUP
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1348508 bytes
Variable Size             176163940 bytes
Database Buffers          352321536 bytes
Redo Buffers                5828608 bytes
ORA-00205: error in identifying control file, check alert log for more info

 To know the reason, I had to search the alert log as there is a different location in 11g as compared to the 10g. I checked the following parameter to know the location of alert log.

SQL> show parameter diagnostic_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      C:\ORACLE\PROD\DB\TECH_ST\11.1.0\LOG
SQL>

it was on the following location on my environment 

C:\oracle\PROD\db\tech_st\11.1.0\log\diag\rdbms\ocm1\ocm1\trace

The alert log showed the following error.

ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: 'C:\ORACLE\PROD\DB\TECH_ST\11.1.0\DATABASE\CTL1OCM1.ORA'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-205 signalled during: ALTER DATABASE   MOUNT...

I just added the following parameter in the initocm1.ora and started the instance again and it was with success.

control_files=(C:\oracle\PROD\OCM\oradata\OCM1\CONTROLFILE\O1_MF_79BHFWOR_.CTL)

After this I multiplexed the control files as copy/paste and amend the init file again stop the instance and started again with success.

control_files=(C:\oracle\PROD\OCM\oradata\OCM1\CONTROLFILE\O1_MF_79BHFWOR_.CTL,C:\oracle\PROD\OCM\oradata\OCM1\CONTROLFILE\control2OCM1.CTL,C:\oracle\PROD\OCM\oradata\OCM1\CONTROLFILE\control3OCM1.CTL)

After this I created the spfile from the pfile to start the instance automatcally with new settings.

SQL> create spfile from pfile='C:\oracle\PROD\OCM\initocm1.ora';
File created.
SQL>

Specifying Oracle Managed Files at Database Creation
By including any of the initialization parameters DB_CREATE_FILE_DEST, DB_CREATE_
ONLINE_LOG_DEST_n, or DB_RECOVERY_FILE_DEST in your initialization parameter file,
you instruct Oracle Database to create and manage the underlying operating system
files of your database. Oracle Database will automatically create and manage the
operating system files for the following database structures, depending on which
initialization parameters you specify and how you specify clauses in your CREATE
DATABASE statement:
■ Tablespaces and their datafiles
■ Temporary tablespaces and their tempfiles
■ Control files
■ Redo log files
■ Archived redo log files
■ Flashback logs
■ Block change tracking files
■ RMAN backups
Setting the Database Time Zone
Set the database time zone when the database is created by using the SET TIME_ZONE clause of the CREATE DATABASE statement. If you do not set the database time zone, then it defaults to the time zone of the host operating system. You can change the database time zone for a session by using the SET TIME_ZONE clause of the ALTER SESSION statement.
Specifying FORCE LOGGING Mode
Some data definition language statements (such as CREATE TABLE) allow the NOLOGGING clause, which causes some database operations not to generate redo records in the database redo log. The NOLOGGING setting can speed up operations that can be easily recovered outside of the database recovery mechanisms, but it can negatively affect media recovery and standby databases.
To put the database into FORCE LOGGING mode, use the FORCE LOGGING clause in the CREATE DATABASE statement.

SQL> alter database force logging;
Database altered.
SQL>

SQL> alter database no force logging;
Database altered.

Sample Initialization Parameter File

# Change '' to point to the oracle base (the one you specify at
# install time)
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest=''
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

Specifying a Fast Recovery Area
The Fast Recovery Area is a location in which Oracle Database can store and manage files related to backup and recovery. It is distinct from the database area, which is a location for the current database files (datafiles, control files, and online redo logs).
You specify the Fast Recovery Area with the following initialization parameters:
■ DB_RECOVERY_FILE_DEST: Location of the Fast Recovery Area. This can be a directory, file system, or Automatic Storage Management (Oracle ASM) disk group. It cannot be a raw file system. In an Oracle Real Application Clusters (Oracle RAC) environment, this location must be on a cluster file system, Oracle ASM disk group, or a shared directory configured through NFS.
■ DB_RECOVERY_FILE_DEST_SIZE: Specifies the maximum total bytes to be used by the Fast Recovery Area. This initialization parameter must be specified before DB_ RECOVERY_FILE_DEST is enabled.  
In an Oracle RAC environment, the settings for these two parameters must be the same on all instances.
You cannot enable these parameters if you have set values for the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters. You must disable those parameters before setting up the Fast Recovery Area. You can instead set values for the LOG_ARCHIVE_ DEST_n parameters. The LOG_ARCHIVE_DEST_1 parameter is implicitly set to point to the Fast Recovery Area if a local archiving location has not been configured and LOG_ ARCHIVE_DEST_1 value has not been set.
Oracle recommends using a Fast Recovery Area, because it can simplify backup and recovery operations for your database.
 

Specifying the DDL Lock Timeout
Data definition language (DDL) statements require exclusive locks on internal structures. If these locks are unavailable when a DDL statement runs, then the DDL statement fails, though it might have succeeded if it had been executed subseconds later.
To enable DDL statements to wait for locks, specify a DDL lock timeout—the number of seconds a DDL command waits for its required locks before failing. To specify a DDL lock timeout, use the  DDL_LOCK_TIMEOUT parameter. The permissible range of values for DDL_LOCK_TIMEOUT is 0 to 1,000,000. The default is 0. You can set DDL_LOCK_TIMEOUT at the system level, or at the session level with an ALTER SESSION statement.
Clearing Initialization Parameter Values
You can use the ALTER SYSTEM RESET command to clear (remove) the setting of any initialization parameter in the SPFILE that was used to start the instance. Neither SCOPE=MEMORY nor SCOPE=BOTH are allowed. The SCOPE = SPFILE clause is not required, but can be included.
You may want to clear a parameter in the SPFILE so that upon the next database startup a default value is used.
About Database Services
Database services (services) are logical abstractions for managing workloads in Oracle Database. Services divide workloads into mutually disjoint groupings. Each service represents a workload with common attributes, service-level thresholds, and priorities. The grouping is based on attributes of work that might include the application function to be used, the priority of execution for the application function, the job class
to be managed, or the data range used in the application function or job class. For example, the Oracle E-Business suite defines a service for each responsibility, such as general ledger, accounts receivable, order entry, and so on. When you configure database services, you give each service a unique global name, associated performance goals, and associated importance. The services are tightly integrated with Oracle
Database and are maintained in the data dictionary.
Connection requests can include a database service name. Thus, middle-tier applications and client/server applications use a service by specifying the service aspart of the connection in TNS connect data. If no service name is included and the Net Services file listener.ora designates a default service, the connection uses the default service.

Services enable you to configure a workload, administer it, enable and disable it, and measure the workload as a single entity. You can do this using standard tools such as the Database Configuration Assistant (DBCA), Net Configuration Assistant (NetCA), and Oracle Enterprise Manager. Enterprise Manager supports viewing and operating services as a whole, with drill down to the instance-level when needed. In an Oracle Real Application Clusters (Oracle RAC) environment, a service can span one or more instances and facilitate workload balancing based on transaction performance. This provides end-to-end unattended recovery, rolling changes by workload, and full location transparency. Oracle RAC also enables you to manage several service features with Enterprise Manager, the DBCA, and the Server Control utility (SRVCTL).

Services also offer an extra dimension in performance tuning. Tuning by "service and SQL" can replace tuning by "session and SQL" in the majority of systems where all sessions are anonymous and shared. With services, workloads are visible and measurable. Resource consumption and waits are attributable by application.
Additionally, resources assigned to services can be augmented when loads increase or decrease. This dynamic resource allocation enables a cost-effective solution for meeting demands as they occur. For example, services are measured automatically and the performance is compared to service-level thresholds. Performance violations are reported to Enterprise Manager, enabling the execution of automatic or scheduled
solutions.

Services describe applications, application functions, and data ranges as either functional services or data-dependent services. Functional services are the most common mapping of workloads. Sessions using a particular function are grouped together. In contrast, data-dependent routing routes sessions to services based on data keys. The mapping of work requests to services occurs in the object relational mapping layer for application servers and TP monitors. For example, in Oracle RAC, these ranges can be completely dynamic and based on demand because the database is shared.

You can also define preconnect application services in Oracle RAC databases. Preconnect services span instances to support a service in the event of a failure. The preconnect service supports TAF preconnect mode and is managed transparently when using Oracle RAC.

In addition to services to be used by applications, Oracle Database also supports two internal services: SYS$BACKGROUND is used by the background processes only and SYS$USERS is the default service for user sessions that are not associated with services.

Using services requires no changes to your application code. Client-side work can connect to a named service. Server-side work, such as Oracle Scheduler, parallel execution, and Oracle Streams Advanced Queuing, set the service name as part of the workload definition. Work requests executing under a service inherit the performance thresholds for the service and are measured as part of the service.

Database Service Data Dictionary Views
You can find service information in the following service-specific views:
■ DBA_SERVICES
■ ALL_SERVICES or V$SERVICES
■ V$ACTIVE_SERVICES
■ V$SERVICE_STATS
■ V$SERVICE_EVENT
■ V$SERVICE_WAIT_CLASSES
■ V$SERV_MOD_ACT_STATS
■ V$SERVICE_METRICS
■ V$SERVICE_METRICS_HISTORY
The following additional views also contain some information about services:
■ V$SESSION
■ V$ACTIVE_SESSION_HISTORY
■ DBA_RSRC_GROUP_MAPPINGS
■ DBA_SCHEDULER_JOB_CLASSES
■ DBA_THRESHOLDS

Opening a Database in Read-Only Mode
Opening a database in read-only mode enables you to query an open database while eliminating any potential for online data content changes. While opening a database in read-only mode guarantees that datafile and redo log files are not written to, it does not restrict database recovery or operations that change the state of the database without generating redo. For example, you can take datafiles offline or bring them online since these operations do not affect data content.

ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE OPEN READ WRITE;

SQL> alter database open read only;
Database altered.
SQL> create table t(id number);
create table t(id number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

Restricting Access to an Open Database
To place an already running instance in restricted mode, use the SQL statement ALTER SYSTEM with the ENABLE RESTRICTED SESSION clause. After this statement successfully completes, only users with the RESTRICTED SESSION privilege can initiate new connections. Users connecting as SYSDBA or connecting with the DBA role have this privilege.
SQL> alter system enable restricted session;
System altered.
SQL> alter system disable restricted session;
System altered.
SQL>

Shutdown Timeout
Shutdown modes that wait for users to disconnect or for transactions to complete have a limit on the amount of time that they wait. If all events blocking the shutdown do not occur within one hour, the shutdown operation aborts with the following message:
ORA-01013: user requested cancel of current operation. This message is also displayed if you interrupt the shutdown process, for example by pressing CTRL-C. Oracle recommends that you do not attempt to interrupt an instance shutdown. Instead, allow the shutdown process to complete, and then restart the instance. After ORA-01013 occurs, you must consider the instance to be in an unpredictable state.
You must therefore continue the shutdown process by resubmitting a SHUTDOWN command. If subsequent SHUTDOWN commands continue to fail, you must submit a SHUTDOWN ABORT command to bring down the instance. You can then restart the instance.
Quiescing a Database
Occasionally you might want to put a database in a state that allows only DBA transactions, queries, fetches, or PL/SQL statements. Such a state is referred to as a quiesced state, in the sense that no ongoing non-DBA transactions, queries, fetches, or PL/SQL statements are running in the system.
When the database is in the quiesced state, it is through the facilities of the Database Resource Manager that non-DBA sessions are prevented from becoming active.

SQL> ALTER SYSTEM QUIESCE RESTRICTED;
System altered.
SQL>

SQL> ALTER SYSTEM UNQUIESCE;
System altered.
SQL>

Suspending and Resuming a Database
The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to datafiles (file header and file data) and control files. The suspended state lets you back up a database without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state.
Use the ALTER SYSTEM RESUME statement to resume normal database operations. The suspend/resume feature is useful in systems that allow you to mirror a disk or file and then split the mirror, providing an alternative backup and restore solution. If you use a system that cannot split a mirrored disk from an existing database while writes are occurring, then you can use the suspend/resume feature to facilitate the split.

SQL> ALTER SYSTEM SUSPEND;
System altered.
SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS
-----------------
SUSPENDED
SQL> ALTER SYSTEM RESUME;
System altered.
SQL>