ADMIN I
Note : admin I – about physical information
about server
Admin II – about logical information about
server
TNS
/oracle/product/10.20/db-2/nw/admin
Orcl =
(Description =
Address = (protocol=tcp) (HOST=IP)(poer=1521)
Connect-data=
Server=dedicated
Service=name=orcl))
Setting
Time zone & Date format
SQL > alter session set
NLS_DATE_FORMAT=’DD-MON-YYYY HH24:MI:SS’
SQL > alter session set time_zone =’5:0’
SQL > select sessiontimezone,current_date
from dual
Creating user in oracle
SQL > create user
<user1> identified by <password>;
Note – By default no
permission given to user to grant the permission
SQL > grant create table/seesion/view to
<user1>;
SQL > alter user <user1> quota 10M
on users;
SQL > revoke create session from
<user1> - To revoke the permission
SQL > show user;
- To see the current user
SQL > select *
from all_user – To see all the users in
database
ROLES
SQL > create role
<name>
SQL > grant
select,update,alter,all on emp <role name>
SQL > grant
<role> to <user1,user2,user3>
Privillege
2 types of privilege
1. System privilege
– User,Session,Table,view,sequence (owned by DBA)
2. Object privilege
– Giving permission like (select,insert,alter,index,select ) (owned by owner)
Object Privilege are
Alter,delete,index,insert,reference,select,update
SQL > grant
select/alter/all on table to user
SQL > revoke
select/alter/all on table to user
To give all object creation permission
SQL > grant
connect,resource to <user1>
To give all basic permission in table
SQL > grant all
on <table> to <user>
Note: all includes
select ,insert,update,delete,alter
To revoke all permission from user
SQL > revoke all
on <table> from <user>
Check the given & received permissions
To check what are all permission assigned to user
SQL > select * from
user_tab_privs_made where
table_name=’<TABLE>’
SQL > select
privilege from user_tab_privs_made;
To see who are all have the permission to access particular table
SQL > select *
from user_tab_privs_recd where
table_name=’<table>’
SQL > select *
from role_sys_privs – To see system privilege granted to roles
SQL > select *
from role_tab_privs – To see table privilege granted to roles
SQL > select *
from user_role_privs – To see roles accessable by user
SQL > select * from
user_col_privs_made/recd – To see granted/received column privilege
SQL > select *
from user_sys_privs – List system privilege granted to user
The most commonly
used views for checking privileges are
dba_role_privs
dba_sys_privs
dba_tab_privs
dba_role_privs
dba_sys_privs
dba_tab_privs
To user current user
privilege
SQL > select *
from session_privs
SQL > select *
from user_sys_privs
As as sys to see
other user privilege
SQL > select
privilege from dba_sys_privs where grantee=’User name’
SQL > select
granted_role from dba_role_privs where grntee=’SCOTT’ – To check scott’s
applied roles
To
all children to give permission to grant children
SQL > grant select on <table> to
<user>,<user2> with grant / with admin
Note : If the grant child wanna see the
table, He/she has to user root user name not a parent user name
Public
privilege
SQL > grant select on <table> to
public
To
view the datas shared in public
SQL > select * from
all_tab_privs_recd where table_name=<table>
Oracle
Instances
Figure 1http://sarith.wordpress.com/tag/architecture-of-oracle-9i/
An Oracle database is a combination of
oracle Instance and data files on the file system.
Oracle Database = Oracle Instance +
Datafiles
Again Oracle Instance is nothing but Memory
architecture and Background processes. Lets start the discussion with Memory
architecture first.
Memory
Architecture
Oracle database uses memory for its
operation. The total memory allocated to the Oracle database can be broadly
categorized into SGA (System Global Area) and PGA (Program Global Area).
SGA
Contains following data structure
Database buffer cache
Redo log buffer
Shared pool
Stream pool (new in 10)
Java pool
Large pool (optional)
Data dictionary cache
Other miscellaneous information
SGA_target - specifies the total size of SGA
We can also categorize SGA into
fixed SGA and variable SGA. When asked about Fixed SGA, Ask Tom says that
“fixed SGA is a component of the SGA that varies in size from platform to
platform and release to release. It is
compiled into the database. The fixed
SGA contains a set of variables that point to the other components of the SGA
and variables that contain the values of various parameters. The size of the fixed SGA is something over
which we have no control and it is generally very small. Think of this area as a bootstrap section of
the SGA, something Oracle uses internally to find the other bits and pieces of
the
SGA.”
Variable SGA contains 4 main
components as listed above, those are “Database Buffer Cache”, “Redo Log
Buffer”, “Shared Pool” and “Large Pool”. We call it variable SGA because we can
alter the size of each of these components manually using ALTER SYSTEM command.
The size of each of the components of variable SGA is determined by INIT.ORA
parameters. Following are the INIT.ORA parameter for each of the component.
Database Buffer Cache – db_block_buffers
Redo Log Buffer – log_buffer
Shared Pool – shared_pool_size
Large Pool – Large_pool_size
We cannot however alter the size
of Fixed SGA.
Database Buffer Cache – This
is used to hold the data into the memory. When ever a user access the data, it
gets fetched into database buffer cache and it will be managed according to LRU
(Least recently used) algorithm. Advantages – If a user is requesting data,
which gets fetched into the buffer cache, then next time if he ask for same
data with in a short period of time, the data will be read from buffer cache
and Oracle process does not have to fetch data again from disk. Reading data
from buffer cache is a faster operation. Another advantage is that if a user is
modifying the data, it can be modified in the buffer cache which is a faster
operation then modifying the data directly on the disk.
Buffer in buffer cache can be in
one of four state
Pinned – Multiple session are
kept from writing same block
Clean – Or unpinned candidate
for immediate aging out
Free – Similar to clean except
here block not used
Dirty – The dirty block is no
longer pinned but the content have changed (modify) and must be flushed to disk
by DBWn process before ageing out
Redo Log Buffer - This
memory block hold the data which is going to be written to redo log file. Why
do we need this data? To rollback the changes if the need be. But instead of
writing the data directly to the redo log files, it is first written to log
buffer which improves performance and then with the occurrence of certain event
it will be written to redo log file.
Shared Pool - This contains 2
memory section, 1) Library Cache 2) Dictionary Cache. Library cache hold the
parsed SQL statement and execution plans and parsed PLSQL codes. Dictionary
cache hold the information about user privileges, tables and column
definitions, passwords etc. These 2 memory components are included in the size
of shared pool.
Large Pool - If defined then
used for heavy operations such as bulk copy during backup or during restore
operation.
The total size of SGA is
determined by a parameter SGA_MAX_SIZE.
Below is the simple calculation of memory sizes.
SQL> show sga
Total System Global Area 577574308 bytes
Fixed Size 452004 bytes
Variable Size 402653184 bytes
Database Buffers 163840000 bytes
Redo Buffers 10629120 bytes
This will show fixed and
variable size SGA. Fixed size SGA, as I said is not in our control. However we
can verify the size of variable SGA and other memory values shown above.
Database Buffers 163840000 bytes
SQL> show parameters db_block_buffer
NAME TYPE VALUE
———————————— ———– ——————————
db_block_buffers integer 20000
This value is in terms of
blocks. we can find the size of a block using DB_BLOCK_SIZE parameter
SQL> show parameters db_block_size
NAME TYPE VALUE
———————————— ———– ——————————
db_block_size integer 8192
So Database Buffers =
db_block_buffers X db_block_size = 20000 X 8192 = 163840000 bytes
Also Variable size = “Shared
Pool Size” + “Large Pool Size” + “Java Pool size” (some times defined)
SQL> SELECT pool, sum(bytes) from v$sgastat group by pool;
SQL > select * from v$sgainfo;
POOL SUM(BYTES)
———– ———-
java pool 50331648
shared pool 352321536
11069860
Variable size = 352321536 +
50331648 = 402653184 bytes
Program Global Area
PGA contains information about
bind variables, sort areas, and other aspect of cursor handling. This is not a
shared area and every user has its own PGA. But why PGA is required for every
user? The reason being that even though the parse information for SQL or PLSQL
may be available in library cache of shared pool, the value upon which the user
want to execute the select or update statement cannot be shared. These values
are stored in PGA. This is also called Private Global Area.
Going still deeper into the
memory structure…
Database buffer cache is again
divided into 3 different types of cache.
- Default Cache
- Keep Cache
- Recycle Cache
If we define the cache size
using DB_CACHE_SIZE (or DB_BLOCK_BUFFER and specify the block
size) then this will be default cache. The cache has a limited size, so not all
the data on disk can fit in the cache. When the cache is full, subsequent cache
misses cause Oracle to write dirty data already in the cache to disk to make
room for the new data.
You can configure the database
buffer cache with separate buffer pools that either keep data in the buffer
cache or make the buffers available for new data immediately after using the
data blocks.
The KEEP buffer pool retains the
schema object’s data blocks in memory. This is defined using the INIT.ORA
parameter DB_KEEP_CACHE_SIZE
The RECYCLE buffer pool
eliminates data blocks from memory as soon as they are no longer needed. This
is defined using the INIT.ORA parameter DB_RECYCLE_CACHE_SIZE
You can also define multiple DB
block sizes using following parameters. Example if you have defined standard
default block size of 4K, then following parameters can be used to define a
size of 2K, 8K, 16K and 32K.
DB_2K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE
Note that you can define the
Keep and Recycle cache only on standard block size and buffer cache size is the
sum of sizes of each of these pools.
Shared Pool Reserved Size
Shared Pool, as we have seen
previously contains the parsed SQL statements and execution plans. With
continuous use of database, after a period of time the shared pool will get
fragmented. New parsed SQL and execution plans comes and old one gets aged out
and hence overwritten. This will also lead to larger packages being aged out
with new entries going into shared pool. Hence access to such larger packages
will take time to parse and create execution plan. This might cause performance
issues.
To avoid such situation, you can
define a parameter SHARED_POOL_RESERVED_SIZE.
This will reserve some additional space other then shared_pool_size. If an object (either parsed SQL statement or
execution plan) is stored in reserved shared pool area then it will not age
out.
For large allocations, the order
in which Oracle attempts to allocate space in the shared pool is the following:
From the unreserved part of the
shared pool.
If there is not enough space in
the unreserved part of the shared pool, and if the allocation is large, then
Oracle checks whether the reserved pool has enough space.
If there is not enough space in
the unreserved and reserved parts of the shared pool, then Oracle attempts to
free enough memory for the allocation. It then retries the unreserved and
reserved parts of the shared pool.
Process Architecture
Oracle has several process
running in the background for proper functioning of database. Following are the
main categories of process.
Server Process
Background Process
Server Process – to handle the
requests of user processes connected to the instance. Server processes (or the
server portion of combined user/server processes) created on behalf of each
user’s application can perform one or more of the following:
Parse and execute SQL statements
issued through the application
Read necessary data blocks from
datafiles on disk into the shared database buffers of the SGA, if the blocks
are not already present in the SGA
Return results in such a way
that the application can process the information
Background Process - An Oracle
instance can have many background processes; not all are always present. The
background processes in an Oracle instance include the following:
Database Writer (DBW0 or DBWn)
Log Writer (LGWR)
Checkpoint (CKPT)
System Monitor (SMON)
Process Monitor (PMON)
Archiver (ARCn)
Recoverer (RECO)
Lock Manager Server (LMS) – Real
Application Clusters only
Queue Monitor (QMNn)
Dispatcher (Dnnn)
Server (Snnn)
On many operating systems,
background processes are created automatically when an instance is started.
Database writer (DBWn) - The
database writer process (DBWn) writes the contents of buffers to datafiles. The
DBWn processes are responsible for writing modified (dirty) buffers in the
database buffer cache to disk. Although one database writer process (DBW0) is
adequate for most systems, you can configure additional processes (DBW1 through
DBW9) to improve write performance if your system modifies data heavily. These
additional DBWn processes are not useful on uniprocessor systems.
Log Writer (LGWR) – The
log writer process (LGWR) is responsible for redo log buffer management–writing
the redo log buffer to a redo log file on disk. LGWR writes all redo entries
that have been copied into the buffer since the last time it wrote.
Checkpoint (CKPT) - When
a checkpoint occurs, Oracle must update the headers of all datafiles to record
the details of the checkpoint. This is done by the CKPT process. The CKPT
process does not write blocks to disk; DBWn always performs that work.
System Monitor (SMON) – The
system monitor process (SMON) performs crash recovery, if necessary, at
instance startup. SMON is also responsible for cleaning up temporary segments
that are no longer in use and for coalescing contiguous free extents within
dictionary-managed tablespaces. If any dead transactions were skipped during
crash and instance recovery because of file-read or offline errors, SMON recovers
them when the tablespace or file is brought back online. SMON wakes up
regularly to check whether it is needed.
Process Monitor (PMON) -The
process monitor (PMON) performs process recovery when a user process fails.
PMON is responsible for cleaning up the database buffer cache and freeing
resources that the user process was using. For example, it resets the status of
the active transaction table, releases locks, and removes the process ID from
the list of active processes.
PMON periodically checks the
status of dispatcher and server processes, and restarts any that have died (but
not any that Oracle has terminated intentionally). PMON also registers
information about the instance and dispatcher processes with the network
listener.
Archiver Process (ARCn) -The
archiver process (ARCn) copies online redo log files to a designated storage
device after a log switch has occurred. ARCn processes are present only when
the database is in ARCHIVELOG mode, and automatic archiving is enabled.
An Oracle instance can have up
to 10 ARCn processes (ARC0 to ARC9). The LGWR process starts a new ARCn process
whenever the current number of ARCn processes is insufficient to handle the
workload. The ALERT file keeps a record of when LGWR starts a new ARCn process.
Recoverer (RECO) – The
recoverer process (RECO) is a background process used with the distributed
database configuration that automatically resolves failures involving
distributed transactions. The RECO process of a node automatically connects to
other databases involved in an in-doubt distributed transaction. When the RECO
process reestablishes a connection between involved database servers, it
automatically resolves all in-doubt transactions, removing from each database’s
pending transaction table any rows that correspond to the resolved in-doubt
transactions.
The RECO process is present only
if the instance permits distributed transactions and if the
DISTRIBUTED_TRANSACTIONS parameter is greater than zero. If this initialization
parameter is zero, RECO is not created during instance startup.
Lock Manager Server (LMS) - In
Oracle9i Real Application Clusters, a Lock Manager Server process (LMS)
provides inter-instance resource management.
Queue Monitor (QMNn) – The
queue monitor process is an optional background process for Oracle Advanced
Queuing, which monitors the message queues. You can configure up to 10 queue
monitor processes. These processes, like the Jnnn processes, are different from
other Oracle background processes in that process failure does not cause the
instance to fail.
The above once explained are the
mail background processes. Please refer to the Oracle documentation for
detailed Oracle 9i Architecture.
Is combination of BG process
& memory structure. The instance must be started to access the DATA in
database. Every time instance started SGA is allocated & oracle BG process
are started
Tools Used to
administer an ORACLE Database
v
Oracle universal installer
v
Database configuration assistance
v
Database Upgrade assistance
v
Oracle net manager
v
Oracle Enterprise
v
SQL*Plus and iSQL*plus
v
Recovery Manager
v
Oracle Secure backup
v
Data pump
v
SQL*Loder
v
Command-Line tools
To
see the Database status
SQL > select status from v$instance
To
Give all object creation and resource to new user
SQL > grant connect,resource
to <user1>
To
see the Database name
SQL > show parameter db_name
To
see the Instance name
SQL > show parameter instance_name
To
check who are all logged in now
SQL > select sid,serial#,program,terminal
from v$session where username is not null
To
see the user account status
SQL > select account_status from
dba_users where username=’USRE1’
To
Lock & unlock the user account
SQL > alter user <name> account
lock/unlock
Forcefull
close the loged user account from server
SQL > select sid,serial#,program,terminal
from v$session where username is not null
SQL > alter system kill session ‘<SID>,<Serial
number>’ - to know the sid & serial number we have to use previous
command
Shutdown
Options
Immediate – Rollback the un commited
tranaction, kill the logged in user session
Transaction – If the user transaction is
pending wait for wither commit/roll back,
Does not allow new user to log in
Abort - Immediate abort for emergency condition
Optional
flexible architecture(OFA)
Storage path
Oracle\product\10.2
Admin
Db name -> init.ora
Bdump -> alert.log
Udump ->
trace files
ORADATA
DB name -> Service name(orcl) (datefile/control
file/redo
log file
DB Software
DUMP is
specifying the user dump directory where all user error logs (trace files) will
be placed.
BDUMP is specifying the background dump directory where all database error logs (trace files) will be placed.
CDUMP is specifying the core dump directory where all OS error logs (core dump files) will be placed.
BDUMP is specifying the background dump directory where all database error logs (trace files) will be placed.
CDUMP is specifying the core dump directory where all OS error logs (core dump files) will be placed.
SQL > show parameter
SQL > show background_dump
Alert.log –File will
show all logs (oracle\product\10.20\admin\orcl\bdump)
Three
important file
- pfile
- bdump - To check the
system log
- udump – To check user log
To
trace particular user
SQL > exec
dbms_system.set_sql_trace_in_session(143,83,true) – To enable log
SQL > select sid,serial# from v$session
where username=’SCOTT’ – To see log
SQL > exec
dbms_system.set_sql_trace_in_session(143,83,false) – To disable log
Note – After all the above 3 steps output
file is created in \...\admin\orcl\ddump
To see where User trace file is located
SQL > show parameter user_dump_dest
When
session starts & issuing create table command
- User process sent to - > server process
- Server process -> where each login & session ids
created in PGA
- Server check for same compilation query in shared pool in SGA
- Checked in library cach (For pasred sql/plsql)
- Checked in data dictionary cache(for authentication)
- table created & stored in data base
When
session starts & issuing insert command
- User process ->sent to -.server process
- Server process -> where each login & session ids
created in PGA
- Compilation code and privilege checked in shared pool
- new data stored in unused block of the database buffer cache
then it become dirty block
- When commit executed data written to server in data file then
dirty blocks are become unused block in database buffer cache
When
session starts & issuing select command
- after the above 1-3 steps
- in database buffer cache data stored in unused block then
stored block become dirty block
- Job number is written in control file
When
session starts & issuing Update command
- after the above 1-3 steps
- When we issue update statement internally select statement
will select specific row where data store in database buffer cache un used
block which is then become dirty block
Is a used bloc which has to be
replaced as 200
Will become Unsed block before
update executed
After replacing the same block
becomes dirty block
- When we commit data is stored in data file in database
- Jod id is stored in control file
- Old replaced data is stored in redo log file from redo log
buffer with help of LGWR BG
process
- When redo log file become full it’s data is stored to archive
log file with help of ARC BG
process
Note: redo log file
is only used for recovery purpose
Master
instance & master control file
CKPT – is master instance which control all
other process
Control file – is master file in DB which is
used to control data file & redo
log file
PMON – is process monitor which is used to
clear instance memory when abnormal termination will happen in user session
SMON – When entire DB abnormally shutdown
SMON will monitor & clear the entire instance
Parameter file – which has complete instance
& db information without this parameter file db wont boot it is like
boot.ini file which has all parameter info
Instant status
SQL > conn sys
SQL > select
status from from v$instance
SQL > alter
database mount
SQL > select
status from from v$instance
SQL > alter
database open
SQL > select
status from from v$instance
Start up sequence
- shutdown
- no mount
a.
instance started
- mount
a.
Control file mounted
- open
a.
All files opened as described by the control
file for this instance
Starting up an oracle database instance:no mount
An instance is started in nomount only in
following scenario
- During database creation
- During Control file creation
- During backup & recovery process
Searching oracle_home/DBS for file in
particular
-spfile<sid>.ora
-if
not found spfile.ora
-if
not found init<sid>.ora
This is
the file that contain the initialization parameter for the instance
Specifying PFILE parameter during the
startup override the default behavior
-allocating
SGA
-starting
BG process
-opening
alert<SID>.log file
Starting up an oracle database instance: mount
An instance is started in mount only in
following scenario
- Renameing data files
- Enabling disabling online redo-log file archiving option
- Performing full database recovery
Mounting database inckudes following tasks
-
Associate the database with previously
started instance
-
Locating & opening control file
specified in parameter file
-
Reading the control file to obtain name
& status of the data file & online redo-log file. However no checks are
performed to verify the existence of data & redo-log file
To
open Database in read only mode
SQL > start up
SQL > alter database open read only
SQL > select open_mode from v$database
Note : It is not possible to change from
read only mode to read write mode only we have to restart
To
stop the users logging in
SQL > alter system enable restricted
session
SQL > alter system disable restricted
session
Startup
Parameter file
When we use startup command it will read 2
parameter fil
1. SPFILE.ORA
2. PFILE.ORA
Database
information Query
SQL > show parameter db_name
SQL > show parameter instance_name
SQL > show parameter sysmax_size
SQL > show parameter spfile
SQL > show parameter background
SQL > show parameter control_file
SQL > show parameter
SQL > show parameter db_block_size –
Default value is 8k
SQL > show parameter db_cach_size – size
of the standard block buffer cache default size is 48 MB
SQL > show parameter
db_file_multiblock_read_count – maximum number of block read during I/O operation
SQL > show parameter db_files – show the
maximum number of data files that can be opened for this database
SQL > show parameter pga_aggregate_target
–PGA allotted memory size
How
to change parameter
SQL > select parameter
background_dump_dest
SQL > Alter system set
background_dump_dst=’e:\’
SQL > select background_dump_dst
Parameter
file
Types of parameter file
1. pfile – is a static file (text file)
2. spfile – is a dynamic file (binary file)
To see where spfile is located
SQL > show parameter spfile
Note – Spfile can be created only under
/../dbs (or) /../database folder
pfile
can be created any where
To see the parameter inside spfile &
pfile
SQL > select parameter
SQL > select parameter spfile
To change parameter in spfile
 |
Before knowing query see the above diagram.
When we change the parameter in spfile it will not refelect in memory to make
refelect either we have to restart server or while changing parameter we have
to give scope=both
SQL > alter system set
background_dump_dest=’e:\folder’ – Here we changed the location of bdump
parameter in spfile
SQL > alter system set
background_dump_dest=’e:\folder’ scope=both– Here we changed the
location of bdump parameter in spfile & memory
SQL > show parameter background_dump_dest
If
the spfile file become corrupt
We can boot the server with pfile
SQL > startup
pfile=’\path\pfile\init.ora’
To create new spfile
SQL > create spfile from
pfile=<path>
TO create pfile
SQL > create pfile=<path> from
spfile
Note : Spfile always stored in same two
location
Pfile can be stored anywhere in server
Maintaining
Control files
To add control file
SQL> alter system set
control_file=<old file path>,c:/new file path scope=spfile
Note:the same control file manually created
in specified drive
Note: control_file is spfile parameter we
can not give both in scope we have exclusively give spfile scope.
Note: All error can be seen in alert log
file to see the alert log path iuuse below given command
To see alert.log file location
SQL> show parameter background_dump_dest-
To see the parameter
SQL> show parameter control_file
Practise done in class I
SQL> alter system set
control_file=<old>,<new> - to create new control file
SQL> shutdown immediate
SQL> Conn
SQL> startup - Got the error because
control file which is added logically has to be created
SQL> show parameter control_file -
alert.log file checked to see the error
* Physically same control file created in
specified drive
SQL> Conn
SQL> startup - Now worked fine
Practise done in class II
* Created new control file manually in
'pfile'
SQL> startup pfile=<path> - started with pfile
SQL> shutdown immediate
SQL> Conn
SQL> startup - works fine but here spfile
doesn’t know about new control file hence we add new one manually in pfile
SQL> SQL> shutdown immediate
SQL> Conn
SQL> startup pfile=<path> - Throws an error because
in privious startup with spfile only old control file is updated, when we try
to start with pfile new control file (SCL) number doesn’t match with data file
content.
SQL> create spfile from pfile=<path> - To fix above
problem
Notes: when control file size become huge it
will consume lot of h/d space to optimize
we can reduce number of date control file
keeping SCL information
SQL> show parameter
control_file_record_keep_time - To see control file can keep how many days data
by default it can 7 days data which is
advisable
SQL> alter system set
control_file_record_keep_time=3 - to change parameter to keep the data only for
3 days
Maintaining
REDO Log files
Redo log file only used for recovery purpose
Structure of redo log files
Disk
I
Disk
2
Group - is Logical
Member - is physical
To see the information from log like member,their
status,etc
SQL> Select * from V$log
To see the location of all the redo log
files
SQL> select * from V$logfiles
When one gourps all the member full
internally following command will execute
SQL> alter system switch logfile
SQL> alter system checkpoint
To add more log files or group
SQL> alter database add log file group 4
"d: \... \ordata \orcl\redo41.log size 5m
To Delete log file
SQL> alter database drop logfile group1
SQL> select * from v$log
Note: It
is not possible to delete the current group
Minimum
2 log files are required
To add new member
SQL> alter database add logfile member
'e: \ orcl\redo32.log to group 3
Note: While adding new member no need to
specify size auomatically neighbour member size applied.
because All member should be same size
To delete group
SQL> alter database drop logfil member
'e: \ orcl\redo32.log'
OMF (ORACLE Manager Files)
SQL > show
parameter db_create_online
To set red log file
location when ever we create new one
SQL > alter
system set db_create_online_log_dest_1=<d:\oracle\...\oradata\orcl’
To create 2nd
red log file location when ever we
create new one for mulitiplexing
SQL > alter
system set db_create_online_log_dest_2=<d:\oracle\...\oradata\orcl’
To Remove the path from dictionary
SQL > alter system set
db_create_online_log_dest_2=’ ‘;
Note: When we set the path in for
above log files at the time or deletion it will be removed automatically.
Otherwise we have to remove manually.
Configuring
Archive mode
Steps
- SQL > shutdown immediate
- SQL > startup mount
3. SQL > alter database archivelog
4. SQL > alter database open
To view whether archive log
enabled or not
SQL > archive log list
Note: In 9i service has to manually started
by changing value column of log_archive_structure
SQL > shoe parameter log_archive_structure
To Create archive log file
manually
SQL > alter system switch logfile
SQL > alter system checkpoint
No comments:
Post a Comment