Oracle Database Creation: A Manual Approach

So here are we back on track again. After taking a short break from the regular course on installation, let us continue with the database creation section.

We have already gone through the Oracle Installation Part in the previous blog. If you have missed it. Kindly go through these links:

Installation Guide Using OUI- Graphical way

Installation guide Using Silent Installation: Command-line Way

Done with the Oracle Software Installation the next steps comes up as creating the database, configuring listeners and tnsnames and other network and security options.

Lets start with the Database Creation Part and then follow the others.

Well first and foremost question that must have aroused in your mind is that why go on manually creating the database rather than using the DBCA which is the best and the most easy way on creating and configuring the database. Well the answer lies within the past blogs only. As I said DBCA can be best while accessing the GUI and while the parameters can also be set in responseFile during the SILENT installation, its always a good practice to create the Database Manually after the software installation as it gives more control over the parameters than the other tools.

In the real scenario, the database needs to be customized according to the environment and the customer needs. Here manually creating the DB can give you edge over the DBCA. Also being a DBA you need to know how and what are the essentials for the DB creation.

Done with all the blah blah.. Lets get straight to the point of DB Creation.

The most important part for the DB instance creation is PFILE or Server Parameter File. This file defines all the instance related parameters like the control file names with destination, destination to store various logs and trace files, instance name, DB name, Memory Related parameters like SGA and PGA, buffer sizes and cache sizes. Well better seen than just reading. Here is a sample PFILE for starting an Oracle 10g instance.

//**************************************************************************

*.aq_tm_processes=1
*.archive_lag_target=900
*.background_dump_dest='/oracle/admin/prtp/bdump'
*.compatible='10.2.0.2.0'
*.control_files='/oradata1/prtp/control-file/control01.ctl','/oradata2/prtp/control-file/control02.ctl','/oradata3/prtp/control-file/control03.ctl'
*.core_dump_dest='/oracle/admin/prtp/cdump'
*.cursor_sharing='SIMILAR'
*.cursor_space_for_time=TRUE
*.db_block_size=8192
*.db_cache_size=4838860800
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_flashback_retention_target=0
*.db_name='prtp'
*.event='10511 trace name context forever, level 2'
*.fast_start_mttr_target=600
*.instance_name='prtp'
*.java_pool_size=152428800
*.job_queue_processes=10
*.large_pool_size=652428800
*.log_archive_dest='/archive/archive-logs/'
*.log_archive_format='arc_%t_%s_%r.arc'
*.open_cursors=5000*.open_links_per_instance=30
*.open_links=30
*.pga_aggregate_target=22619430400
*.processes=3250
*.query_rewrite_enabled='TRUE'
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=FALSE
*.sessions=3500
*.sga_max_size=24294967296
*.sga_target=24294967296
*.shared_pool_size=719430400
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=12000
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/prtp/udump'
//**************************************************************************

Please note this is just sample file and the parameter values may differ depending upon the user requirements.

You need to store this file at the location: $ORACLE_HOME/dbs/initprtp.ora

While creating the spfile it should be kept in mind that the file name should be created and saved with the instance name in the format: init<ORACLE_SID>.ora. This is mandatory because on the instance STARTUP the Oracle looks for this file in order to start the instance. If not you can find errors during the startup. So its good practice to follow the above recommendation also it becomes easy to manage when there are more than one instance and databases running on the same server (not a good practice in PRODUCTION environment but most the time it is found in the TEST setups).

Once you create the pfile you can start the database in NOMOUNT mode. Note that you have not created the database just the instance hence database cannot be started in MOUNT mode as of now.

bash$ export ORACLE_SID=prtp

bash$ sqlplus "/as sysdba"

sql> STARTUP NOMOUNT;

ORACLE instance started.
 

Total System Global Area 24294967296 bytes
Fixed Size                  1267716 bytes
Variable Size             704645116 bytes
Database Buffers          855638016 bytes
Redo Buffers               15507456 bytes

Once the instance is started you can now create the database. Sample script for the database creation is given below.

//************************

