my notes...

Operating system authentication setup:

For NT:

• Create NT user-group : ORA_SID_DBA, ORA_SID_OPER specific to an instance SID

• Create NT user-group : ORA_DBA, ORA_OPER not specific to an instance

• Add a NT OS user to that group. Once you access this domain you will automatically be 
  validated as an authorized DBA.

• sqlnet.ora parameter setting : SQLNET.AUTHENTICATION_SERVICES=(NTS)

• init.ora     parameter setting : REMOTE_LOGIN_PASSWORDFILE=NONE

REMOTE_LOGIN_PASSWORDFILE  allowed values:

exclusive

Note : Any object created by anyone logging in as SYSOPER or SYSDBA will be owned by SYS.

Password file authentication setup:

Create a password file through ORAPWD utility:

In NT :

C:\ > orapwd FILE='%ORACLE_HOME%\database\orapwSKDB.pwd' PASSWORD=jason ENTRIES=5

Default location of password file : %ORACLE_HOME%\database

A non default password file location can be specified in the windows registry  with the key 
ORA_SID_PWFILE.

In Unix:

orapwd \FILE='/u01/app/oracle/product/9.0.1/dbs/orapwSKDB.pwd' \PASSWORD=jason
ENTRIES=5
Default location of password file : $ORACLE_HOME\dbs

• Password assigned to the pwd file will automatically set the same for the SYS user to login Oracle.
   The password for this file & user SYS is the same. 

• Entries sets the maximum number of user entries for the password file. To add more later, the
  password will need to be deleted & re-created, which is risky. So exercise due care while deciding.

• init.ora parameter setting : REMOTE_LOGIN_PASSWORDFILE=exclusive

• To view users in the database password file : V$PWFILE_USERS

well past midnight now... In the background of my brains i can almost hear her say these words...

tu hi meri shaab bai... subah hai... tu hi din hai mera... 
tu hi mera raab hai... jahan hai... tu hi meri duniya... tu waqt mere liye, mei hu tera lamha... 
kaisi rahega bhala... tu mujhse jooda..

crazy & maddening... memories overpowering...

[Gangster movie song (translation) : u r my night... morning... day..., u r my God... my world... universe...
You are my time and i am ur moment... how will u live without me?]  

posted by sk @ 12:35 PM 0 comments

Steps to create a Database manually under Windows: 

1. Setting SID environment variable:

C:\ >

SET ORACLE_SID=SKDB 

2. To create a new Oracle instance:

C:\ >

ORADIM -NEW -SID SKDB -INTPWD password -STARTMODE a 
-pfile 'D:\Oracle\admin\SKDB\pfile\init.ora' 

3. To startup a service & instance:

C:\ > 

ORADIM -STARTUP -SID SKDB 
[-pfile 'D:\Oracle\admin\SKDB\pfile\init.ora'] 

4. Starting SQL & connecting:

C:\ >
SQL> 

SQLPLUS /NOLOG
CONNECT / AS SYSDBA  
(response -> connected to an idle instance) 

SQL>

STARTUP NOMOUNT pfile='D:\Oracle\admin\SKDB\pfile\init.ora'
(Response -> ORACLE instance started.
Total System Global Area  118255568 bytes
Fixed Size                   282576 bytes
Variable Size              83886080 bytes
Database Buffers           33554432 bytes
Redo Buffers                 532480 bytes) 

SQL> 

