steps for database creation manually in oracle

->in this example i assume database name is demo

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

Step 1: Specify an Instance Identifier (SID)

Decide on a unique Oracle system identifier (SID) for your instance, open a command window, and set the ORACLE_SID environment variable.

$export ORACLE_SID=demo

Step 2: Ensure That the Required Environment Variables Are Set

For example, on most platforms, ORACLE_SID and ORACLE_HOME must be set. In addition, it is advisable to set the PATH variable to include theORACLE_HOME/bin directory

$export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

$export PATH=$PATH:$ORACLE_HOME/bin

Step 3: Choose a Database Administrator Authentication Method

·         With a password file

·         With operating system authentication

To be authenticated with a password file, create the password file

$orapwd FILE=orapworcl password=****** ENTRIES=30

 To be authenticated with operating system authentication, ensure that you log in to the host computer with a user account that is a member of the appropriate operating system user group. On the UNIX and Linux platforms, for example, this is typically the dba user group.

Step 4: Create the Initialization Parameter File

If you create the initialization parameter file manually, ensure that it contains at least the parameters listed in . All other parameters not listed have default values.

Parameter Name	Mandatory	Notes
DB_NAME	Yes	Database identifier. Must correspond to the value used in the CREATE DATABASE statement. Maximum 8 characters.
CONTROL_FILES	No	Strongly recommended. If not provided, then the database instance creates one control file in the same location as the initialization parameter file. Providing this parameter enables you to multiplex control files. Ex: CONTROL_FILES = (/u01/oracle/demo/control01.ctl,                  /u02/oracle/demo/control02.ctl,                  /u03/oracle/demo/control03.ctl)
MEMORY_TARGET	No	Sets the total amount of memory used by the instance and enables automatic memory management. You can choose other initialization parameters instead of this one for more manual control of memory usage. See "Configuring Memory Manually".

Example:

*.audit_file_dest='/u01/app/oracle/admin/demo/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/demo/control01.ctl','/u01/app/oracle/flash_recovery_area/demo/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='demo'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4039114752

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=856686592

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.resource_limit=TRUE

*.undo_tablespace='UNDOTBS1'

Step 5: (Windows Only) Create an Instance

On the Windows platform, before you can connect to an instance, you must manually create it if it does not already exist. The ORADIM command creates an Oracle instance by creating a new Windows service.

>oradim -NEW -SID sid -STARTMODE MANUAL -PFILE pfile

Step 6: Connect to the Instance

To authenticate with a password file, enter the following commands, and then enter the SYS password when prompted:

$ sqlplus /nolog

SQL> CONNECT SYS AS SYSDBA

To authenticate with operating system authentication, enter the following commands:

$ sqlplus /nolog

SQL> CONNECT / AS SYSDBA

Step 7: Create a Server Parameter File

SQL>CREATE SPFILE FROM PFILE;

Step 8: Start the Instance

SQL> STARTUP NOMOUNT

Step 9: Issue the CREATE DATABASE Statement

CREATE DATABASE demo

   USER SYS IDENTIFIED BY oracle

   USER SYSTEM IDENTIFIED BY oracle

   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/demo/redo1a.log') SIZE 64M REUSE,

           GROUP 2 ('/u01/app/oracle/oradata/demo/redo2a.log') SIZE 64M REUSE

   MAXLOGFILES 32

   MAXLOGMEMBERS 3

   MAXLOGHISTORY 1

   MAXDATAFILES 200

   CHARACTER SET AL32UTF8

   NATIONAL CHARACTER SET AL16UTF16

   EXTENT MANAGEMENT LOCAL

   DATAFILE '/u01/app/oracle/oradata/demo/system01.dbf' SIZE 325M REUSE

   SYSAUX

   DATAFILE '/u01/app/oracle/oradata/demo/sysaux01.dbf' SIZE 128M REUSE

   DEFAULT TABLESPACE users

   DATAFILE '/u01/app/oracle/oradata/demo/users01.dbf'  SIZE 32M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

   DEFAULT TEMPORARY TABLESPACE tempts1

   TEMPFILE '/u01/app/oracle/oradata/demo/temp01.dbf' SIZE 32M REUSE

   UNDO TABLESPACE undotbs1

   DATAFILE '/u01/app/oracle/oradata/demo/undotbs01.dbf' SIZE 128M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Step 11: Run Scripts to Build Data Dictionary Views

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?/sqlplus/admin/pupbld.sql

Script	Description
CATALOG.SQL	Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
CATPROC.SQL	Runs all scripts required for or used with PL/SQL.
PUPBLD.SQL	Required for SQL*Plus. Enables SQL*Plus to disable commands by user.

transportable tablespace example

-          Create tablespace

Sql> create tablespace tts datafile ‘/u01/app/oracle/oradata/orcl/tts01.dbf’ size 100M;

Create user with tts as default tablespace

Sql> create user demouser identified by demouser default tablespace tts;

Sql>grant connect,resource to demouser;

Connect to user:

Sql>conn demouser

Password:*******

Create table

Sql>create table demotable(num number);

