Recovery: LOSS OF ALL CONTROLFILES (NO CATALOG)

SQL> insert into myobjects select * from myobjects;
919664 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from myobjects;
  COUNT(*)
----------
   1839328   >>>> need to check this record count after recovery
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
Note - current log sequence is 7 - not archived but contains the last committed changes that we made
Note - archive logs will not be found in $ARCV area, but in the flashback location
Simulate a failure
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/ORACLE/testdb/control01.ctl
/u01/ORACLE/testdb/control02.ctl
/u01/ORACLE/testdb/control03.ctl
SQL> !rm /u01/ORACLE/testdb/*.ctl

SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area  893386752 bytes
Fixed Size                  2076816 bytes
Variable Size             432017264 bytes
Database Buffers          452984832 bytes
Redo Buffers                6307840 bytes
Since we are not using a RMAN catalog we need to set the DBID
RMAN> set dbid=2415549446;
executing command: SET DBID

Restore the controlfile
RMAN> run {
2> restore controlfile from autobackup;
3> }
Starting restore at 18-SEP-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: /u01/ORACLE/flash_recovery_area
database name (or database unique name) used for search: TESTDB
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2007_09_18/o1_mf_s_633601094_3gynd74g_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/ORACLE/testdb/control01.ctl
output filename=/u01/ORACLE/testdb/control02.ctl
output filename=/u01/ORACLE/testdb/control03.ctl
Finished restore at 18-SEP-07
Mount and recover the database
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
Starting recover at 18-SEP-07
Starting implicit crosscheck backup at 18-SEP-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 18-SEP-07
Starting implicit crosscheck copy at 18-SEP-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-SEP-07
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2007_09_18/o1_mf_s_633601094_3gynd74g_.bkp
using channel ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=155 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0
datafile 4 not processed because file is offline
starting media recovery
archive log thread 1 sequence 6 is already on disk as file /u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2007_09_18/o1_mf_1_6_3gyn7vnk_.arc
archive log thread 1 sequence 7 is already on disk as file /u01/ORACLE/testdb/redo03.log
archive log filename=/u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2007_09_18/o1_mf_1_6_3gyn7vnk_.arc thread=1 sequence=6
archive log filename=/u01/ORACLE/testdb/redo03.log thread=1 sequence=7  >>>> current redo log with committed but unarchived changes applied
media recovery complete, elapsed time: 00:00:09
Finished recover at 18-SEP-07

SQL> alter database open resetlogs;
Database altered.
 conn scott/tiger
Connected.
SQL> select count(*) from myobjects;
  COUNT(*)
----------
   1839328

Unix Commands

1> List and remove all files older than 30 days
                 

find /u01/local/logs -mtime +30 -exec ls -l {} \;

find /u01/local/logs -mtime +30 -exec rm {} \;  

 

2> Tar and gzip commands

 

tar and compress a bunch of datafiles and then untar and uncompress them
cd /u02/oradata/test- (area of database files )
Now tar and compress and copy the files to backup area – /u02/oradata/test_bkup and name the file as test.tar.gz
tar cvf – * |gzip -c >/u02/oradata/test_bkup/test.tar.gz –
cd /u02/oradata/test_bkup > ls -lrt
-rw-r–r– 1 ofsad1 dba 105952962 Feb 26 11:31 test.tar.gz
Now to untar and uncompress the files back to the original area -
cd /u02/oradata/test
gzip -dc < /u02/oradata/test_bkup/test.tar.gz | tar xvf -

3> Locating Files under a particular directory
find . -print |grep -i test.sql

4> Using AWK in UNIX
To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)
ps -ef |grep -i oracle |awk ‘{ print $2 }’

5> Changing the standard prompt for Oracle Users
Edit the .profile for the oracle user
PS1=”`hostname`*$ORACLE_SID:$PWD>”

6> Display top 10 CPU consumers using the ps command
/usr/ucb/ps auxgw | head –11

7> Show number of active Oracle dedicated connection users for a particular ORACLE_SID
ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc –l

8> Display the number of CPU’s in Solaris
psrinfo -v | grep “Status of processor”|wc –l

9> Display the number of CPU’s in AIX
lsdev –C | grep Process|wc –l

10> Display RAM Memory size on Solaris
prtconf |grep -i mem

11> Display RAM memory size on AIX
First determine name of memory device
lsdev -C |grep mem
then assuming the name of the memory device is ‘mem0’
lsattr -El mem0

12> Swap space allocation and usage
Solaris : swap -s or swap -l
Aix : lsps -a

13> Total number of semaphores held by all instances on server
ipcs -as | awk ‘{sum += $9} END {print sum}’

14> View allocated RAM memory segments
ipcs -pmb

15> Manually deallocate shared memeory segments
ipcrm -m ‘ID’

16> Show mount points for a disk in AIX
lspv -l hdisk13

17> Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory
du -ks * | sort -n| tail

18> Display total file space in a directory
du -ks

19> Cleanup any unwanted trace files more than seven days old
find . *.trc -mtime +7 -exec rm {} \;

20> Locate Oracle files that contain certain strings
find . -print | xargs grep rollback

21> Locate recently created UNIX files (in the past one day)
find . -mtime -1 –print

22> Finding large files on the server (more than 100MB in size)
find . -size +102400 -print

23> Crontab :
To submit a task every Tuesday (day 2) at 2:45PM
45 14 2 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1
To submit a task to run every 15 minutes on weekdays (days 1-5)
15,30,45 * 1-5 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1
To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)
15 * 0,6 * * opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

24> Remove file with unseen characters
This works on AIX – Please test on other systems.
If a file name is vague and can’t be removed.
Ex – a file name has some hidden characters which prevents it’s removal as the name is not recognized by the rm command.
ls -lai sqlnet.log
19462 -rw-r—– 1 oraofsap dba 7741 Jun 4 13:14 sqlnet.log
look for number against file and rm number .
find . -inum 19462 -exec rm {} \;

25> How to kill all similar processes in Unix
In this example all opmn processes are being killed. This command is useful if we find a lot of processes still present at the OS level even after shutting down in this case the Oracle Application Server
ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}
 

cronjob RMAN Script for Friday Full Backup and all other days incremental Backup

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~cronjob entry~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[oracle@emsleerpt01 oracle/] $ crontab -l
# RMAN Backup
# Full Friday, incremental every other day
#########################################################
21 20 * * 5 /u01/backups/rman/scripts/run_rman weekly.rcv
21 20 * * 0,1,2,3,4,6 /u01/backups/rman/scripts/run_rman daily.rcv

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~run_rman~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[oracle@emsleerpt01 scripts/] $ more run_rman
#!/bin/ksh
. /apps/oracle/.profile
#typeset -L day=$date+%e
cd /u01/backups/rman/scripts
for ORACLE_SID in PV
do
        export ORACLE_SID
        rman target / nocatalog log logfile.log cmdfile $1
        cp logfile.log ../logs/$ORACLE_SID.$day
done

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~daily.rcv~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[oracle@emsleerpt01 scripts/] $ more daily.rcv
# This is a daily cumulative incremental backup
# Ensure a daily one has been done as it will setup the persistent parameters
# in RMAN
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE FILESPERSET 4;
BACKUP ARCHIVELOG ALL;
DELETE OBSOLETE;

LIST BACKUP;
#-end of file-

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~weekly.rcv~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[oracle@emsleerpt01 scripts/] $ more weekly.rcv
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/backups/rman/backupsets/ora_df%t_s%s_s%p';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backups/rman/backupsets/PV/%F';
# Set the retention policy to a recovery window of 14 days.  This ensures that
# RMAN retains all backups needed to recover the database to any point in time
# in the last 10 days.  You can use the DELETE OBSOLETE command to delete
# backups that are no longer required by the retention policy. To exclude a
# backup from consideration by the policy, you can use KEEP option with the
# BACKUP command.
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
# Enable the autobackup feature to backup the controlfile after each database
# or archivelog backup.
CONFIGURE CONTROLFILE AUTOBACKUP ON;
# Enable the backup optimization feature introduced in 9i to make sure that
# RMAN won't backup an archivelog or datafile if there already exists a backup
# of that file. The FORCE option can be used to override optimization on a
# specific BACKUP command.
CONFIGURE BACKUP OPTIMIZATION ON;

# IMPORTANT: Save the database id displayed in the RMAN output if you are
# operating RMAN backups in nocatalog mode, since it is required during
# disaster recovery.  You will see the database id in output from RMAN on
# connecting to target database like:
#
# connected to target database: PV (DBID=1409605046)
# Use the SHOW ALL command to see the current configuration settings.
# Following scenario assumes that you want to take one full database once a
# week, doing every day incrementals. Backup cycle starts on friday,
# i.e., every friday full backup, and on other days incrementals.

# Section 2.1 - Start script for backup cycle
# -------------------------------------------
# The following commands are run each Friday to start the backup cycle.
# The steps are:
#  - Take an incremental level 0 backup of the database.  A level 0 backup is
#    a complete backup of the entire file which can be used as the basis
#    for a subsequent incremental backup.
#  - Backup all archivelogs that have not already been backed up.
#  - Delete on-disk archivelogs older than seven days.
BACKUP INCREMENTAL LEVEL 0 DATABASE FILESPERSET 4;
BACKUP ARCHIVELOG ALL;
DELETE OBSOLETE;
# If the above backup fails for any reaon, you can use the NOT BACKED UP SINCE
# option on the BACKUP command (9i restartable backup feature) to continue
# from the point of failure.  The small value of FILESPERSET is good for
# restartable backups.  However you should note that smaller FILESPERSET
# produces more backup sets.
# Use the following commands to re-start backups after a failure:
#BACKUP INCREMENTAL LEVEL 0 DATABASE FILESPERSET 4
#   NOT BACKED UP SINCE TIME 'SYSDATE-1';
#BACKUP ARCHIVELOG ALL DELETE ALL INPUT;
#DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7';
LIST BACKUP;


#-end of file-

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Drop Oracle database with DBCA and Command Line

Drop Oracle database with DBCA

We can use dbca to drop the database.

Use DBCA GUI

 



 

Select NO.

 

 

The database is being drop. The password file and parameter file are being removed too. ( Both files are under $ORACLE_HOME/dbs).

sqlplus / as sysdba

 

Use DBCA Command line

 

dbca -silent -deleteDatabase -sourceDB oracledb1 -sysDBAUserName sys -sysDBAPassword

 

Create Oracle database -- GUI and Response file

To use DBCA to create the Oracle database.
• Use DBCA GUI mode
• Use Response file with DBCA
• Post configuration for adding Oracle auto start after host reboot

Use GUI

 

 

 

Use response file

dbca -silent -responseFile

 

[GENERAL]
RESPONSEFILE_VERSION = "11.2.0"
OPERATION_TYPE = "createDatabase"
[CREATEDATABASE]
GDBNAME = "oradb1.shilpa.localdomain"
SID = "oradb1"
EMCONFIGURATION = "LOCAL"
TEMPLATENAME = "General_Purpose.dbc"
SYSPASSWORD = "XXXX"
SYSTEMPASSWORD = "XXXX"
EMCONFIGURATION = "LOCAL"
SYSMANPASSWORD = "XXXX"
DBSNMPPASSWORD = "XXXX"
DATAFILEDESTINATION =
#RECOVERYAREADESTINATION=
STORAGETYPE=FS
DISKGROUPNAME=DATA
CHARACTERSET = "WE8MSWIN1252"
NATIONALCHARACTERSET= "AL16UTF16"
LISTENERS = "LISTENER_oracledb1"
INITPARAMS = DB_CREATE_FILE_DEST=/u01/app/oracle/oradata/oracledb1,LOCAL_LISTENER=LISTENER_oracledb1
SAMPLESCHEMA=TRUE
MEMORYPERCENTAGE = "40"
DATABASETYPE = "MULTIPURPOSE"
AUTOMATICMEMORYMANAGEMENT = "TRUE"
#TOTALMEMORY = "800"



 

Tips:

• Because I want to use the Named Listener, so I need to add the LOCAL_LISTENER parameter
• The listener name is not case sensitive so even I set the LISTENERS = "LISTENER_oracledb1", the DBCA would add the LISTENER_ORACLEDB1 in the tnsname.ora .
• For OMF, the DB_CREATE_FILE_DEST must set for /u01/app/oracle/oradata/oracledb1 and can not set for /u01/app/oracle/oradata . The extra oracledb1 is necessary. Otherwise, I would get the below error:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/u01/app/oracle/oradata/oracledb1/control01.ctl'
ORA-27040: file create error, unable to create file
Update ( 2/4): Oracle instance would set the DB_NAME parameter base on the SID. However, the DB_NAME has restriction for 8 characters. Therefore, if the SID name is oracledb1, the DB_NAME would be oracledb. Also if we want to implement the Oracle stream, the DB_DOMAIN is necessary in the parameter. Therefore, I have to change the responds file to accommodate the requirement

 



 

The Enterprise manager is also configure properly.



Update ( 2/4): I often get the dbca create database fail with below error in the log.

ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/oradb2/control02.ctl'
ORA-27040: file create error, unable to create file

 

The permission is indeed set up properly, but some how the dbca fail to create the folder. I just need to manually create the folder first and re run the dbca. 

Sample Netca Response File

###############################################################
## Copyright(c) Oracle Corporation 2002. All rights reserved.##
## ##
## Specify values for the variables listed below to customize##
## your installation. ##
## ##
## Each variable is associated with a comment. The comment ##
## identifies the variable type. ##
## ##
## Please specify the values in the following format: ##
## ##
## Type Example ##
## String "Sample Value" ##
## Boolean True or False ##
## Number 1000 ##
## StringList {"String value 1","String Value 2"} ##
## ##
###############################################################
## ##
## This sample response file causes the Oracle Net ##
## Configuration Assistant (NetCA) to complete an Oracle Net ##
## configuration during a custom install of the Oracle11g ##
## server which is similar to what would be created by the ##
## NetCA during typical Oracle11g install. It also documents ##
## all of the NetCA response files variables so you can ##
## create your own response file to configure Oracle Net ##
## during an install the way you wish. ##
## ##
###############################################################
[GENERAL]
RESPONSEFILE_VERSION="11.2"
CREATE_TYPE="CUSTOM"
#-------------------------------------------------------------------------------
# Name : SHOW_GUI
# Datatype : Boolean
# Description: This variable controls appearance/suppression of the NetCA GUI,
# Pre-req : N/A
# Default : TRUE
# Note:
# This must be set to false in order to run NetCA in silent mode.
# This is a substitute of "/silent" flag in the NetCA command line.
# The command line flag has precedence over the one in this response file.
# This feature is present since 10.1.0.3.
#-------------------------------------------------------------------------------
SHOW_GUI=false
#-------------------------------------------------------------------------------
# Name : LOG_FILE
# Datatype : String
# Description: If present, NetCA will log output to this file in addition to the
# standard out.
# Pre-req : N/A
# Default : NONE
# Note:
# This is a substitute of "/log" in the NetCA command line.
# The command line argument has precedence over the one in this response file.
# This feature is present since 10.1.0.3.
LOG_FILE=""/usr/lib/oracle/xe/app/oracle/product/11.2.0/client/network/tools/log/netca.log""
[oracle.net.ca]

#INSTALLED_COMPONENTS;StringList;list of installed components
# The possible values for installed components are:
# "net8","server","client","aso", "cman", "javavm"
INSTALLED_COMPONENTS={"server","client","net8","javavm"}

#INSTALL_TYPE;String;type of install
# The possible values for install type are:
# "typical","minimal" or "custom"
INSTALL_TYPE=""custom""

#LISTENER_NUMBER;Number;Number of Listeners
# A typical install sets one listener
LISTENER_NUMBER=1

#LISTENER_NAMES;StringList;list of listener names
# The values for listener are:
# "LISTENER","LISTENER1","LISTENER2","LISTENER3", ...
# A typical install sets only "LISTENER"
LISTENER_NAMES={"LISTENER"}

#LISTENER_PROTOCOLS;StringList;list of listener addresses (protocols and parameters separated by semicolons)
# The possible values for listener protocols are:
# "TCP;1521","TCPS;2484","NMP;ORAPIPE","IPC;IPCKEY","VI;1521"
# A typical install sets only "TCP;1521"
LISTENER_PROTOCOLS={"TCP;1521"}

#LISTENER_START;String;name of the listener to start, in double quotes
LISTENER_START=""LISTENER2""

#NAMING_METHODS;StringList;list of naming methods
# The possible values for naming methods are:
# LDAP, TNSNAMES, ONAMES, HOSTNAME, NOVELL, NIS, DCE
# A typical install sets only: "TNSNAMES","ONAMES","HOSTNAMES"
# or "LDAP","TNSNAMES","ONAMES","HOSTNAMES" for LDAP
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}

#NOVELL_NAMECONTEXT;String;Novell Directory Service name context, in double quotes
# A typical install does not use this variable.
#NOVELL_NAMECONTEXT = ""NAMCONTEXT""
#SUN_METAMAP;String; SUN meta map, in double quotes

# A typical install does not use this variable.
#SUN_METAMAP = ""MAP""
#DCE_CELLNAME;String;DCE cell name, in double quotes

# A typical install does not use this variable.
#DCE_CELLNAME = ""CELL""

#NSN_NUMBER;Number;Number of NetService Names
# A typical install sets one net service name
NSN_NUMBER=1

#NSN_NAMES;StringList;list of Net Service names
# A typical install sets net service name to "EXTPROC_CONNECTION_DATA"
NSN_NAMES={"EXTPROC_CONNECTION_DATA"}

#NSN_SERVICE;StringList;Oracle11g database's service name
# A typical install sets Oracle11g database's service name to "PLSExtProc"
NSN_SERVICE={"PLSExtProc"}

#NSN_PROTOCOLS;StringList;list of coma separated strings of Net Service Name protocol parameters
# The possible values for net service name protocol parameters are:
# "TCP;HOSTNAME;1521","TCPS;HOSTNAME;2484","NMP;COMPUTERNAME;ORAPIPE","VI;HOSTNAME;1521","IPC;IPCKEY"

# A typical install sets parameters to "IPC;EXTPROC"
NSN_PROTOCOLS={"TCP;gwl09072appd131;1521"}

Configure Oracle Listener--GUI-RESPONSE-MANUAL-listener does not listen to the default port

Use netca to configure Oracle Listener

netca (Network Configuration Assistant) is Oracle command line for configure the Oracle listener.
Enterprise Manager has dependence on the listener. If listener is not running, we can not login to the Enterprise manager as EM require connects to database to verify the password. If the listener is started after the EM started, we can login to the EM but the agent would fail connect to TNS. In this case, we need to bounce the EM.

Note: Listener name is not case sensitive. LISTENER_ORACLEDB is same as LISTENER_oracledb.

Use the netca GUI to create the listener:





 

 

 

 

 

After Installation Checking the listener status:

 

lsnrctl status

The listener is listen to the default port 1521. The oracle database would auto register to the listener ( PMON processes).

Use response file to create the listener

[ All the response files can be found in the software eg location: /u01/client/response]

Prepare the response file

The tip for the response file is INSTALL_TYPE. If you want to have the custom listener name and port instead of default name < LISTENER> and port 1521.
We need to set this property as “CUSTOM”

[GENERAL]
RESPONSEFILE_VERSION="11.2"
CREATE_TYPE="CUSTOM"
SHOW_GUI=false
LOG_FILE=""/u01/app/oracle/product/11.2.0/network/tools/log/netca.log""
[oracle.net.ca]
INSTALL_TYPE=""CUSTOM""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER_ORACLEDB1"}
LISTENER_PROTOCOLS={"TCP;7001"}
LISTENER_START=""LISTENER_ORACLEDB1""
INSTALLED_COMPONENTS={"server","client","net8","javavm"}

Execute the netca

$ORACLE_HOME/bin/netca –silent -responsefile=/local_dir/netca.rsp
or
$ORACLE_HOME/bin/netca /silent /log $ORACLE_HOME/network/tools/log/netca.log /responsefile /home/oracle/netca.rsp

Finally, we check the status of the listener:lsnrctl status LISTENER_ORACLEDB1

Manually Create the listener

Manually create the listener.ora file and start the listener from command line.
A]

LISTENER_ORACLEDB1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = shilpa.localdomain)(PORT = 7001))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC7001))
    )
  )

ADR_BASE_LISTENER_ORACLEDB1 = /u01/app/oracle

 B]

lsnrctl stop LISTENER_ORACLEDB
lsnrctl status LISTENER_ORACLEDB

If listener does not listen to the default port

In the example below, my listener is set to listen port 7001 hence the database instance would not connect to the listener automatically.

 lsnrctl status

The solution is to set the local listener ( LOCAL_LISTENER) parameter.

set local listener

localhost can refer to the local host or the host name on the host ( In this example, panda1 ) or the remote host where the listener is running.

alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=7001))' scope=both;





Alternately, we can put the host configuration in the tnsname.ora.
LISTENER_ORACLEDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 7003))

and change the LOCAL_LISTENER point to the LISTENER_ORACLEDB1
alter system set LOCAL_LISTENER='LISTENER_ORACLEDB1' scope=both;

We must create the entry in the tnsname.ora first otherwise we could get the error while trying to run the alter system command.

 

 

 

Install Oracle 11gr3 silent on linux

Install Oracle preinstall package

The oracle preinstall package used to be call‘”Oracle Validate” in 10g. This package would also update the kernel parameter as Oracle suggest. Before it modify the parameter, it would backup the old one to the /etc/sysctl.conf.orabackup.

yum install oracle-rdbms-server-11gR2-preinstall

or
 Make manual changes to /etc/sysctl.conf
 

Refer <http://docs.oracle.com/cd/E11882_01/install.112/e22489/manpreins.htm#BABCHAED>for the Oracle Requirement and fix kernel parameters. oracle-rdbms-server-11gR2-preinstallwould take care of the changes, just double check and adjust it if necessary.

Create Required Group and User

The oracle-rdbms-server-11gR2-preinstall would create oracle user, add oinstall and dba group. Also make the oracle user as part of member of the group.

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba

/usr/sbin/useradd -c "Oracle Software Owner" -g oinstall -G dba -d /home/oracle oracle

 We also need to reset the oracle user password.

passwd oracle

Create Software installation folders

•  /u01/app/oracle –> for $ORACLE_BASE for Oracle Database instance
• /u01/app/oracle/product/11.2.0 –> for $ORACLE_HOME

mkdir -p /u01/app/oracle/product/11.2.0
chown -R oracle:oinstall /u01/app
chown -R oracle:oinstall /u01/app/oracle
chmod 775 /u01/app


Configure User environment

As oracle user:

Update ~oracle/.bash_profile

TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=HOST01.shilpa.com; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/11.2.0 export ORACLE_HOME
ORACLE_SID=oracledb1; export ORACLE_SID
ORACLE_UNQNAME=oracledb1; export ORACLE_UNQNAME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
 

Creating the oraInst.loc File

As root user:

Create the /etc/oraInst.loc as below and change the permission and ownership

inventory_loc=/u01/app/oraInventory
inst_group=oinstall

chown oracle:oinstall oraInst.loc
chmod 664 oraInst.loc

Prepare the response file: Oracle Software Only

The sample response file can be found under Oracle installation media“database/response ”

• dbca.rsp : Silent install for the Oracle database ( for DBCA)
• db_install.rsp: silent install for the Oracle Software.
• netca.rsp: Silent install for Oracle Net Configuration Assistant

Here is the example of db_install.rsp.

Even many of the parameters are empty, but if we don’t include in the rsp file, we would get the error like:

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=HOST01.shilpa.com
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/11.2.0
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.EEOptionsSelection=falseoracle.install.db.optionalComponents=oracle.rdbms.partitioning:11.2.0.3.0,oracle.oraolap:11.2.0.3.0,oracle.rdbms.dm:11.2.0.3.0,oracle.rdbms.dv:11.2.0.3.0,oracle.rdbms.lbac:11.2.0.3.0,oracle.rdbms.rat:11.2.0.3.0
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
oracle.install.db.CLUSTER_NODES=
oracle.install.db.isRACOneInstall=
oracle.install.db.racOneServiceName=
oracle.install.db.config.starterdb.type=
oracle.install.db.config.starterdb.globalDBName=
oracle.install.db.config.starterdb.SID=
oracle.install.db.config.starterdb.characterSet=WE8MSWIN1252
oracle.install.db.config.starterdb.memoryOption=
oracle.install.db.config.starterdb.memoryLimit=
oracle.install.db.config.starterdb.installExampleSchemas=falseoracle.install.db.config.starterdb.enableSecuritySettings=trueoracle.install.db.config.starterdb.password.ALL=
oracle.install.db.config.starterdb.password.SYS=
oracle.install.db.config.starterdb.password.SYSTEM=
oracle.install.db.config.starterdb.password.SYSMAN=
oracle.install.db.config.starterdb.password.DBSNMP=
oracle.install.db.config.starterdb.control=DB_CONTROL
oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL=
oracle.install.db.config.starterdb.automatedBackup.enable=falseoracle.install.db.config.starterdb.automatedBackup.osuid=
oracle.install.db.config.starterdb.automatedBackup.ospwd=
oracle.install.db.config.starterdb.storageType=
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
oracle.install.db.config.asm.diskGroup=
oracle.install.db.config.asm.ASMSNMPPassword=
SECURITY_UPDATES_VIA_MYORACLESUPPORT=falseDECLINE_SECURITY_UPDATES=truePROXY_HOST=
PROXY_PORT=
PROXY_USER=
PROXY_PWD=
PROXY_REALM=
COLLECTOR_SUPPORTHUB_URL=
oracle.installer.autoupdates.option=
oracle.installer.autoupdates.downloadUpdatesLoc=

Run the Installer in Silent mode: Oracle Software Only

./runInstaller -silent -responseFile /home/oracle/db_install.rsp

or

./runInstaller -silent -ignoreSysPrereqs -ignorePrereq -noconfig -force -responseFile /home/oracle/db_install.rsp -debug

Common Problems:

1> Xlib: No Protocol

[root@HOST01 logs]# echo $DISPLAY
:0.0
[root@HOST01 logs]# xauth list $DISPLAY
HOST01.shilpa.com/unix:0  MIT-MAGIC-COOKIE-1  13bce55c5549e0dfc6844a24b8ef546e
[oracle@HOST01 ~]$ xauth add HOST01.shilpa.com:0 . 13bce55c5549e0dfc6844a24b8ef546e
xauth:  creating new authority file /home/oracle/.Xauthority
~~~~~~~~~
[oracle@HOST01 ~]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost.localdomain HOST01.shilpa.com
::1             localhost6.localdomain6 localhost6
192.168.72.1 HOST01.shilpa.com
~~~~~~~~~
export DISPLAY=192.168.72.1:0.0

2> Exception in thread "main" java.lang.NoClassDefFoundError

--> Check if java is installed (if not installed: yum install *java* and yum install *jdk* and yum install *jar*)
--> Test for the env:
i)   which java
ii)  java -version
iii) JAVA_HOME =
  SET your JAVA_HOME to the location of your installed java and
iv)  export PATH=$JAVA_HOME/bin:$PATH
v) unset DISPLAY

3> If installation fails at email entry, please check the following two parameters in the response file as below:

SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true

As a root user, execute root.sh

If faced the below error:

$ sqlplus / as sysdba
$ sqlplus: error while loading shared libraries: $ORACLE_HOME/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

Its due to the SELINUX settings, which should have been initially disabled; But, never mind it can be fixed with the following command:

Commands, as root:
======================
getenforce (returns "Enforcing")
setenforce 0
getenforce (returns "Permissive")

 Further, check logging into sql prompt by the following command:

sqlplus / as sysdba

  Create the Listener

Create the $ORACLE_HOME/network/admin/listener.ora with below information:

LISTENER_oracledb1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOST01.shilpa.com)(PORT = 7001))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC7001))
    )
  )

ADR_BASE_LISTENER_oracledb1 = /u01/app/oracle

  

Then start the listener via

lsnrctl start LISTENER_ORACLEDB1

Alternatively, we can use netca to create the listener.

 

Oracle Tablespace usage

 

DBA_TABLESPACE_USAGE_METRICS

select * from DBA_TABLESPACE_USAGE_METRICS;

The size is based on the block.

DBA_HIST_TBSPC_SPACE_USAGE

DBA_HIST_TBSPC_SPACE_USAGE contains the historical usage information collect by AWR.

select TABLESPACE_ID,NAME,TABLESPACE_SIZE,TABLESPACE_USEDSIZE,RTIME from DBA_HIST_TBSPC_SPACE_USAGE,v$tablespace where TABLESPACE_ID=TS# order by RTIME,dba_hist_tbspc_space_usage.tablespace_id;