Default Temporary Tablespace Creation

The DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement specifies that a temporary tablespace is to be created at database creation time. This tablespace is used as the default temporary tablespace for users who are not otherwise assigned a temporary tablespace.

Users can be explicitly assigned a default temporary tablespace in the CREATE USER statement. But, if no temporary tablespace is specified, they default to using the SYSTEM tablespace. It is not good practice to store temporary data in the SYSTEM tablespace. To avoid this problem, and to avoid the need to assign every user a default temporary tablespace at CREATE USER time, you can use the DEFAULT TEMPORARY TABLESPACE clause of CREATE DATABASE.

If you decide later to change the default temporary tablespace, or to create an initial one after database creation, you can do so. You do this by creating a new temporary tablespace (CREATE TEMPORARY TABLESPACE), then assign it as the temporary tablespace using the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement. Users will automatically be switched (or assigned) to the new temporary default tablespace.

The following statement assigns a new default temporary tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempts2;

The new default temporary tablespace must be an existing temporary tablespace. When using a locally managed SYSTEM tablespace, the new default temporary tablespace must also be locally managed.

You cannot drop a default temporary tablespace, but you can assign a new default temporary tablespace, then drop the former one. You are not allowed to change a default temporary tablespace to a permanent tablespace, nor can you take a default temporary tablespace offline.

Users can obtain the name of the current default temporary tablespace using the DATABASE_PROPERTIES view. The PROPERTY_NAME column contains the value "DEFAULT_TEMP_TABLESPACE" and the PROPERTY_VALUE column contains the default temporary tablespace name.

Related Post:-

• Creation Oracle Database Manually
• Creation Of Undo Tablespace By Using Automatic Undo Management

Read More

Creation Of Undo Tablespace By Using Automatic Undo Management

Oracle recommends that instead of using rollback segments in your database, you use an undo tablespace. This requires the use of a different set of initialization parameters, and optionally, the inclusion of the UNDO TABLESPACE clause in your CREATE DATABASE statement.

You must include the following initialization parameter if you want to operate your database in automatic undo management mode:

UNDO_MANAGEMENT=AUTO

In this mode, rollback information, referred to as undo, is stored in an undo tablespace rather than rollback segments and is managed by Oracle. If you want to create and name a specific tablespace for the undo tablespace, you can include the UNDO TABLESPACE clause at database creation time. If you omit this clause, and automatic undo management is specified, Oracle creates a default undo tablespace named SYS_UNDOTBS


Related Post:-

• Create Oracle Database Manually 
• Oracle Database Configuration Assistant 

Read More

How To Protect Your database

The clauses of the CREATE DATABASE statement used for specifying the passwords for users SYS and SYSTEM are:

- USER SYS IDENTIFIED BY password
- USER SYSTEM IDENTIFIED BY password
If not specified, these users are assigned the default passwords change_on_install and manager, respectively. A record is written to the alert file indicating that the default passwords were used. To protect your database, you should change these passwords using the ALTER USER statement after database creation.

Read More

Oracle Database Creation Manually

When the database is created manually, there is complete control over the process. Tablespaces can be added or reduced and the flow of the creation process can be managed. Here are the steps to be followed to create a database manually.

• Make SID:-Decide on a unique Oracle system identifier (SID) for your instance and set the ORACLE_SID environment variable accordingly. This identifier is used to avoid confusion with other Oracle instances that you may create later and run concurrently on your system. The value of the DB_NAME initialization parameter should match the SID setting. 

• Preparing  the Parameter File:- Modify the initSID.ora  by editing the parameters . Store your initialization parameter file in Oracle's default location, using the default name. That way, when you start your database, it is not necessary to specify the PFILE parameter because Oracle automatically looks in the default location for the initialization parameter file. For UNIX  $ORACLE_HOME/dbs is default location of parameter file and For Window the default location of parameter file is $ORACLE_HOME\database. The following is the initialization parameter file used to create new database.

         db_name =dbname

         shared_pool=52428800

         db_block_size= 8192

         Control_files=’/opt/db01/con.ctl’

         background_dump_dest=’/opt/db01/bdump’

         user_dump_dest=’/opt/db01/udump’

         undo_management=auto

         undo_tablespace=undo01

•  Connect to the instance:- connect oracle instance as SYSDBA

         Export_SID=dbname

               Sqlplus ‘/as sysdba’ 

