Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

Friday 29 November 2019

ORA-00600: internal error code, arguments: [4194], [59], [58], [], [], [], [], [], [], [], [], []

Error:

Block recovery completed at rba 431394.4853.16, scn 3257.2985719940
Errors in file /d01/app/oracle/diag/rdbms/bacdbpr/bacdb/trace/bacdb_m000_3280.trc  (incident=1907003) (PDBNAME=CDB$ROOT):
ORA-00600: internal error code, arguments: [4194], [59], [58], [], [], [], [], [], [], [], [], []
Incident details in: /d01/app/oracle/diag/rdbms/bacdbpr/bacdb/incident/incdir_1907003/bacdb_m000_3280_i1907003.trc
Thu Nov 28 13:01:39 2019
Dumping diagnostic data in directory=[cdmp_20191128130139], requested by (instance=1, osid=4294970576 (M000)), summary=[incident=1907003].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Nov 28 13:01:40 2019
Errors in file /d01/app/oracle/diag/rdbms/bacdbpr/bacdb/trace/bacdb_m000_3280.trc:
ORA-00600: internal error code, arguments: [4194], [59], [58], [], [], [], [], [], [], [], [], []
Thu Nov 28 13:02:34 2019
Sweep [inc][1907003]: completed
Sweep [inc2][1907003]: completed

Reason:

This also can be cause by the following defect

Bug 8240762 Abstract: Undo corruptions with ORA-600 [4193]/ORA-600 [4194] or ORA-600 [4137] after SHRINK

Details:
Undo corruption may be caused after a shrink and the same undo block may be used
for two different transactions causing several internal errors like:
ORA-600 [4193] / ORA-600 [4194] for new transactions
ORA-600 [4137] for a transaction rollback

Solution:

1. Create pfile from spfile to edit
2. Shutdown the instance and change the undo_management parameter to MANUAL

idle@SYS> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
idle@SYS>

alter system set undo_management='MANUAL' scope=spfile;

3. Check the segments

idle@SYS> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

TABLESPACE_NAME                STATUS           SEGMENT_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM

idle@SYS>


4. Create new undo tablespace - example
SQL>create undo tablespace datafile size 2000M;


CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
  '+DATA/bacdb/undotbs03.dbf' SIZE 29696M AUTOEXTEND ON NEXT 10M MAXSIZE 29696M,
  '+DATA/bacdb/undotbs04.dbf' SIZE 16316M AUTOEXTEND ON NEXT 10M MAXSIZE 29696M
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

5. Drop old undo tablespace
SQL>drop tablespace including contents and datafiles;

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

6. SQL>shutdown immediate;

7. SQL>startup nomount;  --> Using your Original spfile

8. Modify the spfile with the new undo tablespace name

SQL> Alter system set undo_tablespace = '' scope=spfile;

Alter system set undo_tablespace = 'UNDOTBS2' scope=spfile;
alter system set undo_management='AUTO' scope=spfile;

9. SQL>shutdown immediate;

10. SQL>startup;  --> Using spfile

BACDB@CDB$ROOT@SYS> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
BACDB@CDB$ROOT@SYS>



Saturday 28 September 2019

Error 'ORA-28081: Insufficient privileges - the command references a redacted object'


Reason:
-----------ERROR: ORA-28081

Error 'ORA-28081: Insufficient privileges - the command references a redacted object' occurs when performing DML/DDL by a schema on a object where one of the column has a redaction policy enabled.
If a redacted column appears as the source in a DML or DDL operation, then Oracle Data Redaction considers this as an attempt to circumvent the policy and prevents it with the error:
   ORA-28081 "Insufficient privileges - the command references a redacted object."


If a redacted column appears as the source in a DML or DDL operation, then Oracle Data Redaction considers this as an attempt to circumvent the policy and prevents it
with an 'ORA-28081: Insufficient privileges - the command references a redacted object' error unless you have the EXEMPT REDACTION POLICY system privilege.

Apart from a privileges issue it also prevents redacted data to enter the database and in the process lose the original truth.

Solution:

grant exempt redaction policy to ;

grant exempt redaction policy to SAID;

Tuesday 2 July 2019

step by step to apply patch in Oracle Weblogic Server

Step 1: set WL_HOME variable

/u01/oracle/middleware/wlserver_10.3/server/bin/setWLSEnv.sh

$ . $WL_HOME/server/bin/setWLSEnv.sh
  $ java weblogic.version

  [oracle@SAID-DR bin]$ . ./setDomainEnv.sh

CLASSPATH=/u01/oracle/middleware/patch_wls1036/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/u01/oracle/middleware/patch_ocp371/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/usr/java/jdk1.6.0_34/lib/tools.jar:/u01/oracle/middleware/wlserver_10.3/server/lib/weblogic_sp.jar:/u01/oracle/middleware/wlserver_10.3/server/lib/weblogic.jar:/u01/oracle/middleware/modules/features/weblogic.server.modules_10.3.6.0.jar:/u01/oracle/middleware/wlserver_10.3/server/lib/webservices.jar:/u01/oracle/middleware/modules/org.apache.ant_1.7.1/lib/ant-all.jar:/u01/oracle/middleware/modules/net.sf.antcontrib_1.1.0.0_1-0b2/lib/ant-contrib.jar:

