MAX_COLUMNS parameter in Oracle Database 23ai

Before Oracle 23ai, the maximum allowed columns in a table is 1000. From Oracle 23ai you can increase this value by modifying a parameter MAX_COLUMNS up to 4096 if you have any use case. This initialization parameter can be set at system level only and in case of PDB you can limit to specific PDB. 

To use this MAX_COLUMN parameter the compatibility should be set to 23.0.0.0 or higher. To increase max allowed colums you must set the MAX_COLUMNS value to “EXTENDED”. Note that you can change MAX_COLUMNS from STANDARD to EXTENDED any time but to change the value back to STANDARD only when any table or view in the database contains 1000 or fewer columns. 

By default, the MAX_COLUMNS initialization parameter is set to STANDARD

SQL>show parameters max_columns
NAME                                TYPE      VALUE
-------------------------------- ----------- -------------------------
max_columns                      string       STANDARD

When user trying to add columns more than 1000 in a table will receive below error

ORA-01792: maximum number of columns in a table or view is 1000

You can change MAX_COLUMNS value using below command that will allow up to 4096 columns

SQL> ALTER SYSTEM set MAX_COLUMNS=EXTENDED scope=spfile;
SQL> shutdown immediate;
SQL> startup

If the database has tables with more than 1000 columns and trying to update the MAX_COLUMNS parameter value back to STANDARD, then user should receive below error

SQL> ALTER SYSTEM SET set MAX_COLUMNS =STANDARD scope=spfile;

ORA-32017: failure in updating SPFILE
ORA-60471: max_columns cannot be set to STANDARD as there are one or more objects with more than 1000 columns

The only way user can change this value by dropping the objects with more than 1000 columns.

Note that older Oracle client versions (before Oracle 23ai) do not support columns more than 1000 in a table and only Oracle 23ai clients support the 4096 column limit.

Thanks & Regards,

https://oracleracexpert.com

Oracle AutoUpgrade with source and Target Database on different servers

AutoUpgrade utility can be used on upgrading databases from 12c R2 release (12.2 + DBJAN2019RU and newer) and it has Analyze, Fixup, Deploy and Upgrade processing modes. 

Before you proceed make sure you meet following requirements.

• create config file
• Source Database should be running in original oracle home
• The Database server should be registered in DNS
• JAVA8 required for Autoupgrade to run. Oracle 12c R2 (12.2.0.1) or newer oracle homes have a java version by default.
• If you run AutoUpgrade in batch or script mode use “noconsole” parameter

From Oracle 19c (10.3) autoupgrade.jar file exists by default, for earlier releases you must download latest autoupgrade.jar file from Oracle support.

There are four AutoUpgrade modes and each mode performs different steps

•  Analyze Mode: Setup, Pre-checks.
• Fixups Mode: Setup, Pre-checks, and Pre-fixups.
• Deploy Mode: Setup, Guaranteed Restore Point, Pre-upgrade, Pre-checks, Pre-fixups, Drain,      Database Upgrade, Post-checks, Post-fixups, and Post-upgrade.
• Upgrade Mode: Setup, Database Upgrade, Post-checks, and Post-fixups.

Analyze Mode: This mode runs few checks to see weather database is ready for upgrade. In this mode it only reads data from database to analyze and does not perform any changes.

You can run this step on source database before you setup target.

Run below command to start AutoUpgrade Analyze

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode analyze

Make sure you analyze output files for errors and run fixup on Source database server. You should see SID.html and SID_preupgrade.log files

Note that this mode also generates status files such as status.json, progress.json which can be located under cfgtoollogs/upgrade/auto/status.

Status.json – This file contains High level status of upgrade.
Progress.json – This file has contains status of progress of all upgrades

In case if target database is not on the same server then you must set the source home path in the configuration file, so that Autoupgrade analyze can run on source oracle home.

For example,
upgrade1.source_home=/home/oracle/product/12.2.0.1/dbhome_1 # Source ORACLE_HOME Path
upgrade1.target_home=/home/oracle/product/19.0.0.0/dbhome_1 # Target ORACLE_HOME Path

Fixups Mode : This mode performs all required automated fix up that are required before you start an upgrade. In this mode it creates guaranteed restore point.

Run below command on source Database server to run fixups.

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode fixups

Deploy Mode: This mode performs all required actions for an upgrade, which include analyze, fixups, upgrade and post upgrade steps. You will use this mode when source and target Oracle Home’s are on the same server. This mode also creates guaranteed restore point.

Run below command on source Database server to run fixups.

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode Deploy

Move the database from source to Target Database server and perform Upgrade mode.

Upgrade Mode: You will run this Autoupgrade mode when you are moving database to Target server or don’t have source Oracle home access. This mode is used only when you are moving to new server. But note that this mode doesn’t create guaranteed restore point and it doesn’t perform the post upgrade steps.

Before you perform upgrade make sure you copy the during_upgrade_pfile_dbname.ora from source to target Oracle Home with default name init<SID>.ora. You can also create the spfile using during_upgrade_pfile_dbname.ora which can be found under temp directory.

SQL> create spfile from pfile=' /home/oracle/autoupgrade/au21/TESTDB/temp/during_upgrade_pfile_testdb.ora';

On Target database server start the database in upgrade mode and run below command to upgrade

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode upgrade