• Start the Instance:- Start an instance at nomount stage . Use the STARTUP command with the NOMOUNT option. Initialization parameter file is stored in the default location, you are not required to specify the PFILE clause: SQL>startup nomount 
• Creating the Database:- after connecting the intance at nomount stage u execute create database statement in SQL prompt.

1. Create database dbname
2. Datafile ‘/opt/tt/data.dbf’ size 200m
3. Sysaux datafile’/opt/tt/sysaux.dbf’ size 150m
4. Logfile group1 ‘/opt/tt/log01.log’ size 10m
5.               group2 ‘/opt/tt/log02.dbf’ size 10m
6. defaulttemporary tablespace temp tempfile’/opt/tt/temp.dbf’ size 50m
7. undotablespace undo1 datafile ‘/opt/tt/undo.dbf’ size 50m
8. Max instance            4
9. MaxDatafiles         100
10. MaxLogMembers      6
11. MaxLogHistory      200 

•  Run necessary scripts to build views, synonyms, etc.

1. Catalog.sql – create the views of data dictionary table and the dynamic performance views.
2. Catproc.sql- establishes the usage of PL/SQL functionally and many of the Pl/SQL Oraclesupplied packages. 

          Catalog statement - @?/rdbms/admin/catalog.sql

                Catproc statement- @?/rdbms/admin/catproc.sql 

• Create SPfile:- The SPFILE must initially be created from an initialization parameter file. You must have the SYSDBA or SYSOPER role to execute the CREATE SPFILE statement.                                                                       Create spfile from pfile 
• Database Backup:- You should make 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.

Related Post:-

• How To Protect Your Database

Read More

DBCA's Advantage

These are a few of the advantages of using DBCA:

• You can use its wizards to guide you through a selection of options providing an easy means of creating and tailoring your database. It allows you to provide varying levels of detail. You can provide a minimum of input and allow Oracle to make decisions for you, eliminating the need to spend time deciding how best to set parameters or structure the database. Optionally, it allows you to be very specific about parameter settings and file allocations. 
• It builds efficient and effective databases that take advantage of Oracle's new features 
• It uses Optimal Flexible Architecture (OFA), whereby database files and administrative files, including initialization files, follow standard naming and placement practices.

Read More

Step To Create Oracle Database Using DBCA

If you choose to install software only and later create a database, or if you want to create additional databases using the release software that you just installed, you can do so by using the Database Configuration Assistant (DBCA).

Follow the steps below to create a database:

- Log onto your computer as a member of the administrative group that is authorized to install Oracle software and create and manage the database.

- Launch the DBCA on a Windows operating system as follows :                           Start > Programs > Oracle - home_name > Configuration and Migration Tools >DatabaseConfigurationAssistant

- The Welcome page appears. 

- Select Create a Database on the Operations window to begin an interview that enables you to configure and create a database.

- On the Database Templates page, select the type of database template to be used in creating the database. You can click Show Details to see the configuration for each type of database. Choose the template suited to the type of workload your database will support. If you are not sure, select the default General Purpose template.

- On the Database Identification page, enter the Global Database Name and SID. 

- The Management Options page appears. To use Enterprise Manager, select Configure the Database with Enterprise Manager. Select Use Database Control for Database Management to manage your database locally or select Use Grid Control for Database Management as appropriate to your configuration.

- Enter passwords for the administrative users.

- Select File System, Automatic Storage Management, or Raw Devices as appropriate to your environment.

- Specify the location for the creation of the datafiles. Choose one of the following:Use Database File Locations from Template, Use Common Location for All Database Files, or Use Oracle-Managed Files.

- Select Flash Recovery Area and specify a directory location and size. Select Enable Archiving to place your database in ARCHIVELOG mode.

- Click the Sample Schemas tab. Select Sample Schemas if you want to include the Sample Schemas (EXAMPLE) tablespace in your database. Click the Custom Scripts tab.

- Specify one or more SQL scripts to be run after your database is created if required in your environment. Otherwise, accept the default No scripts to run.

- The Memory page appears. Select Typical and enter a percentage value. Click the Sizing tab. - The Sizing page appears. Specify the smallest block size and the maximum number of operating system user processes that can simultaneously connect to the database. 

- The Character Set page appears. Select the character set for your database. Click the Connection Mode tab.

