Tuesday, July 04, 2006

Password authentication setup: OS v/s password file

Operating system authentication setup:

For NT:
REMOTE_LOGIN_PASSWORDFILE  allowed values:

none

  • Disallows remote administration

shared

  • More than one instance can use the password file.
  • Only SYS user is recognized by the password file.
  • Only SYS can logon remotely to perform system administration.
exclusive
  • Only one instance can use the password file and it contains names other than SYS.
  • Indicates that a password file exists and user/password combination in this file can 
    log into Oracle remotely and administer the instance.
  • DBA can use the create user command in Oracle to create user who are added to the
    password file, and grant sysoper & or sysdba privileges to them.
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


******


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?]


******



Creating & restoring Oracle Database Manually

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)

5. Starting up Oracle Server in no mount mode:

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)

6. Creating database:

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>

CONNECT SYSTEM/MANAGER;

@D:\Oracle\ora90\sqlplus\admin\PUPBLD.SQL; 
BACKING UP / CLONING an existing Database :

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

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Response: Database altered

Result : control file creation script backed up at USER_DUMP_DEST\ORAxxxxx.TRC

************************************************
Dump file D:\Oracle\admin\chik\udump\ORA02144.TRC
Wed Jul 05 11:06:28 2006
ORACLE V9.0.1.1.1 - Production vsnsta=0
vsnsql=10 vsnxtr=3
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Personal Oracle9i Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Instance name: chik
Redo thread mounted by this instance: 1
Oracle process number: 12
Windows thread id: 2144, image: ORACLE.EXE

*** SESSION ID:(7.3) 2006-07-05 11:06:28.000
Evaluating checkpoint for thread 1 sequence 22 block 177
*** 2006-07-05 11:06:29.000
KCRA: start recovery buffer claims
*** 2006-07-05 11:06:29.000
KCRA: buffers claimed = 13/13, eliminated = 0
*** 2006-07-05 11:07:04.000
Registered presentation http://admin on database startup
*** 2006-07-05 11:07:33.000

# The following commands will create a new control file and use it
# to open the database.

# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CHIK" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 170
LOGFILE
GROUP 1 'D:\ORACLE\ORADATA\CHIK\REDO01.LOG' SIZE 100M,
GROUP 2 'D:\ORACLE\ORADATA\CHIK\REDO02.LOG' SIZE 100M,
GROUP 3 'D:\ORACLE\ORADATA\CHIK\REDO03.LOG' SIZE 100M
# STANDBY LOGFILE

DATAFILE
'D:\ORACLE\ORADATA\CHIK\SYSTEM01.DBF',
'D:\ORACLE\ORADATA\CHIK\UNDOTBS01.DBF',
'D:\ORACLE\ORADATA\CHIK\CWMLITE01.DBF',
'D:\ORACLE\ORADATA\CHIK\DRSYS01.DBF',
'D:\ORACLE\ORADATA\CHIK\EXAMPLE01.DBF',
'D:\ORACLE\ORADATA\CHIK\INDX01.DBF',
'D:\ORACLE\ORADATA\CHIK\TOOLS01.DBF',
'D:\ORACLE\ORADATA\CHIK\USERS01.DBF'
CHARACTER SET WE8MSWIN1252
;

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

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

# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\CHIK\TEMP01.DBF' REUSE;

# End of tempfile additions.
#

************************************************

SQL >

SHUTDOWN NORMAL; [or IMMEDIATE]

note: don't use shutdown abort

DOS>

Copy all datafiles & redo log files to an alternate filesystem location

Restoring a backed up Database: (Recreating control file): 
  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
     MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 170
LOGFILE
    GROUP 1 'D:\Oracle\oradata\chik\REDO01.LOG'  SIZE 100M,
    GROUP 2 'D:\Oracle\oradata\chik\REDO02.LOG' SIZE 100M,
    GROUP 3 'D:\Oracle\oradata\chik\REDO03.LOG' SIZE 100M
Rem STANDBY LOGFILE
DATAFILE
    'D:\Oracle\oradata\chik\SYSTEM01.DBF',
    'D:\Oracle\oradata\chik\UNDOTBS01.DBF',
    'D:\Oracle\oradata\chik\CWMLITE01.DBF',
    'D:\Oracle\oradata\chik\DRSYS01.DBF',
    'D:\Oracle\oradata\chik\EXAMPLE01.DBF',
    'D:\Oracle\oradata\chik\INDX01.DBF',
    'D:\Oracle\oradata\chik\TOOLS01.DBF',
    'D:\Oracle\oradata\chik\USERS01.DBF'
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;


Done!



Redo Log Files

Online Redo log file GROUPS:


ADDING

ALTER DATABASE ADD LOGFILE GROUP 3

('ORA_DIR\log3a.rdo', 'ORA_DIR\log3B.rdo') SIZE 10M;

DROPPING

ALTER DATABASE DROP LOGFILE GROUP 3;

Online Redo log file group Members:


ADDING

ALTER DATABASE ADD LOGFILE MEMBER

'ORA_DIR\log1d.rdo' TO GROUP 1,

'ORA_DIR\log2d.rdo' TO GROUP 2,

'ORA_DIR\log3d.rdo' TO GROUP 3;

DROPPING

ALTER DATABASE DROP LOGFILE MEMBER

'ORA_DIR\log3d.rdo';

RENAMING / RELOCATING Redo log files

ALTER DATABASE CLEAR LOGFILE 'new_ilename';

ALTER DATABASE RENAME FILE 'filename' TO 'new_filename;

Views:

V$LOG , V$LOGFILE

Parameter

MAXLOGFILES, MAXLOGMEMBERS, DB_CREATE_ONLINE_LOG_DEST_n,
LOG_ARCHIVE_START (ARCHIVELOG, NOARCHIVELOG)


Structure of Redo log files:

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.

Checkpoint: During a checkpoint DBWn writes dirty databse buffers, that are covered by the logs that is being checkpointed, to the 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']...)
}
]....

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:

OEM : Database->Storage ->Redo Log Groups-> [right click] remove -> confirm

Dropping Online Redo Log file members:

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

Restrictions:

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

Relocating/Renaming Online Redo Log files:

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:

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

Groups can be added without any file specifications:

ALTER DATABASE ADD LOGFILE;

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:

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 :

TRUE

Indicates archiving is automatic. ARCn initiates archiving of the filled log groups at eveyr log switch.

FALSE

Default value. Disables. DBA must manually execute a command to back up redo log files each time. All or specific redo log files can be archived manually.

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

Status

Description

UNUSED

indicates that the online redo log group has never been written to. This is the state when an online redo log file is just added.

CURRENT

the current & active online redo log file group.

ACTIVE

implies active but not current. It is required for crash recovery. It may be in use for block recovery. It may or maynot be archived.

CLEARING

Indicates that the log is being recreated as an empty log after an ALTER DATABASE CLEAR LOGFILE command. Once log is cleared, the status changes to UNUSED.

CLEARING_CURRENT

Indicated that the current log is being cleared of a closed thread. The log can stay in this status for long if there is some failure in the switch such as I/O error writing the new log header.

INACTIVE

Indicates it is no longer needed for instance recovery. It may or maynot be archived.

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


Status

Description

INVALID

indicates the file is inaccessable.

STALE

Indicates the contents of the file are incomplete.

DELETED

The file is no longer used.

Blank

implies file is in use.


Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google