//Database Creation Script
//************************
CREATE DATABASE prtp ARCHIVELOG
MAXLOGFILES 40
MAXLOGMEMBERS 4
MAXDATAFILES 500
MAXINSTANCES 1
MAXLOGHISTORY 2000
DATAFILE '/oradata1/prtp/datafiles/system01.dbf' SIZE 1024M
SYSAUX DATAFILE '/oradata1/prtp/datafiles/sysaux01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
LOGFILE
GROUP 1 ('/oradata1/prtp/redologs/redo01_1.log','/oradata2/prtp/redologs/redo01_2.log') SIZE 512M,
GROUP 2 ('/oradata1/prtp/redologs/redo02_1.log','/oradata2/prtp/redologs/redo02_2.log') SIZE 512M,
GROUP 3 ('/oradata1/prtp/redologs/redo03_1.log','/oradata2/prtp/redologs/redo03_2.log') SIZE 512M,
GROUP 4 ('/oradata1/prtp/redologs/redo04_1.log','/oradata2/prtp/redologs/redo04_2.log') SIZE 512M,
GROUP 5 ('/oradata1/prtp/redologs/redo05_1.log','/oradata2/prtp/redologs/redo05_2.log') SIZE 512M
UNDO TABLESPACE UNDOTBS1
DATAFILE '/oradata3/prtp/datafiles/undotbs01.dbf' SIZE 6144M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
default temporary tablespace temp tempfile '/oradata4/prtp/datafiles/temp01.dbf' SIZE 6144M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M
character set UTF8
national character set UTF8;

The above script will create the database in archive log mode. Keep in mind that from Oracle 10g it is mandatory to specify atleast one redo log group , SYSTEM , SYSAUX and UNDO tablespace. It is a good practice always to specify a default TEMP tablespace because otherwise the SYSTEM tablespace will be used for heavy sorting operations causing performance issues.

You need to keep in mind that although DB can work with one CONTROL file and REDO LOG Group its always good practice to create mirror multiplexed copies of these files on different locations to be on safer side in case of disasters.

Once you create the database, You need to create the internal objects that are required by Oracle for its functioning so you need to run these scripts for the objects creation one by one.

sql> @?/rdbms/admin/catalog.sql
sql> @?/rdbms/admin/catproc.sql
sql> conn system/manager
sql> @?/sqlplus/admin/pupbld.sql

Volllaaa!!!! You are done with the database creation. Now start with creation of your own tablespaces and users to operate the database as per your requirement.

Syntax for Tablespace Creation:

create tablespace <tablespace_name> datafile '<path & filename.dbf>' size <size>m segment space management auto;

Syntax to Create Profile for TEST User
 
      CREATE PROFILE PRO_TEST    LIMIT
   SESSIONS_PER_USER          3500    
   CPU_PER_SESSION            UNLIMITED
   CPU_PER_CALL               UNLIMITED
   FAILED_LOGIN_ATTEMPTS      UNLIMITED ;


Syntax to Create User TEST

      CREATE USER TEST IDENTIFIED BY TEST
   DEFAULT TABLESPACE PRTP_DATA
   TEMPORARY TABLESPACE TMP
   PROFILE PRO_TEST
   ACCOUNT UNLOCK;

Grant Permissions to User TEST user

      GRANT CONNECT TO TEST;
   GRANT RESOURCE TO TEST;
   ALTER USER TEST DEFAULT ROLE ALL;
   GRANT CREATE SESSION TO TEST;
   GRANT UNLIMITED TABLESPACE TO TEST;

Note these are just examples, you can alter the grants as per your requirements.

Done with this you need to create the SPFILE which is the binary version of PFILE and restart your database. Now what is difference between SPFILE and PFILE. Well in simple terms PFILE is static while SPFILE is dynamic. By this I mean that you need not restart your Oracle Instance each and every time to make any changes in the database parameters using spfile to apply them but in terms of PFILE you need to rebounce the instance in order to apply the changes. In production environment it is not feasible to restart the DB instance so frequently so its a good practice to start the DB instance with SPFILE before you go LIVE.

Relax no one is gonna cut your throat for not doing this but missing to do this shows that you are not a good DBA. So better keep in mind this thing because I have many of my colleagues who inspite of years of experience forget to do this and it only comes into scene when we face any issues in production and the parameter values cannot be changed dynamically.

So how to make SPFILE from PFILE. Simple follow the below steps.

bash$ sqlplus "/as sysdba"

sql> create spfile from pfile;

     File Created.

sql> exit

bash$ cd ORACLE_HOME/dbs

Here at this location you will check that the file with name spfileprtp.ora will be created. Well this is the spfile of your currently running pfile.

Now shutdown the instance and point the instance to the spfile you have created.

bash$ sqlplus "/as sysdba"

sql> shu immediate