PATH=/u01/oracle/middleware/wlserver_10.3/server/bin:/u01/oracle/middleware/modules/org.apache.ant_1.7.1/bin:/usr/java/jdk1.6.0_34/jre/bin:/usr/java/jdk1.6.0_34/bin:/usr/lib64/qt-3.3/bin:/usr/NX/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

Your environment has been set.

Step 2: Check Weblogic Version

----Check Weblogic Version---

[oracle@SAID-DR ClassicDomain]$cd /u01/oracle/middleware/user_projects/domains/ClassicDomain/
[oracle@SAID-DR ClassicDomain]$ java weblogic.version

WebLogic Server 10.3.6.0  Tue Nov 15 08:52:36 PST 2011 1441050

Use 'weblogic.version -verbose' to get subsystem information

Use 'weblogic.utils.Versions' to get version information for all modules

Step 3: Download required patch to apply

----Download Weblogic Patch ----

[oracle@SAID-DR ClassicDomain]$

[oracle@SAID-DR ClassicDomain]$ pwd
/u01/oracle/middleware/user_projects/domains/ClassicDomain
[oracle@SAID-DR ClassicDomain]$
root@SAID-DR ~]# cd /u01/patch/
[root@SAID-DR patch]# chown oracle:oinstall p28710912_1036_Generic.zip
[root@SAID-DR patch]# chmod 755 p28710912_1036_Generic.zip
[root@SAID-DR patch]#

Step 4: Apply patch

--------Apply Patch--------------
[oracle@SAID-DR ~]$ cd /u01/oracle/middleware/utils/bsu/
[oracle@SAID-DR bsu]$ ls -lrt
total 40
-rw-r--r-- 1 oracle oinstall 25214 Apr 24  2018 smartupdate.ico
-rw-r--r-- 1 oracle oinstall   516 Apr 24  2018 patch-client.jar
-rw-r--r-- 1 oracle oinstall   520 Apr 24  2018 bsu.jar
-rwxr-x--- 1 oracle oinstall   135 Apr 24  2018 bsu.sh
[oracle@SAID-DR bsu]$./bsu.sh

This is the syntax for the command to view the downloaded patches as below:

[oracle@SAID-DR bsu]$ ./bsu.sh -prod_dir=/u01/oracle/middleware/wlserver_10.3 -patch_download_dir=/u01/patch -status=downloaded -view -verbose
ProductName:       WebLogic Server
ProductVersion:    10.3 MP6
Components:        WebLogic Server/Core Application Server,WebLogic Server/Admi
                   nistration Console,WebLogic Server/Configuration Wizard and
                   Upgrade Framework,WebLogic Server/Web 2.0 HTTP Pub-Sub Serve
                   r,WebLogic Server/WebLogic SCA,WebLogic Server/WebLogic JDBC
                    Drivers,WebLogic Server/Third Party JDBC Drivers,WebLogic S
                   erver/WebLogic Server Clients,WebLogic Server/WebLogic Web S
                   erver Plugins,WebLogic Server/UDDI and Xquery Support,WebLog
                   ic Server/Evaluation Database,WebLogic Server/Workshop Code
                   Completion Support
BEAHome:           /u01/oracle/middleware
ProductHome:       /u01/oracle/middleware/wlserver_10.3
PatchSystemDir:    /u01/oracle/middleware/utils/bsu
PatchDir:          /u01/oracle/middleware/patch_wls1036
Profile:           Default
DownloadDir:       /u01/patch
JavaVersion:       1.6.0_29
JavaVendor:        Sun

[oracle@SAID-DR bsu]$

Install the patch:

[oracle@SAID-DR cache_dir]$ cp /u01/patch/p28710912_1036_Generic.zip /u01/oracle/middleware/utils/bsu/cache_dir/
[oracle@SAID-DR cache_dir]$ ls -lrt
total 98428
-rwxr-xr-x 1 oracle oinstall 100789655 Feb 14 03:39 p28710912_1036_Generic.zip
[oracle@SAID-DR cache_dir]$ unzip p28710912_1036_Generic.zip
Archive:  p28710912_1036_Generic.zip
  inflating: patch-catalog_26407.xml 
  inflating: 7HKN.jar               
  inflating: README.txt             
[oracle@SAID-DR cache_dir]$ pwd
/u01/oracle/middleware/utils/bsu/cache_dir
[oracle@SAID-DR cache_dir]$

./bsu.sh -install -patch_download_dir=/u01/oracle/middleware/utils/bsu/cache_dir -patchlist=7HKN -prod_dir=/u01/oracle/middleware/wlserver_10.3


