Oracle 12C: Moving datafiles feature

Trying to test new Oracle 12C feature "Moving or renaming datafiles" in attempt to fix "
ORA-19566: exceeded limit of 0 corrupt blocks for file". This Oracle error simply means, the block no longer belonging to any extents and Oracle 'marked' it as corruption, so, logically, if I can find a way to scrub it, it should  fix the issue.

http://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN13837

According to the Oracle DOC this should perform the following upon moving the datafile."When you rename or relocate online data files, the pointers to the data files, as recorded in the database control file, are changed. The files are also physically renamed or relocated at the operating system level."


set current session to the database that I intend to test which is pdorcl.

SQL> alter session set container=PDBORCL;

Session altered.

Intent to change the vpxadmin3.dbf to vpxadmin4.dbf. Essentially, this can be point to somewhere else.

SQL> col file_name format a130;

SQL> SELECT FILE_NAME, BYTES FROM dba_data_files;

FILE_NAME                                                                               BYTES

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

/u01/data/ORCL/E1428863D6CF20F7E043CB41830AFB4F/datafile/o1_mf_system_8xy2dwhf_.dbf     272629760

/u01/data/ORCL/E1428863D6CF20F7E043CB41830AFB4F/datafile/o1_mf_sysaux_8xy2dwhd_.dbf     681574400

/u01/data/ORCL/E1428863D6CF20F7E043CB41830AFB4F/datafile/o1_mf_users_8xy2fwlk_.dbf      5242880

/u01/data/ORCL/datafile/vpxadmin3.dbf                                                   1073741824

Move the datafile. This takes about 3 minutes to complete.

SQL> alter database move datafile '/u01/data/ORCL/datafile/vpxadmin3.dbf' to '/u01/data/ORCL/datafile/vpxadmin4.dbf';

Database altered.

The datafile name vpxadmin3.dbf is changed to vpxadmin4.dbf

SQL> col file_name format a130;

SQL> SELECT FILE_NAME, BYTES FROM dba_data_files;

FILE_NAME                                                                                 BYTES

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

/u01/data/ORCL/E1428863D6CF20F7E043CB41830AFB4F/datafile/o1_mf_system_8xy2dwhf_.dbf       272629760

/u01/data/ORCL/E1428863D6CF20F7E043CB41830AFB4F/datafile/o1_mf_sysaux_8xy2dwhd_.dbf       681574400

/u01/data/ORCL/E1428863D6CF20F7E043CB41830AFB4F/datafile/o1_mf_users_8xy2fwlk_.dbf        5242880

/u01/data/ORCL/datafile/vpxadmin4.dbf                                                     1073741824

First time I get the chance to test out the "datafile move" feature. I can see that this may come very handy moving things around. Though, the performance can be questionable for large production environment.

Oracle: CREDENTIAL: ORA-27369: job of type EXECUTABLE failed with exit code: 7 !@#--!@#7#@!--#@!&

This issue is caused by incorrect user being used during dbms_scheduler credential setup.  The exit code of 7 is basically referring to permission issue. Pre-requisite should make sure the jssu, externaljob.ora and extjob have correct permission setup.

Note: there are 2 ways to create scheduler credential. DBMS_SCHEDULER.CREATE_CREDENTIAL is deprecating in 12.1 and preferably be using DBMS_CREDENTIAL.CREATE_CREDENTIAL package 12.1 onward.

Setup: 
Legit OS level user: oracle and password is vmware
Instance level user setup: scott
ascript.sh is a script that is calling another sql script at the OS level.

TEST # 1 : dbms_scheduler with valid OS user - PASSED

exec dbms_scheduler.create_credential('EXT_JOB_CRED1', 'oracle', 'vmware');

exec dbms_scheduler.drop_job ('scott.SCRIPT_JOB1');

