11.2.0.3 Silent Install and Database Creation using OMF

Performing Silent Install

Operating System:

> uname -an
SunOS <hostname> 5.10 Generic_142909-17 sun4u sparc SUNW,Sun-Fire-880

oraInst.loc:

> cat /var/opt/oracle/oraInst.loc
inventory_loc=/u01/app/11.2.0.3/oraInventory
inst_group=dba

Response File:

> cat /home/mdinh/ora_stage/11.2.0.3/patchset/database/response/swonly.rsp
#------------------------------------------------------------------------------
# Do not change the following system generated value.
#------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=dba
INVENTORY_LOCATION=/u01/app/11.2.0.3/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.EEOptionsSelection=true
# oracle.rdbms.partitioning:11.2.0.3.0 - Oracle Partitioning
oracle.install.db.optionalComponents=oracle.rdbms.partitioning:11.2.0.3.0
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true
oracle.installer.autoupdates.option=SKIP_UPDATES

Shell Script:

> cat swonly.sh
#!/bin/sh -x
/home/mdinh/ora_stage/11.2.0.3/patchset/database/runInstaller -debug -silent -force -responseFile /home/mdinh/ora_stage/11.2.0.3/patchset/database/response/swonly.rsp
exit

Execute as Root:

/u01/app/oracle/product/11.2.0.3/dbhome_1/root.sh -bindir /opt/oracle/bin
Check /u01/app/oracle/product/11.2.0.3/dbhome_1/install/root_<hostname>_2011-10-26_11-33-40.log

Creating database using Oracle Managed Files (OMF)

I have not been able to figure out why the directory for the database files  is created in UPPERCASE.

Hence, the pre-created directory will need to be in UPPERCASE as well.

Create directories:

mkdir -p /u01/app/oracle/admin/DEVDB01/adump
mkdir -p /oracle/oradata/DEVDB01/arch/

Create init.ora:

> cat initdevdb01.ora
audit_file_dest='/u01/app/oracle/admin/DEVDB01/adump'
audit_sys_operations=FALSE
audit_trail=NONE
compatible=11.2.0.3
control_files='/oracle/oradata/DEVDB01/control01.dbf','/oracle/oradata/DEVDB01/control02.dbf'
db_block_size=8192
db_create_file_dest='/oracle/oradata'
db_create_online_log_dest_1='/oracle/oradata'
db_create_online_log_dest_2='/oracle/oradata'
db_name=devdb01
diagnostic_dest='/u01/app/oracle'
fast_start_mttr_target=300
job_queue_processes=20
log_archive_dest_1='LOCATION=/oracle/oradata/DEVDB01/arch'
log_archive_format='arc_%d_%t_%r_%s.dbf'
open_cursors=1000
pga_aggregate_target=1G
processes=200
remote_login_passwordfile=EXCLUSIVE
sec_case_sensitive_logon=FALSE
session_cached_cursors=500
sga_max_size=2G
sga_target=2G
timed_statistics=TRUE
undo_management=AUTO
undo_retention=60000
undo_tablespace=undotbs1
workarea_size_policy=AUTO

Create database:

startup nomount;
create database
maxlogfiles 8
maxlogmembers 4
maxdatafiles 1024
character set AL32UTF8
national character set AL16UTF16
logfile group 1 size 256M,group 2 size 256M,group 3 size 256M,group 4 size 256M
default temporary tablespace temp tempfile size 1024M
undo tablespace undotbs1 datafile size 1024M;

@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catproc.sql;
@?/rdbms/admin/catblock.sql;
@?/rdbms/admin/catoctk.sql;
@?/rdbms/admin/catio.sql
@?/rdbms/admin/dbmsiotc.sql
@?/rdbms/admin/dbmsotrc.sql
@?/rdbms/admin/dbmspool.sql
@?/rdbms/admin/userlock.sql

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
conn system
@?/sqlplus/admin/pupbld.sql

Create and Set DEFAULT tablespace:

CREATE TABLESPACE user_data DATAFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 8G;
ALTER DATABASE DEFAULT TABLESPACE user_data;

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200
SELECT * FROM database_properties WHERE property_name like '%TABLESPACE';

SQL> SELECT * FROM database_properties WHERE property_name like '%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                           Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USER_DATA                      Name of default permanent tablespace

Directory Structures:

