Wednesday, May 26, 2021

ORA-01555: snapshot too old: rollback segment number x with name "_xxxxx" too small

When we come across “snapshot too old” error we need to look into all possibilities

1. Determine if UNDO_MANAGEMENT is MANUAL or AUTO – Make sure you are using Auto, this will take care of auto management and will help to tune

2. In the ora-01555, if you see segment number with name that means it is caused by UNDO segment, if not LOG segment due to read consistency

3. Find out which client sessions or programs causing the issue and QUERY DURATION.

The possible solution will be set UNDO_MANAGEMENT to AUTO then make sure we have set correct value for UNDO_RETENTION.

Run below SQL query to identify weather undo tablespace was too small to maintain UNDO_RETENTION

select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
UNXPSTEALCNT "# Unexpired|Stolen", EXPSTEALCNT "# Expired|Reused",
SSOLDERRCNT "ORA-1555|Error", NOSPACEERRCNT "Out-Of-space|Error",
MAXQUERYLEN "Max Query|Length" from gv$undostat
where begin_time between
to_date(‘Start time of the query','MM/DD/YYYY HH24:MI:SS')
and
to_date('End time of the query','MM/DD/YYYY HH24:MI:SS')
order by inst_id, begin_time;


Find out the current retention period by querying the tuned_undoretention column of v$undostat. The database tunes the undo retention period to be longer than the long running query. The v$undostat view contains one row for each 10-minute stats collection interval over the last 4 days. The Data beyond 4 days can query the dba_hist_undostat view.

The below query will display the tuned_undoretention value in seconds:

select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time,
tuned_undoretention from v$undostat
order by end_time;


Refer Oracle notes
Note 563470.1 Lob retention not changing when undo_retention is changed
Note 800386.1 ORA-1555 - UNDO_RETENTION is silently ignored if the LOB
Note 422826.1 How To Identify LOB Segment Use PCTVERSION Or RETENTION
Bug:3200789 Abstract: VISIBILITY OF LOB SEGMENT USAGE FOR UNDO


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

Monday, May 24, 2021

Oracle software cloning VERIFICATION_FAILED with ACFSUtil:PRCT-1011 : Failed to run "acfsutil". Detailed error

I recently come across below error message during the Oracle Binaries cloning for ORacle 19c  using “runInstaller”

I fond below messages during the troubleshooting 

INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isCRSConfigured() returns value false
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isHAConfigured() returns value false
FINEST: [May 8, 2021 12:02:43 AM] oracle.install.library.asm.ACFSUtil:PRCT-1011 : Failed to run "acfsutil". Detailed error:
/bin/sh: /sbin//acfsutil: No such file or directory
INFO: [May 8, 2021 12:02:43 AM] Retrieving ASM Cluster File System information...
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isCRSConfigured() returns value false
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isHAConfigured() returns value false
INFO: [May 8, 2021 12:02:43 AM] Getting the last existing parent of: /oracle/product/19.0.0.0/dbhome_1
INFO: [May 8, 2021 12:02:43 AM] Path: /oracle/product/19.0.0.0/dbhome_1
INFO: [May 8, 2021 12:02:43 AM] Last existing parent: /oracle/product/19.0.0.0/dbhome_1
INFO: [May 8, 2021 12:02:43 AM] Executing [df, -P, /oracle/product/19.0.0.0/dbhome_1]
INFO: [May 8, 2021 12:02:43 AM] Starting Output Reader Threads for process df
INFO: [May 8, 2021 12:02:43 AM] Filesystem 1024-blocks Used Available Capacity Mounted on
INFO: [May 8, 2021 12:02:43 AM] The process df exited with code 0
INFO: [May 8, 2021 12:02:43 AM] Waiting for output processor threads to exit.
INFO: [May 8, 2021 12:02:43 AM] Output processor threads exited.
INFO: [May 8, 2021 12:02:43 AM] PATH has :==>/oracle/product/19.0.0.0/dbhome_1/lib:/oracle/product/19.0.0.0/dbhome_1/oui/lib/linux64:/oracle/product/19.0.0.0/dbhome_1/bin:/oracle/product/19.0.0.0/dbhome_1/ctx/lib:/usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib
INFO: [May 8, 2021 12:02:43 AM] PATH has :==>/oracle/product/19.0.0.0/dbhome_1/lib:/oracle/product/19.0.0.0/dbhome_1/oui/lib/linux64:/oracle/product/19.0.0.0/dbhome_1/bin:/oracle/product/19.0.0.0/dbhome_1/ctx/lib:/usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isCRSConfigured() returns value false
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isHAConfigured() returns value false
FINEST: [May 8, 2021 12:02:43 AM] oracle.install.library.asm.ACFSUtil:PRCT-1011 : Failed to run "acfsutil". Detailed error:
/bin/sh: /sbin//acfsutil: No such file or directory


Verify any pre-requisites verifications failed.
$ cat installActions2021-05-08_00-02-39AM.log |grep VERIFICATION_FAILED

TaskKernelParam:OS Kernel Parameter: shmmax[CHECK_KERNEL_PARAMETER_shmmax]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [139 Milliseconds]
TaskKernelParam:OS Kernel Parameter: shmall[CHECK_KERNEL_PARAMETER_shmall]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [155 Milliseconds]
TaskKernelParam:OS Kernel Parameter: file-max[CHECK_KERNEL_PARAMETER_file-max]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [107 Milliseconds]
TaskKernelParam:OS Kernel Parameter: rmem_default[CHECK_KERNEL_PARAMETER_rmem_default]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [105 Milliseconds]
TaskKernelParam:OS Kernel Parameter: rmem_max[CHECK_KERNEL_PARAMETER_rmem_max]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [103 Milliseconds]
TaskKernelParam:OS Kernel Parameter: wmem_default[CHECK_KERNEL_PARAMETER_wmem_default]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [103 Milliseconds]
TaskKernelParam:OS Kernel Parameter: wmem_max[CHECK_KERNEL_PARAMETER_wmem_max]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [106 Milliseconds]
TaskKernelParam:OS Kernel Parameter: aio-max-nr[CHECK_KERNEL_PARAMETER_aio-max-nr]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [104 Milliseconds]
TaskPackage:Package: gcc-c++-4.8.2[CHECK_PACKAGE_EXISTENCE_gcc-c++]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [80 Milliseconds]

1. Make sure you verify that ORACLE_HOME has owned by Oracle and Group and it has proper permissions…
chown -R oracle:dba /oracle/product/19.0.0.0/dbhome_1
chmod 775 /oracle/product/19.0.0.0/dbhome_1

2. Also verify the logs under oraInventory for any VERIFICATION_FAILED error messages. Make sure you fix all Oracle pre-requisites before you run runInstaller again. This should resolve many of the issues during the cloning.

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



Tuesday, May 18, 2021

Oracle RMAN backup using DataDomain-Boost

Data Domain boost plug-in enables database to communicate with Data domain systems in an optimized way and use of DD Boost for RMAN backup improves performance while reducing the data transfer.

You need to install RMAN pug-in on each DB server and these binaries communicate with Data domain systems. When taking backup using RMAN, the configuration channels should use media manager SBT_LIBRARY parameter to provide the path of the media management library of data domain.

RMAN issues an ORA-27211 error and exits if the media management library not able to locate.

Admin needs to enable DD Boost on a Data Domain System before we proceeding.

Steps:-  

1. Download the required pulg-in and install
For Ex:- RMAN_1.1.1.0-378849_RMAN_linux_64.tar

For Windows Download the RMAN plugin installer “libDDobkSetup.exe”

2. Installation: Set the Oracle Home then run the install.sh script
$ ./install.sh
Installing the Data Domain plugin for RMAN ...
Copying libraries to /oracle/home/11204/lib
cp libddobk.so /oracle/home/11204/lib/libddobk.so
cp libDDBoost.so /oracle/home/11204/lib/libDDBoost.so
Successfully installed the Data Domain plugin for RMAN

/orasnb/oracle_ddbda.cfg will have all configuration details

3. Verify: Verify the DDBoost install using below command , make the changes as needed

RUN {
ALLOCATE CHANNEL C1 TYPE SBT_TAPE PARMS 'SBT_LIBRARY=/oracle/home/11204/lib/libddobk.so';
send 'set username <username> password <password> servername <data domain/backup host server>';
RELEASE CHANNEL C1;
}

4. Run backup using DD Boost Media management library

You can run manual backup

run {
allocate channel dd1 type 'sbt_tape' parms='BLKSIZE=1048576,SBT_LIBRARY=/oracle/home/11204/lib/libddobk.so,ENV=(STORAGE_UNIT=<storage_unit>,BACKUP_HOST=<backup host>)';
backup filesperset 1 database format '%u_%p';
release channel dd1;
}

Configure BRTools using DDBoost: - Add/Change the below parameter in .sap file of database and comment out other unwanted parameters. Note that DDBoost will call RMAN internally to backup and restore.

backup_type = online
backup_dev_type = rman_disk
rman_channels = 10 <or any number of channels desired>
rman_filesperset = 1
rman_parms = "BLKSIZE=1048576,SBT_LIBRARY=<path-to-libddobk.so>,ENV=(STORAGE_UNIT=<LSU-name>,BACKUP_HOST=<DDR-FQDN>,ORACLE_HOME=<path-to-ORACLE_HOME>)"
rman_compress = no
backup_mode = all


RMAN Changes: Make below rman configuration changes
CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%F';

Perform Backup: 
Backup command:
brbackup -m all -p /oracle/home/11204 /dbs/initORCL.sap -u / -t online -d rman_disk
"-t online" option is the same as specifying "backup_type = online" in the configuration file
"-d rman_disk" option is to force "backup_dev_type = rman_disk", this is the observed behavior
Complete list of Command Options for BRBACKUP : http://help.sap.com/saphelp_nw70/helpdata/en/c7/c90e6b7cfd44309bd90b2b7a8a89fd/content.htm
 
Archive Log backup:
brarchive -s -c -p /oracle/home/11204/dbs/initORCL.sap -u /

Restore command:
brrestore -m all -p initKBW.disk.sap -b belcjoiv.anr -c force
We never tested restore using above command but we tested thoroughly with BRTools and RMAN

Steps to Enable DDBoost on a Data domain system: Unix Admin will execute these steps. The below are high-level steps and few other steps involved to configure as per the best practice.

1. Login as Administrator
 
2. Verify file sytem is enable dand running
# filesys status
Enable the file system, if diabled.
#filesys enable
 
3. Apply license key if not installed
# license add <license key>
 
4. Establish username and password for Data domain system
#user add <username> password <password>
# ddboost set user-name <username>
 
5. Enable DDBoost
# ddboost enable
 
6. Create the storage unit on the data domain system
# ddboost storage-unit create <storage_unit_name>

Thanks & Regards

Friday, May 14, 2021

Configure Oracle RMAN Backups using Media Manager

In order to backup and restore from sequential media such as tape you must integrate a media manager with your Oracle database. Note that media manager is not an Oracle product and must be obtained and licensed fom a third-party vendor.

1. Prerequisites for a Media Manager 

Before using any media manager with your Oracle database you need to install the required plug in and make sure that RMAN can communicate with it. For Instructions, refer vendor documentation. The third-party media management module contains the media management library that the Oracle database loads and uses when accessing the media manager..

2. Locating the Media Management Library

User should use SBT_LIBRARY parameter when allocating or configuring channels for RMAN to use to communicate with a media manager, SBT_LIBRARY parameter to provide the path to the media management library and this library will be loaded during the communication with media manager

On UNIX, the default library location is $ORACLE_HOME/lib/libobk.so, with the extension name varying according to platform: .so, .sl, .a, and so forth.

On Windows, the default library location is %ORACLE_HOME%\bin\orasbt.dll.

If the database is unable to locate a media library specified by the SBT_LIBRARY parameter RMAN issues an ORA-27211 error and exits. Note that there is no default media management library file as part of standard database installation.

When channel allocation fails, the database writes a trace file to the USER_DUMP_DEST directory.

For ex:- SBT Initialize failed for /oracle/home/lib/libobk.so
 
3. Media Manager Library Integration

You need to define the media manager library , Media manager server, storage system, configure user…etc to configure. The PARMS parameter will be used to ALLOCATE or CONFIGURE CHANNEL and FORMAT and BACKUP commands will be used to backup. To limit backup piece sizes, use the parameter MAXPIECESIZE,

Example 1:-
RUN {
ALLOCATE CHANNEL C1 TYPE SBT_TAPE PARMS='SBT_LIBRARY=<media manager > lib/libddobk.so'; ENV=(STORAGE_UNIT=<storage_unit>,BACKUP_HOST=<backup host>)'
}

Example 2: -
RUN {
ALLOCATE CHANNEL C1 TYPE SBT_TAPE PARMS='SBT_LIBRARY=<media manager > lib/libddobk.so'; ENV=(NSR_SERVER=<Media Manager server>,NSR_CLIENT=<oracle Client> ,NSR_GROUP=<Tapes>)’
}

Note that the parameters will vary based upon Media Manager vendor

If the media manager could not be located user will receive below error message
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on c1 channel at 12/10/2020 10:11:20
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 25

Once you configure the channel, you can run below commands to backup the database files.
To check SBT configuration
RMAN> SHOW CHANNEL FOR DEVICE TYPE SBT_TAPE;
To backup the control file to SBT_TAPE
RMAN> BACKUP DEVICE TYPE SBT_TAPE CURRENT CONTROLFILE;
Backup archived redo log backup to tape
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE

You can configure the default device to SBT_TAPE and FORMAT so that RMAN sends all backups to the media manager in specified format

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO SBT_TAPE;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT = '?/bkup_%U';

Once you configuring the default device all the backups should go to media manager:

RMAN> BACKUP CURRENT CONTROLFILE;
RMAN> BACKUP DATABASE;

Please see the below link to configure Oracle Backups using DDBoost Media Manager
Oracle RMAN backup using DataDomain-Boost

Thanks & Regards

Monday, May 10, 2021

Managing Immutable Tableas in ORacle 19c and Oracle 21c

Oracle 19c (19.11) introduced Immutable tables, these tables provides protection against unauthorized data modification that means these are read-only tables.

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;

The following are NOT supported with immutable tables:
· Creating immutable tables in the CDB root or application root
· Online redefinition using the DBMS_REDEFINITION package
· Truncating the immutable table
· Flashback table
· Sharded tables
· Adding columns, renaming columns
· Dropping columns, and dropping partitions
· Updating rows, merging rows
· Logical Standby and Oracle GoldenGate
· Direct-path loading and inserting data using parallel DML
· Defining BEFORE ROW triggers that fire for update operations
· Creating Automatic Data Optimization (ADO) policies
· Creating Oracle Label Security (OLS) policies
· Transient Logical Standby and rolling upgrades
· Converting a regular table to an immutable table or vice versa

Creating Immutable tables

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.

DML on Immutable Tables: Except insert, DML are not allowed
  • INSERT
SQL> INSERT INTO IMMU_TAB1 values ( 1,’TEST’,sysdate);
1 row inserted.
SQL> commit;
Commit complete.
  • DELETE
SQL> DELETE FROM IMMU_TABL1 WHERE COLA = 1;
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table
  • UPDATE
SQL> update IMMU_TAB1 set COLB = ‘TEST2’ where COLA= 1;
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table

DDL on Immutable Tables
  • TRUNCATE TABLE – This operation is now allowed as it deletes the rows
sql> TRUNCATE TABLE IMMU_TAB1;
Error report -
ORA-05715: operation not allowed on the blockchain or immutable table
  • ALTER RETENTION- User will get below error when try to reduce the retention period.
SQL> ALTER TABLE IMMU_TAB1 NO DROP UNTIL 2 DAYS IDLE;
Error report -
ORA-05732: retention value cannot be lowered

SQL> ALTER TABLE IMMU_TAB1 NO DELETE UNTIL 20 DAYS AFTER INSERT;
Error report -
ORA-05732: retention value cannot be lowered

SQL> ALTER TABLE IMMU_TAB1 NO DELETE;
Error report -
ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []

When trying to modify the rows to NO DELETE, we got ORA-600 error and it may be related to bug and contact Oracle for support.
  • MODIFY COLUMN - User can run below command to extend column length
SQL> ALTER TABLE IMMU_TAB1 MODIFY (COLB VARCHAR2(30));
Table IMMU_TAB1 altered.

  • DROP COLUMN - User will get below error when dropping column as this operation is not allowed
SQL> ALTER TABLE IMMU_TAB1 DROP COLUMN COLB;
Error report -
ORA-05715: operation not allowed on the blockchain or immutable table
  • ADD COLUMN - User will get below error when adding a new as this operation is not allowed
SQL> ALTER TABLE IMMU_TAB1 ADD (COLD VARCHAR2(10));
Error report -
ORA-05715: operation not allowed on the blockchain or immutable table

User can use DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS procedure to delete all rows that are beyond the specified retention period or obsolete

Example1: The below example deletes the rows that were created 31 days before the current system date.

DECLARE
NROWS NUMBER;
BEGIN
DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS('TESTUSR','IMMU_TAB1', SYSDATE-31, NROWS);
DBMS_OUTPUT.PUT_LINE('NO_OF_ROWS_DELETED=' || NROWS);
END;
/

Example2: The below procedure delete the rows that are beyond the retention period or obsolete
 
DECLARE
       NROWS NUMBER;
BEGIN
       DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS(
       SCHEMA_NAME   =>'TESTUSR',
       TABLE_NAME    => 'IMMU_TAB1',
       BEFORE_TIMESTAMP  => NULL,);
       DBMS_OUTPUT.PUT_LINE('NUMBER_OF_ROWS_DELETED=' || NROWS);
END;
/
  • DROP IMMUTABLE Table - The immutable table must be in the owner schema or must have the DROP ANY TABLE system privilege.
SQL> DROP TABLE IMMU_TAB1;

Oracle Database does not prevent flashback and point-in-time recovery operations to undo changes on immutable tables as these may be required to undo logical and physical corruptions. These recovery operations on a database undo the changes made to all tables, including immutable tables.

Note that retention policies in immutable tables relies on the system time.

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