3.25 PM 
[root@SAID-DR bsu]# cat bsu.sh
#!/bin/sh

JAVA_HOME="/usr/java/jdk1.6.0_34"

MEM_ARGS="-Xms1024m -Xmx4096m"            -----Modify the Memory parameter from 256 to 1024 and 512 to 4096

"$JAVA_HOME/bin/java" ${MEM_ARGS} -jar patch-client.jar $*
[root@SAID-DR bsu]#

[oracle@SAID-DR bsu]$ ./bsu.sh -install -patch_download_dir=/u01/oracle/middleware/utils/bsu/cache_dir -patchlist=7HKN -prod_dir=/u01/oracle/middleware/wlserver_10.3
Checking for conflicts.....
.....
No conflict(s) detected

Installing Patch ID: 7HKN..
Result: Success

[oracle@SAID-DR bsu]$
[oracle@SAID-DR bsu]$

----check if the patch is installed-------
./bsu.sh -prod_dir=/u01/oracle/middleware/wlserver_10.3 -status=applied -verbose -view

[oracle@SAID-DR bsu]$ ./bsu.sh -prod_dir=/u01/oracle/middleware/wlserver_10.3 -status=applied -verbose -view
ProductName:       WebLogic Server
ProductVersion:    10.3 MP6
Components:        WebLogic Server/Core Application Server,WebLogic Server/Admi
                   nistration Console,WebLogic Server/Configuration Wizard and
                   Upgrade Framework,WebLogic Server/Web 2.0 HTTP Pub-Sub Serve
                   r,WebLogic Server/WebLogic SCA,WebLogic Server/WebLogic JDBC
                    Drivers,WebLogic Server/Third Party JDBC Drivers,WebLogic S
                   erver/WebLogic Server Clients,WebLogic Server/WebLogic Web S
                   erver Plugins,WebLogic Server/UDDI and Xquery Support,WebLog
                   ic Server/Evaluation Database,WebLogic Server/Workshop Code
                   Completion Support
BEAHome:           /u01/oracle/middleware
ProductHome:       /u01/oracle/middleware/wlserver_10.3
PatchSystemDir:    /u01/oracle/middleware/utils/bsu
PatchDir:          /u01/oracle/middleware/patch_wls1036
Profile:           Default
DownloadDir:       /u01/oracle/middleware/utils/bsu/cache_dir
JavaVersion:       1.6.0_29
JavaVendor:        Sun


Patch ID:          7HKN
PatchContainer:    7HKN.jar
Checksum:          -1767993263
Severity:          optional
Category:          General
CR/BUG:            28710912
Restart:           true
Description:       WLS PATCH SET UPDATE 10.3.6.0.190115
WLS PATCH SET UPDATE 10
                   .3.6.0.190115


[oracle@SAID-DR bsu]$




Monday 29 December 2014

ORA-00604: error occurred at recursive SQL level 1 ORA-01450: maximum key length (3215) exceeded

Error:

ALTER INDEX  ATMUTL.IDX_ATMUTILITY  REBUILD ONLINE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded


ALTER INDEX  ATMUTL.IDX_ATMUTILITY_QUEUE  REBUILD ONLINE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

Cause of the Problem

When creating a index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498
How the maximum index key length is measured by?
Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)
The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.
So we can say that the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.
Solution of the Problem

The causes already indicates what might be the solutions. Solution may be,
1)Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.
2)If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.
3)Rebuild the index without online clause. That is
ALTER INDEX index_name REBUILD;

Because The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.

Ref: http://samadhandba.wordpress.com/2011/01/04/ora-01450-maximum-key-length-3215-exceeded/

Saturday 15 November 2014

Import (IMPDP) Oracle Database backup from network location


Scenario:

Suppose we want to restore backup into destination database server (192.168.10.6) and backup resides in source database server (192.168.10.32)

Solution:

Step1: We need to keep the backup dump file into source database server (192.168.10.32)

Step2: We need to create a database link into source database server (192.168.10.32)

CREATE PUBLIC DATABASE LINK DPUMP
 CONNECT TO SYSTEM
 IDENTIFIED BY
 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.6)(PORT=1521))(CONNECT_DATA=(SID=orcl)))';

Step3: Now we need to create a directory where database backup will reside 

DROP DIRECTORY data_dump;
CREATE OR REPLACE DIRECTORY data_dump AS '/back/exp/';

GRANT EXECUTE, READ, WRITE ON DIRECTORY SYS.data_dump TO SYSTEM WITH GRANT OPTION;

Step4: Now we need to execute import (impdp) command from source database server (192.168.10.32) to restore
       backup into destination database server (192.168.10.6)

Step5: Put the schemas value which schema you want to import

impdp system/sys123 schemas=scott,hr,test network_link=DPUMP directory=data_dump dumpfile=orcl_full.dmp logfile=orcl_full.log    


  
Step6: Now the data values that you want to import..

Cheers.....