CREATE DATABASE SKDB
LOGFILE
GROUP 1 ('D:\ORACLE\ORADATA\SKDB\redo01.log') SIZE 100M,
GROUP 2 ('D:\ORACLE\ORADATA\SKDB\redo02.log') SIZE 100M,
GROUP 3 ('D:\ORACLE\ORADATA\SKDB\redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE 'D:\ORACLE\ORADATA\SKDB\system01.dbf' SIZE 325M
UNDO TABLESPACE undotbs
DATAFILE 'D:\ORACLE\ORADATA\SKDB\undotbs01.dbf' SIZE 200M
AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
SET TIME_ZONE = 'America/New_York' 

7. CREATING default temporary
tablespace if it fails to create in
the above command: 

SQL>



SQL>

CREATE TEMPORARY TABLESPACE temp TEMPFILE
 'D:\ORACLE\ORADATA\SKDB\temp01.dbf' SIZE 50M 
EXTENT MANAGEMENT LOCAL;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP; 

8. Creating views on base_tables,
dynamic performance views &
synonyms:

SQL>
SQL>

CONNECT SYS AS SYSDBA;
@D:\Oracle\ora90\rdbms\admin\CATALOGUE.SQL; 

9. Creating packages & procedures
required for PL/SQL:

SQL>

@D:\Oracle\ora90\rdbms\admin\CATPROC.SQL;

10. Creating Product User Profile
 tables & related procedures:

SQL>
SQL>

@D:\Oracle\ora90\sqlplus\admin\PUPBLD.SQL; 

BACKING UP / CLONING an existing Database :

Purpose : 

• rename a database as ORACLE does not allow two database to have the same name on the same host. 

• to change database settings such as maxlogfiles

• lost control file? Need to recreate it....

Steps to backup control file, redo log files & data_files : 

SQL >

SQL >

DOS>

Restoring a backed up Database: (Recreating control file): 

• Edit init.ora with the correct path names for control files,  log_archive_dest, 
  dump destinations and supply the instance_name & db_name.

1. Edit dumped control file with the correct path names for the redo log & datafiles.

C:\ > 

SET ORACLE_SID=chik

C:\ > 

ORADIM -STARTUP -SID chik [-pfile 'D:\Oracle\admin\chik\pfile\init.ora']

C:\ > 

SQLPLUS /NOLOG

SQL >

CONNECT / AS SYSDBA

SQL >

STARTUP NOMOUNT pfile='D:\Oracle\admin\chik\pfile\init.ora'

SQL >

Rem  Using dumped control file to restore the database.
CREATE CONTROLFILE REUSE DATABASE "CHIK" NORESETLOGS NOARCHIVELOG

LOGFILE

Rem STANDBY LOGFILE
DATAFILE

CHARACTER SET WE8MSWIN1252; 

SQL >

Rem  Recovery is required if any of the datafiles are restored backups,
Rem  or if the last shutdown was not normal or immediate.
RECOVER DATABASE

SQL >

Rem  Database can now be opened normally.
ALTER DATABASE OPEN;

SQL >

Rem Commands to add tempfiles to temporary tablespaces.
Rem Online tempfiles have complete space information.
Rem Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\Oracle\oradata\chik\TEMP01.DBF' REUSE;

posted by sk @ 11:58 AM 0 comments

Structure of Redo log files:

• Redo log groups : consists of a set of identical copies of online redo log files. LGWR concurrently writes the same information to all the online redo log files in a group.

  Multiple copies should be maintained to avoid losing database due to a single point of failure. Oracle needs minimum two online redo log groups for normal operation of a database.

• Log sequence number: Each member in a group has identical log sequence number and are of the same size. This log sequence is assigned each time Oracle server writes to a log group, to uniquely identify each redo log file. This sequence number is stored in the control file & in the header of all datafiles.

• Redo log specification during DATABASE CREATE :

MAXLOGFILES	Absolute maximum of online redo log file groups.
MAXLOGMEMBERS	Maximum number of members per group.

Functioning of Redo log: Redo log files record all the changes made to the data & provide a recovery mechanism. Redo log files are only used for recovery. These are used in a situation such as instance failure to recover committed data that has not been written to the datafiles. Oracle server sequentially writes all changes made to the database to Redo log buffers.

• These include:

• transactions that have not yet been committed,

• undo segment information, and

• schema & object management statements.

• LGWR writes these buffers to Redo log files sequentially under the following circumstances:

• when a transaction commits

• when Redo log buffer become 1/3^rd full

• when more tha 1Mb of changed record in the Redo log buffer

• Before DBWn writes modified blocks in the Database Buffer cache to the datafiles.

• Log Switch : Redo log work in a cyclic fashion. When a redo log file is full, the LGWR moves on to the next log goup. This is the log switch. A checkpoint occurs and information is written to control files.

• A log swich can also be manually forced by: ALTER SYSTEM SWITCH LOGFILE;

Checkpoint: During a checkpoint DBWn writes dirty databse buffers, that are covered by the logs that is being checkpointed, to the datafiles.

• The checkpoint background process CKPT updates the control files to reflect that successful completion of a checkpoint. Checkpoints caused by a log-switch causes CKPT to update datafile headers also.

• Checkpoints can occur for all datafiles in the database or only for specific datafiles. For example a checkpoint occurs in following situations:

• SHUTDOWN [ NORMAL | TRANSACTIONAL | IMMEDIATE ]

• FAST_START_MTTR_TARGET = 600

  Default value is zero.This initialization parameter implies instance recovery shouldnot take more than 600 seconds, thus forcing checkpoints to meet this goal. (it replaces FAST_START_IO_TARGET & LOG_CHECKPOINT_TIMEOUT). It determines the number of dirty buffers that DBWn needs to write during a checkpoint.

• ALTER SYSTEM CHECKPOINT; <----- Forcing checkpoint manually.

• ALTER TABLESPACE [ OFFLINE NORMAL | READ ONLY | BEGIN BACKUP ]

  Causes checkpointing on specific datafiles.

Adding Online Redo log file groups:

ALTER DATABASE [database] ADD LOGFILE
[GROUP group_integer] filespec
[, GROUP group_integer] filespec]...

