Database Manual Creation.

 Database Manual Creation

           Dear Friends follow the below steps for Database manual creation means without using DBCA command. Follow the below step by step without fail and change the file system as per your requirement.

           Install the Oracle Home.

For Installation of Oracle Home below steps to be followed.

Ø  Go to the DUMP location and click on setup.exe

 Should maintain the above directory structure.

Ø  Click NEXT…….

Ø   Click NEXT…..

Ø   Click on Install….

Ø  Wait For Some time to be finished.

Ø  Click on Exit.

Now Oracle Home is Ready to use. Based on this Oracle HOME we are going to be created Database manually. Below are steps for Database Manual Creation.

Manual DB Creation

Don’t for get to open the cmd prompt as Administrator.

Click on Start ->cmd ->Run as Administrator

ü  C:\>set ORACLE_HOME=E:\app\Administrator\product\11.1.0\db_1

ü  C:\>set PATH=%ORACLE_HOME%;%PATH%

ü  C:\>set ORACLE_SID=ORACLE

Create below DUMP Directories required …………

ü  C:\>mkdir E:\app\Administrator\admin\adump

ü  C:\>mkdir E:\app\Administrator\admin\dpdump

ü  C:\>mkdir E:\app\Administrator\admin\pfile

ü   C:\>mkdir E:\app\Administrator\diag

ü   C:\>mkdir E:\app\Administrator\flash_recovery_area

ü   C:\>mkdir E:\app\Administrator\oradata

ü  C:\>mkdir E:\app\Administrator\oradata\ORACLE

=======================================

Create the parameter file (Pfile)

=========================================

below are minimum requirement for  DB creation. Based on the oracle version we have to change compatible ='11.1.0'.In this scenario our oracle version 11.1.0. & make sure that file extension is initoracle.ora

db_name='ORACLE'

db_block_size=8192

memory_target=500m

processes=100

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

compatible ='11.1.0'

audit_trail ='db'

db_recovery_file_dest_size=5g

db_recovery_file_dest='E:\APP\ADMINISTRATOR\flash_recovery_area'

audit_file_dest='E:\APP\ADMINISTRATOR\admin\adump'

diagnostic_dest='E:\APP\ADMINISTRATOR\diag'

control_files = ('E:\APP\ADMINISTRATOR\oradata\control1.ctl', 'E:\APP\ADMINISTRATOR\oradata\control2.ctl', 'E:\APP\ADMINISTRATOR\oradata\control3.ctl')

========================================================================

=======================

Create a Windows service

==============================

C:\>oradim –new –sid oracle  startmode auto 

Instance created.

C:\>sc query oracleserviceORACLE

•  Connect to instance and create SPFILE

C:\>sqlplus

SQL> create spfile from pfile=’ E:\app\Administrator\admin\pfile\initoracle.ora’;

SQL> starup nomount;

ORACLE instance started.

Total System Global Area  523108352 bytes

Fixed Size                  1375704 bytes

Variable Size             314573352 bytes

Database Buffers          201326592 bytes


Redo Buffers                5832704 bytes

==========================================
 Execute the CREATE DATABASE Command

==========================================

CREATE DATABASE oracle
    USER sys IDENTIFIED BY cloud12c@
    USER system IDENTIFIED BY cloud12c@
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 200
    MAXINSTANCES 1
    MAXLOGHISTORY 500
LOGFILE
GROUP 1 ('E:\app\Administrator\oradata\oracle\redo01.log','E:\app\Administrator\oradata\oracle\redo02.log') SIZE 50M,
GROUP 2 ('E:\app\Administrator\oradata\oracle\redo03.log','E:\app\Administrator\oradata\oracle\redo04.log') SIZE 50M,
GROUP 3 ('E:\app\Administrator\oradata\oracle\redo05.log','E:\app\Administrator\oradata\oracle\redo06.log') SIZE 50M
DATAFILE 'E:\app\Administrator\oradata\oracle\system01.dbf' SIZE 300M EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 'E:\app\Administrator\oradata\oracle\sysaux01.dbf' SIZE 200M
UNDO TABLESPACE UNDOTBS1 DATAFILE 'E:\app\Administrator\oradata\oracle\undotbs01.dbf' SIZE 300M AUTOEXTEND OFF
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'E:\app\Administrator\oradata\oracle\temp01.dbf' SIZE 200M REUSE AUTOEXTEND OFF
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET UTF8;

========================

Create data dictionary objects

========================

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

SQL> connect system/system

SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