> ls -l /oracle/oradata/DEVDB01/
total 68432
drwxr-xr-x   2 oracle   dba         1024 Oct 27 09:03 arch
-rw-r-----   1 oracle   dba      17481728 Oct 27 18:57 control01.dbf
-rw-r-----   1 oracle   dba      17481728 Oct 27 18:57 control02.dbf
drwxr-x---   2 oracle   dba         1024 Oct 27 09:42 datafile
drwxr-x---   2 oracle   dba         1024 Oct 26 13:41 onlinelog

Advertisement

Shell and SQL Script

A quick note on separating SQL from shell script.

If I have a shell script name longops.sh, then it will call longops.sql.

Don’t embed SQL in shell script so that SQL script can be executed independently.

> cat longops.sh
#!/bin/sh
. /home/oracle/.common.conf > /dev/null
DN=`dirname $0`
BN=`basename $0`

sql=`echo $BN|cut -d'.' -f1`.sql
for sid in `ps -eo args|grep ora_smon|grep -v grep|awk -F_ '{print $3}'`
do
  OUTF=/tmp/longops.${sid}.html
  rm -f $OUTF
  ORACLE_SID=$sid
  . oraenv
  $ORACLE_HOME/bin/sqlplus -SL > /dev/null / << END
    spool $OUTF
    prompt From: oracle@mail.com
    prompt To: dba@mail.com
    prompt Subject: WARNING ${BN}: $ORACLE_SID - SQL running over one hour
    prompt Content-type: text/html
    prompt MIME-Version: 1.0
    set markup html on entmap off table 'BORDER="2"'
    @${sql}
    spool off
    exit
END
  if [ "$?" -ne "0" ]; then
    mailx -s "FAILED: $BN $*" $EMAIL < /dev/null
  exit 1
  fi
  if [ `grep -ic MACHINE $OUTF` != 0 ]; then
    /usr/sbin/sendmail -t < $OUTF
  fi
done
exit


> cat longops.sql
ALTER SESSION SET NLS_DATE_FORMAT='Dy, DD-Mon-YYYY HH24:MI:SS';
SELECT s.sid, s.serial#,
       s.username,  s.osuser, s.machine, s.program,
       s.sql_id, s.sql_exec_start, s.logon_time,
       ROUND (s.last_call_et/3600,3) last_call_hr,
       ROUND ((SYSDATE-s.SQL_EXEC_START)*24,3) exec_start_hr,
       CASE WHEN s.state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END AS state,
       CASE WHEN s.state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END AS sw_event
FROM   v$session s
WHERE  s.type = 'USER' AND s.status = 'ACTIVE' AND ROUND (s.last_call_et/3600)>1
ORDER BY s.last_call_et DESC;

Create Standby Database from Active Database

This is just another post on how to create standby database from active database. By the way, I do love Oracle’s technology most of the time.

This option was primarily chosen because the database size was ~400GB and there is no storage available to create backup for standby database.

The process took almost 6 hours and was running while I was sleeping 🙂

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Oct 7 21:49:57 2011
Finished Duplicate Db at 08-OCT-2011 03:31:22

Configuration:

PRIMARY pfile
> more initdb08.ora
ifile=/home/oracle/pfile/initprodoltp_common.ora
_log_deletion_policy=ALL
event="10298 trace name context forever, level 32"
*.audit_file_dest="/u01/app/oracle/admin/db08/adump"
*.control_files=/oracle/oradata/db08/control01.ctl,/oracle/oradata/db08/control02.ctl
*.db_16k_cache_size=2G
*.db_32k_cache_size=2G
*.db_cache_size=2G
*.db_name="db08"
*.db_unique_name="lax_db08"
*.fal_client=lax_db08
*.fal_server=phx_db08
*.log_archive_config="DG_CONFIG=(lax_db08,phx_db08)"
*.log_archive_dest_1="LOCATION=/oracle/oradata/db08/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lax_db08"
*.log_archive_dest_2='SERVICE=phx_db08 LGWR ASYNC COMPRESSION=ENABLE REOPEN=15 NET_TIMEOUT=30 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=phx_db08'
*.shared_pool_reserved_size=512M
*.shared_pool_size=3G

STANDBY pfile
> more initdb08.ora
ifile=/home/oracle/pfile/initdr_common.ora
_log_deletion_policy=ALL
event="10298 trace name context forever, level 32"
*.audit_file_dest='/u01/app/oracle/admin/db08/adump'
*.control_files='/oracle/oradata/db08/control01.ctl','/oracle/oradata/db08/control02.ctl'#Restore Controlfile
*.db_16k_cache_size=512M
*.db_32k_cache_size=512M
*.db_cache_size=512M
*.db_name='db08'
*.db_unique_name='phx_db08'
*.fal_client='phx_db08'
*.fal_server='lax_db08'
*.log_archive_config='DG_CONFIG=(lax_db08,phx_db08)'
*.log_archive_dest_1='LOCATION=/oracle/oradata/db08/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=phx_db08'
*.log_archive_dest_2='SERVICE=lax_db08 LGWR ASYNC REOPEN=15 NET_TIMEOUT=30 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lax_db08'
*.shared_pool_size=256M