Database dismounted

Database Closed

sql>exit

bash$ cd  $ORACLE_HOME/dbs

cp initprtp.ora initprtp.ora_bak 

Its always a good practice to create the backups of all the files you make changes so that reverting the changes back is much more easier.

bash$ vi initprtp.ora

spfile='/oracle/ora10g/dbs/spfileprtp.ora'

Save the above changes in the pfile and start the instance.

bash$ sqlplus "/as sysdba"

sql> STARTUP

ORACLE instance started.
 

Total System Global Area 24294967296 bytes
Fixed Size                  1267716 bytes
Variable Size             704645116 bytes
Database Buffers          855638016 bytes
Redo Buffers               15507456 bytes
Database Mounted.
Database Opened.

sql> show paramater spfile
NAME                   TYPE          VALUE
---------------------- -------------------------------- -----------------------
spfile                 string        /oracle/ora10g/dbs/spfileprtp.ora
 
Now the database has started with the SPFILE;

Well this is done and now you are ready to hit the roads with your newly created database.

We will have another session for configuring LISTERNER.ORA,TNSNAMES.ORA and SQLNET.ORA files.

Hope this helps. Your comments are welcome!!!!!!!!!!!!!!!!

Oracle Data-guard Issues - 'APPLIED'-Column not updated in v$archived_log table

I am taking a break from my regular route to Beginners Guide because indeed today I faced an issue which typically was confusing. So confusing that at-last when I got over it I thought of blogging it so that if similar issues you face in the near future you can get an instant solution to it and won’t go around pulling your hairs.

So coming to the issue let me brief the scenario in short. 

We have a Production Database where heavy OLTP transactions occur and to support the disaster recovery for this server we have a Data-guard solution set up. The Oracle version we are using here is 10.2.0.4 Enterprise Edition on Sun SPARC OS.

Yesterday, the client reported that DG is not in Synch with the Production so that’s where my job came into scene. Logging into the database I found that indeed there was an archive log gap and due to this the DG had stop synching with the Production.

I ran the following command on DG to verify the archive log gap.

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP; 

 THREAD#          LOW_SEQUENCE  HIGH_SEQUENCE#

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

1          65524      65533