exec dbms_scheduler.create_job ( job_name => 'SCRIPT_JOB1', job_type => 'EXECUTABLE', job_action => '/home/oracle/Downloads/script/ascript.sh', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly;interval=2', end_date => NULL,enabled => TRUE,auto_drop => FALSE,comments => 'Executable jobfor ascript.sh',credential_name => 'scott.EXT_JOB_CRED1');

EXEC dbms_scheduler.run_job('SCRIPT_JOB1');

SQL> EXEC dbms_scheduler.run_job('SCRIPT_JOB1');

PL/SQL procedure successfully completed.

TEST 2: DBMS Scheduler with random user - FAILED

exec dbms_scheduler.create_credential('EXT_JOB_CRED2', 'somerandomuser', 'vmware123');

exec dbms_scheduler.drop_job ('scott.SCRIPT_JOB2');

exec dbms_scheduler.create_job ( job_name => 'SCRIPT_JOB2', job_type => 'EXECUTABLE', job_action => '/home/oracle/Downloads/script/ascript.sh', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly;interval=2', end_date => NULL,enabled => TRUE,auto_drop => FALSE,comments => 'Executable jobfor ascript.sh',credential_name => 'scott.EXT_JOB_CRED9');

EXEC dbms_scheduler.run_job('SCRIPT_JOB2');

SQL> EXEC dbms_scheduler.run_job('SCRIPT_JOB2');

BEGIN dbms_scheduler.run_job('SCRIPT_JOB2'); END;

*

ERROR at line 1:

ORA-27369: job of type EXECUTABLE failed with exit code: 7 !@#--!@#7#@!--#@!&

ORA-06512: at "SYS.DBMS_ISCHED", line 209

ORA-06512: at "SYS.DBMS_SCHEDULER", line 594

ORA-06512: at line 1

Test 3 – using DBMS_CREDENTIAL with random user - FAILED

 exec DBMS_CREDENTIAL.CREATE_CREDENTIAL ('EXT_JOB_CRED3', 'randomuser', 'vmware123');

exec dbms_scheduler.create_job ( job_name => 'SCRIPT_JOB3', job_type => 'EXECUTABLE', job_action => '/home/oracle/Downloads/script/ascript.sh', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly;interval=2', end_date => NULL,enabled => TRUE,auto_drop => FALSE,comments => 'Executable jobfor ascript.sh',credential_name => 'scott.EXT_JOB_CRED10');
EXEC dbms_scheduler.run_job('SCRIPT_JOB3');

SQL> EXEC dbms_scheduler.run_job('SCRIPT_JOB3');BEGIN dbms_scheduler.run_job('SCRIPT_JOB3'); END; *ERROR at line 1:ORA-27369: job of type EXECUTABLE failed with exit code: 7 !@#--!@#7#@!--#@!&ORA-06512: at "SYS.DBMS_ISCHED", line 209ORA-06512: at "SYS.DBMS_SCHEDULER", line 594ORA-06512: at line 1

These are just some portions of my tests. There were other things I have tried such as incorrect passwords and etc and they were all failing with exit code of 7. At this point, I am not completely sure if this is a bug or part of the design.

Oracle EBS: RW-50010: Error: - script has returned an error: 1

This one is fun. EBS installation failed at 29% after half a day of waiting.


Error

C:\\oracle\\dev\\fs2\\FMW_Home\webtier\OPatch ## Node=NodeId=1698 Type=24 TypeName=filesys_patch_action Name= RefId=901 State=init ConfigDoc=APPS_OHS_HOME ParentDoc=null Topology=R12 Action=os_cmd

    at oracle.apps.fnd.txk.config.FileSysPatchActionNode.doFileSysOSCmd(FileSysPatchActionNode.java:169)

    at oracle.apps.fnd.txk.config.FileSysPatchActionNode.processState(FileSysPatchActionNode.java:101)

    at oracle.apps.fnd.txk.config.PatchActionNode.processState(PatchActionNode.java:187)

    at oracle.apps.fnd.txk.config.PatchNode.processState(PatchNode.java:338)

    at oracle.apps.fnd.txk.config.PatchesNode.processState(PatchesNode.java:79)

    at oracle.apps.fnd.txk.config.InstallNode.processState(InstallNode.java:68)

    at oracle.apps.fnd.txk.config.TXKTopology.traverse(TXKTopology.java:594)

    at oracle.apps.fnd.txk.config.InstallService.doInvoke(InstallService.java:224)

    at oracle.apps.fnd.txk.config.InstallService.invoke(InstallService.java:237)

    at oracle.apps.fnd.txk.config.InstallService.main(InstallService.java:291)

C:\Users\ADMINI~1\AppData\Local\Temp\1>if 1 == 0 goto :INSTAS_OK

C:\Users\ADMINI~1\AppData\Local\Temp\1>echo Cannot install one-off patches

Cannot install one-off patches

RW-50010: Error: - script has returned an error:   1

RW-50004: Error code received when running external process.  Check log file for details.

Running APPL_TOP Install Driver for dev instance

This error is very generic. One should not stopped at this level of troubleshooting. I do not believed looking at this one can guess what is the root cause. Quite a few Oracle noteid look the same but having a different root cause at the end of the resolution.

User will need to look at the install log to look at which stage and line that failed. At 29%, it does half a dozen things particularly moving java files around. User can look at the entire installation in the  adrunfmw.cmd. 

Locate the install log. It usually reside in the EBS install folder under temp.  Example, oracle.apps.fnd.txk.install0.

Look for stdout and stderr or any exception errors. In my case, I encountered the following error.

  <message>Process Completed (3)  cmd /c rmdir /s /q C:\\oracle\\dev\\fs2\\FMW_Home\webtier\OPatch
Stdout:  See C:\oracle\dev\fs2\EBSapps\appl\admin\dev_ebsdev\tmp\T1456139825284_122.tmp
Stderr: The system cannot find the path specified.
</message>
</record>
<record>
  <date>2016-02-22T18:46:09</date>
  <millis>1456141569147</millis>
  <sequence>344</sequence>
  <logger>oracle.apps.fnd.txk.install</logger>
  <level>SEVERE</level>
  <class>oracle.apps.fnd.txk.config.InstallService</class>
  <method>printError</method>
  <thread>10</thread>
  <message>oracle.apps.fnd.txk.config.ProcessStateException: FileSys OS COMMAND Failed : Exit=3 See log for details. CMD= cmd /c rmdir /s /q C:\\oracle\\dev\\fs2\\FMW_Home\webtier\OPatch ## Node=NodeId=1698 Type=24 TypeName=filesys_patch_action Name= RefId=901 State=init ConfigDoc=APPS_OHS_HOME ParentDoc=null Topology=R12 Action=os_cmd
    at oracle.apps.fnd.txk.config.FileSysPatchActionNode.doFileSysOSCmd(FileSysPatchActionNode.java:169)
    at oracle.apps.fnd.txk.config.FileSysPatchActionNode.processState(FileSysPatchActionNode.java:101)
    at oracle.apps.fnd.txk.config.PatchActionNode.processState(PatchActionNode.java:187)
    at oracle.apps.fnd.txk.config.PatchNode.processState(PatchNode.java:338)
    at oracle.apps.fnd.txk.config.PatchesNode.processState(PatchesNode.java:79)
    at oracle.apps.fnd.txk.config.InstallNode.processState(InstallNode.java:68)
    at oracle.apps.fnd.txk.config.TXKTopology.traverse(TXKTopology.java:594)
    at oracle.apps.fnd.txk.config.InstallService.doInvoke(InstallService.java:224)
    at oracle.apps.fnd.txk.config.InstallService.invoke(InstallService.java:237)
    at oracle.apps.fnd.txk.config.InstallService.main(InstallService.java:291)

It is pretty self-explanatory. The required tmp file was not found. What I found though, not only the T1456139825284_122.tmp not found but the entire directory of C:\oracle\dev\fs2\EBSapps\appl\admin\dev_ebsdev\tmp\ was empty. The team have decided to get through this and re-visitng this missing file once the installation is successfully. Just to fast forward a little bit, there will be another missing file that I will point out in a bit.

Taking a look at the adrunfw.cmd command, I realized the script is hack-able. Since we are going to come back and revisit this issue if the installation ever completed, I decided to make a copy of the script and delete the script ending line so it break out of the loop if it failed and move on. Now - another issue found here, the  j11067592_fnd.zip was not residing in the right path where the script will be copying from later on. It was sitting one level outside of fnd directory. I decided to just copy and paste the file into the fnd folder.

As expected, a few hours into the installation, it did make it passed 29% and completed successfully. As of the "T1456139825284_122.tmp" and possibly other files, I have no idea how to address it. That file was no where to be found in any of the medias nor I know what they are or for. Perhaps, run a one-off patching might restore them since, the it failed at the One-Off stage. 

Please leave comments if you know what this T1456139825284_122.tmp is.

Oracle datapump migration to 11G triggered [qerxtgetrefoffset_911]

I have worked on this issue with Oraclec Support for over 8 months. It took 5 months for Oracle Engineering to recognize this as a bug and another 3 months find resolution. Oracle Engineering finally came back and stated that they are unable to fix it due to its complexity and will be fixed in latest  12.1.0.2.0 version. The patch id is 13770504 and has been rolled in to it as well.

I have done hundreds of datapump and this one just would not go through. I have tried everything that I can dig through in Oracle Metalink and attempted informal patches from Oracle on 11.2.0.1,11.2.0.3, 11.2.0.4 and 12.0.1.0. Some of the attempts I have made are using VERSION=12, parallel=1 and tried recommended one-off patch from Oracle.

OK, OK, so what was my issue ??

I was trying to migrate their database at 10.2.0.5 to 11.2.0.4 and been getting ORA-­600 [qerxtGetRefOffset_911]. Oracle alert.log, trace files and datapump traces and the issue appear to hit the Datapump Worker Process bug. There were quite a bit of similar bugs have been reported and it seems to me that the fix did not make it into 11.2.0.4 at first. They all appear similar to the following errors


ORA­39014: One or more workers have prematurely exited. 
ORA­39029: worker 1 with process name "DW00" prematurely terminated 
ORA­31671: Worker process DW00 had an unhandled exception. 
ORA­00600: internal error code, arguments: [qerxtGetRefOffset_911], [], [], [], [], [], [], [], [], [], [], [] 
ORA­06512: at "SYS.KUPW$WORKER", line 1887 ORA­06512: at line 2 
ORA­39014: One or more workers have prematurely exited. 
ORA­39029: worker 2 with process name "DW00" prematurely terminated 
ORA­31671: Worker process DW00 had an unhandled exception. 
ORA­00600: internal error code, arguments: [qerxtGetRefOffset_911], [], [], [], [], [], [], [], [], [], [], [] 
ORA­06512: at "SYS.KUPW$WORKER", line 1887 
ORA­06512: at line 2 Job "SYS"."SYS_IMPORT_FULL_01" stopped due to fatal error

DataPump Import (IMPDP) Fails With Internal Error ORA­600 [qerxtgetrefoffset_911] When Importing V10 Dump Files (Doc ID 1369347.1) ORA­600 [qerxtGetRefOffset_911] (Doc ID 1267718.1) ­ this bug stated bug fix at 11.2.0.3, 12.1.0.1 and 11.2.0.2. Upon, trying to patch, it still did not fixed my issue.

Basically, nailed it down to this one table in Oracle 10G with CLOB column storing XML documentation that was causing all the issue. I was able to reproduce the issue in all Oracle 11G dot releases and early Oracle 12G base as well. Oracle finally stated that the bug has been checked into the patch 13770504 . I no longer have the opportunity to test it out as of writing this and I will just take the words for it.

I haven't tried conventional export/import or files copying as the options were not given and this issue is not my call. I was just being instructed to fix the issue by using Datapump.

Oracle RAC Scan and resolv.conf issue

Oracle RAC evicted. SCAN no longer working. At first, everything look sporadic. Then, it seem to have something to do with network bounced.


Things to check/verify and double check in case someone changed something.

/etc/hosts
/etc/hostname or hostname
/etc/resolv.conf
/etc/named.conf
/var/named/mhrac1.zone (the zone file)
 /etc/sysconfig/network-scripts   -- check all the eth0, eth1, eth2 files here to have a good picture of how the networking looks like in this setup

Long story short -
When running an nslookup check, I was getting the following. I have disabled the firewall for the test, so port 53 should not be blocked. I can ping freely in and out of the node yet I kept getting the following. All configured IPs ping-ed fine.

nslookup dnsrac-scan.wwdom.com

Server:          10.11.188.211
Address:     10.11.188.211#53

** server can't find mhrac1-scan.wwdom.com: NXDOMAIN

Upon checking my resolv.conf, the following did not look right at all. I should have a "search" and the bottom 2 IPs addresses I set up in my mhrac1.zone file, instead, I am having the following

Generated by NetworkManager
search wwdom.com
nameserver 10.11.188.211
nameserver 10.11.188.212
nameserver 10.16.169.90
# NOTE: the libc resolver may not support more than 3 nameservers.
# The nameservers listed below may not be recognized.
nameserver 10.10.1.110

nameserver 75.75.75.75

After fixing the extra 3 IPs from the top, I was able to perform an expected nslookup. I was also able to replicate the issue by restarting the network - "service network restart" and new IPs get inserted every time I did that.

This could be due to when network restarted, it appended other IP addresses on top and the intended ones did not get read from - as the message already hinted.

My guess to permanently preventing from this issue, I need to change the PEERDNS in the  ifcfg-eth0 so, when network restarted, the VM will not reach outside and grab the real domain DNS IPs.  (By the way, the  ifcfg-eth0 is a make up name. Centos7 will provide a random name by default. So, keep track of you MAC address and assigned IPs. Default might look like this  ifcfg-234133445. the same goes for eth1 and eth2.

Oracle Troubleshooting: Datapump - ORA-27038: created file already exists

Do you spot my issue?  Damn .. those Monday's.



[oracle@Cloud3 clone]$ expdp vpx/vmware dumpfile=vpx.dmp schemas=vpx directory=vdump logfile=vpx.dmp

Export: Release 11.2.0.1.0 - Production on Mon Oct 19 09:51:14 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/backup/vpx.dmp"
ORA-27038: created file already exists
Additional information: 1

.....

................

..........................

dumpfile and logfile has the same name!

Oracle Troubleshooting: Datapump - ORA-39002: invalid operation

Some issue when working  on a datapump on an old machine. It used to work and out of a sudden, I was getting errors.

C:\Windows\system32>expdp bobdb/vmware dumpfile=bobdb.dmp full=n directory=DMPDIR logfile=bobdb.export.log

Export: Release 11.2.0.1.0 - Production on Thu Oct 15 10:16:07 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

UDE-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 536

ORA-29283: invalid file operation

C:\Windows\system32>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 15 10:16:28 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select directory_name, directory_path from dba_directories;

DIRECTORY_NAME

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

DIRECTORY_PATH

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

XMLDIR

c:\ade\aime_dadvfm0254\oracle\rdbms\xml

DMPDIR

C:\Users\orcldb\Downloads\bobdb\

DATA_PUMP_DIR

C:\app\orcldb\admin\zig\dpdump\

DIRECTORY_NAME

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

DIRECTORY_PATH

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

ORACLE_OCM_CONFIG_DIR

C:\app\orcldb\product\11.2.0\dbhome_2\ccr\state

SQL> quit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Windows\system32>

It turned out, entire directory of "C:\Users\orcldb\Downloads\bobdb\" was removed and causing the error. I wish the error would me more intuitive.

Created "bobdb" under download and fixed the issue.

Oracle Troubleshooting: Gathering incident package.

Most of the time, gathering incident to be submitted to Oracle Support pretty straightforward. This case, I need to walk through someone on gathering it since there are multiple HOMEs due to machine name changes.


adrci> IPS CREATE PACKAGE INCIDENT 74892
Created package 1 based on incident id 74892, correlation level typical


ADR base = "/home/oracle/app/oracle"
adrci> IPS GENERATE PACKAGE 1 IN $ORACLE_HOME
DIA-48448: This command does not support multiple ADR homes

adrci> show control
DIA-48448: This command does not support multiple ADR homes

adrci> SHOW HOMES
ADR Homes:
diag/rdbms/orcl/orcl
diag/tnslsnr/Cloud3/listener
diag/tnslsnr/Cloud2/listener
diag/tnslsnr/Cloud/listener
diag/tnslsnr/Cloud2-001/listener
adrci>

adrci> SET HOME diag/rdbms/orcl/orcl

adrci>  IPS GENERATE PACKAGE 1 IN /home/oracle/app/oracle
Generated package 1 in file /home/oracle/app/oracle/ORA600qer_20150827105025_COM_1.zip, mode complete


Voila, upload this over to Oracle Support ORA600qer_20150827105025_COM_1.zip. 

Oracle Troubleshoot: ORA-20005: object statistics are locked (stattype = ALL)

Getting Oracle errors stating object statistics are locked. This is severe enough to cause vCenter server to go down sporadically.

2015-08-11T12:57:01.838-14:32 [08712 error 'Default' opID=XXXXXXXXXXX] [VdbStatement] SQLError was thrown: "ODBC error: (HY000) - [Oracle][ODBC][Ora]ORA-20005: object statistics are locked (stattype = ALL)
--> ORA-06512: at "SYS.DBMS_STATS", line 23429
--> ORA-06512: at "SYS.DBMS_STATS", line 23480
--> ORA-06512: at line 1
--> " is returned when executing SQL statement "BEGIN dbms_stats.gather_table_stats(null, 'vpx_hist_stat1_125'); END;"

Simulating the fix

VPXADMIN55> exec dbms_stats.lock_table_stats('VPXADMIN55','vpx_hist_stat1_125');

PL/SQL procedure successfully completed.

VPXADMIN55> exec  dbms_stats.gather_table_stats(null, 'vpx_hist_stat1_125');
BEGIN dbms_stats.gather_table_stats(null, 'vpx_hist_stat1_125'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1


VPXADMIN55> exec dbms_stats.unlock_table_stats('vpx_hist_stat1_125');
BEGIN dbms_stats.unlock_table_stats('vpx_hist_stat1_125'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'UNLOCK_TABLE_STATS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


VPXADMIN55>  exec dbms_stats.unlock_table_stats('VPXADMIN55','vpx_hist_stat1_125');

PL/SQL procedure successfully completed.

VPXADMIN55>  exec  dbms_stats.gather_table_stats(null, 'vpx_hist_stat1_125');

PL/SQL procedure successfully completed.

VPXADMIN55>

This is not a common practice with vCenter database. I suspect someone might have put a lock on the table statistics for unknown reasons.

Oracle Troubleshoot: code, arguments: [25027], [TS#],

Updating a table causing the following error. Dropping and recreating the table fixed the issue. Do this only if the table is not a critical table. The tablespace # 29 was the culprit. Seen this the 3rd time this year. They all fixed the same way.

SQL state [60000]; error code 
[600]; ORA-00600: internal error code, arguments: [25027], [29], [0], [], 
[], [], [], [], [], [], [], [] 
; nested exception is java.sql.SQLException: ORA-00600: internal error 
code, arguments: [25027], [29], [0], [], [], [], [], [], [], [], [], [] 

Resolution: Doc ID 1608861.1.

Summary of the Oracle note
The error is fixed by:
recreating the table using exp-drop-import.
OR
Move the lob in a new tablespace.
Alter table <name> move lob(&lob_column) store as (tablespace &tbsp);

Oracle 12C: slight behavioral changes in schema creation in Oracle12C

Creating user in Oracle12C has changed a bit. Session will need to be altered to get old script working.




CREATE USER XX_A PROFILE "DEFAULT" IDENTIFIED BY "oracle" DEFAULT TABLESPACE "XX_DATEN" ACCOUNT UNLOCK
            *
ERROR at line 1:
ORA-65096: invalid common user or role name




SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.




SQL> CREATE USER "XX_" PROFILE "DEFAULT" IDENTIFIED BY "oracle" DEFAULT TABLESPACE "XX_DATEN" ACCOUNT UNLOCK;

User created.

Oracle decompress

Oracle "Compress" feature was introduced in early 11G. Anyone aware of any customers actually using that feature at the fields and if we know it has any adverse effect in general? It seems like a pretty promising feature for cutting down space usage and at the same time having some performance gains. I am tempted to have couple of my customers to try it out on vCenter database where vpx_event and vpx_event_args are growing out of control. If we can make use of this feature without major drawbacks, it can be helpful in other areas of our product as well where we have large analytic data reside. VPX_HIST_TEMP* are one of those examples as well.

Having to set this to compress all, all the data onward will be compressed and existing data remain uncompressed.

Note: "alter table abc compress for all operations" and "alter table abc compress for oltp" basically the same thing with different name.

SQL> alter table RESOURCEFULNESS compress for all operations;

Table altered.

SQL> select  compression, compress_for, table_name from user_tables where table_name like 'RESOURCEUDFKIND';

COMPRESS COMPRESS_FOR TABLE_NAME

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

DISABLED                              RESOURCEUDF

ENABLED  OLTP                    RESOURCEUDFKIND

Another example to illustrate on how to compress and to reset to nocompress.

SQL> alter table vpx_event compress for oltp;

Table altered.

SQL> select  table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';

TABLE_NAME                     COMPRESS COMPRESS_FOR

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

VPX_EVENT                      ENABLED  OLTP

MGMT_METRICS                   ENABLED  OLTP

MGMT_POLICIES                  ENABLED  OLTP

SQL> alter table vpx_event nocompress;

Table altered.

SQL> select  table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';

TABLE_NAME                     COMPRESS COMPRESS_FOR

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

MGMT_METRICS                   ENABLED  OLTP

MGMT_POLICIES                  ENABLED  OLTP

SQL>

Second example from another 12C database

SQL> col table_name format a30
SQL> col compress format a15
SQL> select  table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';

no rows selected

SQL> alter table vpx_event compress for oltp;

Table altered.

SQL> select  table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
VPX_EVENT                      ENABLED  ADVANCED

SQL> alter table vpx_event nocompress;

Table altered.

SQL> select  table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';

no rows selected


SQL>

Oracle 12C: connecting within container to another database in Oracle 12C

In my Oracle 12.0.1, I cannot connect to a schema directly without specifying the services name.

alter session set container=PDBVCDB;

SQL> conn vpxd/oracle
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn vpxd/oracle@pdbvcdb
Connected.


It probably trying to find the vpxd schema at the root level.

Oracle: ASMM Oracle 10g,11g and 12c

A handy dandy old note of mine and found that it is still good for Oracle 12C as well.

Script taken from Oracle NoteID: 295626.1. Example was from my own lab on Oracle 12C.

SGA_TARGET non-zero. ASMM enabled.

SQL> show parameter sga

NAME                                 TYPE        VALUE

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

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     TRUE

sga_max_size                         big integer 4816M

sga_target                           big integer 4816M

unified_audit_sga_queue_size         integer     1048576

Looking at the patterns of automatic management.

SQL> set lines 150

SQL> col component format a25

SQL> select component, current_size/1024/1024 "CURRENT_SIZE", min_size/1024/1024 "MIN_SIZE", user_specified_size/1024/1024 "USER_SPECIFIED_SIZE", last_oper_type "TYPE" from v$sga_dynamic_components;

COMPONENT                 CURRENT_SIZE   MIN_SIZE USER_SPECIFIED_SIZE TYPE

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

shared pool                        832        800                   0 GROW

large pool                         256        256                   0 SHRINK

java pool                           64         64                   0 STATIC

streams pool                         0          0                   0 STATIC

DEFAULT buffer cache              3392       3392                   0 SHRINK

KEEP buffer cache                    0          0                   0 STATIC

RECYCLE buffer cache                 0          0                   0 STATIC

DEFAULT 2K buffer cache              0          0                   0 STATIC

DEFAULT 4K buffer cache              0          0                   0 STATIC

DEFAULT 8K buffer cache              0          0                   0 STATIC

DEFAULT 16K buffer cache             0          0                   0 STATIC

COMPONENT                 CURRENT_SIZE   MIN_SIZE USER_SPECIFIED_SIZE TYPE

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

DEFAULT 32K buffer cache             0          0                   0 STATIC

Shared IO Pool                     240          0                   0 GROW

Data Transfer Cache                  0          0                   0 STATIC

ASM Buffer Cache                     0          0                   0 STATIC

15 rows selected.

Looking at the historical data of ASMM movements.

SQL> col component format a25;

SQL> set pages 100;

SQL> set lines 150;

SQL> col init format 9999;

SQL> col target format 9990;

SQL> select component, oper_type, initial_size/1024/1024 "INITIAL", TARGET_SIZE/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", TO_CHAR(end_time, 'DD/MM/YYYY HH:MI:SS') "DATE" from V$SGA_RESIZE_OPS order by end_time;

COMPONENT                 OPER_TYPE      INIT TARGET      FINAL DATE

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

DEFAULT 8K buffer cache   STATIC            0      0          0 30/12/2014 12:51:36

KEEP buffer cache         STATIC            0      0          0 30/12/2014 12:51:36

large pool                STATIC            0    336        336 30/12/2014 12:51:36

shared pool               STATIC            0    800        800 30/12/2014 12:51:36

java pool                 STATIC            0     64         64 30/12/2014 12:51:36

streams pool              STATIC            0      0          0 30/12/2014 12:51:36

DEFAULT 4K buffer cache   STATIC            0      0          0 30/12/2014 12:51:36

ASM Buffer Cache          STATIC            0      0          0 30/12/2014 12:51:36

DEFAULT buffer cache      STATIC            0   3584       3584 30/12/2014 12:51:36

RECYCLE buffer cache      STATIC            0      0          0 30/12/2014 12:51:36

DEFAULT 32K buffer cache  STATIC            0      0          0 30/12/2014 12:51:36

DEFAULT 16K buffer cache  STATIC            0      0          0 30/12/2014 12:51:36

DEFAULT 2K buffer cache   STATIC            0      0          0 30/12/2014 12:51:36

DEFAULT buffer cache      INITIALIZING   3584   3584       3584 30/12/2014 12:51:37

large pool                SHRINK          336    256        256 30/12/2014 12:52:18

DEFAULT buffer cache      GROW           3584   3664       3664 30/12/2014 12:52:18

DEFAULT buffer cache      SHRINK         3664   3424       3424 30/12/2014 12:52:29

shared pool               GROW            800    832        832 02/01/2015 01:05:16

DEFAULT buffer cache      SHRINK         3424   3392       3392 02/01/2015 01:05:16

19 rows selected.

Oracle Troubleshooting: Gathering orachk

Download ORAchk Oracle Metalink.

ORAchk - Health Checks for the Oracle Stack (Doc ID 1268927.2)


Ideally, download the tool and set it asides as it will extract bunch of files and you do not want to mix up with other existing and unrelated files. The rest really straightforward. 

[oracle@Cloud3 oracle]$ mkdir orachk
[oracle@Cloud3 oracle]$ mv orachk.zip orachk  


[oracle@Cloud3 orachk]$ unzip orachk.zip
Archive:  orachk.zip
  inflating: readme.txt
  inflating: UserGuide.txt
  inflating: rules.dat
  inflating: user_defined_checks.xsd


[oracle@Cloud3 orachk]$  ./orachk
List of running databases
1. orcl
2. None of above

Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1].orcl
That's it!