After upgrade make sure you perform post upgrade steps manually

· Copy network files sql.net ora, listener,ora, tnsnames.ora files..etc
· Restart of database in case of RAC
· Remove guaranteed restore point

Thanks & Regards,
https://oracleracexpert.com, Oracle ACE

Immutable tables in Oracle 19c or 21c

Oracle 19c (19.11) introduced Immutable tables, these tables provides protection against unauthorized data modification that means these are read-only tables. User can able add or insert the rows to the table but cannot able to modify. In order to manage the data the user must specify retention period for both immutable table and for the rows with in the table.

The immutable tables will have hidden columns and these are same as Block chain tables introduced in Oracle 21c. Based upon the retention period rows become obsolete in the tableau then only user can be able to delete the rows.

In order to use this feature user must set set the COMPATIBLE parameter set to 19.11.0
SQL> alter system set compatible='19.11.0' scope=spfile;

There are several data types are not supported with immutable tables ROWID, UROWID, LONG, object type, REF, varray, nested table, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, BFILE, and XMLType and XMLType tables are not supported.

Immutable tables contain system-generated hidden columns and they are the same as blockchain tables. When a row is inserted, a non-NULL value is set for the ORABCTAB_CREATION_TIME$ and ORABCTAB_USER_NUMBER$ columns,  value of remaining system-generated hidden columns is set to NULL.

When creating IMMUTABLE TABLE statement user should use below NO DROP, NO DELETE to specify retention period. The LOCKED keyword specifies table or rows retention setting cannot be modified.

1. NO DROP clause in CREATE IMMUTABLE TABLE statement for retention period

• NO DROP : If you use this clause without any retention then then you cannot able to drop and user needs to be careful when using this clause
• NO DROP UNTIL number DAYS IDLE : By using retention user can able to drop the but User cannot able to drop the table if newest row is less than n days old

To set the table retention period to 0 days, the initialization parameter BLOCKCHAIN_TABLE_MAX_NO_DROP must be set to 0. Pls note that the minimum value is 16

2. NO DELETE clause to specify the retention period for the rows

• NO DELETE [LOCKED] – If you use without any retention then Rows cannot be deleted from the immutable table
• 
  
• NO DELETE UNTIL n DAYS AFTER INSERT [LOCKED] – if you use this clause the row cannot be deleted until n days after it was added.

User can use ALTER TABLE statement with the NO DELETE UNTIL clause to modify this setting and increase the retention period but cannot reduce the retention period. The minimum value for n is 16 days. If you use LOCKED then you cannot subsequently modify the row retention.

SQL> CREATE IMMUTABLE TABLE IMMU_TAB1 (
COLA NUMBER,
COLB VARCHAR2(15),
COLC DATE
) NO DROP UNTIL 3 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT;

The user can able to drop table only after 3 days of inactivity and cannot deleted rows until 30 days after it has been inserted.

Use the DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS procedure to delete rows beyond the specified retention period or obsolete . Either SYS user or the owner of the schema can delete immutable table rows.

In order to DROP immutable table, it must be the owner of the schema or must have the DROP ANY TABLE system privilege.

SELECT row_retention "Row Retention Period", row_retention_locked "Row Retention Lock", table_inactivity_retention "Table Retention Period" FROM dba_immutable_tables
WHERE table_name = 'IMMU_TAB1';

Row Retention Period Row Retention Locked Table Retention Period
-------------------- -------------------- ----------------------
30 NO 3

User can query {CDB|DBA|ALL|USER}_IMMUTABLE_TABLES views information about immutable tables.

Regards,

http://oracleracexpert.com, Oracle ACE

How to reset SYS, SYSTEM or User password or grant SYSDBA, SYSOPER privilege

This article helps to reset SYS, SYSTEM or any other user password and also grant admin privileages. There are different methods to rest the password in Oracle.

As SYSDBA or DBA privilege - As long as you have SYSDBA or DBA privilege you should be able to reset any user password.

$ sqlplus / as sysdba
SQL> alter user <username> identified by <new password>;
For ex:- alter user sys identified by xxxaaxxx;
For ex: - alter user EMP identified by xxssxxxx;

Orapwd Utility – This is very useful utility to rest the password. This utility helps in granting SYSOPER, SYSDBA privileges. Creating orapwd enables users to connect with admin privileges. From Oracle 11g the passwords are case sensitive.

orapwd file=orapwd<SID> password=password entries=max_users

In order Oracle to check for password file the REMOTE_LOGIN_PASSWORDFILE must be set to either SHARED or EXCLUSIVE

SHARED - More than one database can use a password file. But only SYS user recognized by the password file
EXCLUSIVE - The password file can be used by only one database and the password file can contain names other than SYS.

SQL> alter system set remote_login_passwordfile =’EXCLUSIVE’;
SQL> show parameter password
NAME TYPE VALUE
----------------------------- ----------- ----------
remote_login_passwordfile string EXCLUSIVE

You can also grant SYSDBA, SYSOPER privileges to user by logging as SYSDBA. Once the privilege is granted, the users will be added to password file

SQL> grant sysdba to AdminUser;
SQL> grant sysoper to DBAUser;

To see who are all the users has sysdba, sysoper privilege run below command. You can also see the users listed in password file