SQL> SELECT max(sequence#) AS "STANDBY", applied FROM v$archived_log GROUP BY applied;

STANDBY APPLIED

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

65783    NO

65523    YES

From above output it is quite clear that there is an archive gap and due to which the recovery services had stopped. The first thing to do insuch scenario is to check if the missing archives are present at the Production. If they are present.. Pheww!!!! You are saved from a hectic recovery schedule because you just need to take the backup of this archives from production or transfer these archives from Production to DR and apply them there. But in case you are not so lucky and you don’t have archive backups at the production you need to follow the other way around where you need to take the incremental RMAN backup at Production and restore the same at the DR end. But that is the topic for another blog. 

In my case I was too lucky as the retention policy at the Production for archives is 3 days. So the archives were present there. Lucky me… So what I did was just transferred the missing archives from Production to DR and at DR stopped the media recovery process, recovered the DR with RMAN and started the media recovery process. Guess this should have solved my problem. Right!!!!!! Well I was wrong… So what went wrong? These are the steps I followed.

1. Used Simple SCP to transfer the missing archives from sequence 65524 to 65533 from Production to DR.
2. Stopped the Media recovery Process at DR.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3. Recover the DR database using simple RMAN recovery.

bash$  rman target /

rman> RECOVER DATABASE UNTIL SEQUENCE 65533 THREAD 1;

4. Start the media recovery process again.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM CURRENT LOGFILE;

At first instance all was well. The recovery process started well and the gap was filled and synching was completed. Then the real problem arose.

Well I was checking the DR end regarding the synching using following commands.

sql>  ARCHIVE LOG LIST

      Database log mode                     Archive Mode

Automatic archival                    Enabled

Archive destination                   C:\app\mudassar\archive

Oldest online log sequence            65783

Next log sequence to archive  

Current log sequence                  65794

      sql> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP; 

         no rows selected

sql> SELECT max(sequence#) AS "STANDBY", applied FROM v$archived_log GROUP BY  applied;

STANDBY APPLIED

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

65794    YES

Seems fine… But hell No Client was not convinced he ran the following query at the Production and the output was always NO.

sql> select a.sequence#,a.applied,a.archived, b.sequence#, b.applied,b.archived from  (select * from v$archived_log where sequence# in(select max(sequence#)-1 from v$archived_log) and name='stndby') a, (select * from v$archived_log where sequence# in(select max(sequence#)-1 from v$archived_log) and name like '/archive/archive-log%') b where a.sequence#!=b.sequence# OR a.APPLIED!='YES' OR b.APPLIED!='NO';

SEQUENCE#  APP ARC

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

65794       NO  YES

65794       NO  YES

Whats wrong in this then? Why the output different at Production and DR when it should be same? After lots of searching, I found that this is bug in Oracle 10g whereby the MEDIA RECOVERY PROCESS gets hanged due to some reason causing this issue. I won’t go in more detail regarding the bug but if you have metalink account on Oracle you can logged in and go through Note: 1369630.1 if not you can download the documentation here.

Bug Note: 1369630.1

Note that this error doesn’t mean that Production and DR are not in synch but as the Media Recovery Process is hanged at Production Site it is not able to update the internal views. The solution to this is upgrade to Oracle 11g or higher (Oracle 12c is already introduced) or you can restart the Production Database if possible to overcome the issue temporarily.

Hope this would have been help to all you folks!!!! Comments are WELCOME!!!!

Silent Installation: Oracle 10g

Done with the OUI part lets continue with silent installation.

In silent installation, you need not any GUI interface in order to carry out the installation. All the installation commences on the command line prompt giving it an edge over OUI in the case where remote server’s GUI is not accessible. Just a little practice, knowledge and patience and you are fit to go.

The main part of the silent installation is the RESPONSE file.

RESPONSE File is nothing but a file in which you specify all the parameters needed for Oracle Installation. You can set the parameters accordingly in this file and feed this file as an input to the SILENT installation. While installation the Oracle Installer derives  values from this file and continues with the installation.

Although you can directly specify parameters and there values on the command line, I prefer to create a RESPONSE file as it becomes more clean (all parameters are dumped in a single file), easy (you get inbuilt help from the comments for the parameters you are setting) and also handy.

So done with this, let’s try our HANDS on Oracle Silent Installation.

Here is the SAMPLE RESPONSE File for Silent Installation. Need no Go in much detail as I said that the comment parts take care of what you need to set.

You can find this file at the following location: ~/database/response/

after you unzip the binaries.

Here is the link to the Sample Original RESPONSE FILE.

Sample Original Response File

Here is the link to Sample Response file updated with parameter values for Installation

Sample Response File for Installation with Parameter Values Set

I have set n_configurationOption option to 3 as we will be having separate manually DB creation session. So gear up for it!!!

#------------------------------------------------------------------------------

# Name       : n_configurationOption

# Datatype   : Number

# Description: Determines the type of configuration to perform for the session.

#

#              This entry should be specified as an number.  The valid values

#              that you can use map to the following options:

#              1 - Create a Database

#              2 - Configure an ASM instance

#              3 - Install Software Only

#

# Example    : n_configurationOption=1

#------------------------------------------------------------------------------

n_configurationOption=3

You can make changes in the parameters and set the values as you like or leave the default values. 

Note: That you need to specify values for the fields <value needed> as they are mandatory and skip the rest if you are not interested.

Done with all the configuration needed you can run the installer using the following command.

                      bash$ ./runInstaller -silent -responseFile /oracle/database/response/enterprise.rsp

Note that the path is the responsefile path with name with .rsp extension. So set it accordingly.

Once done with it wait for the installation to commence and end. 

While the setup exits successfully it will ask you to run few scripts at the end to set the OraInventory values.

Run these scripts by logging as the root user and you are done with installation.

Scripts you need to run are:

../oraInventory/orainstRoot.sh

../root.sh

Note that paths for the scripts can differ according to the system environment. But at the end of installation Oracle evokes the script path and accordingly you do execute the same from ROOT user.

So that's it. Done with silent installation too.

One thing worth noting down here is that pre-requites for installation are same as that of OUI. If you have missed any pre-requisites refer these links:

Pre-requisites for Installation

Basics of Oracle Installation- Oracle10g: A Case Study Part 4

11. Go for the password management, if required. Click OK.

12. After the database completion system asks you to run some script. Proceed as suggested.

Installation successful dialog appears.

Congratulations!! You have just completed the Oracle Installation. Was a Piece of cake isn’t it. Now try to re-install the same without looking at the document and just memorizing the steps.

We will continue with the Silent Installation in next arcticle!!!