RMAN rcv 
> cat db08.crstby.rcv
connect target;
connect auxiliary sys/password@phx_db08;
run {
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;}
exit

I manually created the parameter and password file at the STANDBY, opted not to use spfile.

From STANDBY, start listener and database (nomount)

From PRIMARY, execute RMAN using RCV

> rman log=’db08.crstby.log’ cmdfile=’db08.crstby.rcv’

Snippets from the log file:

RMAN> connect target;
2> connect auxiliary *
3> run {
4> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;}
5> exit
connected to target database: DB08 (DBID=1578229963)
connected to auxiliary database: DB08 (not mounted)

Starting Duplicate Db at 07-OCT-2011 21:50:02
using target database control file instead of recovery catalog
contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/orapwdb08' auxiliary format
 '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/orapwdb08'   ;
}
executing Memory Script
contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/oracle/oradata/db08/control01.ctl';
   restore clone controlfile to  '/oracle/oradata/db08/control02.ctl' from
 '/oracle/oradata/db08/control01.ctl';
}
executing Memory Script
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
contents of Memory Script:
{
   set newname for tempfile  1 to
 "/oracle/oradata/db08/temp01.dbf";
   set newname for tempfile  2 to
 "/oracle/oradata/db08/temp02.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/oracle/oradata/db08/system01.dbf";
   set newname for datafile  2 to
 "/oracle/oradata/db08/undotbs01.dbf";
   set newname for datafile  3 to
 "/oracle/oradata/db08/sysaux01.dbf";
   set newname for datafile  4 to
 "/oracle/oradata/db08/undotbs02.dbf";
   set newname for datafile  5 to
 "/oracle/oradata/db08/ctxsys01.dbf";
~~~~
   backup as copy reuse
   datafile  1 auxiliary format
 "/oracle/oradata/db08/system01.dbf"   datafile
 2 auxiliary format
 "/oracle/oradata/db08/undotbs01.dbf"   datafile
 3 auxiliary format
 "/oracle/oradata/db08/sysaux01.dbf"   datafile
 4 auxiliary format
 "/oracle/oradata/db08/undotbs02.dbf"   datafile
 5 auxiliary format
 "/oracle/oradata/db08/ctxsys01.dbf"   datafile
   sql 'alter system archive log current';
}
executing Memory Script
Starting backup at 07-OCT-2011 21:50:45
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Finished backup at 08-OCT-2011 03:24:14

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75240.dbf" auxiliary format
 "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75240.dbf"   archivelog like
 "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75241.dbf" auxiliary format
 "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75241.dbf"   archivelog like
 "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75242.dbf" auxiliary format
~~~
   catalog clone archivelog  "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75240.dbf";
   catalog clone archivelog  "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75241.dbf";
   catalog clone archivelog  "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75242.dbf";
   catalog clone archivelog  "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75243.dbf";
   catalog clone archivelog  "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75244.dbf";
   switch clone datafile all;
}
executing Memory Script
Starting backup at 08-OCT-2011 03:24:21
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=75240 RECID=135728 STAMP=763941140
channel ORA_DISK_2: starting archived log copy
input archived log thread=1 sequence=75241 RECID=135729 STAMP=763941998
channel ORA_DISK_3: starting archived log copy
input archived log thread=1 sequence=75242 RECID=135730 STAMP=763942998
channel ORA_DISK_4: starting archived log copy
input archived log thread=1 sequence=75243 RECID=135731 STAMP=763944071
Finished backup at 08-OCT-2011 03:26:56

cataloged archived log
archived log file name=/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75240.dbf RECID=1 STAMP=763961217

cataloged archived log
archived log file name=/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75241.dbf RECID=2 STAMP=763961218
contents of Memory Script:
{
   set until scn  10533738778008;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 08-OCT-2011 03:27:19
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

starting media recovery
media recovery complete, elapsed time: 00:03:08
Finished recover at 08-OCT-2011 03:30:49
Finished Duplicate Db at 08-OCT-2011 03:31:22

Recovery Manager complete.