SQL> select * from v$pwfile_users;

Oracle has listed several articles how to recover LOST password and resolve issues connecting as YSSDBA.

 
How to Set up the Oracle Password File MOSC Note: 1029539.6
Checklist for Resolving Connect AS SYSDBA Issues MOSC Note: 69642.1
How To Recover From Lost SYS Password MOSC Note: 805084.1

Thanks & Regards,

http://oracleracexpert.com

Patching Oracle Database Platform using Opatch or MOPatch

When applying the Oracle patches, make sure you follow the processes pre-checks, patch execution and post checks.

In SAP environment, MOPatch is very helpful and it has bundle patchs (SBP). Using this tool we can install multiple patches at single run, it will automate the oracle patching processes. However, note that MOPatch internally calls OPatch to install patches.

Pre-checks

• Make sure you have enough space in $ORACLE_HOME file system
• Take a Tar or backup of $ORACLE_HOME

tar cvf - $ORACLE_HOME/12201 | gzip > 12201.101020.tar.gz

• Install latest Opatch and MOpatch (in case of SAP)

$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch-pre-SBP_12201201020_101020
$ unzip -qd $ORACLE_HOME OPATCH12201P_2008-70001555.ZIP -x SIGNATURE.SMF
$ unzip -qd $ORACLE_HOME/sapbundle SAP12201P_2011-70001555.ZIP 'SBP_12201201020_101020/MOPatch/*'
$ test -d $ORACLE_HOME/MOPatch && mv $ORACLE_HOME/MOPatch $ORACLE_HOME/MOPatch-pre-SBP_12201201020_101020
$ mv $ORACLE_HOME/sapbundle/SBP_12201201020_101020/MOPatch $ORACLE_HOME/MOPatch

• check the version of MOPatch & Opatch version

$ORACLE_HOME/MOPatch/mopatch.sh –h
$ORACLE_HOME/OPatch/opatch version

Execution

• Stop all database applications
• Backup the database and related files
• Shut down the databases, listeners, and other processes running from the Oracle Home, use the fuser OS command to check for stale SQL*Plus sessions: $SBPFUSER $ORACLE_HOME/bin/oracle
• Install the patch –

For SAP systems using MOPatch
env ORACLE_HOME=$ORACLE_HOME $ORACLE_HOME/MOPatch/mopatch.sh -v -s SAP12201P_2011-70001555.ZIP

You should see a message
MOPatch completed successfully
Overall Status : COMPLETE

For Other Oracle Systems using Opatch
$ cd /opt/patches/<patch number>
$ opatch apply

You should see a message
Opatch completed successfully
Overall Status : COMPLETE

Post-Checks

• Start the database and verify the database processes and logs. The logs should not have any error messages
• Run the post patch update steps for SAP system 

$ORACLE_HOME/sapbundle/catsbp

• Apply _FIX_CONTROL, EVENT parameters if required.

Where _FIX_CONTROL, initialization parameter will give control over specific features and bug fixes. However, it should be set under the direction of Oracle Corporation

Ex:- ALTER SYSTEM SET "_FIX_CONTROL"='4545454:ON','4547778:OFF',……. COMMENT='SAP_12201201020_101020' SCOPE=SPFILE;

Where EVENT, initialization parameter will be used to generate information in form of a trace file in the context of the event. The trace file is located in a directory specified by the parameter USER_DUMP_DEST

ALTER SYSTEM SET EVENT= '10027','10028', '10142' COMMENT='SAP_12201171017_101020' SCOPE=SPFILE;

• Restart the database and Take a backup
• Start the application and check the application

SAP Oracle Patches Reference

• Known issues related to the installation of Database Release Update 12.2.0.1.201020 and the other patches contained in this SBP are described in SAP Note 2507228 Database: Patches for 12.2.0.1.
• Known issues related to the Oracle installation utilities, including MOPatch, OPatch, and the Oracle Universal Installer, are described in SAP Note 1915299 Troubleshooting Software Installation (12.1/12.2).

Oracle Patches reference

Please refer Oracle documentation
https://docs.oracle.com/cd/E24628_01/doc.121/e39376/opatch_overview.htm

Oracle Help Center

https://docs.oracle.com/cd/E25290_01/doc.60/e25224/patchesupdates.htm

ORA-04031: unable to allocate nn bytes of shared memory

You will receive ORA-04031 error when allocated memory is small too small and does not have enough system global area (SGA).

Recently I got below error...

ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS_INTERNAL"
< ORA-06512: at line 1

< ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer")

Please find the cause and action

ORA-04031: unable to allocate nn bytes of shared memory
Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE. If the error is issued from an Oracle Streams or XStream process, increase the initialization parameter STREAMS_POOL_SIZE or increase the capture or apply parameter MAX_SGA_SIZE.

I will explain some of the possible root causes and how to fix it.

1. Insufficient RAM – This is most common issue when the database usage increased over the period. Increase the RAM and also adjust related SGA_MAX_TARGET or MEMORY_MAX_SIZE in case of AMM.

2. Shared pool fragmentation – This can be addressed by increasing the shared_pool_size in spfile or init.ora parameter. If you cannot bounce the database for changes to effect then I flush shared pool using “alter system flush shared pool;” command for temporary fix

