Dynamic File Name in Command Spool

Is it possible to change the spool file name dynamically?

Yes its possible to change the name dynamically. but its depending on the requirement.Here is the sample example how we can add the date string to the file name.

once you know how to add the date string to file name then you can apply the same formula to any others.

we have to take one dummy column to print and will take the date in to that dummy column. see the example below

SQL> column filename new_val filename
SQL> select sysdate filename from dual;

FILENAME
---------
28-JUL-16

SQL> spool &filename
SQL> select sysdate from dual;

SYSDATE
---------
28-JUL-16

SQL> spool off;
SQL>

once its done ,you can check the file in the same folder from where you have login to oracle.
or you can specify the path to generate the file.

for this case all the files will generate with the extinction is ".LST". example : "28-JUL-16.LST"

Oracle listener errors : Troubleshooting Oracle Net Services :ORA-12541 TNS :no listener:ORA-01034: ORA-27101

Oracle listener errors :

you may get following type of error for listener :

C:\Users\Administrator>lsnrctl status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=WIN-JSJDDUESQH9)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
64-bit Windows Error: 61: Unknown error

When you get the error "TNS no listener errors", that the Windows listener service is not accepting connections or the service is not running.As recommended need to re-start the listener by using the command "lsnrctl start" or lsnrctl reload  

 

 Once the listener started using "LNSRCTL START or LSNRCTL RELOAD" listener will start and you can observe the status of current listener by run the following command "LSNRCTL STATUS"

Most of the cases once the listener will start then your are able to connect the oracle and issue got resolved.Even though listener was running some time you may get another type of errors like "ORA-01034: ORA-27101:  "as following :

C:\Users\Administrator>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 25 17:44:31 2016

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

Enter user-name: system
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0

For these kind of errors even listener was running, we have to run the following commands to resolve the issue. We have to connect the oracle by using /nolog first and then run the below commands.

set oracle_sid=orcl
sqlplus /nolog
conn sys/sys as sysdba
shutdown abort
startup

check the below image :

now the oracle has started successfully and you are able to see the ORCL instance in listener status also.

ORACLE 12C INSTALLATION STEP BY STEP

Prerequisites to install ORACLE 12C  :

Download Software :

Download the Oracle software from OTN depending on your requirements.

    OTN: Oracle Database 12c Release 1 (12.1.0.2.0) for Linux x86-64

download it from ORACLE site or use the following url

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-linux-download-2240591.html

Verify that your system has correct hostname, static IP address and distribution version, using following commands.

If you’ve not set your system hostname, edit the system hosts file ‘/etc/hosts‘ and enter your hostname entry along with IP address as shown below.

hostname   # to check the hostname vi /etc/hosts  # to Update the new hostname

 

Need to change the SELinux mode to permissive and restart the system for Permanent changes of selinux
edit the file using vi editor and update as SELINUX=permissive

vi /etc/sysconfig/

#Installing Packages and changing Kernel Values
Check the below listed packages are already installed or not if not please install them :
# From Public Yum
yum install binutils -y
yum install compat-libcap1 -y
yum install compat-libstdc++-33 -y
yum install compat-libstdc++-33.i686 -y
yum install gcc -y
yum install gcc-c++ -y
yum install glibc -y
yum install glibc.i686 -y
yum install glibc-devel -y
yum install glibc-devel.i686 -y
yum install ksh -y
yum install libgcc -y
yum install libgcc.i686 -y
yum install libstdc++ -y
yum install libstdc++.i686 -y
yum install libstdc++-devel -y
yum install libstdc++-devel.i686 -y
yum install libaio -y
yum install libaio.i686 -y
yum install libaio-devel -y
yum install libaio-devel.i686 -y
yum install libXext -y
yum install libXext.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install libXi -y
yum install libXi.i686 -y
yum install make -y
yum install sysstat -y
yum install unixODBC -y
yum install unixODBC-devel -y

Once completed the package installation, Edit the kernal parameters in file and update the following as suggested by the ORACLE:

vi /etc/sysct.conf

kernel.shmmax = 4294967295 kernel.shmall = 2097152 fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576

Once the file got updated then issue following command to effect the new changes.

/sbin/sysctl -p

