1. From within Oracle SQL*Plus session.
2. Using the userenv(‘SESSIONID’) to retrieve current sid and serial#.
select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);
SQL> select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);
SID SERIAL#
———- ———-
408 16328
SQL>
3. Note: That logging out of SQL*Plus session or reconnecting will change your SID and SERIAL#.
Larry Catt
OCP
Error:
ORA-01578: ORACLE data block corrupted (file # 3, block # 136)
ORA_01110: data file 3: ‘/u01/oradata/ORCL/UNDOTBS01.DBF’
This procedure replaces the current UNDO tablespace which contains a corrupted block with a new clean tablespace.
1. Startup database in nomount mode.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.2761E+10 bytes
Fixed Size 2240256 bytes
Variable Size 8388608256 bytes
Database Buffers 4362076160 bytes
Redo Buffers 7634944 bytes
SQL>
2. Verify that you are using an spfile and not a pfile for your system paramaters.
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string
SQL>
2a. If parameter spfile has a value, continue to step 3, if it does not perform 2b through 2e.
2b. Execute ‘create spfile’ command:
SQL> create spfile from pfile;
File created.
SQL>
2c. Shutdown the oracle instance.
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
2d. Startup the oracle instance in nomount mode.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.2761E+10 bytes
Fixed Size 2240256 bytes
Variable Size 8388608256 bytes
Database Buffers 4362076160 bytes
Redo Buffers 7634944 bytes
SQL>
2e. Verify the use of spfile with the show command.
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /opt/app/oracle/ORCL_db/dbs/sp
fileORCL.ora
SQL>
3. Get the values for parameters undo_management and undo_tablespaces.
SQL> show parameter undo_management
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
SQL> show parameter undo_tablespace
NAME TYPE VALUE
———————————— ———– ——————————
undo_tablespace string UNDOTBS1
SQL>
4. Change the undo_management setting to manual.
SQL> alter system set undo_management=manual scope=spfile;
System altered.
SQL>
5. Mount and Open the database instance.
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL>
6. Create new undo tablespace.
SQL> create undo tablespace undotbs02 datafile ‘/u02/oradata/ORCL/UNDOTBS02.DBF’ size 5G;
Tablespace created.
SQL>
7. alter parameter undo_tablespace to undotbs02.
SQL> alter system set undo_tablespace=undotbs02 scope=spfile;
System altered.
SQL>
8. alter parameter undo_management to auto.
SQL> alter system set undo_management=auto scope=spfile;
System altered.
SQL>
9. Shutdown and startup the oracle instance.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1.2761E+10 bytes
Fixed Size 2240256 bytes
Variable Size 8388608256 bytes
Database Buffers 4362076160 bytes
Redo Buffers 7634944 bytes
Database mounted.
Database opened.
SQL>
10. Check parameters undo_management and undo_tablespaces.
SQL> show parameter undo_management
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
SQL> show parameter undo_tablespace
NAME TYPE VALUE
———————————— ———– ——————————
undo_tablespace string UNDOTBS02
SQL>
That completes changing undotablespaces in 11g
Larry Catt
OCP
You can call into SQLPLUS to execute SQL script from any shell script. This procedure shows how to structure your statements to LINUX environment for call into SQLPLUS from shell script.
1. Logon to your LINUX server as a user with access to SQLPLUS.
2. Use VI to create a executable file. Replace the connection string with appropriate username/password@SID values. Additionally, change the script you which to execute in this example we are executing “utlrp.sql”.
sqlplus sys/password@orcl as sysdba < < EOF @?/rdbms/admin/utlrp.sql exit; EOF
Larry Catt
OCP
There are multiple situations where you have to execute processes in your Oracle environment as a daemon or background process of your OS. This procedure describes the use of “NOHUP” to begin the execution of a background process. In this example we are executing the Oracle 12c upgrade script ‘preupgrd.sql’ on an OEL (Oracle Enterprise Linux) platform.
1. Logon to your OEL server as the oracle software owner.
root@mylinux#su – oracle
oracle@mylinux#
2. Change directory to the location of your script file.
oracle@mylinux#cd /opt/app/oracle/product/12.1.0.2/rdbms/admin/
oracle@mylinux#
3. At the OS prompt execute the NOHUP command passing in a sqlplus connection string and the script name to be executed following by a terminating ampersand sign ‘&’.
nohup sqlplus sys/password as sysdba @./preupgrd.sql &
oracle@mylinux#nohup sqlplus sys/password as sysdba @./preupgrd.sql &
[1] 7260
oracle@mylinux#nohup: ignoring input and appending output to `nohup.out’
oracle@mylinux#
4. The (nohup) command will produce a log file in the local directory named nohup.out which records all terminal output from the daemon.
oracle@mylinux#cat nohup.out
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 26 17:28:19 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Loading Pre-Upgrade Package…
SQL>
5. You can also see the processing with the system process command (ps) as shown below.
oracle@mylinux#ps -ef|grep nohup
oracle 9707 9268 0 18:11 pts/0 00:00:00 grep nohup
[1]+ Exit 1 nohup sqlplus sys/password as sysdba @./preupgrd.sql
oracle@mylinux#
6. This completes executing an Oracle script as a LINUX or UNIX background process.
Larry J. Catt, OCP
This procedure demonstrates the method to change the time zone setting of an Oracle RDBMS installed on a Linux server. NOTE: Oracle RDBMS by default take the time of the OS which they are installed on. Due to the number and variations LINUX distributions, this procedure may not work on all LINUX installs. This procedure was performed on Oracle Enterprise Linux 7.2 (OEL 7.2).
[larry@linux2 ~]$ sudo su
[sudo] password for larry:
[root@linux2 larry]#
[root@linux2 etc]# su – oracle
Last login: Wed Nov 30 16:36:03 UTC 2016 on pts/1
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 30 16:38:13 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
—————————————————————————
30-NOV-16 04.40.33.575810 PM +00:00
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@linux2 ~]$ su –
Password:
Last login: Wed Nov 30 16:38:00 UTC 2016 on pts/1
[root@linux2 ~]#
[root@linux2 ~]# cd /etc
[root@linux2 etc]#
[root@linux2 etc]# date
Wed Nov 30 12:39:08 UTC 2016
[root@linux2 etc]#
[root@linux2 etc]# ls -l localtime
lrwxrwxrwx. 1 root root 23 Nov 30 14:22 localtime -> /usr/share/zoneinfo/UTC
[root@linux2 etc]# rm -rf localtime
[root@linux2 etc]#
ln -s /usr/share/zoneinfo/EST localtime
[root@linux2 etc]# ls -l localtime
lrwxrwxrwx. 1 root root 23 Nov 30 09:23 localtime -> /usr/share/zoneinfo/EST
[root@linux2 etc]#
[root@linux2 etc]# date
Wed Nov 30 11:43:59 EST 2016
[root@linux2 etc]#
[root@linux2 etc]# su – oracle
Last login: Wed Nov 30 11:41:44 EST 2016 on pts/1
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 30 11:45:13 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
—————————————————————————
30-NOV-16 11.46.25.722487 AM -05:00
SQL>
Larry Catt
OCP
RMAN is the preferred method used for backup and recovery of Oracle database. This article gives a brief overview of commands used to Configure, Display Configurations, Reset to Default, and take simple backups.
Larry Catt
OCP
In Oracle 11g a native NFS client has been placed in Oracle Database kernel. It improves performance when Network File Systems are in use. Oracle NFS kernel optimizes and eliminates overhead of NFS, eliminating the need to tune NFS parameters at OS layer.
To use Direct NFS, the NFS file system must be mount and available by OS kernel . The Oracle Direct NFS Client manages Oracles access to drives, by a configuration file call orafstab or mount tab file /etc/mtab on linux. The file $ORACLE_HOME/dbs/orafstab would specify the direct NFS client settings for a single database. Oracle looks for settings in /etc/oranfstab then Oracle reads /etc/mtab to identify available NFS mounts.
To use Direct NFS mount setting you have to replace standard Oracle Disk Manager (ODM) library with one supporting Direct NFS Client.
Example
[oracle@linux2 ~]$ $ORACLE_HOME/dbs/orafstab
server: MyNFSServer1
path: 192.168.1.1
path: 192.168.1.2
path: 192.168.1.3
path: 192.168.1.4
export: /vol/ oradata1
mount: /mnt/ oradata1
Once the file exists, the following commands will enable the Direct NFS Client ODM Library
[oracle@linux2 ~]$ cd $ ORACLE_HOME/ lib
[oracle@linux2 ~]$ cp libodm11. so libodm11. so_stub
[oracle@linux2 ~]$ ln –s libnfsodm11. so libodm11. so
Larry Catt
OCP
When preparing your RHEL server for an Oracle RDBMS or other application software installations, it is common to have multiple oracle RDBMS instances or other related applications which share the same environmental variables, but with different values. This procedure shows how to setup your RHEL environment to support multiple Oracle software installs.
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# User specific environment and startup programs
echo “Choose the number associated with the SID you wish to administrate:”
echo ” 1 – ORCL”
echo ” 2 – MYDB”
read SID_NUMBER
if [ $SID_NUMBER = 1 ]; then
echo “SID Number is $SID_NUMBER”
<ENTER ENVIRONMENTAL Variables for instance 1 here>
elif [ $SID_NUMBER = 2 ]; then
echo “SID Number is $SID_NUMBER”
<ENTER ENVIRONMENTAL Varaibles for instance 2 here>
else
<Default environmental variables>
echo “Invalid SID Number, defaulting to $ORACLE_SID”
fi
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
echo “Choose the number associated with the SID you wish to administrate:”
echo ” 1 – ORCL”
echo ” 2 – MYDB”
read SID_NUMBER
if [ $SID_NUMBER = 1 ]; then
echo “SID Number is $SID_NUMBER”
export JAVAPATH=/usr/bin/java
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/ORCL_db
export ORACLE_SID=ORCL
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$JAVAPATH:$ORACLE_HOME/lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
umask 022
elif [ $SID_NUMBER = 2 ]; then
echo “SID Number is $SID_NUMBER”
export JAVAPATH=/usr/bin/java
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/MYDB_db
export ORACLE_SID=MYDB
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$JAVAPATH:$ORACLE_HOME/lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
umask 022
else
export JAVAPATH=/usr/bin/java
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/MYDB_db
export ORACLE_SID=MYDB
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$JAVAPATH:$ORACLE_HOME/lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
umask 022
echo “Invalid SID Number, Defaulting to $ORACLE_SID”
fi
[root@MYLINUX ~]# su – oracle
Choose the number associated with the SID you wish to administrate:
1 – ORCL
2 – MYDB
1
SID Number is 1
[oracle@MYLINUX ~]$ exit
logout
[root@MYLINUX ~]# su – oracle
Choose the number associated with the SID you wish to administrate:
1 – ORCL
2 – MYDB
2
SID Number is 2
[oracle@MYLINUX ~]$ exit
logout
[root@MYLINUX ~]# su – oracle
Choose the number associated with the SID you wish to administrate:
1 – ORCL
2 – MYDB
5
Invalid SID Number, Defaulting to MYDB
[oracle@MYLINUX ~]$
Larry Catt, OCP
Normally failure of oracle utility commands occurs from improper environmental parameters or configuration problems. This procedure shows the steps in resolving oracle error ORA-12541 during execution of EMCA repository creation.
Initial Error:
[oracle@mylinux1 ~]$ emca -repos create
STARTED EMCA at Apr 7, 2014 4:31:20 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.
Enter the following information:
Database SID: ORCL
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 7, 2014 4:31:36 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /opt/app/oracle/cfgtoollogs/emca/ORCL/emca_2014_04_07_16_31_19.log.
Apr 7, 2014 4:31:36 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
WARNING: ORA-12541: TNS:no listener
Apr 7, 2014 4:31:36 PM oracle.sysman.emcp.EMConfig perform
SEVERE:
Database connection through listener failed. Fix the error and run EM Configuration Assistant again.
Some of the possible reasons may be:
1) Listener port 1521 provided is incorrect. Provide the correct port.
2) Listener is not up. Start the Listener.
3) Database service ORCL is not registered with listener. Register the database service.
4) Listener is up on physical host and ORACLE_HOSTNAME environment variable is set to virtual host. Unset ORACLE_HOSTNAME environment variable.
5) Listener is up on virtual host. Set environment variable ORACLE_HOSTNAME=<virtual host>.
6) /etc/hosts does not have correct entry for hostname.
Refer to the log file at /opt/app/oracle/cfgtoollogs/emca/ORCL/emca_2014_04_07_16_31_19.log for more details.
Could not complete the configuration. Refer to the log file at /opt/app/oracle/cfgtoollogs/emca/ORCL/emca_2014_04_07_16_31_19.log for more details.
[oracle@mylinux1 ~]$
Solution:
1. Validate listener port is 1521 with command “netstat -tulpn|grep 1521”:
[oracle@mylinux1 ~]$ netstat -tulpn|grep 1521
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 192.168.1.120:1521 0.0.0.0:* LISTEN 10883/tnslsnr
udp 0 0 :::15212 :::* 1831/ora_p025_ORCL
[oracle@mylinux1 ~]$
NOTE: Oracle is listening on port 1521 with process 10883/tnslsnr. If the oracle listener is not on port 1521, open the file listener.ora to determine the correct port.
2. Validate that the Listener is up with command “ps -ef|grep tns”:
[oracle@mylinux1 ~]$ ps -ef|grep tns
root 90 2 0 Apr01 ? 00:00:00 [netns]
oracle 10883 1 0 Apr01 ? 00:03:54 /opt/app/oracle/ORCL_db/bin/tnslsnr LISTENER -inherit
oracle 21669 20714 0 16:45 pts/0 00:00:00 grep tns
[oracle@mylinux1 ~]$
NOTE: The listener is up with process id of 10883. If the listener is not running, start the listener with command: “lsnrctl start”
3. Verify that the Database service ORCL is registered with listener by using the command “tnsping ORCL”:
[oracle@mylinux1 ~]$ tnsping ORCL
TNS Ping Utility for Linux: Version 11.2.0.3.0 – Production on 07-Apr-2014 16:47:16
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/opt/app/oracle/ORCL_db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.120)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) (SID = ORCL)))
OK (200 msec)
[oracle@mylinux1 ~]$
NOTE: The ORCL service is register with the listener. If it is not, edit the file listener.ora to add the service.
4. Verify that ORACLE_HOSTNAME is set with command “echo $ORACLE_HOSTNAME”:
[oracle@mylinux1 ~]$ echo $ORACLE_HOSTNAME
[oracle@mylinux1 ~]$
NOTE: the ORACLE_HOSTNAME variable is not set, set the ORACLE_HOSTNAME variable with the command “export ORACLE_HOSTNAME=mylinux1” NOTE: place this variable in your bash startup profile.
[oracle@mylinux1 ~]$ export ORACLE_HOSTNAME=mylinux1
[oracle@mylinux1 ~]$ echo $ORACLE_HOSTNAME
mylinux1
[oracle@mylinux1 ~]$
5. Same as step 4 but using virtual hostname.
6. Verify that the file /etc/hosts contains the hostname of current system with command “cat /etc/hosts”
[oracle@mylinux1 ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 mylinux1 mylinux1.mydomain.com
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 mylinux1 mylinux1.mydomain.com
192.168.1.120 mylinux1 mylinux1.mydomain.com
[oracle@mylinux1 ~]$
NOTE: The hostname is in the /etc/hosts file. But if it is not edit the file /etc/hosts.
7. Re-execute the EMCA command for successful completion:
[oracle@mylinux1 ~]$ emca -repos create
STARTED EMCA at Apr 7, 2014 4:59:36 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.
Enter the following information:
Database SID: ORCL
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 7, 2014 5:00:12 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /opt/app/oracle/cfgtoollogs/emca/ORCL/emca_2014_04_07_16_59_35.log.
Apr 7, 2014 5:00:13 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …
Apr 7, 2014 5:14:27 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 7, 2014 5:14:27 PM
[oracle@mylinux1 ~]$
Larry Catt, OCP
After building your Oracle 11g database you receive the following error upon login to SQLPLUS as a user other than SYS or SYSTEM:
Error:
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
The following procedure install the correct components to resolve this error:
1. Login to SQLPLUS as the DBSNMP user and note the error received.
SQL> connect dbsnmp/ucop_pass@ucop1
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL>
SQL> connect system/ucop_pass@ucop1
Connected.
SQL>
Windows:
SQL> @?/sqlplus/admin/pupbld.sql
Linux / Unix
SQL> @?\sqlplus\admin\pupbld.sql
SQL> connect dbsnmp/ucop_pass@ucop1
Connected.
SQL>
Note: the error message does not appear.
Larry Catt