An Oracle instance is the bunch of server process, which plays the core processing to manage the Oracle database. It's composed by memory segments and server processes.
The fist thing you need to manage is to create the Initialization Parameter file.
Two types of parameters:
- Explicit: Having an entry in the file
- Implicit: No entry within the file, but assuming the Oracle default values;
Parameter file (pfile):
- pfile (parameter file): The text based parameter file to configure the Oracle instance.
- modified with an operating system editor
- Modifications made manually
- changes take effect on the next start up
- Only opened during instance start up
- Default location is $ORACLE_HOME/dbs
- Creating a PFILE
- You create a pfile from the sameple pfile:
- Sample is installed by the Oracle Universal Installer
- Copy sample using operating system copy command
- Uniquely identified by database SID.
cp init.ora $ORACLE_HOME/dbs/initdba01.ora |
- Modify the initSID.ora file
- Edit the parameters
- Specific to database needs
db_name = dba01
instance_name = dba01
control_files = ( /home/dba01/ORADATA/u01/control01dba01.ctl,
/home/dba01/ORADATA/u02/control01dba02.ctl)
db_block_size = 4096
db_cache_size = 4M
shared_pool_size = 50000000
java_pool_size = 50000000
max_dump_file_size = 10240
background_dump_dest = /home/dba01/ADMIN/BDUMP
user_dump_dest = /home/dba01/ADMIN/UDUMP
core_dump_dest = /home/dba01/ADMIN/CDUMP
undo_management = AUTO
undo_tablespace = UNDOTBS
. . .
- spfile (server parameter file): The binary based parameter file, since 9i it's the default parameter file an Oracle instance will look at first
- Binary based file
- Maintained by the Oracle server
- Always resides on the server side
- Ability to make changes persistent across shut down and start up
- Can self-tune parameter values
- Can Have Recovery Manager support backing up to the initialization parameter file.
- Creating an spfile
- Created frmo a pfile file
CREATE SPFILE = ‘$ORACLE_HOME/dbs/spfileDBA01.ora’ FROM PFILE = ‘$ORACLE_HOME/dbs/initDBA01.ora’; |
- where
- SPFILE-NAME: SPFILE to be created
- PFILE-NAME: PFILE creating the SPFILE
- This command can be executed before and after instance start up.
* .background_dump_dest= ‘/home/dba01/ADMIN/BDUMP’
*.compatible=’9.2.0′
*.control_files=’/home/dba01/ORADATA/u01/ctrl01.ctl’ *.core_dump_dest= ‘/home/dba01/ADMIN/CDUMP’
*.db_block_size=4096
*.db_name=’dba01′
*.db_domain= ‘world’
*.global_names=TRUE
*.instance_name=’dba01′
*.remote_login_passwordfile=’exclusive’
*.java_pool_size=50000000
*.shared_pool_size=50000000
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS’
SQL> CREATE SPFILE FROM PFILE; |
To modify a spfile, you need to issue the 'alter system set' command to change the value of an parameter through sqlplus command prompt.
ALTER SYSTEM SET parameter_name = parameter_value
[ COMMENT 'text' ] [ SCOPE = MEMORY|SPFILE|BOTH ]
[ SID = 'sid'|'*']
'sid': Specific SID to be used in altering the SPFILE, usefully in RAC environment.
'*': Uses the default SPFILE.
example:
- Changing parameter values
| ALTER SYSTEM SET undo_tablespace = UNDO2; |
- Specifying temporary or persistent changes
| ALTER SYSTEM SET undo_tablespace = UNDO2 SCOPE=BOTH; |
- Deleting or resetting values
| ALTER SYSTEM SET undo_tablespace = UNDO2 SCOPE=BOTH SID=’*’; |
Managing an Oracle instance,
STARTUP command behavior
- Order of precedence:
- spfileSID.ora
- Default SPFILE
- initSID.ora
- Default PFILE
- Specified PFILE can override precedence.
| STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora |
There are four modes of Oracle instance:
Starting the Instance (NOMOUNT):
An instance would be started in the NOMOUNT stage only during database creation or the re-creation of control files.
Starting an instance includes the following tasks:
- Reading the initialization file from $ORACLE_HOME/dbs in the following order:
- First spfileSID.ora
- if not found then, spfile.ora
- if not found not initSID.ora
- Allocating the SGA
- Starting the background processes
- Opening the alertSID.log file and the trace files.
To perform specific maintenance operations, you start an instance and mount a database but do not open the database.
For example, the database must be mounted but not open during the following tasks:
- Renaming data files
- Enabling and disabling online redo log file archiving options
- Performing full database recovery
- Associating a database with a previously started instance.
- Locating and opening the control files specified in the parameter file
- Reading the control files to obtain the names and status of the data files and online redo log files. However, no checkes are performed to verify the existence of the data files and online redo log files at this time.
Normal database operation means that an instance is started and the database is mounted and open. Tyicl database access and normal database operation are valid on the database at this time.
Opening the database includes the following tasks:
- Opening the online data files
- Opening the online redo log files
| STARTUP |
| STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora |
Startup command syntax:
STARTUP [FORCE] [RESTRICT] [PFILE=filename]
[OPEN [RECOVER] [database]
|MOUNT
|NOMOUNT ]
- FORCE: Aborts the running instance before performing a normal startup.
- RESTRICT: Enablesonly users with RESTRICTED SESSION privilegeto access the dataabase.
- RECOVER: Begins media recovery when the database starts.
| Shutdown Mode | A | I | T | N |
| Allow new connections | No | No | No | No |
| Wait until current sessions end | No | No | No | Yes |
| Wait until current transactions end | No | No | Yes | Yes |
| Force a checkpoint and close files | No | Yes | Yes | yes |
shutdown mode:
- A = Aboort
- I = Immediate
- T = Transactional
- N = Normal
Normal is the default shut down mode. Normal database shut down proceeds with the following conditions:
- No new connection can be made
- The Oracle server waits for all users to disconnect before completing the shutdown.
A transactional shutdown prevents clients from losing work. A transactional database shutdown procees with the following conditions:
- No client can start a new transactino on this particular instance.
- A client is disconnected when the client ends the transaction that is in process.
- When all transactions have finished, a shut down occurs immediately.
Immediate database shut down proceeds with the following conditions:
- Current SQL statements being processed by Oracle are not completed.
- The Oracle server does not wait for the users, who are currently connected to the database, to disconnect.
- Oracle rolls back active transactions and disconnencts all connected users.
- Oracle closes and dismounts the database before shutting down the instance.
- alertSID.log file
- Background trace files
- User trace files
- Location defined by BACKGROUND_DUMP_DEST
- Must be managed by DBA
- Each entry has a time stamp associated with it
- alertSID.ora file:
- Records the commands
- Records results of major events
- Used to day-to-day operational information
- Used for diagnosing database erros
- Background trace files
- Log erros detected by any background process
- Are used to diagnose and troubleshoot erros
- Created when a background process encounters an error
- Location defined by BACKGROND_DUMP_DEST
- User trace files
- Produced by the user process
- Can be generated by a server process
- Contain statistics for traced SQL statements
- Contain user error messages
- Created when a user encounters user session errors
- Location is defined by USER_DUMP_DEST
- Size defined by MAX_DUMP_FILE_SIZE
No comments:
Post a Comment