- The Connection Mode page appears. Select Dedicated Server or Shared Server as appropriate for your environment.

 - The Database Storage page appears. Accept the configuration or make changes as needed.

 - Select Create Database to create your database. You can also select Save as a Database Template to save your configuration. Click Finish.

 -  At the Confirmation window, confirm the options that will be installed and click OK.

 - Your database is now being created.

Read More

How to create an Oracle Database

Creating a database includes the following operations:

• Creating information structures, including the data dictionary, that Oracle requires to access and use the database 
• Creating and initializing the control files and redo log files for the database 
• Creating new datafiles or erasing data that existed in previous datafiles

You use the CREATE DATABASE statement to perform these operations, but other actions are necessary before you have an operational database. A few of these actions are creating users and temporary tablespaces, building views of the data dictionary tables, and installing Oracle built-in packages.

An Oracle database can be created using:

• Oracle Database Configuration Assistant 
• The CREATE DATABASE command

Creating a database can be done either using the Oracle DatabaseConfiguration Assistant (DBCA) or by creating a SQL script using the CREATE DATABASE command. The DBCA is a graphical user interface that interacts with the Oracle Universal Installer, or can be used stand-alone, to simplify the creation of a database. The DBCA is Java-based and can be launched from any platform with a Java engine. During the installation of the Oracle Server, DBCA is launched by the Oracle Universal Installer and can automatically create a starter database for you. You have the option of using DBCA or not, and you also have the option to create a starter database. You also have the option to launch DBCA later as a standalone application to create a database. You can also migrate or upgrade an existing database if you are using a previous version of Oracle.

Related Post:

• Planning Database File Locations 
• Creation Prerequisites
• How to protect your database

Read More

Creation Prerequisites

To create a new database, the following prerequisites must be met:

• There is sufficient memory available to start the Oracle instance. 
• There is sufficient disk storage space for the planned database on the computer that executes Oracle. 
• The desired Oracle software is installed. 
• You have the operating system privileges associated with a fully operational database administrator. You must be specially authenticated by your operating system or through a password file, allowing you to start up and shut down an instance before the database is created or opened.

 All of these are discussed in the Oracle installation guide specific to your operating system. Additionally, the Oracle Universal Installer will guide you through your installation and provide help in setting up environment variables, directory structure, and authorizations.

Related Post:-

• Planning Database File Locations 
• How to create an Oracle Database

Read More

Planning Database File Locations

Plan how to protect the database, including the online redo log files, control files, data files, archived redo log files, and provide a backup strategy.

Control Files

For the sake of safety, you should create at least two control files on two different disks.

Online Redo Log Files

The online redo log files of a database should consist of multiplexed groups of online redo log files. A group of log files consists of identical copies, which should be located on different disks. To distinguish between groups and their members, use a name such as log0101.log or log01a.log.

Data Files

Name data files by relating to the contents as the root of the name—for example, data files such as system01.dbf, temp01.dbf, and users01.dbf on Unix. Consider the characteristics of the data to be stored before determining the structure appropriate for your database, in order to:

    Minimize fragmentation

    Minimize disk contention

    Separate objects

To minimize fragmentation of the database, you should separate database objects with different life spans, such as application data and temporary data, into different tablespaces. To ensure well-balanced I/O loads, you should separate objects with competing I/O requirements, such as tables and indexes, into different tablespaces.

Read More

Creation Of Oracle Database

Database creation is a task that prepares several operating system files and is needed only once no matter how many data files the database has. This is a very important task, because you must decide on database settings.

Depending on the operating system, a database may have been created automatically as part of the installation. You can use this initial database, or you can create a new one. The following topics can help prepare you for database creation:

• Planning Database File Locations 
• Creation Prerequisites
• How to create an Oracle Database

Read More

Abort Shutdown Of Oracle Database

If the normal and immediate shutdown options do not work, you can abort the current database instance. The shutdown abort command is pretty much a guaranteed way to get your database to shutdown. It’s a “hard crash” of the database, and this can result in a longer time to start the database back up. Still, you can’t really hurt the database using the shutdown abort command, and during your DBA years you will find more than a few occasions to use the shutdown abort command. Aborting an instance proceeds with the following conditions:

• Current SQL statements being processed by the Oracle server are immediately terminated. 
• Oracle does not wait for users currently connected to the database to disconnect. 
• Database and redo buffers are not written to disk. 
• Uncommitted transactions are not rolled back. 
• The instance is terminated without closing the files. 
• The database is not closed or dismounted. 
• The next startup requires instance recovery, which occurs automatically.

