CSV format native support in 12.2.0.1 Oracle Sqlplus!! Finally

Long awaited!!!!

SQL> set markup csv on delim ‘|’ quote on

SQL>select object_name,object_id,object_id from dba_objects where rownum<5

“OBJECT_NAME”|”OBJECT_ID”|”OBJECT_ID”
“/1000323d_DelegateInvocationHa”|35562|35562
“/10016a97_ZipArchiveZipFileObj”|124771|124771
“/100277f0_XMLErrorResources_ko”|130343|130343
“/100361c3_MotifOptionPaneUI1″|126065|126065

—

SQL> alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;

Session altered.

SQL> set hist on

SQL> hist
..

..

8 set markup csv on delim ‘|’ quote on
9 select object_name,object_id,object_id,name from dba_objects where rownum<5;
..

14 select sysdate,systimestamp,dummy from dual;
15 alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;

..

SQL> hist 14 run

“SYSDATE”|”SYSTIMESTAMP”|”DUMMY”
“2017-09-09 01:22:50″|”09-SEP-17 01.22.50.346463 AM +00:00″|”X”

 

Advertisements

ntpd -L – ORACLE RAC 11gR2 (may be 12c) – eviction &/or cpu spike

Please check if you are noticing “Delet” interface by ntpd in /var/log/messages* when node is evicted and or you notice some strange “spike” if crs process CPU utilization

 

Add option -L to /etc/sysconfig/ntpd – to tell ntpd to skip looking at virtual interfaces …. Pretty bad why it never showed up in documents and testing ….

12cR1 – GRID install fails with error INS-06006

• from shell where OUI is launched –
• run following –
• $ ssh-add
• Now try again ssh connectivity test option on OUI (where you have added all relevant RAC nodes)

Worked for me – It was frankly nightmare. MOS has no word on this – which is strange at best.

(It looks like the session launching the OUI is not able to get to ssh keys for some reason from debug)

 

Curious little problem with ASM and dataguard in ASM

– Source is ASM diskgroup using +DATA convention datafiles .. i.e automatically given names

– Target is ASM based dataguard +DGDATA (for example)

– You need to refresh the standby controlfile

– One little problem – the target names are all different than source names

– Solution:

(a) bring over standby control file

(b)

RMAN> catalog start with ‘+DGDATA’;

RMAN> switch database to copy;

– Done!

 

PS: This should work for file based systems as well… I think

ORA-01775: looping chain of synonyms

Deadly for database – I mean you may end up totally down for all you folks out there – including active data guards and all brothers & sisters of them!

SQL> select * from dual;

select * from dual
*
ERROR at line 1:
ORA-01775: looping chain of synonyms

Any way around it? Ask support and use following with caution – but due your due deligence – Obviously time is of great essence here – your business has catered – make a sql such as following. Change the offending synonym in investigation (it could be anything SYS.DBMS_SQL or SYS.DBMS_OUTPUT or any of your objects –)

Identify offending one —

Enable==>

SQL> alter system set events=’1775 trace name errorstack level 3′;

Disable==>

SQL> alter session set events=’1775 trace name errorstack off’;

Once you have the problem object

=========================================================

In my case DUAL on sys.DUAL

ALTER SYSTEM SET “_SYSTEM_TRIG_ENABLED”=FALSE SCOPE=MEMORY;
create or replace public synonym dual for sys.dual;
ALTER SYSTEM SET “_SYSTEM_TRIG_ENABLED”=true SCOPE=MEMORY;

=========================================================

Problem fixed! Amazing

Dreaded error – PRVF-4007 : User equivalence check failed for user “oracle”

Purpose:

I am trying to install 11gr2 on 2 node OEL6 x86-64bit nodes

Problem:

Trying to verify the node equivalance

[oracle@linux1 bin]$ ./cluvfy comp admprv -n linux1,linux2 -o user_equiv -sshonly

Verifying administrative privileges

Checking user equivalence…
PRVF-4007 : User equivalence check failed for user “oracle”
Check failed on nodes:
linux1

Verification of administrative privileges was unsuccessful.
Checks did not pass for the following node(s):
linux1

[oracle@linux1 bin]$ id oracle
uid=1100(oracle) gid=1000(oinstall) groups=1000(oinstall),1200(dba),1201(vboxsf)

[oracle@linux1 bin]$ ssh linux2 id oracle
uid=1100(oracle) gid=1000(oinstall) groups=1000(oinstall),1200(dba),1201(vboxsf)

Hunting around:

First of all, I did search web for information and really couldn’t find anything that fixed my issue. There were few suspect items:

1. directory perm on .ssh (700)

2. permission on authorized_keys file  (600)

Still no luck

Finally I did following:

a. export SRVM_TRACE=true

b. export SRVM_TRACE_LEVEL=10

c. ran the cluvfy (installed in /home/oracle/cvu)

d. Somehow thought of looking into /home/oracle/cvu/cv/log/cvutrace.log.0

That told me blurb about following command was failing!

/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 linux1 -n /bin/true

That lead me to run it manually and sure enough, its “ssh” that was failing

No RSA host key is known for linux1 and you have requested strict checking.
Host key verification failed.

OK – so I ran it such as following:

[oracle@linux1 .ssh]$ /usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=no -o NumberOfPasswordPrompts=0 linux1 -n /bin/true
Warning: Permanently added ‘linux1,192.168.0.101’ (RSA) to the list of known hosts.

Notice, the command added the IP address to the known_hosts file!

That gave me clue to run ssh <ip-address> for both nodes – and that did the trick

[oracle@linux1 .ssh]$ /usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 linux1 -n /bin/true

Was successful!

[oracle@linux1 bin]$ ./cluvfy comp admprv -n linux1,linux2 -o user_equiv -sshonly

Verifying administrative privileges

Checking user equivalence…
User equivalence check passed for user “oracle”

Verification of administrative privileges was successful.

Hope this helps