3. Pinned packages – You will see this error if you have pinned lots of packages with dbms_shared_pool.keep and DB does not have enough resources

If your application is not using bind variables then it will cause SGA to become fragmented. In this case, if you turn on cursor_sharing it will help. Also make sure you use bind varibales. 


Also, run below queries to get SGA/PGA memory advice.
select * from v$sga_target_advice; select * from v$pga_target_advice;

Refer: MOSC notes 146599.1 and 396940.1 for more details for resolving the ORA-04031 error:

Regards,

http://oracleracexpert.com

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes]

When working with Oracle DB Version 19.5 recently noticed below errors in alert.log file

Starting background process VKTM
2020-02-04T17:05:58.711181-08:00
Errors in file /home/oracle/diag/rdbms/testdb/TESTDB/trace/TESTDB_vktm_43832.trc (incident=42521):
ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Incident details in: /home/oracle/diag/rdbms/testdb/TESTDB/incident/incdir_42521/TESTDB_vktm_43832_i42521.trc
2020-02-04T17:05:58.713567-08:00
Error attempting to elevate VKTM's priority: no further priority changes will be attempted for this process
VKTM started with pid=5, OS id=43832

After research found that parameter _high_priority_processes="VKTM" may help, but it is a internally parameter you cannot able to apply directly on SPFILE. I always raise a ticket with Oracle support to got the confirmation before making changes.

This issue has been reported in the internal bug - Bug 30664554 : ORA-00800: SOFT EXTERNAL ERROR, ARGUMENTS: [SET PRIORITY FAILED], [VKTM]
In order to update internal parameters on a DB using SPFILE you need to follow bellows steps.

1. Take a backup of SPFILE and PFILE to revert changes in case of any isuses.
2. Create PFILE from SPFILE
SQL> create pfile=’$ORACLE_HOME/dbs/initTESTDB.ora’ from spfile
3. Add/update the parameter in PFILE
4. Rename SPFILE and stop/start the DB to use PFILE that was created
5. Create SPFILE from PFILE
SQL> create SPFILE=’$ORACLE_HOME/dbs/spfileTESTDB.ora’ from pfile;
6. stop/start the DB to use SPFILE that was created
Now the changes are applied to SPFILE.

Note that every environment, errors, load, use cases are different, I would highly recommend users to open a ticket with Oracle support and get confirmation before making any changes to your databases.
Thanks
Satishbabu Gunukula,ORACLE ACE
https://oracleracexpert.com

Webinar: Data Guard Physical Standby Setup in Oracle?

This Webinar helps you to understand the benefits of Oracle Data Guard, available methods and how to setup and common issues.

Date and time: Feb 28th 2019 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.
· Oracle Data Guard and its Benefits
· Overview of different Data Guard Options
· Difference between RAC vs DG
· Prerequisites
· DataGuard Demonstration
· Common Issues
· Post verification Steps
· References
· Contact Info
· Q&A

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For presentation link Click here

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Physical standby redo log must be renamed

When creating standby database using database duplication it completed but received below standby redo error. This error will not stop the replication but user don’t want to see these ORA- errors.

Finished recover at 2019-02-12:15:02:32
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/oracle/redo1/ORCL/ ORCL _1A.rdo'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/oracle/redo1/ORCL/ORCL _2A.rdo'

Though this didn't stop the data guard replication, the customer wanted to get-rid the message. Starting with v10g, this was an expected behavior to improve the switchover and failover. With v10g, when MRP is started, it will attempt to clear the online log files.

If PRIMARY and STANDBY has the same directory structure then you will see these errors. This is an expected behavior when you don’t set log_file_name_convert parameter. You can get rid-off the error message just setup dummy value

SQL> ALTER SYSTEM SET log_file_name_convert='dummy','dummy';

You should not see ORA messages any more in the alert.log file.

Thanks
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

ORA-16664 with ORA-01031 password file issue

Data Guard Broker switchover fails with an ORA-16664 error

DGMGRL> show configuration;
Configuration – ORCL_DG
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
orcl_stdby - Physical standby database
Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED
Configuration Status:
ERROR

Errors from Primary DRC log 2012-01-02 13:46:23.675 NSV2: database actually reached ORCL_STDBY
2012-01-02 13:46:23.677 NSV2: Failed to send message to site ORCL_STDBY. Error code is ORA-16642.
2012-01-02 13:46:23.678 03000000 973032160 DMON: Database ORCL_STDBYreturned ORA-16642

Errors from Standby DRC log 2012-01-03 13:51:24.902 Connection to database ORCL returns ORA-01031.
2012-01-03 13:51:24.903 Please check database ORCL is using a remote password file,
2012-01-03 13:51:24.904 its remote_login_passwordfile is set to SHARED or EXCLUSIVE,
2012-01-03 13:51:24.905 and the SYS password is the same as this database.
2012-01-03 13:51:24.905 NSV0: Failed to connect to remote database ORCL. Error is ORA-01031
2012-01-03 13:51:24.906 NSV0: Failed to send message to site ORCL. Error code is ORA-01031.

Solution : The password file mismatch at primary and standby cuased the issue. Make sure the same password file copied to all nodes

Also verify the connect identifier for for standby database is correct and you are able to tnsping and connect using sqlplus as sysdba.