An example using the shutdown abort command:

SQL>shutdown abort

You should be connected to the database with SYSDBA privileges to perform startup

or shutdown.

Related Post:-

• Normal Shutdown
• Immediate Shutdown
• Transactional Shutdown

Read More

Transactional Shutdown Of Oracle Database

A transactional shutdown prevents clients from losing work. A transactional database shutdown proceeds with the following conditions

• No new connections are allowed 
• No client can start a new transaction on this particular instance. 
• A client is disconnected when the client ends the transaction that is in progress. 
• When all transactions have finished, a shutdown immediately occurs. 
• The next startup does not require an instance recovery. 

The next startup of the database will not require any instance recovery procedures.


Related Posts:-  

• Normal Shutdown 
• Immediate Shutdown 
• Abort Shutdown

Read More

Immediate Shutdown Of Oracle Database

Immediate database shutdown proceeds with the following conditions:

• Current SQL statements being processed by Oracle are not completed. 
• The Oracle server does not wait for users currently connected to the database to disconnect. 
• Oracle rolls back active transactions and disconnects all connected users. 
• Oracle closes and dismounts the database before shutting down the instance. 
• The next startup does not require an instance recovery. 
• The next startup of the database will not require any instance recovery procedures

 Use immediate database shutdown only in the following situations: 

• To initiate an automated and unattended backup 
• When a power shutdown is going to occur soon 
• When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off 
• To shut down a database immediately, use the SHUTDOWN command with the IMMEDIATE option

Shutting down a database with the shutdown immediate command:

SQL>shutdown immediate

The shutdown immediate command will work most of the time, but there are times when it can hang and fail to shutdown the database. In these cases, the shutdown abort command is called for:

Related Posts:- 

• Normal Shutdown 
• Transactional Shutdown
• Abort Shutdown

Read More

Normal Shutdown Of Oracle Database

A normal shutdown of an Oracle database is actually rarely used. This is because the normal shutdown waits for everyone to complete their work. When a normal shutdown occurs, the database is closed in a normal manner, and all changes made in the database are flushed to the database datafiles.

SQL> shutdown

When you execute a shutdown, Oracle will flush all the changes in memory out to the database datafiles. This makes database startup quicker because the database is in a consistent state.

Related Post:

• Immediate Shutdown 
• Transactional Shutdown
• Abort Shutdown

Read More

Shutting Down Oracle Database

To initiate database shutdown, use the SQL*Plus SHUTDOWN command. Control is not returned to the session that initiates a database shutdown until shutdown is complete. The shutdown command comes in many forms.  Oracle has three shutdown modes:

* Normal (default) - waits for in-flight work to complete

* Immediate - terminates all sessions and does a rollback on all

* Abort - aborts all sessions, leaving current DML in need of rollback, de-allocates the SGA and terminates the background processes.

The "normal" and "immediate" modes can take a long time and many Oracle DBA's ensure a swift clean shutdown this way, aborting the sessions, re-starting to allow warmstart rollback of the aborted transactions, and a shutdown immediate to close cleanly:

SQL> shutdown abort

SQL> startup

SQL> shutdown immediate

Related Posts:-

• Oracle Shutdown Normal
• Oracle Shutdown Immediate 
• Oracle Shutdown Transactional 
• Oracle Shutdown Abort 

Read More

Startup Database At Restricted Mode

You can also start the database in restricted mode. Restricted mode will only allow users with special privileges (we will discuss user privileges in a later chapter) to access the database (typically DBA’s), even though the database is technically open. We use the startup restrict command to open the database in restricted mode as seen in this example.

SQL> startup restrict

You can take the database in and out of restricted mode with the alter database command as seen in this example:

SQL> alter system enable restricted session;

-- Take the database out of restricted session mode.

SQL> alter system disable restricted session

Related Post:

• Startup Database at NoMount Stage
• Startup Database at Mount Stage
• Startup Database at Open Stage

Read More

Startup Oracle Database at Open Stage

When Oracle opens the database, it accesses all of the datafiles associated with the database. Once it has accessed the database datafiles, Oracle makes sure that all of the database datafiles are consistent.Oracle opens the online datafiles and online redo log files. If a tablespace was offline when the database was previously shut down, the tablespace and its corresponding datafiles will still be offline when you  reopen the database.