Inset some rows into the table.

Exporting:

Put the tablespace into read only mode

Sql>alter tablespace tts read only;

Export using transportable tablespace

$exp file=/u01/backup/tts.dmp tablespaces=tts transport_tablespace=y;

Username: system as sysdba

Password:*******

-copy the tablespace datafiles to backup location

Importing:

-copy the datafiles to to the location where ever you want

-import dumpfile

$imp file=/u01/backup/tts.dmp transport_tablespace=y datafiles=’/u01/app/oracle/tts01.dbf’

Username: system as sysdba

Password:*******

-Check the tablespace in database

Sql>select name from v$tablespace;

check table in user

  

sql> conn ttsuser

password:ttsuser

sql>select * from tab;

user creation in oracle tips

User management:

DBA_USERS	describes all users of the database
DBA_TS_QUOTAS	describes tablespace quotas for all users
V$SESSION	lists session information for each current session

                   

                         All users need a default tablespace, this is where all objects created by the user will be stored and a temporary tablespace which is where they perform work such as sorting data during SQL execution. Make sure that you assign the tablespaces as on some systems they could end up using the system tablespace which is not a good idea.

Creating users:

Prerequisites

You must have the CREATE USER system privilege. When you create a user with the CREATE USER statement, the user's privilege domain is empty. To log on to Oracle Database, a user must have the CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION system privilege.

Creating user syntax:

Sql> create user <username> identified by <password>

The above command creats a user with default tablespace as users and default temporary table space temp(in 10g and 11g), find the example bellow;

Sql> create user sample identified by sample;

For above user default tablespace will be default permanent tablespace of the database, temporary tablespace is TEMP and profile will be DEFAULT profile

To know database default tablespace:

SQL> select property_value from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE

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

USERS

If we need to change database default tablespace execute following

SQL> alter database default tablespace users;

Database altered.

EXTERNALLY Clause

Specify EXTERNALLY to create an external user. Such a user must be authenticated by an external service, such as an operating system or a third-party service. In this case, Oracle Database relies on authentication by the operating system or third-party service to ensure that a specific external user has access to a specific database user.

DEFAULT TABLESPACE Clause and TEMPORARY TABLESPACE clause:

SQL>  create user sample identified by sample default tablespace test temporary tablespace temp;

User created.

if we want to check it

SQL> select default_tablespace,temporary_tablespace from dba_users where username='SAMPLE';

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE

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

TEST                             TEMP

PROFILE clause:

Specify the profile you want to assign to the user. If you omit this clause, then Oracle Database assigns the DEFAULT profile to the user.

PASSWORD EXPIRE Clause:

Specify PASSWORD EXPIRE if you want the user's password to expire. This setting forces the user or the DBA to change the password before the user can log in to the database.

Ex:

SQL> create user sample identified by sample default tablespace test temporary tablespace temp profile test_profile password expire;

User created.

Tablespace quotas:

Use the QUOTA clause to specify the maximum amount of space the user can allocate in the tablespace. A CREATE USER statement can have multiple QUOTA clauses for multiple tablespaces. UNLIMITED lets the user allocate space in the tablespace without bound.

SQL> create user sample identified by sample default tablespace test temporary tablespace temp quota 50M on data;

User created.

Locking a user:

SQL> alter user sample account lock;

User altered.

Unlocking a locked user:

SQL> alter user sample account unlock;

User altered.

Steps to configure physical standy database 11gr2 (backup based)

I have a database installed in server with database name prod

Primary DB_NAME=prod

Primary DB_UNIQUE_NAME=prod

Ip: 192.168.187.137

On physical standby we need to install only oracle software 11gr2, in this example standby database name will be stdby

Standby DB_NAME=prod

Standby DB_UNIQUE_NAME=stdby

Ip:192.168.187.132

Required parameters

DB_NAME                                                               -must be same on all standby databases

DB_UNIQUE_NAME                                            -must be different primaey and all standby databases

LOG_ARCHIVE_CONFIG                                 -this parameter includes db_unique_name of primary

                                                                                    And all standby databases

LOG_ARCHIVE_DEST_n                                 -define local and standby servers archive log location

LOG_ARCHIVE_DEST_STATE_n                 -defines the status ENABLE or DIFFER

REMOTE_LOGIN_PASSWORDFILE             -must be EXCLUSIVE

