CREATING DATABASE directly on ASM
--------
Assumption:
Configured Oracle Grid infrastructure for Standalone server (Oracle Restart). Grid user
---------
As Oracle Uuser :
cd $ORACLE_HOME/dbs
#Creating the pfile
cat > initORCL.ora
db_name = ORCL
db_block_size = 8192
memory_target = 1024M
processes = 300
control_files = ('+DATA/ORCL/CONTROLFILE/CONTROL01.ctl','+FRA/ORCL/CONTROLFILE/CONTROL02.ctl')
job_queue_processes = 10
open_cursors = 300
fast_start_mttr_target = 500
undo_management = AUTO
undo_tablespace = UNDOTBS
remote_login_passwordfile = EXCLUSIVE
PRESS CTRL+D
#Create the Database
CREATE DATABASE ORCL
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE
GROUP 1 ('+DATA','+FRA') SIZE 100M,
GROUP 2 ('+DATA','+FRA') SIZE 100M,
GROUP 3 ('+DATA','+FRA') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '+DATA' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '+DATA' SIZE 325M REUSE
DEFAULT TABLESPACE USERS DATAFILE
'+DATA' SIZE 325M REUSE
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'+DATA' SIZE 20M REUSE
UNDO TABLESPACE UNDOTBS DATAFILE
'+DATA' SIZE 200M REUSE
AUTOEXTEND ON MAXSIZE 300M;
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
connect system/oracle
@?/sqlplus/admin/pupbld
conn /as sysdba
create spfile='+DATA' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL.ora';
shutdown;
exit
#From Grid user login to ASMCMD to create the alias for spfile
$ asmcmd
ASMCMD> ls -lt
State Type Rebal Name
MOUNTED EXTERN N DATA/
MOUNTED EXTERN N FRA/
ASMCMD> cd DATA
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
Y ORCL/
Y ASM/
ASMCMD> cd ORCL
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
Y TEMPFILE/
Y PARAMETERFILE/
Y ONLINELOG/
Y DATAFILE/
Y CONTROLFILE/
ASMCMD> cd PARAMETERFILE
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE MAR 15 00:00:00 Y spfile.264.810087663
ASMCMD> cd ..
ASMCMD> pwd
+DATA/ORCL
ASMCMD> mkalias +DATA/ORCL/PARAMETERFILE/spfile.264.810087663 spfileORCL.ora
ASMCMD> ls -tlr
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfileORCL.ora => +DATA/ORCL/PARAMETERFILE/spfile.264.810087663
#Log back to the Oracle user session:
cd /u01/app/oracle/product/11.2.0/db_1/dbs
cat > initORCL.ora
spfile='+DATA/ORCL/spfileORCL.ora'
PRESS CTRL+D
$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 15 00:26:46 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 620758200 bytes
Database Buffers 440401920 bytes
Redo Buffers 5541888 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------------------
spfile string +DATA/orcl/parameterfile/spfile.264.810087663
SQL> select name from v$controlfile;
NAME
--------------------------------------
+DATA/orcl/controlfile/control01.ctl
+FRA/orcl/controlfile/control02.ctl
SQL> select name from v$datafile;
NAME
------------------------------------------
+DATA/orcl/datafile/system.266.810085907
+DATA/orcl/datafile/sysaux.256.810085915
+DATA/orcl/datafile/undotbs.271.810085927
+DATA/orcl/datafile/users.259.810085931
SQL> select name from v$tempfile;
NAME
---------------------------------------
+DATA/orcl/tempfile/temp.258.810085931
SQL> select member from v$logfile;
MEMBER
-------------------------------------------------
+DATA/orcl/onlinelog/group_1.268.810085891
+FRA/orcl/onlinelog/group_1.262.810085893
+DATA/orcl/onlinelog/group_2.257.810085895
+FRA/orcl/onlinelog/group_2.263.810085897
+DATA/orcl/onlinelog/group_3.265.810085901
+FRA/orcl/onlinelog/group_3.256.810085901
SQL> exit
References:
Creating 11g R2 Oracle Database , Directly on ASM.
TNS: Connection Closed.
Few days back day got an error on my DB all of sudden , TNS Connection closed . After looking up MOS learned that . One of the several reason could be that your library files are not linked correctly and will have to relink them.
Sharing the output of the incident with the soln.
[ORCL]$ sqlplus kumarn/*****
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 26 17:38:40 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-12547: TNS:lost contact
Enter user-name:
[ORCL]$ sqlplus kumarn/****@ORCL
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 26 17:38:56 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-12537: TNS:connection closed
[ORCL]$ oerr ora 12537
12537, 00000, "TNS:connection closed"
// *Cause: "End of file" condition has been reached; partner has disconnected.
// *Action: None needed; this is an information message.
Sol: Relinked the Oracle Home again as the owner of the Oracle
$ORACLE_HOME/bin/relink all
[ORCL]$ sqlplus kumarn/*****SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 26 17:45:00 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> select * from global_name;
GLOBAL_NAME
-------------
ORCL