Refer : Troubleshooting ORA-16191 and ORA-1017/ORA-1031 in Data Guard Log Transport Services or Data Guard Broker (Doc ID 1368170.1)

Regards,

Satishbabu Gunukula, Oracle ACE

http://www.oracleracepxert.com

ORA-16053: DB_UNIQUE_NAME ORCL_STBY is not in the Data Guard Configuration with ORA-02097 error

The ORA-02097 is very generic error you need to check following error message to understand the issue.

ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16053: DB_UNIQUE_NAME ORCL _STBY is not in the Data Guard Configuration
In my case I am trying to update DB_UNIQUE_NAME but it is failed, because the specified DB_UNIQUE_NAME is not in the Data Guard Configuration.

Before you update DB_UNIQUE_NAME parameter make sure LOG_ARCHIVE_CONFIG is enabled and it has valid DB_UNIQUE_NAME.

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STDBY)'

You can find list of valid DB_UNIQUE_NAMEs in V$DATAGUARD_CONFIG view

Thanks,
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib

Normally users see ORA-04030 error with shortage of RAM or small PGA and kernel parameter config.

ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib)
Non critical error ORA-48913 caught while writing to trace file "/u01/home/oracle/diag/rdbms/ORCL/ORCL/incident/incdir_8937/ORCL_ora_11258_i8937.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached
ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib)
Non critical error ORA-48913 caught while writing to trace file "/u01/home/oracle/diag/rdbms/ORCL/ORCL/incident/incdir_8938/ORCL_ora_11258_i8938.trc" Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached

Users can refer metalink Note 233869.1 titled "Diagnosing and Resolving ORA-4030 errors"

By looking the error it is clear that “out of process memory”, where oracle cannot get the required RAM. But note that you need to analyze the trace to find out the root cause

In my case I found that one of the un-optimized PL/SQL job consuming the resources and caused the issue.

Current SQL Statement for this session (sql_id=gjn38xnrxcfbj) -----
BEGIN orcl_server_bom_script.all_level(:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 ) ; END;
57% pid 67: 4089 MB used of 4092 MB allocated <= CURRENT PROC
Dump of Real-Free Memory Allocator Heap [0x7f2c640b4000]
mag=0xfefe0001 flg=0x5000003 fds=0x6 blksz=65536
blkdstbl=0x7f2c640b4010, iniblk=66560 maxblk=524288 numsegs=115
In-use num=65424 siz=4290117632, Freeable num=0 siz=0, Free num=1 siz=20512768

In our case max_map_count value is not enough and needs to investigate why the pl/sql processes requires such a large amount of memory. In this case adjusting PGA_AGGREGATE_TARGET or MEMORY_TARGET will not limit the size processes can grow and will not help.

To fix the issue increase the value of max_map_count. To find out current vale of max_map_count run below command

$cat /proc/sys/vm/max_map_count
65530

To change the value run below command
sysctl -w vm.max_map_count= <greater than 65530>

Follow below metalink note to change the value of vm.max_map_count.
Modification of "vm.max_map_count" on Oracle Linux 6 (Doc ID 2195159.1) 

Regards

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

opidcl aborting process unknown ospid (xxxx) as a result of ORA-1000

By looking the error message we can easily find out the issue

01000, 00000, "maximum open cursors exceeded"

Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.

First you need to work with application team and find out why the cursors are not closing.

If required you can increase the OPEN_CURSORS value by running below command.

ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;

Note that to increase the value of OPEN_CURSORS no downtime required, but cursor storing require more memory.

Regards
Satishbabu Gunukula
htt://oracleracexpert.com

SPFILE restore failed with RMAN-04014 and RMAN-04014

User normally restores SPFILE when it is deleted accidently or the database is completed decommissioned and database need to restore from backup.

To restore SPFILE user must set the DBID
RMAN> set DBID=4563434343232

Before you restore SPFILE you need to bring up the database in nomount. But there is not SPFILE to bring the database.

Where SPFILE is not available startup the database with “STARTUP FORCE NOMOUNT”, RMAN will start the instance with a dummy parameter

You might see below error when trying to startup the database

RMAN> startup force nomount;
executing command: SET DBID
database name is “TST” and DBID is 4563434343232
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/home/oracle/product/10.2.0/db_1/dbs/initTST.ora’
starting Oracle instance without parameter file for retrival of spfile RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 11/13/2014 15:37:45
RMAN-04014: startup failed: ORA-04031: unable to allocate 4128 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(1,0)”,”kglsim hash table bkts”)

As you see ERROR message in bold, the default values to startup the database is not sufficient. These default values depend up on the version and operating system.

Set the SGA_TARGET environment variable with higher value at O/S level.
export ORA_RMAN_SGA_TARGET= 10240 

RMAN> startup force nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/home/oracle/product/10.2.0/db_1/dbs/initTST.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 107374182400 bytes
Fixed Size 2286808 bytes
Variable Size 1325402920 bytes
Database Buffers 106032005120 bytes
Redo Buffers 14487552 bytes

Now the database instance opened NOMOUNT state and you can restore the SPFILE

