Oracle 12C Database creation - manual method

-

Oracle 12C Database Creation - Manual Method


1. Install the Oracle Software in the Primary Server 192.2.4.45.

1.1 Create Directories for the File Locations :

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

[oracle@orman01 prod]$ pwd                                                                                                                                                     
/oracle/app/oracle/oradata
[oracle@orman01 oradata]$ mkdir prod
[oracle@orman01 prod]$ cd /oracle/app/oracle/oradata/prod/
[oracle@orman01 prod]$ mkdir -p flash_recovery_area archive_log adump 
[oracle@orman01 prod]$ ls -ltr
total 5242908
drwxr-xr-x 2 oracle oinstall       4096 Oct 30 05:24 archive_log
drwxr-xr-x 2 oracle oinstall       4096 Oct 30 05:24 flash_recovery_area
drwxrwxr-x 3 oracle oinstall       4096 Oct 30 05:28 adump

Note :  Diag Location Mentioned in the Pfile so it will create the directory while creating the database.

 ==> Go to the ORACLE_HOME/dbs Location and crate the pfile (initprod.ora) for the instance.

audit_file_dest='/oracle/app/oracle/oradata/prod/adump'
audit_trail='db'
compatible='12.2.0.1'
control_files='/oracle/app/oracle/oradata/prod/control01.ctl', '/oracle/app/oracle/oradata/prod/control02.ctl', '/oracle/app/oracle/oradata/prod/control03.ctl'
db_block_size=8192
db_cache_size=104857600
db_domain=''
db_name='prod'
db_recovery_file_dest='/oracle/app/oracle/oradata/prod/flash_recovery_area'
db_recovery_file_dest_size=1073741824
deferred_segment_creation=false
diagnostic_dest='/oracle/app/oracle/oradata/prod/'
dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
log_buffer=5242880
log_archive_dest_1='location=/oracle/app/oracle/oradata/prod/archive_log'
log_archive_format='ARC%S_%R.%T'
memory_target=1G
open_cursors=500
processes=500
remote_login_passwordfile='EXCLUSIVE'
shared_pool_size=41943040
_Trace_files_Public='TRUE'
undo_tablespace='UNDOTBS1'
Optimizer_index_cost_adj=25
Optimizer_index_caching=75
# to avoid core dump when using SQL query with view clause
_optimizer_distinct_agg_transform=FALSE

2. Instance Creation :

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

[oracle@orman02 dbs]$ setenv ORACLE_BASE=/oracle/app/oracle
[oracle@orman02 dbs]$ setenv ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/dbhome_1
[oracle@orman02 dbs]$ setenv PATH=$ORACLE_HOME/bin:$ORACLE_HOME/jdk/bin:$PATH
[oracle@orman02 dbs]$ setenv LD_LIBRARY_PATH=$ORACLE_HOME/lib
[oracle@orman02 dbs]$ setenv ORACLE_SID=prod
[oracle@orman02 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprod password=sys

[oracle@orman02 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.2.0.1 Production on Thu Oct 9 06:53:30 2014

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

Connected to an idle instance.

SQL> startup pfile=initprod.ora nomount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2220200 bytes
Variable Size             583012184 bytes
Database Buffers          478150656 bytes
Redo Buffers                5554176 bytes
SQL>

SQL> CREATE DATABASE prod
LOGFILE GROUP 1 ('/oracle/app/oracle/oradata/prod/redo01.log') SIZE 100M,
GROUP 2 ('/oracle/app/oracle/oradata/prod/redo02.log') SIZE 100M,
GROUP 3 ('/oracle/app/oracle/oradata/prod/redo03.log') SIZE 100M
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 200
NOARCHIVELOG
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/oracle/app/oracle/oradata/prod/system01.dbf' SIZE 5g AUTOEXTEND ON
SYSAUX DATAFILE '/oracle/app/oracle/oradata/prod/sysaux01.dbf' SIZE 5g AUTOEXTEND ON
Default tablespace ELITETAB1 datafile '/oracle/app/oracle/oradata/prod/elitetab101.dbf' size 3g
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/oracle/app/oracle/oradata/prod/temp01.dbf' size 4g
UNDO TABLESPACE undotbs1 DATAFILE '/oracle/app/oracle/oradata/prod/undotbs01.dbf' size 5g AUTOEXTEND ON;
 
Database created.

SQL> select name,open_mode,created from v$database;

NAME      OPEN_MODE            CREATED
--------- -------------------- ---------
PROD      READ WRITE           04-FEB-19

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

SQL > @$ORACLE_HOME/rdbms/admin/catproc.sql 
 
SQL > create tablespace ELITETAB2 datafile '/oracle/app/oracle/oradata/prod/elitetab201.dbf' size 2G;

SQL > create tablespace ELITEIDX1 datafile '/oracle/app/oracle/oradata/prod/eliteidx101.dbf' size 2G;

SQL > create tablespace ELITEIDX2 datafile '/oracle/app/oracle/oradata/prod/eliteidx201.dbf' size 2G;

SQL > create spfile from pfile;

4. Listener and TNS Entry File Creation :

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

Listener Entry in 192.1.2.69 Server :
*************************************

[oracle@orman01 admin]$ pwd
/oracle/app/oracle/product/12.2.0/db_1/network/admin

[oracle@orman01 admin]$ cat listener.ora
prod =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.1.2.65)(PORT = 1521))
  )

