How to compress/purge a listener log file?

Sometimes listener log file grows very large in size and we have to shrink that in order to reclaim space on the mount. I see some people just wonder how they can compress the listener log file while it is currently in use in production system as they can't just stop and start the listener to compress/rename the file as unavailability of listener will cause user connections to fail and that will be encountered as production outage by end user.

Below I have outlined two steps where we tell listener to stop logging in the listener log file and then rename/compress/purge the listener log file and finally we can tell listener back that it can start logging the info in the listener.log file.


Step : 1 - First of all turn off the logging status of listner using below command at lsnrctl prompt.

LSNRCTL> SET LOG_STATUS OFF
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajdbsolutions.com)(PORT=1522))
LISTENER parameter "log_status" set to OFF
The command completed successfully

After the above step, go to listener log location and rename the listener.log file to something else e.g. Listener_old.log

Again turn back the listener logging ON using below command.

LSNRCTL> SET LOG_STATUS ON
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajdbsolutions.com)(PORT=1522))
LISTENER parameter "log_status" set to ON
The command completed successfully

When you turn ON the listener log using above command, a new listener.log file will be created there and it will be used hereafter for database connectivity.


Hope it would help someone...!!

2 Node Oracle 11g R2 RAC Installation Part - I - Nodes and Shared Storage Preparation.

RAC Installation Part - I    -- Nodes and Shared Storage Preparation.  



Are you scared of RAC installation?

Are you just planning to go for course or training to do RAC only just because of its complex architecture and implementation?


Here just walk with me for a while with below outlined steps and tell me if you still have trouble in RAC Implementation on your personal laptop to get practised:-)


I have been receiving lots of request to write a paper on Oracle RAC implementation for practice newbies. It is quite challenging for those who never deployed RAC and want to try it on their own laptops / PC due to its complex architecture and shared storage availability. In order to install Oracle RAC(Real Application Cluster), we must have a shared storage configured there to hold all database files accessible to all nodes in the cluster configuration. In this post I will explain how we can prepare two virtual machines / nodes with a shared storage device shared between both nodes to install 2 Node Oracle RAC for our practice. In this 2 Node Oracle RAC configuration I have compromised to use host file for Public IPs, Private IPs, VIP and SCAN NAME / IPs instead of using DNS.


Let's proceed here with creating two virtual machines for our Oracle RAC implementation.


Note:- I have not put the screenshots for VM creation steps in this paper just to keep this paper light weight :-)



Create Virtual Machines in VMware Workstation:
===============================

=> Click on File Menu of VMware Workstation.
=> Click Next > Click Next.
=> Choose the option > I will install operating system later and click Next.
=> Select Linux under Guest Operating System region and and Select Version from drop down menu and Click Next.
=> Choose Virtual machine Name(RAC1 in my case) and the location where you want to store the configuration files for this virtual machine(E:\RAC-Nodes\Node01\, in my case)
=> Click on next at Processor Configuration window.
=> Specify Memory for the virtual machine(I choosen 2048M in my case).
=> Use default Network type(NAT in my case) and click on Next.
=> Select I/O controller types as LSI Logic(Recommended) and click on next.
=> Select a Device Type as SCSI(recommended) and click on Next.
=> Select "Create a new virtual Disk" and click on Next.
=> Specify Disk capacity : 30GB enough for 11g R2 and Select option "Allocate all disk space now"and "Store virtual disk as single file"and click on Next.
=> Provide complete path before Disk file : E:\RAC-Nodes\Node01\RAC1.vmdk and click On Next.
=> Click on Finish.....You're done here for the first virtual machine.


Note : - Create another virtual machine with name "RAC2" following same steps outlined above and install Linux OS in both VMs with following mount details at minimum in order to configure Oracle RAC.

------------------
/ 10GB

/swap 4096MG

/u01 10204MB

/boot 100MB

/tmp 4096MB



Step : 2 - Now let's add a separate Hard Disk device in our first virtual machine(RAC1) to be used as a shared storage between both nodes as follows.

First of all, create a separate folder (E:\RAC-Shared-Storage - in my case) to store this disk file separately in there.

=> Make sure you virtual machine(RAC1) is shut-down before adding a new disk to it.
=> Right click on Virtual Machine Name and then click on Settings.
=> On Virtual machine Setting window, click on Add.
=> Select Hard Disk as Hardware Type and click on Next.
=> Choose SCSI(recommended) as virtual disk type - and the most important on this page is select Independent Mode and persistent on this page and click on Next.
=> Select Create New Virtual Disk and click on Next.
=> Specify disk capacity (40GB or so) and select option Ällocate all disk space now and choose option Store virtual disk as a single file > click on Next.
=> Provide complete path for this disk file name(E:\RAC-Shared-Storage\ - in my case) and click on next.
=> Click on Finish.

IMP : As we added a new HDD to our fist node(RAC1) to be used as a shared storage for our RAC installation, we need to change the virtual device node controller to SCSI 1:0 from SCSI 0:1 - so in-order to do it go to virtual machine setting and click on Persistent Hard disk we added just above and click on Advance tab at right side and change to SCSI 1:0.


Step : 3 - Now let's configure the virtual machine(RAC1) configuration file so that newly added persistent hard disk can be shared/accessed by another node(RAC2).