Please share your valuable feedback....

Create New Database Using DBCA With Silent Option

Create New Database Using DBCA With Silent Option

Below are the steps for how to create Database Manual.

• Make sure you have no existing database entry in /etc/oratab file for the SID you intend to create
• Make sure you have no initSID.ora under the $ORACLE_HOME/dbs
• Construct the dbca command line:
• Make sure you have no existing Listener & TNS entries.
• Make to be created all the directory structure as required.

How to Configuring the FRA

Configuring the FRA is easy,by default Flash Recovery area is ORACLE_BASE.First you create the base directory of the FRA from the OS, and then you set the following parameters in any database that will use the FRA:

 

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

The DB_RECOVERY_FILE_DEST parameter defines the FRA base directory location. This

is the only directory you will need to create when configuring the FRA. You will need to

make sure that this directory is owned by the owner of the Oracle executable so that Oracle

can create other subdirectories beneath it.

The DB_RECOVERY_FILE_DEST_SIZE parameter defines the total amount of space for this

database instance is allowed to consume in the FRA. This is a logical limit, which can be

greater than or less than the actual physical limit of space on that device. For example, you

may have a file system with 500GB of space available on it. However, you may want to

indicate that your database can consume only up to 100GB of space within the FRA while

assigning the FRA to the 500GB file system.

You use the alter system command to configure the FRA. Note that DB_RECOVERY_

FILE_DEST is not dynamic, while DB_RECOVERY_FILE_DEST_SIZE is dynamic. To configure the FRA, do the following:

1. Create the base FRA directory:

E:\>mkdir \oracledb\flashback

E:\>

2. Log into SQL*Plus:

E:\>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 25 15:53:42 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: sys@test as sysdba

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Now use the alter system command to set the parameter DB_RECOVERY_FILE_DEST to

\oracledb\flashback and DB_RECOVERY_FILE_DEST_SIZE to 400GB.

sql>alter system set db_recovery_file_dest_size=400GB;

sql>alter system set db_recovery_file_dest=’ \oracledb\flashback’ scope=spfile;

4. Now shut down and restart the database. Once the database has been restarted, the

FRA will become operational.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2538741760 bytes

Fixed Size                  2257872 bytes

Variable Size             889195568 bytes

Database Buffers         1627389952 bytes

Redo Buffers               19898368 bytes

Database mounted.

Database opened.

SQL>

HOW TO CONFIGURE RMAN

HOW TO  CONFIGURE RMAN:

1.      Start RMAN from the command line:

E:\app\Administrator\product\11.2.0\dbhome_1\BIN>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jan 25 16:11:39 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ASM (DBID=1255844286)

RMAN>

2. Display your RMAN configuration (yours may look different than our output—

that’s okay):

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE

TYPE DISK TO ‘%F’; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP

TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF;

CONFIGURE ENCRYPTION ALGORITHM ‘AES128’;

CONFIGURE COMPRESSION ALGORITHM ‘zlib’;

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘E:\app\Administrator\product\11.2.0\dbhome_1\DATABASE\SNCFORCL.ORA’; # default

3. Configure the retention policy to redundancy of 2:

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

old RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

new RMAN configuration parameters are successfully stored

4. Configure control-file autobackups on:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;

old RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

5. Configure for compressed backup sets:

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP

TYPE TO COMPRESSED BACKUPSET;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE

TO COMPRESSED BACKUPSET;

new RMAN configuration parameters are successfully stored

6. Create a directory to hold the snapshot control file (you will want to use your own directory paths, of course):

RMAN> host “mkdir \rman”;

host command complete

RMAN> host “mkdir \rman\snapshot”;

host command complete

7. Configure RMAN so the snapshot control file will be created in the new directory:

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘E:\rman\snapshot’;

new RMAN configuration parameters:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘E:\rman\snapshot’;

new RMAN configuration parameters are successfully stored

8. Exit RMAN:

RMAN> exit

Recovery Manager complete.

HOW TO RECOVER REDOLOGS

If You have lost all your online redo logs. As a result, your database has crashed. You have

tried to restart the database and clear the online redo log files, but when you try to open the

database you get the following error.

SQL> startup

ORACLE instance started.

Total System Global Area 167395328 bytes

Fixed Size 1298612 bytes

Variable Size 142610252 bytes

Database Buffers 20971520 bytes

Redo Buffers 2514944 bytes

Database mounted.

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02a.log’

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02.log’

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> alter database clear logfile group 2;

alter database clear logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)

ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02.log’

ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02a.log’