SID_LIST_LISTENER_QA =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /oracle/app/oracle/product/12.2.0.1/dbhome_1)
      (SID_NAME = prod)                                                                                                                                                                )
  )

[oracle@orman01 admin]$ lsnrctl status prod

LSNRCTL for Solaris: Version 12.2.0.1.0 - Production on 04-FEB-19 08:22:23

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.1.2.69)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Solaris: Version 12.2.0.1.0 - Production
Start Date                04-FEB-19 08:21:03
Uptime                    0 days 0 hr. 1 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/orman01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.1.2.69)(PORT=1521)))
Services Summary...
Service "prod" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
Service "prodXDB" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@orman01 admin]$

Tns Entry in 192.1.2.69 Server :
********************************

[oracle@orman01 admin]$ cat tnsnames.ora
prod =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.2.65)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )
  )

[oracle@orman01 admin]$ 

Comments

Post a Comment

Popular posts from this blog

RMAN-08137: WARNING: archived log not deleted

-
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process When we are taking archive log backup with delete then got an error. RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/pac/lci9t1/arch/redo_1_16470_842608348.arc thread=1 sequence=16470 RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/pac/lci9t1/arch/redo_1_16471_842608348.arc thread=1 sequence=16471 RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process SOLUATION: Cause: The archive logs seems that it is not yet applied in the standby database.                         Select sequence# , applied from v$archived_log; Action: Check the standby database and apply t...
Read more

steps to setup rman

-
Steps to setup RMAN This article is tested in oracle12C R1. How do we setup the RMAN in oracle? There are couple of ways, we can setup the RMAN. We can use control file to store backup catalog info or we can have separate database to store catalog info. Here I am using separate database to store backup catalog information. I am using Linux OS. Please remember, the directories and folder might change based on the operating system and environment. But the below steps are pretty much same for any environment. Here i am using ORCL as primary database and CATDB as catalog database.   Step1  Enabling Archive log. See the following link for the First step  Click Here Step2  Create the tablespace and user in catalog database to hold backup information. SQL> CONNECT sys/password@catdb AS SYSDBA Connected. SQL> CREATE TABLESPACE RMAN 2 DATAFILE '/app/oracat/oradata/catdb/rman01.dbf' SIZE 6208K REUSE 3 AUTOEXTEND ON NEXT 64K MAXSIZE 32767M ...
Read more

Enabling Archive log

-
Enabling Archive log in RMAN This article is tested in oracle12C R1. How do we setup the RMAN in oracle? There are couple of ways, we can setup the RMAN. We can use control file to store backup catalog info or we can have separate database to store catalog info. Here I am using separate database to store backup catalog information. I am using Linux OS. Please remember, the directories and folder might change based on the operating system and environment. But the below steps are pretty much same for any environment. Here i am using ORCL as primary database and CATDB as catalog database. Step1  Enable the archive log in ORCL database. Step 1.1  We need to build the pfile from spfile to add new entries. If you  already have recent pfile, the you do not need to do this step. Login as sys user and execute this to create pfile. create pfile='/u01/app/oraint/product/12.1.0/dbhome_1/dbs/pfile.ora' from spfile; Step 1.2  Once pfile is created, t...
Read more