Oracle Instance Management

1. Database Startup and Shutdown

• Database Control - A tool for managing one database even though the database can be clustered.

• If there are several databases then each database must have its own instance of the database control.
• All communications to DB Control id thru HTTPS.
• DB Control Configuration done at DB creation time - hostname and TCP port
  
• Start DB Control - emctl utility: emctl start dbconsole, emctl stop dbconsole, emctl status dbconsole. ORACLE_HOME, PATH and ORACLE_SID must be set for these cmds to run - for configuration settings.
  
• emctl status dbconcole will give the port on which the DB control is listening.
• Logon window will show only if the listener is running.

Starting the Listener

• Requests to listener uses Oracle Net
• To start DB Listener: With the lsnrctl utility, using DB control, or as a service(Windows)
• lsnrctl start [listener], lsnrctl stop [listener]

Starting SQL*Plus

• sqlplus /nolog then sqlplus /as sysdba sqlplus /as sysoper, etc.

Database Startup and Shutdown

• A DB is mounted and opened or dismounted and closed.
  
• Through SQL*PLUS: startup, shutdown or through DB Control.
• Alert log will give details of such operations - bcoz are critical operations.
• Can also be carried out by privileged users.

Connecting with Approprite Privilege

• Ordinary cannot start DB because they are authenticated against the Data Dictionary and DD cannot be read unless the DB is opened.
• Login thru Operatins System or external password file.
• DD Authentication: connect username/password. Anyone connecting this way cannot issue startup or shutdown.
• Password file or OS Authentication: Passwd File Auth - connect user/pass as sysdba, connect user/pass as sysoper, OS Auth - connect /as sysdba, connect /as sysoper.

SYSOPER and SYSDBA

• Special privilege with special capabilities
• Enabled only when an external auth means is used - Passwd file or OS.
• SYSOPER cannot CREATE DATABASE nor do incomplete recovery nor create SYSOPER and SYSDBA users.
  
• Both SYSOPER and SYDBA can: STARTUP, SHUTDOWN, ALTER DATABASE MOUNT|OPEN|CLOSE|DISMOUNT, ALTER [DATABASE|TABLESPACE] [BEGIN|END] BACKUP RECOVER
• show user cmd show the logged on user.
• SYSDBA logs you on as SYS user, SYSOPER logs you on as PUBLIC - a notional user with administration privileges.
  

My first encounter with challenging questions

I was not able to answer the following questions after satisfactory study of the relevant topics and materials:
Qn.1. Is it necessary to have a database listener created before creating a database?
I had an idea but I wasn't able to pick the best options from the choices given. As I came to learn, the answer was: It depends on whether Database Control option is selected in the DBCA.

Qn.2. Several actions are necessary to create a database. Place these in the correct order:
1) Create the data dictionary views
2) Create the parameter file
3) Create the password file
4) Issue the CREATE DATABASE command
5) Issue the STARTUP command

In my view, the sequence would be 2,3,1,4,5. In choosing I didn't think of Data Dictionary as having to act on underlying database(it must have been created) to make the database usable.
But as I came to learn the correct order is 2,3,5,4,1, the reason being that a parameter and a password file must be created first before anything else as what it to follow will be dependent on the two files, for instance tablespaces, block_size, and the like. You then have to startuo the database instance using the STARTUP command. It is only then you can issue the CREATE DATABASE command. At this point the database has been created but it's NOT usable at all. To make the database usable, you have create the data dictionary views. I thought I was sure of the sequence at first, but on studying the answe I was able to understand it.

Qn.3. What files are created by the CREATE DATABASE command? (Choose all the correct answers).
Apart from control files, I was not sure about any other files created by this command. But as I came to learn, the Control file, the Online Redo log files, the SYSAUX table space datafile and the SYSTEM tablespace datafile are all created by this command.

Qn.4. What will happen if you do not run the CATALOG.SQL and CATPROC.SQL scripts after creating a database?
I didn't have a clue on what might happen as I didn't know the role of the two Scripts. As I came to learn, it won't be possible to query the data dictionary views. The database will functuon just fine but without the data dictionary views and PL/SQL packages created by these scripts it will be unusable.

Qn.5. At what point doo you choose or change the database character set?
This was another challenging question but I'm glad I now know the answer and hope to be able to apply it to new situations. The answer, as I came to learn is: At database creation time, whether or not you are using a template, and after database creation time using SQL*PLUS.