Steps for Recovery.

11)      Restore the last full database backup.

22)      Restore all archived redo logs generated during and after the last full database backup.

33)      Issue the Startup Mount command to mount the database.

44)      Perform a point-in-time recovery, applying all archived redo logs that are available.

55)      Open the database using the alter database open resetlogs command.

ORA-03113: end-of-file on communication channel Process ID: 1380 Session ID: 191 Serial number: 3 When archive destination is full

Delete Archivelog Using RMAN in Oracle

SQL> startup
ORACLE instance started.

Total System Global Area 1636814848 bytes
Fixed Size 2176248 bytes
Variable Size 1140853512 bytes
Database Buffers 486539264 bytes
Redo Buffers 7245824 bytes

Database mounted.

ORA-03113: end-of-file on communication channel
Process ID: 1380
Session ID: 191 Serial number: 3

Check in Alert Log file …….

NOTE: initiating MARK startup

Starting background process MARK

Mon Jan 06 16:41:54 2014

MARK started with pid=19, OS id=2952

NOTE: MARK has subscribed

Mon Jan 06 16:41:54 2014

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

ORACLE_BASE from environment = E:\app\Administrator

Mon Jan 06 16:41:56 2014

ALTER DATABASE MOUNT /* db agent *//* {0:1:28} */

NOTE: Loaded library: System

SUCCESS: diskgroup DATA was mounted

NOTE: dependency between database ASM and diskgroup resource ora.DATA.dg is established

Successful mount of redo thread 1, with mount id 3855157908

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT /* db agent *//* {0:1:28} */

ALTER DATABASE OPEN /* db agent *//* {0:1:28} */

LGWR: STARTING ARCH PROCESSES

Mon Jan 06 16:42:02 2014

ARC0 started with pid=26, OS id=8080

ARC0: Archival started

ARC0: Becoming the 'no FAL' ARCH

ARC0: Becoming the 'no SRL' ARCH

LGWR: STARTING ARCH PROCESSES COMPLETE

ARCH: Error 19504 Creating archive log file to 'E:\ARCHIVE\ARC0000003106_0832442370.0001'

ARC0: Error 19504 Creating archive log file to 'E:\ARCHIVE\ARC0000003107_0832442370.0001'

Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\asm\asm\trace\asm_ora_1500.trc:

ORA-16038: log 1 sequence# 3106 cannot be archived

ORA-19504: failed to create file ""

ORA-00312: online log 1 thread 1: '+DATA/asm/onlinelog/group_1.261.832442371'

USER (ospid: 1500): terminating the instance due to error 16038

Mon Jan 06 16:42:04 2014

System state dump requested by (instance=1, osid=1500), summary=[abnormal instance termination].

System State dumped to trace file E:\APP\ADMINISTRATOR\diag\rdbms\asm\asm\trace\asm_diag_1608.trc

Dumping diagnostic data in directory=[cdmp_20140106164204], requested by (instance=1, osid=1500), summary=[abnormal instance termination].

Mon Jan 06 16:42:11 2014

Instance terminated by USER, pid = 1500

SQL>Shutdown Immediate;

SQL> startup mount

Then follow the below steps….

SQL> HOST

E:\app\Administrator\product\11.2.0\dbhome_1\BIN>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jan 6 17:36:48 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ASM (DBID=1255844286)

RMAN>

Archivelog List Commands

RMAN>list archivelog all;
RMAN>list copy of archivelog until time ‘SYSDATE-10′;
RMAN>list copy of archivelog from time ‘SYSDATE-10′
RMAN>list copy of archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>list copy of archivelog from sequence 1000;
RMAN>list copy of archivelog until sequence 1500;
RMAN>list copy of archivelog from sequence 1000 until sequence 1500;

Archivelog Delete Commands

RMAN>delete archivelog all;
RMAN>delete archivelog until time ‘SYSDATE-10′;
RMAN>delete archivelog from time ‘SYSDATE-10′
RMAN>delete archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>delete archivelog from sequence 1000;
RMAN>delete archivelog until sequence 1500;
RMAN>delete archivelog from sequence 1000 until sequence 1500;

Note : Also, you can use noprompt statement for do not yes-no question.
 
RMAN>delete noprompt archivelog until time ‘SYSDATE-10′;

RMAN> quit

Recovery Manager complete.

E:\app\Administrator\product\11.2.0\dbhome_1\BIN>sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 6 17:39:15 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL>alter database open;

Now Issue is solved…..

Please share your valuable feedback.