FALL_SERVER                                                     -use for acknowledge gap resolution( required in                    

                                                                                     Standby server only

DB_FILE_NAME_CONVERT                              - it will convert the datafiles location from primary to

                                                                                      Standby datafiles structure(if you have different 

                                                                                       File location in standby)

LOG_FILE_NAME_CONVERT                              -it will convert redo log file locations in primary to

                                                                                         Standby database (if you have different 

                                                                                        File location in standby)

STANDBY_FILE_MANAGEMENT                        -to keep auto file creation in standby

Steps to perform in primary server

Make sure database in archive log mode:

Sql> archive log list;

If not put it in archive log mode

SHUT IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

Make sure database in force logging mode

Sql> select force_logging from v$database;

Sql>alter database force logging;

Check DB_NAME and DB_UNIQUE_NAME of primary database

Sql>show parameter db_name

Sql>show parameter db_unique_name

Configure listener.ora and tnsnames.ora in primary database for stdby and prod;

$netca (or) netmgr

Make db_unique name in the part of the datagaurd

Sql>alter system set LOG_ARCHIVE_CONFIG= ‘DG_CONFIG=(orcl,stdby)’;

Set archivelog destination

Sql>Alter System Set LOG_ARCHIVE_DEST_2=’SERVICE=stdby LGWR SYNC VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=stdby’;

Sql>alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Set remote login password to exclusive

Sql>alter system set remote_login_passwordfile=exclusive scope=spfile;

Set fal server and file name convert parameter incase if  directory structure is different in primary and standby database

Sql>alter system set fal_server=stdby;

Sql>alter system set standby_file_management=auto;

If you want to change directory structure, in this example we are using same directory structure in both primary and standby so we are ignoring this parameter 

Sql> alter system set db_file_name_convert=’stdby’,’prod’

Sql> alter system set log_file_name_convert=’stdby’,’prod’

Check the log  members

Sql>select member from v$logfile;

-Now configure services prod

$vi tnsnames.ora

prod =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.187.137)(PORT = 1521))

    )

    (CONNECT_DATA =

      (ORACLE_SID = prod)

    (UR=A)

    )

  )

Now backup primary database using rman

mkdir -p /u01/backup/

$rman target=/

RMAN>backup database format='/u01/backup/';

Now create standby controlfile and pfile;

Sql>alter database create standby controlfile as ‘/u01/backup/stdcontrol.ctl’;

Sql>create pfile=’/u01/backup/initstdby.ora’ from spfile;

Add standby log files

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/prod/std_redo01.log’) size 50M;

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/prod/std_redo02.log’) size 50M;

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/prod/std_redo03.log’) size 50M;

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/prod/std_redo04.log’) size 50M;

copy the pfile, password file, standby controlfile and backups to physical standby (/u01/backup):

$scp /u01/backup/* oracle@192.168.187.132:/u01/backup/

On physical standby:

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

create reqiored directories by checking in pfile(adump,controlfile locations… etc)

$mkdir –p  /u01/app/oracle/fast_recovery_area/stdby/

$mkdir –p  /u01/app/oracle/oradata/stdby/

$mkdir –p  /u01/app/oracle/admin/stdby/adump/

copy standby controlfile to all locations

$cp /u01/backup/stdcontrol.ctl /u01/app/oracle/oradata/stdby/control01.ctl

$cp /u01/backup/stdcontrol.ctl /u01/app/oracle/fast_recovery_area/stdby/control02.ctl

Copy parameter file

$scp /u01/backup/initstdby.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstdby.ora

Copy remote login password file

$cp /u01/backup/orapworcl /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstdby

Modify initstdby.ora file

$vi initstdby.ora

Replace prod with stdby

:%s/prod/stdby/g

and modify following

fal_server=prod

db_unique_name=’stdby’
log_archive_dest_2=’SERVICE=prod LGWR SYNC VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=prod’;

If you want to change directory structure, in this example we are using same directory structure in both primary and standby

db_file_name_convert=’prod’,’stdby’

log_file_name_conver=’prod’,’stdby’

update /etc/oratab

vi /etc/oratab

stdby: /u01/app/oracle/product/11.2.0/dbhome_1:N

:wq

Create service for prod

$netmgr

Start listener

$lsnrctl start

Restore backup on standby

if you copied backups to different location apart from '/u01/backup'  we have to catalog the backups as below.

RMAN> catalog start with '<backup_location>'

Restore backupfile

$rman target=/

RMAN>startup mount

RMAN>restore database;

now check  standby redo logs

sql>select member from v$logfile;

Add standby log files in standby

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/stdby/std_redo01.log’) size 50M;

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/stdby/std_redo02.log’) size 50M;

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/stdby/std_redo03.log’) size 50M;

Sql>alter database add standby logfile (‘/u01/app/oracle/oradata/stdby/std_redo04.log’) size 50M;

now start redo apply process on standby

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

To stop redo apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

For active datagaurd (form 11g)

Sql>startup

Sql>alter database recover managed standby database disconnect from session;

check the active datagaurd status in standby

sql>select open_mode from v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

If we want to change protection modes

In primary

Sql> alter database set standby to maximize availability/protection/performance;

Queries for standby database:

SQL> select process, status from v$managed_standby;

PROCESS   STATUS

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

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

RFS       IDLE

RFS       IDLE

RFS       IDLE

MRP0      WAIT_FOR_LOG

SQL> select SEQUENCE#, applied from v$archived_log;

 SEQUENCE# APPLIED

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

         8 YES

         9 YES

         7 YES

        10 YES

        11 YES

        12 YES

        13 YES

        14 YES

        15 YES

SQL> select protection_mode from v$database;

PROTECTION_MODE

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

MAXIMUM AVAILABILITY

SQL> select * from v$archive_gap;

no rows selected