=> Open the virtual machine configuration file(E:\RAC-Nodes\Node01\RAC1.vmx) of first Node(RAC1) in notepad. Before opening the file make a copy of that file as backup to restore in case of any corruption of it.


=> Add/append the below lines at the top of that file.

disk.locking = "FALSE"
diskLib.dataCacheMaxSize = "0"
diskLib.dataCacheMaxReadAheadSize = "0" 
diskLib.dataCacheMinReadAheadSize = "0" 
diskLib.dataCachePageSize = "4096" 
diskLib.maxUnsyncedWrites = "0" 
scsi1.sharedBus = "virtual"


=> Save the changes in the file.

Note : - Add the above same lines in the virtul machine configuration file of Node 2(RAC2) and save that.


=> Now go to the Setting of Virtual machine of Node 2 and Click on Add to add a new HDD in there and click on Next.
=> Select SCSI(recommended) and Check the Independent checkbox and select Persistent option and click on Next.
=> Select the option "Use an existing virtual disk"option and click on Next.
=> Provide the complete path of shared HDD we added earlier(E:\RAC-Shared-Storage\) in node one(RAC1) and Select Independent and Persistent option and click on Finish.


Note :- Once you are done with step 3 and start back the VMs then you will get a message that "Clustering is not supported......................", just ignore it.



Step : 4 - Add the following lines in the host files of both VMs for Public IPs, Private IPs, Scan Name and VIPs.


#Public IP
192.168.100.50  rac2.oraclenotes.in rac2
192.168.100.45  rac1.oraclenotes.in rac1

# Private
10.0.0.1   rac1-priv.oraclenotes.in   rac1-priv
10.0.0.2   rac2-priv.oraclenotes.in   rac2-priv

# Virtual
192.168.100.103   rac1-vip.oraclenotes.in    rac1-vip
192.168.100.104   rac2-vip.oraclenotes.in    rac2-vip

# SCAN
192.168.100.105   scan.oraclenotes.in        rac-scan

Note : So far we have created 2 VMs and configured it as such that both can communicate with each other now. next will create partition on shared disk and will create ASM disks and install Grid Infrastructure for Cluster.




Next Part is coming soon........stay tuned here...!!

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

Below is the issue I faced a while back when GATHER STATS JOB was run so just wanted to share my knowledge how did I managed to resolve it.

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-29400: data cartridge error

Cause-: DBMS_STATS: GATHER_STATS_JOB encouters this error when it tries to gather stats on an external table as OS level file(test_file.csv) was not present inside directory "TEST_UTIL" created for this external table.

Solution-: After reviewing the trace file we found that OS level flat file(a csv file) was not present in the directory created for the external table so in order to resolve this issue either we need to place the relevant OS level flat file inside directory (TEST_UTIL) created for external table to be used or will have to remove/drop the external table to get this resolved.


Below is the query to list the external tables:
-------------------------------

select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE

from dba_external_tables

order by 1,2

/

Note: This error can be encountered in some other cases like if Data Pump temporary table is left and was not cleaned up properly after export job was done.We will need to find the temporary table that is related to Data Pump Job using below query and purge that and finally re-execute the gather STATS job.

select OWNER,OBJECT_NAME,OBJECT_TYPE, status,

to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created

,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time

from dba_objects

where object_name like 'ET$%'

/

Drop the temporary tables that belong to the DataPump. eg:

---------------------------------

SQL> drop table system.&1 purge;

Enter value for 1: ET$00654E1E0007

Feel free to contact me anytime....!!

ORA-00317: file type 0 in header is not log file

Today I faced below issue with one of our DR database when script was trying to recover the DR database using backup control file.



SQL> Connected.
SQL> ORA-00279: change 5969061003709 generated at 03/05/2016 20:44:59 needed for
thread 1
ORA-00289: suggestion : /u01/archive/STDB/STDB_1_129932_777534547.arc
ORA-00280: change 4969061003109 for thread 1 is in sequence #129942


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00317: file type 0 in header is not log file
ORA-00334: archived log: '/u01/archive/STDB/STDB_1_129932_777534547.arc'




Solutions : I found that archive log seq# 129942 was corrupted at DR host that was copied from Primary hence it was not applying to DR so a fresh copy of this archive log copied to DR manually and re-ran the DR sync script it got resolved.

Hope it would help someone...!!

How to rename a Database Instance?

It is very simple if you want to rename your database instance name. If your database is running with spfile then just alter INSTANCE_NAME parameter directly to a name you want to change to and bounce the instance and if your database is running with pfile then you would have to clean shutdown the database instance and modify the INSTANCE_NAME parameter value in the pfile and start database back with that pfile.



My database is running with spfile hence performed below steps to achieve our goal to rename the database instance name from proddb to TESTDB.



SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      proddb




SQL> alter system set instance_name='TESTDB' scope=spfile;

System altered.




SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  841162752 bytes
Fixed Size                  1339768 bytes
Variable Size             507514504 bytes
Database Buffers          327155712 bytes
Redo Buffers                5152768 bytes
Database mounted.


SQL> alter database open;

Database altered.



We can you database instance name changed to TESTDB now:
==========================

SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      TESTDB