RMAN> restore spfile from autobackup;
Starting restore at 2014-11-13:15:40:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=33 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day: 20141113
channel ORA_DISK_1: looking for autobackup on day: 20141112
channel ORA_DISK_1: looking for autobackup on day: 20141111
channel ORA_DISK_1: looking for autobackup on day: 20141110
channel ORA_DISK_1: looking for autobackup on day: 20141109
channel ORA_DISK_1: looking for autobackup on day: 20141108
channel ORA_DISK_1: looking for autobackup on day: 20141107
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/13/2014 15:40:37
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
 
The SPFILE restore has failed because the database mounted with DUMMY spfile and it does not have information about AUTOBACKUP of the SPFILE.

You have two options.

1. Restore the SPFILE using FROM clause
RMAN> restore spfile from '/ora-backup/TST/cf_c-2786659778-20141110-05';
Starting restore at 2014-11-13:15:40:59
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /ora-backup/TST/cf_c-2786659778-20141110-05
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 2014-11-13:15:41:02

2. Restore the SPFILE using Control file AUTOBACKUP
RMAN> RUN {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'autobackup_format';
RESTORE SPFILE FROM AUTOBACKUP;
}

executing command: SET CONTROLFILE AUTOBACKUP FORMAT Starting restore at 13-JUN-13
using channel ORA_DISK_1 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20141113
channel ORA_DISK_1: AUTOBACKUP found: '/ora-backup/TST/cf_c-2786659778-20141110-05’
channel ORA_DISK_1: restoring spfile from AUTOBACKUP '/ora-backup/TST/cf_c-2786659778-20141110-05
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 2014-11-13:16:49:02

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Oracle RMAN backup fails with ORA-01031 insufficient privileges

RMAN automatically requests a connection to the target database as SYSDBA

In order to connect to the target using RMAN as SYSDBA, user must do one of the following:

• The user account should be part of the operating system DBA group on the target database server. It gives the ability to connect with SYSDBA privilege without password.
• Create a password file using orapwd command and enable initialization parameter REMOTE_LOGIN_PASSWORDFILE.
• Make sure you are passing correct username and password.

If the target database does not have a password file, then the user you are logged in as must be validated with operating system authentication.

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 18 16:37:36 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN>
RMAN> connect target *
RMAN-00571: ============================================
RMAN-00569: ======== ERROR MESSAGE STACK FOLLOWS ============
RMAN-00571: ============================================
ORA-01031: insufficient privileges

RMAN> **end-of-file**
RMAN>
host command complete

RMAN> 2> 3> 4> 5> 6> 7>
RMAN-00571: ============================================
RMAN-00569: ========== ERROR MESSAGE STACK FOLLOWS ==========
RMAN-00571: ============================================
RMAN-03002: failure of allocate command at 04/18/2013 16:37:37
RMAN-06171: not connected to target database

RMAN>
RMAN-00571: =============================================
RMAN-00569: =========== ERROR MESSAGE STACK FOLLOWS =========
RMAN-00571: =============================================
RMAN-03002: failure of list command at 04/18/2013 16:37:37
RMAN-06171: not connected to target database

Cause:  In above scenario, RMAN denies logon to target database

Solution: - create a password file for the target database or add yourself to the administrator list in the operating system.

1. If you are using Password file then make sure that it should be in the format of orapw<SID> under $ORACLE_HOME/dbs directory and also make sure you are using REMOTE_LOGIN_PASSWORDFILE parameter in init.ora or spfile .

$ orapwd file=$ORACLE_HOME/dbs/orapwTESTDB password=xxxxx

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE scope=spfile;
If using pfile then add or edit the parameter.

Query v$pwfile_users to see the list of users who have been granted SYSDBA and SYSOPER privilege as derived from password file.

SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
----------------------------- ----- ----- -----
SYS TRUE TRUE FALSE

2. Operating system authentication, Check the sqlnet.ora in your$ ORACLE_HOME\network\admin directory and add to it the following line, if not exists.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

If above entry exists and commented then uncomment the line. In case if sqlnet.ora file does not exist then create the file.

Below Authentication Methods Available with Oracle Net Services

NONE means not authentication mode. The user must use valid username and password can be used to access database
ALL means for all authentication modes
NTS means for Windows NT native authentication.

Find out that weather user is part of operating system DBA group, if not add the user to the group.

In unix, you can find the group using below command.
$ cat /etc/group | grep dba

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

How to Change Oracle DBNAME and DBID

When you change DBID of the database all previous backups will become UNUSABLE and you must open the database with RESETLOGS option. Once you change the DBID make sure you take database backup immediately.

If you only change DB_NAME then old backups are still USABLE and you NO need to open the database with RESETLOGS options. But you need to update the database name in initialization parameter file and need to re-create password file.

Follow the steps in Changing the DB_NAME and DBID:
1. Take backup of the Database

2. Drop the DB Console, if any.
For DB Control 10.1.x
    $ ORACLE_HOME/bin/emca -x <sid>
For DB Control 10.2.x and 11.x
    $ORACLE_HOME/bin/emca -deconfig dbcontrol db

3. Shutdown and Start the database in MOUNT stage
SQL> STARTUP MOUNT;

4. Use NID utility to change the Database Name and Database ID
$ nid TARGET=sys/password@alias as sysdba DBNAME=NEW_DBNAME
Or
$ nid TARGET=sys/password as sysdba DBNAME=NEW_DBNAME

The nid utility performs the validation of the controlfile and datafiles. Once the validation successful it will prompt you to confirm the database name change operation.