To open the database, you can just use the startup command

SQL> startup mount

If the database is mounted, you can open it with the alter database open command

SQL> alter database open

Related Post :-

• Startup Database At NoMount Stage
• Startup Database At Mount Stage
• Startup Database At Restricted Mode 

Read More

Startup Oracle Database at Mount Stage

You can start an instance and mount a database without opening it, allowing you to perform specific maintenance operations. When the startup command enters the mount stage, it opens and reads the control file. The control file is a binary file that tracks important database information, such as the location of the database datafiles.

In the mount stage, Oracle determines the location of the datafiles, but does not yet open them. Once the datafile locations have been identified, the database is ready to be opened.For example, the database must be mounted but not open during the following tasks

• Enabling and disabling redo log archiving options  
• Performing full database recovery

The following command starts an instance and mounts the database

SQL> startup mount

If you have already started the database instance with the startup nomount command, you might change it from the nomount to mount startup stage using the alter database command:

SQL> alter database mount

 Related Post :- 

• Startup Database At NOMount Stage 
• Startup Database At Open Stage 
• Startup Database At Restricted Mode

Read More

Startup Oracle Database at NoMount Stage

Normal database operation means that an instance is started and the database is mounted and open. This mode allows any valid user to connect to the database and perform data access operations. The following command starts an instance, reads the initialization parameters from the default location, and then mounts and opens the database. (You can optionally specify a PFILE clause.)

STARTUP

You can start an instance without mounting a database. Typically, you do so only during database creation. Use the STARTUP command with the NOMOUNT clause:

STARTUP NOMOUNT

The instance starts, but does not mount the control file or open the database. This status of the instance enables the following tasks:

1. To create a new database or

2. A new control file or

3. To recover control file.

When you start an instance at nomount stage it reads initialization parameter file determine the values of initialization parameters. Then, it allocates an SGA, which is a shared area of memory used for database information, and start background processes. At this point, no database is associated with these memory structures and processes.

Related Post :- 

• Startup Database At Mount Stage 
• Startup Database At Open Stage
• Startup Database At Restricted Mode

Read More

Starting Up Oracle Database

One of the most common jobs of the database administrator is to startup or shutdown the Oracle database. To start an instance, the database must read the initialization parameters or SPFILE. When you issue the SQL*Plus STARTUP command, the database attempts to read the initialization parameters from an SPFILE in a platform-specific default location. If it finds no SPFILE, it searches for a text initialization parameter file.

In the platform-specific default location, Oracle Database locates your initialization parameter file by examining filenames in the following order: 

• spfileSID.ora 
• spfile.ora 
• initSID.ora

You start the Oracle database with the startup command. You must first be logged into an account that has sysdba or sysoper privileges. DBA connecting to the database and starting the instance:

[oracle10@localhost ~]$ export_SID=dbn

[oracle10@localhost ~]$ aqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 9 14:18:33 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved

Connected to an idle instance

SQL> startup

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                            1219160 bytes

Variable Size                       96470440 bytes

Database Buffers               213909504 bytes

Redo Buffers                         2973696 bytes

Database mounted.

Database opened.

SQL>

We set the ORACLE_SID to the name of the database and we log into SQL*Plus using the “sys as sysdba” login. This gives us the privileges we need to be able to startup the database. Finally, after we enter our password, we issue the startup command to startup the database. Oracle displays its progress as it opens the database, and then returns us to the SQL*Plus prompt once the startup has been completed. When Oracle is trying to open your database, it goes through three distinct stages.These stages are 

• Startup nomount
• Mount
• Open 

Read More

Authentication Method for Database Administrators

Database Administrators can authenticate through the database data dictionary, (using an account password ) like other users. In addition to normal data dictionary authentication, the following methods are available for authenticating database administrators with the SYSDBA or SYSOPER privilege:

• Operating system (OS) authentication
• A password file
• Strong authentication with a network-based authentication service

These methods are required to authenticate a database administrator when the database is not started or otherwise unavailable. The remainder of this section focuses on operating system authentication and password file authentication.

Using Operating System Authentication

 This section describes how to authenticate an administrator using the operating system.

OSDBA and OSOPER

Two special operating system groups control database administrator connections when using operating system authentication. These groups are generically referred to as OSDBA and OSOPER. The groups are created and assigned specific names as part of the database installation process.

