Tuesday, July 04, 2006

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!



Comments: Post a Comment

Links to this post:

Create a Link



<< Home
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