Monday, July 19, 2021

Generate Table, Index or tablespace DDLs in oracle

When migrating database from one server to another you need Tablespace creation DDL. Also it is very useful when copying specific table or index or exp/imp from one database to databases.

Here are few ways to generate the tablespace script from source database.

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;
SQL>SPOOL TBS_DDL.SQL
SQL>SELECT DBMS_METADATA.GET_DDL('TABLESPACE',DBA_TABLESPACES.TABLESPACE_NAME) FROM DBA_TABLESPACES;
SQL>SPOOL OFF

You can see below command if you want the TABLESPACE creation script specific to one Table space.

SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual;

You can use below command to generate specific TABLE, INDEX Script from a user

Syntax:-
select dbms_metadata.get_ddl('TABLE','<TABEL NAME>','<SCHEMA>') from dual;
select dbms_metadata.get_ddl('INDEX','<INDEX NAME>','<SCHEMA>') from dual;


Ex:-
select dbms_metadata.get_ddl('TABLE','EMP_SAL','EMP') from dual;
select dbms_metadata.get_ddl('INDEX','EMPNO_IDX','EMP') from dual;


You can use below command to all TABLE, INDEX Script from a user. First, connect to user

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME)
FROM USER_TABLES U;
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)
FROM USER_INDEXES U;  

Thanks & Regards
http://oracleracexpert.com






Wednesday, July 14, 2021

DBCA fails with ORA-29516: Aurora assertion failure

When creating 11g database on a new system we encounter below error…

BEGIN
*
ERROR at line 1:
ORA-29516: Aurora assertion failure: Assertion failure at joez.c:3422
Bulk load of method java/lang/Object.<init> failed; insufficient shm-object space
ORA-06512: at line 3

You should look for dbca error logs for detailed error message that you can find under /<ORACLE_BASE>/cfgtoollogs/dbca/<SID>/

We found below error from postDBCreation.log

IF CatbundleCreateDir(:catbundleLogDir) = 0 THEN
*
ERROR at line 71:
ORA-06550: line 71, column 14:
PLS-00201: identifier 'CATBUNDLECREATEDIR' must be declared
ORA-06550: line 71, column 11:
PL/SQL: Statement ignored


This error could be one of the below reasons

1. The file systems or mount point options are in correct
2. Incorrect ulimit sessions for oracle user
3. You system has JAVA_JIT_ENABLED=TRUE

In my case I have below mount options in /etc/fstab
/dev/shm tmpfs defaults,nodev,nosuid,noexec 0 0

After removing “nodev,nosuid,noexec” from /etc/fstab resolved the issue.
/dev/shm tmpfs defaults 0 0

Thanks & Regards
http://oracleracexpert.com  





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