Please see the below output (changing DBNAME from ORCL1 to ORCL5)

$ nid TARGET=sys/xxxxxx@ORCL1 as sysdba  DBNAME=ORCL5

DBNEWID: Release 10.2.0.4.0 - Production on Mon Feb 03 10:01:06 2012

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

Connected to database ORCL1 (DBID=5989889872)

Connected to server version 10.2.0

Control Files in database:
   /u01/oradata/ORCL1/TESTDB01.ctl
    /u02/oradata/ORCL1/TESTDB02.ctl
    /u03/oradata/ORCL1/TESTDB03.ctl

Change database ID and database name ORCL1 to ORCL5? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 5989889872 to 7984565578

After successfully changing the database name the utility will shutdown the database.

5. Update DB_NAME in initialization parameter file with NEW Database name and rename the parameter file to match NEW Database Name.

6. Mount the database and open with RESETLOGS
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN RESETLOGS;

7. Recreate the password file using below syntax
$ orapwd file=<fname> password=<password> entries=<users> force=<y/n> nosysdba=<y/n>

8. Change the database name in tnsnames.ora and listener.ora (in case of static)  and reload the listener
$ lsnrctl reload

9. Recreate the DB console, if using
$ emca -config dbcontrol db -repos recreate

10. Backup the Database

For Windows: You must recreate the service so the correct name and parameter file are used
C:\> oradim -delete -sid ORCL1
C:\> oradim -new -sid ORCL5 -intpwd password -startmode auto -pfile c:\oracle\product\10.2.0\dbhome_1\dbs\spfileORCL5.ora

Follow the steps in Changing ONLY DBNAME:
Follow the steps 1-3 in above section

4. Use NID utility to change the DBNAME

$ nid TARGET=SYS/password@alias as sysdba DBNAME=NEW_DBNAME SETNAME=YES
or
$ nid TARGET=SYS/password as sysdba DBNAME=NEW_DBNAME SETNAME=YES

5. Update DB_NAME in initialization parameter file with NEW Database name and rename the parameter file to match new DBNAME

6. Startup the database in normal mode
SQL> STARTUP;

Please note that you no need to open the database with resetlogs option as you only changed database the DBNAME.

Follow the steps 7-10 in above section

Follow the steps in Changing ONLY DBID:

1. Take backup of the Database

2. Shutdown and Start the database in MOUNT stage
SQL> STARTUP MOUNT;

3. Use NID utility to change the DBID

$ nid TARGET=SYS/password@alias as sysdba
or
$ nid TARGET=SYS/password as sysdba

4. Mount the database and open with RESETLOGS
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN RESETLOGS;

5. Backup the Database

You can refer metalink note “Note.278100.1 How To Drop, Create And Recreate DB Control In A 10g Database”

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Oracle SPFILE and PFILE

Oracle provides two different parameter files, PFILE and SPFILE.

PFILE is a text based file and in order to add/modify any database parameters, need to edit the INIT.ORA file using “vi” in unix or notepad in windows. To apply new or modified database parameters changes, database restart is required.

SPFILE is a binary file and introduced in Oracle 9i. SPFILE simplifies administration, maintaining parameter settings consistent and Server parameter file is a binary file let you make persistent changes to individual parameters. Use the CREATE SPFILE statement to create a Server Parameter file from PFILE with SYSDBA/SYSOPER privilege.

By default PFILE or SPFILE default location is “$ORACLE_HOME/dbs” for UNIX and LINUX, %ORACLE_HOME%\database for Windows. In case of RAC, SPFILE located on the shared storage.

You can change the SPFILE parameters using Enterprise manager or ALTER SYSTEM SET ‘parameter’ statement with SCOPE clause. The SCOPE clause has three values MEMORY, SPFILE and BOTH.

SPFILE: The change is applied in the server parameter file only and is effective at the next startup.
For Ex: - SQL> ALTER SYSTEM SET SGA_MAX_SIZE=1024m SCOPE=spfile;

Memory: The change is applied in memory only and the effect is immediate.
For Ex:- SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=50 SCOPE=MEMORY;

BOTH: The change is applied in both the server parameter file and memory. For dynamic parameters the effect is immediate.
Ex: - SQL> ALTER SYSTEM SET SGA_TARGET=1024m SCOPE=BOTH;

For Static parameters the MEMROY, BOTH specifications are not allowed. Only SPFILE is allowed.

If you do not specify the SCOPE clause then the default is BOTH. For Dynamic parameters you can specify DEFERRED keyword and the specified change is effective only for future sessions.

During Database startup Oracle searches for initialization parameter file under $ORACLE_HOME/dbs in UNIX and $ORACLE_HOME/database on Windows in following order.

spfileSID.ora
spfile.ora
initSID.ora
init.ora

Backup/Restore parameter files(SPFILE and PFILE):
Using Recovery Manager(RMAN), SPFILE can be backed up with database control file by setting
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
SPFILE can be restored using following RMAN command.
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

PFILE cannot be backed-up using RMAN, backup and restore can be done using O/S copy command.

Common Errors and solutions in modifying SPFILE:
1. SQL>alter system SET LOG_ARCHIVE_DEST='/oradata/TESTDB' scope=both;
alter system SET LOG_ARCHIVE_DEST='/oradata/ TESTDB' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