Note : If GROUP clause is ommitted Oracle Server will automatically generate the value.

Adding Online Redo Log file members:

ALTER DATABASE ADD LOGFILE MEMBER
[ 'filename' [REUSE] TO { GROUP group_integer
[, 'filename [REUSE]]....
| ('filename' [, 'filename']...)
}
]....

• Note : The file name must be fully specified otherwise it will be created in the database server default directory.

• If the files already exists then it must be of the same size and must specify REUSE option.

• Target group may be specified by either of these:

• one or more members of the group.

• Group no.

OEM : Database->Storage -> Redo Log Groups [right click] -> create -> input info under General Tab -> create

Dropping Online Redo log file groups:

ALTER DATABASE [database]

DROP LOGFILE {GROUP integer | ('filename' [,'filename']...)}
[,{GROUP integer|('filename'[,filename']...)}]...

Restrictions:

• An instance requires at 2 online redo log file groups.

• Active or current group can't be dropped.

• Operating system files are not deleted.

Dropping Online Redo Log file members:

ALTER DATABASE [database]
DROP LOGFILE MEMBER
'filename' [,'filename']...

Restrictions:

• Can't drop the last valid member of a group.

• If the group is current, the member can't be dropped. A log-switch must be forced.

• If database is running on ARCHIVELOG mode, and the concerned log group has not been archieved, then the member can't be dropped.

• Operating system file is not dropped unless u r using OMF.

OEM : Database->Storage ->Redo Log Groups-> select redo log group->[General Tab] -> select member->[right click] remove->confirm

Relocating/Renaming Online Redo Log files:

Steps to take to rename/relocate online redo log files:

• Copy the log file to new destination using Operating system commands

• ALTER DATABASE CLEAR LOGFILE 'new_filename'; <-- clears the new file for use.

• ALTER DATABASE [database] RENAME FILE 'old_filename' [,'filename']... TO 'new_filename' [,'filename']...

Note: Before renaming ensure that the new redo log files exists. Oracle server only changes the pointers in the control file.

OEM : Database->Storage ->Redo Log Groups-> [select the redo log group]->modify filename/file Directory or relocate members.

CONFIGURING ONLINE REDO LOG FILE:

• Number of redo log groups needed depends on the database requirement. Just two groups can suffice in some systems. But if LGWR frequently has to wait ( reflected in LGWR trace file or alert files) because a checkpoint has not completed or a group has not been archieved then more groups may be needed.

• When multiplexing relo log files, keep members of a group in separate disks. This way even if one member becomes unavailable, the instance won't shut down.

• Although multiplexed groups can contain different number of members, they should ideally be symmetric. Asymmetric configuration should only be due to unusual situation such as a disk failure.

• Keep archive log files & online redo log files on separate disks to reduce disk contention between LGWR & ARCn background processes.

• Similiarly keep database file & redo log files on separate disks to reduce disk contention between LGWR & DBWn processes.

• Minimum size of redo log file is 50K. Members of different groups can have different sizes, however there is no benefit of different sized groups. This may be needed when you wish to change the size of the online redo log groups, creating new ones with new size and removing the old ones.

• Configuration of redo log files are effected by:

  • no. of log switches & checkpoints

  • number & amount of redo entries

  • amount of space on the storage medium

OMF managed ONLINE REDO LOG FILE:

Parameters: DB_CREATE_ONLINE_LOG_DEST_n

where n the multiplexed copy number.

DB_CREATE_ONLINE_LOG_DEST_1
DB_CREATE_ONLINE_LOG_DEST_2

• implies 2 members per group is created. Default size of members is 100MB. Unique filenames for redo log files are Oracle generated (eg. ora_1_wo94n2xi.log) and is displayed in alertSID.log.

• in the above case two groups with two members each will be created.

Groups can be added without any file specifications:

ALTER DATABASE ADD LOGFILE;

• adds a new log file group with 2 members located in the direcectories specified by parameters. DB_CREATE_ONLINE_LOG_DEST_n.

Dropping a Group:

ALTER DATABASE DROP LOGFILE GROUP 3;

Archieved Redo Log Files: Can't be OMF.

ARCHIVED REDO LOG FILES:

Advantages of running database in ARCHIVELOG mode and archiving redo log files:

• Recovery: a database backup with online & archived redo log files can guarantee recovery of all commited transactions.

• Backup: this can be done while the database is open.

By default the database is created in NOARCHIVELOG mode. The redo log files are overwritten each time an online redo log file is filled & log switch occurs. The overwriting occurs only after the checkpoint for that group is completed.

In ARCHIVEMODE inactive groups of filled online redo log files must be archived. Since all the changes to database are recorded in online redo log files, so the DBA can use physical backup and the archived redo log files to recover database without losing any commited data.

Online redo log files can be archived either manually or automaticallydepending on the initialization parameter LOG_ARCHIVE_START :

• When successfully archived ( manually or automatically by ARCn)

• an entry is made in the control file

• Records : archive log name, log sequence number, and high & low SCN

• filled redo log can't be reused until a checkpoint has taken place and the file is archived by ARCn.

• Can be multiplexed.

• Maintained by DBA

OEM : Database->Instance -> Configuration-> Recovery Tab->mode,filename format, destination directory

Views	V$LOG, V$LOGFILE

SQL>desc v$log;

Name Null? Type

----------------------- -------- --------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE

SQL> SELECT group#, sequence#, bytes, members, status from v$log;

GROUP# SEQUENCE# BYTES MEMBERS STATUS

---------- ---------- ---------- ---------- ----------------

1 0 104857600 1 UNUSED

2 17 104857600 1 CURRENT

3 15 104857600 1 INACTIVE

SQL> desc v$logfile;

Name Null? Type

----------------------------------------- -------- --------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)

SQL> select group#, status, type, substr(member,1,50) from v$logfile;

GROUP# STATUS TYPE SUBSTR(MEMBER,1,50)

---------- ------- ------- ----------------------------------
3 STALE ONLINE D:\ORACLE\ORADATA\CHIK\REDO03.LOG
2 ONLINE D:\ORACLE\ORADATA\CHIK\REDO02.LOG
1 ONLINE D:\ORACLE\ORADATA\CHIK\REDO01.LOG

posted by sk @ 4:50 AM 0 comments