Chapter 3 – Starting Up and Shutting Down

–          If you know that media recovery is required, you can start an instance, mount a database to the instance, and have the recovery process automatically start by using the STARTUP command with the RECOVER clause: STARTUP OPEN RECOVER

–          When SHUTDOWN IMMEDIATE command used, any uncommitted transactions are rolled back. If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name

–          Occasionally you might want to put a database in a state that allows only DBA transactions. Such a state is referred to as s quiesced state. Without ability to quiesce the database, you would need to shut down the database and reopen it in restricted mode. Use ALTER SYSTEM QUIESCE RESTRICTED; command. Moreover, refer to the Tom Kyte’s answer on this issue from the following link:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:902149038276

–          To restore the database to normal operation use ALTER SYSTEM UNQUIESCE;

Chapter 2 – Creating an Oracle Database

Before starting this chapter, I’d like to direct you to my step by step guide on “Create database manually”

–          If you omit to assign a password at CREATE DATABASE command, the SYS and SYSOPER users are assigned the default passwords change_on_install and manager, respectively. Oracle strongly recommends that you specify these clauses, even though they are optional

–          Specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE statement to create a locally managed SYSTEM tablespace. If you do not specify the EXTENT MANAGEMENT LOCAL clause, by default the database creates a dictionary managed SYSTEM tablespace.

–          The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. BECAUSE IT IS THE DEFAULT TABLESPACE FOR MANY Oracle Database features and products that previously required their own tablespaces, it reduces the number of tablespaces required by the database and that you must maintain.

–          You can’t drop or rename the SYSAUX tablespace

–          The SYSAUX tablespace needs to be at least 240MB at the time of database creation

–          The maximum number of datafiles in an Oracle Databsae is limited (usually to 64K files – 65536 files)

–          The maximum number of blocks in a datafile is 4GB (4 million blocks).  From the following link you can find a nice article written about it http://www.dba-oracle.com/t_bigfile_tablespace_tips.htm

–          Bigfile tablespace can contain only one file, but that file can have up to 4G blocks

–          A smallfile tablespace can contain up to 1022 files with up to 4M block each

–          To dynamically change the default tablespace type after database creation, use alter DATABASE SET DEFAULT BIGFILE TABLESPACE;  command

–          It is recommended that you use a server parameter file

–          DB_NAME must be set to a text string of no more than eight characters. During database creation, the name provided for DB_NAME is recorded in the datafiles, redo log files, and contro file of the database.

–          You can’t change the block size after database creation except by recreating the database.

–          The SGA_MAX_SIZE initialization parameter specifies the maximum size of the System Global Area for the lifetime of the instance. You can dynamically alter the initialization parameters affecting the size of the buffer caches, shared pool, large pool, Java pool and streams pool.

–          You enable automatic shared memory management feature by setting the SGA_TARGET parameter to a non-zero value. In addition, you must set the value of all automatically sized SGA components to a zero to enable full automatic tuning of these components. When SGA_TARGET is not set, the automatic shared memory management feature is not enabled.

–           When you increase the size of a manually sized component, extra memory is taken away from one or more automatically sized components. And when you decrease the size of a manually sized component, the memory that is released is given to the automatically sized components

–           The COMPATIBLE initialization parameter enables or disables the use of features in the databsae that affect file format on disk. For example, if you create an Oracle Database 10g database, but specify  COMPATIBLE=9.2.0.2 in the initialization parameter file, then features that requires 10.0 compatibility will generate an error if you try to use them. Such a database is said to be at the 9.2.0.2. compatibility level.

–          To limit the number of users created in a database, set the LICENSE_MAX_USERS initialization parameter in the database initialization parameter file

–          The COMMENT clause at ALTER SYSTEM command lets you associate a text string with the parameter update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the server parameter file.

–          To view initialization file’s parameter use SHOW PARAMETER command, CREATE PFILE command or V$PARAMETER view (this view displays the values of parameters currently in effect)

–          V$SPPARAMETER view displays the current contents of the server parameter file. The view returns FALSE values in the ISSPECIFIED column if a server parameter file is not being used by the instance

–          Check your current release number as follows

SQL> COL PRODUCT FORMAT A35

SQL> COL VERSION FORMAT A15

SQL> COL STATUS FORMAT A15

SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                             VERSION         STATUS

———————————– ————— —————

NLSRTL                              10.2.0.1.0      Production

Oracle Database 10g Enterprise Edit 10.2.0.1.0      Production

PL/SQL                              10.2.0.1.0      Production

TNS for Linux:                      10.2.0.1.0      Production

–          Create at least one additional administrative user and grant to that user an appropriate administrative role to use when performing daily administrative tasks. Do not use SYS and SYSTEM for these purposes.

–          When you create an Oracle Database, the users SYS and SYSTEM are automatically created and granted the DBA role

–          No one should create any tables in the schemas of users SYS and SYSTEM

–          A predefined DBA role is automatically created with every Oracle Database installation

–          When you connect with SYSDBA or SYSOPER privileges, you connect with a default schema, not with the schema that is generally associated with your username. For SYSDBA this schema is SYS, for SYSOPER the schema is PUBLIC

–           If you issue the ALTER USER statement to change the password for SYS after connecting to the database, both the password stored in the data dictionary and the password stored in the password file are updated. You can’t change the password for SYS if REMOTE_LOGIN_PASSWORDFILE is set to SHARED. An error message is issued if you attempt to do so

–          Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER system privileges for a database