The default names assumed by the Oracle Universal Installer can be overridden. How you create the OSDBA and OSOPER groups is operating system specific.

Membership in the OSDBA or OSOPER group affects your connection to the database in the following ways:

• If you are a member of the OSDBA group and you specify AS SYSDBA when you connect to the database, then you connect to the database with the SYSDBA system privilege.
• If you are a member of the OSOPER group and you specify AS SYSOPER when you connect to the database, then you connect to the database with the SYSOPER system privilege.
• If you are not a member of either of these operating system groups and you attempt to connect as SYSDBA or SYSOPER, the CONNECT command fails.

 Preparing to Use Operating System Authentication

To enable operating system authentication of an administrative user:

• Create an operating system account for the user
• Add the account to the OSDBA or OSOPER operating system defined groups.

 Connecting Using Operating System Authentication

A user can be authenticated, enabled as an administrative user, and connected to a local database by typing one of the following SQL*Plus commands:

CONNECT / AS SYSDBA

CONNECT / AS SYSOPER

For the Windows platform only, remote operating system authentication over a secure connection is supported. You must specify the net service name for the remote database:

CONNECT /@net_service_name AS SYSDBA

CONNECT /@net_service_name AS SYSOPER

Both the client computer and database host computer must be on a Windows domain.

Using Password File Authentication

This section describes how to authenticate an administrative user using password file authentication.

Preparing to Use Password File Authentication

To enable authentication of an administrative user using password file authentication you must do the following:

• If not already created, create the password file using the ORAPWD utility: 

             ORAPWD FILE=filename ENTRIES=max_users

• Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE
• Connect to the database as user SYS (or as another user with the administrative privileges).
• If the user does not already exist in the database, create the user and assign a password. Keep in mind that beginning with Oracle Database 11g Release 1, database passwords are case sensitive. (You can disable case sensitivity and return to pre–Release 11g behavior by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.)
• Grant the SYSDBA or SYSOPER system privilege to the user:

             GRANT SYSDBA to oe;

This statement adds the user to the password file, thereby enabling connection AS SYSDBA

Connecting Using Password File Authentication

Administrative users can be connected and authenticated to a local or remote database by using the SQL*Plus CONNECT command. They must connect using their username and password and the AS SYSDBA or AS SYSOPER clause. Note that beginning with Oracle Database 11g Release 1, passwords are case-sensitive unless the password file was created with the IGNORECASE = Y option.

For example, user oe has been granted the SYSDBA privilege, so oe can connect as follows:

CONNECT oe AS SYSDBA

However, user oe has not been granted the SYSOPER privilege, so the following command will fail:

CONNECT oe AS SYSOPER

Read More

Protecting Your Database

The clauses of the CREATE DATABASE statement used for specifying the passwords for users SYS and SYSTEM are:

• ·       USER SYS IDENTIFIED BY password
•       USER SYSTEM IDENTIFIED BY password

If you omit these clauses, these users are assigned the default passwords change_on_install and manager, respectively. A record is written to the alert log indicating that the default passwords were used. To protect your database, you must change these passwords using the ALTER USER statement immediately after database creation.

Oracle strongly recommends that you specify these clauses, even though they are optional in this release of Oracle Database. The default passwords are commonly known, and if you neglect to change them later, you leave database vulnerable to attack by malicious users.

Read More

Administrative Privileges

Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system privileges, SYSDBA and SYSOPER. You must have one of these privileges granted to you, depending upon the level of authorization you require.

The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself.

The SYSDBA and SYSOPER privileges can also be thought of as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other fashion. For example, you if you have the SYSDBA privilege, you can connect to the database by specifying CONNECT AS SYSDBA.

SYSDBA and SYSOPER

SYSDBA               

• Perform STARTUP and SHUTDOWN operations
• ALTER DATABASE: open, mount, back up, or change character set
• CREATE DATABASE
• DROP DATABASE
• CREATE SPFILE
• ALTER DATABASE ARCHIVELOG
• ALTER DATABASE RECOVER
• Includes the RESTRICTED SESSION privilege

SYSOPER             

• Perform STARTUP and SHUTDOWN operations
• CREATE SPFILE
• ALTER DATABASE OPEN/MOUNT/BACKUP
• ALTER DATABASE ARCHIVELOGALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.)
• Includes the RESTRICTED SESSION privilege

Related Post:-

• Authentication Method for Database Administrators

Read More