DB CREATION STEPS IN WINDOWS
SETTING AN ENVIRONMENT(FOR WINDOWS)
ORADIM -new -sid <new_sid> -intpwd oracle -startmode AUTO -pfile c:\oracle\admin\<new_sid>\pfile\init<new_sid>.ora
PASSWORD FILE MANAGEMENT
Check in the initclone.ora REMOTE_LOGIN_PASSWORDFILE is set to exclusive or shared. If this is set, then a valid passwordfile should exist in ORACLE_HOME/dbs or created using orapwd as
orapwd file=/u01/oracle/V816/dbs/orapwV722 password=oracle entries=1
STARTING UP WITH NOMOUNT OPTION
SQL>STARTUP NOMOUNT PFILE=’c:\oracle\admin\mbb\pfile\init.ora’;
THE CRATE DATABASE COMMAND in win NT:-
SQL>CREATE DATABASE “mbb”
LOGFILE GROUP 1
SIZE 5M REUSE,
(‘c:/oracle/oradata/mbb/redo02.log’) SIZE 5M REUSE
CHARACTER SET “WE8MSWIN1252”
NATIONAL CHARACTER SET “AL16UTF16”
DATAFILE ‘c:/oracle/oradata/mbb/system01.dbf’ SIZE 80m
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
UNDO TABLESPACE UNDOTBS
DATAFILE ‘c:/oracle/oradata/mbb/undo01.dbf’ SIZE 35m
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE ‘c:/oracle/oradata/mbb/temp01.dbf’ SIZE 20M;
DB CREATION STEPS FOR LINUX
The Instance, the Database, and the Data Dictionary
An Oracle server is an instance and a database; the two are separate, but connected. The instance is memory structures and processes, in your RAM and on your CPU(s); its existence is transient; it can be started and stopped. The database is files on disk; once created, it persists until it is deleted. Creating an instance is nothing more than building the memory structures and starting the processes. Creating a database is done by the instance as a once-off operation, and the instance can then open and close it many times subsequently. The database is worthless without the instance.
Within the database there is a set of tables and other segments called the data dictionary. The data dictionary describes all the logical and physical structures in the database, including all the segments that store user data.
The process of database creation is creating the bare minimum of physical structures needed to store the data dictionary, and then creating the data dictionary within them.
An instance is defined by an instance parameter file. The parameter file contains directives that define how the instance should be built in memory: the size of the memory structures, the behavior of the background processes. After building the instance, it is said to be in no mount mode. In no mount mode, the instance exists but has not connected to a database. Indeed, the database may not even exist at this point.
All parameters, either specified by the parameter file or implied, have defaults, except for one: the parameter DB_NAME. The DB_NAME parameter names the database to which the instance will connect. This name is also embedded in the controlfile. There is one parameter, CONTROL_FILES, that tells the instance the location of the controlfile. This parameter defines the connection between the instance and the database. When the instance reads the controlfile (which it will find by reading the CONTROL_FILES parameter) if there is a mismatch in database names, the database will not mount. In mount mode, the instance has successfully connected to the controlfile. If the controlfile is damaged or non-existent, it will be impossible to mount the database. The controlfile is small, but vital
Within the controlfile, there are pointers to the other files (the online redo log files and the datafiles) that make up the rest of the database. Having mounted the database, the instance can open the database by locating and opening these other files. An open database is a database where the instance has opened all the available online redo log files and datafiles. Also within the controlfile, there is a mapping of datafiles to tablespaces. This lets the instance identify the datafile(s) that make(s) up the SYSTEM tablespace. In the SYSTEM tablespace, it will find the data dictionary. The data dictionary lets the instance resolve references to objects referred to in SQL code to the segments in which they reside, and work out where, physically, the objects are.
The creation of a database server must therefore involve these steps:
Create the instance.
Create the database.
Create the data dictionary.
In practice, the steps are divided slightly differently:
Create the instance.
Create the database and the data dictionary objects.
Create the data dictionary views.
The data dictionary as initially created with the database is fully functional but unusable. It has the capability for defining and managing user data but cannot by used by normal human beings because its structure is too abstruse. Before users (or DBAs) can actually use the database, a set of views must be created on top of the data dictionary that will present it in a human-understandable form.
The data dictionary itself is created by running a set of SQL scripts that exist in the ORACLE_HOME/rdbms/admin directory. These are called by the CREATE DATABASE command. The first is sql.bsq, which then calls several other scripts. These scripts issue a series of commands that create all the tables and other objects that make up the data dictionary.
The views and other objects that make the database usable are generated with more scripts in the ORACLE_HOME/rdbms/admin directory, prefixed with “cat”. Examples of these are catalog.sql and catproc.sql, which should always be run immediately after database creation. There are many other optional “cat” scripts that will enable certain features—some of these can be run at creation time; others might be run subsequently to install the features at a later date.
DB Installation in silent mode
nohup ./runInstaller -silent -force \
Using the DBCA to Create a Database
To launch the DBCA on Linux, first set the environment variables that should always be set for any Linux DBA session: ORACLE_BASE, ORACLE_HOME, PATH, and LD_LIBRARY_PATH. This is an example of a script that will do this:
These are the steps to follow to create a database:
Create a parameter file and (optionally) a password file.
Use the parameter file to build an instance in memory.
Issue the CREATE DATABASE command. This will generate, as a minimum, a controlfile, two online redo log files, two datafiles for the SYSTEM and SYSAUX tablespaces, and a data dictionary.
Run SQL scripts to generate the data dictionary views and the supplied PL/ SQL packages.
Run SQL scripts to generate the Enterprise Manager Database Control, and any options (such as Java) that the database will require.
On Windows systems, there is an additional step because Oracle runs as a Windows service. Oracle provides a utility, oradim.exe, to assist you in creating this service.
SETTING AN ENVIRONMENT(FOR LINUX)
% setenv ORACLE_SID mynewdb
(A) Change the working directory to /etc and edit the oratab file to put the entry for CLOBE instance.
(B) Setup login profile for the Oracle user having dba group.
The CREATE DATABASE Command
This is an example of the CreateDB.sql script:
connect “SYS”/”&&sysPassword” as SYSDBA
set echo on
startup nomount pfile=”D:\oracle\app\admin\ocp11g\scripts\init.ora”;
CREATE DATABASE “ocp11g”
SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘D:\oracle\app\oradata\ocp11g\sysaux01.dbf’
SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
‘D:\oracle\app\oradata\ocp11g\temp01.dbf’ SIZE 20M REUSE
AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE “UNDOTBS1” DATAFILE
‘D:\oracle\app\oradata\ocp11g\undotbs01.dbf’ SIZE 200M REUSE
AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 (‘D:\oracle\app\oradata\ocp11g\redo01.log’) SIZE 51200K,
GROUP 2 (‘D:\oracle\app\oradata\ocp11g\redo02.log’) SIZE 51200K,
GROUP 3 (‘D:\oracle\app\oradata\ocp11g\redo03.log’) SIZE 51200K
USER SYS IDENTIFIED BY “&&sysPassword”
USER SYSTEM IDENTIFIED BY “&&systemPassword”;
CREATE DATABASE USING DBCA SILENTLY
dbca -silent \
-templateName /u01/app/oracle/product/12.1.0/db_1/assistants/dbca/templates/General_Purpose.dbc \
-gdbName EEOCMSC.tdenopcl.internal \
-sid EEOCMSC \
-SysPassword 3ricss0n \
-SystemPassword 3ricss0n \
-emConfiguration NONE \
-redoLogFileSize 100 \
-recoveryAreaDestination FRA \
-storageType ASM \
-asmSysPassword 3ricss0n \
-diskGroupName DATA \
-characterSet AL32UTF8 \
-nationalCharacterSet AL16UTF16 \
-totalMemory 4072 \
To start Database Control, use the emctl utility. This will be located in the ORACLE_HOME/bin directory. The three commands to start or stop Database Control and to check its status are
emctl start dbconsole
emctl stop dbconsole
emctl status dbconsole
The ORACLE_HOME and ORACLE_SID are needed so that emctl can find the Database Control configuration files. These are in three places: the directory ORACLE_HOME/sysman/config has general configuration directives that will apply to all Database Control instances running from the Oracle Home (one per database), and also ORACLE_HOME/hostname_sid/sysman/config and a similarly named directory beneath
To identify the port, you can use emctl. As shown in Figure 5–1, the output of emctl status dbconsole shows the port on which Database Control should be running. Alternatively, you can look in the file ORACLE_HOME/install/portlist.ini, which lists all the ports configured by the OUI and DBCA.
DB shutdown and startup
So use of the SYSDBA privilege logs you on to the instance as user SYS, the most powerful user in the database and the owner of the data dictionary. Use of the SYSOPER privilege connects you as a user PUBLIC. PUBLIC is not a user in any normal sense—he/she is a notional user with administration privileges, but (by default) with no privileges that let him/her see or manipulate data. You should connect with either of these privileges only when you need to carry out procedures that no normal user can do.
When the database is SHUTDOWN, all files are closed and the instance does not exist. In NOMOUNT mode, the instance has been built in memory (the SGA has been created and the background processes started, according to whatever is specified in its parameter file), but no connection has been made to a database. It is indeed possible that the database does not yet exist. In MOUNT mode, the instance locates and reads the database control file. In OPEN mode, all database files are located and opened and the database is made available for use by end users. The startup process is staged: whenever you issue a startup command, it will go through these stages. It is possible to stop the startup part way. For example, if your control file is damaged, or a multiplexed copy is missing, you will not be able to mount the database, but by stopping in NOMOUNT mode you may be able to repair the damage. Similarly, if there are problems with any datafiles or redo log files, you may be able to repair them in MOUNT mode before transitioning the database to OPEN mode.
At any stage, how does the instance find the files it needs, and exactly what happens? Start with NOMOUNT. When you issue a startup command, Oracle will attempt to locate a parameter file. There are three default filenames. On Unix they are
Transactional No new user connections are permitted; existing sessions that are not in a transaction will be terminated; sessions currently in a transaction are allowed to complete the transaction and will then be terminated. Once all sessions are terminated, the database will shut down.
Immediate No new sessions are permitted, and all currently connected sessions are terminated. Any active transactions are rolled back, and the database will then shut down.
Abort As far as Oracle is concerned, this is the equivalent of a power cut. The instance terminates immediately. Nothing is written to disk, no file handles are closed, and there is no attempt to terminate transactions that be in progress in any orderly fashion.
Shutdown and startup procedure
Use SQL*Plus to start an instance and open a database, then Database Control to shut it down. If the database is already open, do this in the other order. Note that if you are working on Windows, the Windows service for the database must be running. It will have a name of the form OracleServiceSID, where SID is the name of the instance.
Log on to the computer as a member of the operating system group that owns the ORACLE_HOME, and set the environment variables appropriately for ORACLE_HOME and PATH and ORACLE_SID, as described in Chapter 4.
Check the status of the database listener, and start it if necessary. From an operating system prompt:
Check the status of the Database Control console, and start it if necessary. From an operating system prompt:
emctl status dbconsole
emctl start dbconsole
Launch SQL*Plus, using the /nolog switch to prevent an immediate logon prompt:
Connect as SYS with operating system authentication:
connect / as sysdba
Start the instance only:
Mount the database:
alter database mount;
Open the database:
alter database open;
Confirm that the database is open by querying a data dictionary view:
select count(*) from dba_data_files;
select name,value from v$parameter order by name;
select name,value from v$spparameter order by name;
create spfile [=’spfilename’] from pfile [=’pfilename’];
create pfile [=’pfilename’] from spfile [=’spfilename’] ;
from v$spparameter s join v$parameter p on s.name=p.name
where p.isbasic=’TRUE’ order by name;
SELECT DECODE(value, NULL, ‘PFILE’, ‘SPFILE’) “Init File Type”
FROM sys.v_$parameter WHERE name = ‘spfile’;
You can also use the V$SPPARAMETER view to check if you are using a PFILE or not: if the “value” column is NULL for all parameters, you are using a PFILE.
SQL> ALTER SYSTEM RESET timed_statistics SCOPE=SPFILE SID=‘*’;
SQL> ALTER SYSTEM SET timed_statistics = ” SCOPE=SPFILE;
ISDEFAULT VARCHAR2(9) Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)
ISSES_MODIFIABLE VARCHAR2(5) Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)
ISSYS_MODIFIABLE VARCHAR2(9) Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect:
•IMMEDIATE – Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately.
•DEFERRED – Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.
•FALSE – Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.
ISINSTANCE_MODIFIABLE VARCHAR2(5) For parameters that can be changed with ALTER SYSTEm, indicates whether the value of the parameter can be different for every instance (TRUE) or whether the parameter must have the same value for all Real Application Clusters instances (FALSE). If the ISSYS_MODIFIABLE column is FALSE, then this column is always FALSE.
ISMODIFIED VARCHAR2(10) Indicates whether the parameter has been modified after instance startup:
•MODIFIED – Parameter has been modified with ALTER SESSION
•SYSTEM_MOD – Parameter has been modified with ALTER SYSTEM (which causes all the currently logged in sessions’ values to be modified)
•FALSE – Parameter has not been modified after instance startup
ISADJUSTED VARCHAR2(5) Indicates whether Oracle adjusted the input value to a more suitable value (for example, the parameter value should be prime, but the user input a non-prime number, so Oracle adjusted the value to the next prime number)
Viewing Information About the Database
DATABASE_PROPERTIES Displays permanent database properties
GLOBAL_NAME Displays the global database name
V$DATABASE Contains database information from the control file