Solution: OCATION keyword is required.
SQL> alter system set log_archive_dest_1='location=/oradata/ TESTDB' scope=both;
System altered.

2. SQL> alter system set log_archive_dest_1='location=/oradta/ TESTDB' scope=both;
alter system set log_archive_dest_1='location=/oradata/ TESTDB' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Linux-x86_64 Error: 2: No such file or directory

Solution: LOG_ARCHIVE_DEST_1 is incorrect or does not exist. Prove correct path.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

How to Change Static parameters in SPFILE and PFILE

Change Static parameters in SPFILE:
Changing static parameters requires the SPFILE option for the SCOPE clause with ALTER SYSTEM Statement and changes applies to SPFILE only. The changes cannot be applied to active Instance and the database needs to be restarted to take effect the modified parameters.

You can identify the static parameters using below query
SQL> select name, value, issys_modifiable from v$parameter
where name='processes';
NAME                     VALUE          ISSYS_MODIFIABLE
------------------- -------------- ---------------------------
Processes               150                 FALSE

All parameter that have the column ISSYS_MODIFIABLE value FALSE in the V$PARAMETER view are STATIC parameters and remaining are DYNAMIC parameters in Oracle database.

The ALTER SYSTEM command with SCOPE=SPFILE the will not update the value in the V$PARAMETER view but it will show the new value in the V$SPPARAMETER view as Oracle SPFILE parameter is updated.

For ex:-
SQL> alter system set processes=200 scope=both;
alter system set processes=200 scope=both
               *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

Above statement failed because “processes” is Static parameter and can be used with SCOPE=SPFILE only.

SQL> alter system set processes=200 scope=spfile;
System altered.

You can view the new value in V$SPPARAMETER view as the SPFILE is updated.

SQL> select name, value, isspecified from v$spparameter
where name ='processes';
NAME                     VALUE         ISSYS_MODIFIABLE
------------------- -------------- ---------------------------
Processes               150                 TRUE

Changing Static parameters in PFILE:
Edit the “$ORACLE_HOME/dbs/init.ora” file with new value and bounce the database to take effect the modified parameters.

Identify weather using PFILE or SPFILE:
To check SPFILE or PFILE used by Database or instance, run below commands.

You can see the Oracle SPFILE location.

SQL > show parameter spfile;
NAME                    TYPE        VALUE
------------------ ----------- -----------------------------------------
spfile                     string          /oracle/v10201/dbs/spfileOradb.ora

SQL > select name, value from v$parameter where name = 'spfile';
NAME          VALUE
------------ ------------------------------------------------------------
spfile             /oracle/v10201/dbs/spfileOradb.ora

SQL> select count(1) from v$spparameter where isspecified = 'TRUE';

Returns a NON-ZERO value if SPFILE is in use.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Create spfile from pfile

Create spfile from pfile in Oracle:
Use the CREATE SPFILE statement to create a Server Parameter file from PFILE with SYSDBA/SYSOPER privilege. You can specify the file name and path in the create command. See the SPFILE example.

SQL> create spfile from pfile;
SQL> Create spfile='/tmp/spfileOradb.ora' from pfile;

Common Errors:
1. If you edit SPFILE or corrupted, you will receive below error (Don not edit spfile).
SQL> alter system set db_files=200 scope=spfile;
alter system set db_files=200 scope=spfile
*
ERROR at line 1:
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

2. If spfile deleted accidentally, you will get below error during Database startup.
SQL> startup
ORA-01078: failure in processing system parameters

Solution: Recreate a binary SPFILE from PFILE.

Remove parameter from Spfile:
You can remove spfile parameter by using RESET command.

SQL> ALTER SYSTEM RESET session_cached_cursors SCOPE=spfile sid='SID*'

Create pfile from spfile:
Use the CREATE PFILE statement to create a Parameter file from SPFILE with SYSDBA/SYSOPER privilege. You can specify the file name and path in the create command. See the pfile example.

SQL> create pfile from spfile;
SQL> create pfile = '/tmp/initDB1.ora' from spfile;

Common Errors:
you will encounter this error while creating pfile/spfile.

ERROR at line 1:
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory Additional information: 3

Reason:
1. If database is down and you are trying to create a pfile from spfile.
2. Providing wrong file names.

Change Oracle spfile Location :
If you want to change the spfile location, create a PFILE in the default location and in pfile specify the spfile parameter location to non-default location.

For ex: - PFILE contents look like below
spfile = “New_path”/spfileOradb.ora

Create spfile from pfile in RAC :
In order to use same spfile at startup each RAC instance uses its own pfile and that points to one shared spfile on shared storage. If you are using ASM the spfile then the spfile will be located in Shared ASM file system.

SQL > show parameter spfile;
NAME TYPE VALUE
-------- ------- -----------------------------------------
spfile string +DATA/testdb/spfiletestdb.ora

Use above commands to create spfile from pfile and pfile from spfile.

Common Errors in RAC:
You will receive below error when modifying the parameter SCOPE=memory using SID=’*’

SQL> alter system set sga_target=500m scope=memory SID=’*’;
alter system set sga_target=500m scope=memory
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

Solution: - You need to modify the parameter individually on each instance using the SID=’Instance_Name’

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/