Add add the following lines to the "/etc/security/limits.conf" file.

vi /etc/security/limits.conf

oracle   soft   nofile    1024 oracle   hard   nofile    65536 oracle   soft   nproc    16384 oracle   hard   nproc    16384 oracle   soft   stack    10240 oracle   hard   stack    32768

Configuring System for Oracle Installation
 Create new as Oracle DBA,OPER and OINSTALL for oracle installation

groupadd -g 54321 oracle groupadd -g 54322 dba groupadd -g 54323 oper groupadd -g 54324 oinstall

Create user oracle and add the user to specified groups.

useradd -u 54321 -g oracle -G dba,oper oracle passwd oracle

Create the following directories for installing Oracle and change the ownership of the directories and grand permission to the newly created directories.

mkdir -p /u01/app/oracle/product/12.1.0/dbhome_1 chown -R oracle:oracle /u01 chmod -R 775 /u01 ll /u01

Once successfully created the oracle user then we need to add environmental variable for oracle user. Edit the bash_profile file of oracle user and append the oracle environment entries listed below.

/home/oracle/.bash_profile

edit the file from oracle user and add the following lines into the bash_profile.

# Oracle Env Settings export TMP=/tmp export TMPDIR=$TMP export ORACLE_HOSTNAME=murthy.oracle12 export ORACLE_UNQNAME=orcl export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1 export ORACLE_SID=orcl export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Save and exit the file.

Till now we have successfully completed the prerequisites to install the oracle 12c.

Installing Oracle 12c Database in Red hat 6.x

 After download the software extract the .zip files using unzip command, After extracting we will get database directory into the same folder. To start installation need to run the command "./runInstaller" from installer Directory(database directory).

For unzip use the following command unzip linuxamd64_12102_database_1of2 unzip linuxamd64_12102_database_2of2

# cd database/ # ./runInstaller

Once you run the ./runInstaller then you will get the first screen as below.

the above screen I am going to skip this step as I don’t want security updates. then you will get error message as follow :

Click "YES" and choose the installation type.

I am choosing the first option ,Create and configure a database from below screen and click next.

we are going to choose Server Class here. If we want to install in any Desktop machines we can choose the above Option as Desktop Class. NEXT

we are going to select "single instance database installation" ,Next

 Choose Advance install option to get more options in the Installation steps, Next

Default Language will be chooses as English. you can change according to your language if needed , Next

Here we have to choose which edition of database installation we are going to do.Oracle has specified the minimum size of disk space requirement based on installation type.I am going to select Enterprise edition for testing purpose. Next,

Here we have to to specify oracle installation path, it will fetch the configuration based on our ".bash_profile".Next,

Oracle Inventory files will be created under ‘/u01/app/oralnventory‘ directory. Let us choose the Oracle as the Group for Operating system group,Next

Select the type of database, you want to create. I am choosing general option Since, I am using it for General purpose. Next,

Specify the Global Database name for uniquely identified and UN-check the Create as Container database, because we are not going to create multiple databases.

Check the box that says Enable Automatic Memory Management and keep the default memory allocation oracle recommendations.  Next,

Here we are not using ASM and using file system, so we need to choose the location to store the database.I’m going to assign ‘/u01/app/oracle/oradata‘ location to save the databases and Click Next to continue to installer steps.

I don’t have a Cloud control manager credentials from oracle, so I have to skip this step.Next

In realtime environment we have to enable these options as mandatory.Now I am going to skip these steps. Next

Here we need to define the password for different users or same for all users . next

Would suggest to create password oracle standards,if not will get popup like this,click "YES"

Need to provide system privileges to create database. Choose oracle for every options.

Finally we can review all settings before database installation starts. If we want any changes we can edit the settings. click on "Install" to start the installation  

Database installation was started successfully and installation started to Preparation and copying files. This will take long time to complete according to our Hardware Resource.

During setup process, it will ask to run two scripts as a root user as shown in the above picture.

Login as root user and run the scripts.

 After successfully execution of above two scripts, click on OK to continue

 Database configuration Assistant installation process ...

Finally Oracle Database installation was successfully completed. Click on Close to quit the Oracle Installer.Using the URL you can login into browser with the DB credentials and you can do the system operations.

Click close to end the installation.