Unregister a Database from RMAN Recovery Catalog

Use UNREGISTER command to remove the metadata from RMAN recovery catalog. You can unregister one or more databases and but note that physical backups are not deleted by this command. You need to use DELETE BACKUP/ DELETE EXPIRED BACKUP commands to remove physical backups

Before you unregister, make sure you connect to recovery catalog and verify the backups. If the backups no longer needed then you can proceed

The below command provides the summary of all backups taken for a Database using RMAN

RMAN> LIST BACKUP;
RMAN> LIST BACKUP SUMMARY;

You can specify EXPIRED keyword to identify backups not found during a crosscheck
SQL> LIST EXPIRED BACKUP;

You can use below command to delete expired backups
SQL> DELETE EXPIRED BACKUP;

You can use below command to delete all the existing backups going to DEVICE TYPE DISK
RMAN> DELETE BACKUP DEVICE TYPE DISK;

Method 1: In this method, we are connecting to target database and recovery catalog database to unregister. On a primary/standby databases scenario, it removes all metadata associated to primary as well standby.

$ rman

 
RMAN>connect target /
Connected to target database: TEST (DBID=87658657577)

RMAN> CONNECT CATALOG rcat/xxxxxx@RMANDB
connected to recovery catalog database

RMAN> UNREGISTER DATABASE NOPROMPT;
database name is "TEST" and DBID is 87658657577
database unregistered from the recovery catalog

or

RMAN> UNREGISTER DATABASE;
Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

When using NOPROMPT, it doesn't ask for confirmation before UNREGISTER.

Method 2: In this method, we are connecting only recovery catalog database to unregister.

RMAN> CONNECT CATALOG rcat/xxxxxx@RMANDB
connected to recovery catalog database

RMAN> UNREGISTER DATABASE TEST NOPROMPT;

In case if there is more than one DB in the recovery catalog database then you need to use DBID to unregister.

If you want to unregister backups associated with Standby only then you can use this method to unregister Standby database by setting DBID. In this case, the backups are still usable by other primary or standby databases

RMAN> SET DBID 87658657577;
executing command: SET DBID
database name is "TEST" and DBID is 87658657577

RMAN> UNREGISTER DATABASE TEST NOPROMPT;
database name is "TEST" and DBID is 87658657577
database unregistered from the recovery catalog

Method 3: In this method, we are unresigering the database dbms_rcvcat Package. When using this package you need to provide DB_KEY and DBID.

You can get the DBID, DB_KEY from RC_DATEABASE
SQL> SELECT db_key, dbid FROM rc_database WHERE name = ‘TEST’;

SQL> EXECUTE dbms_rcvcat.unregisterdatabase(3422 , 87658657577);
PL/SQL procedure successfully completed.

This UNREGISTER command cannot be used when target database configured with ZERO data loss recovery appliance. You can use DBMS_RA.DELETE_DB procedure to unregister a database from Recovery Appliance.

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE

Convert Partitioned Table to Non-partitioned Table and vice versa

You might come across the situation that you need to convert Partitioned Table to Non-partitioned Table and vice versa.

You can RUN below command to identify weather the Table is partitioned or not

SQL> SELECT TABLE_NAME, PARTITIONED FROM USER_TABLES WHERE TABLE_NAME=‘EMP’;

TABLE_NAME PAR
--------------------- ---
EMP YES

I suggest using data pump to convert Partitioned Table to Non-partitioned Table. Below are the steps
Take a backup of the table that you want to convert to NON-Partitioned table

$ expdp SCOTT/xxxxx directory=EXP_DIR dumpfile=emp_expdp.dmp log=emp_expdp.log tables=EMP

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."EMP":"YEAR_2001" 15.398 MB 40023 rows
. . exported "SCOTT"."EMP":"YEAR_2002" 23.456 MB 89898 rows
. . exported "SCOTT"."EMP":"YEAR_2003" 89.675 MB 100453 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

Import the table into TEST schema using PARTITIONS_OPTIONS parameter

$ impdp TEST/xxxxx directory=EXP_DIR dumpfile=emp_expdp.dmp log=imp_emp.log remap_schema=SCOTT:TEST partition_options=merge

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “TEST"."EMP":"YEAR_2001" 15.398 MB 40023 rows
. . imported "TEST"."EMP":"YEAR_2002" 23.456 MB 89898 rows
. . imported "TEST"."EMP":"YEAR_2003" 89.675 MB 100453 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Sun July 5 08:44:21 2020 elapsed 0 00:00:10

This command will merge all partitions while importing as single table.

RUN below command to verify and you should see the out as
SQL> SELECT TABLE_NAME, PARTITIONED FROM USER_TABLES WHERE TABLE_NAME=‘EMP’;

TABLE_NAME PAR
---------------------- ---
EMP NO

DROP PARTATION

If you need to drop the partition use below command

Method 1: Drop the specific partition
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001;

Method 2: DELETE all rows part of partition and remove partition
SQL> DELETE FROM EMP PARTITION (YEAR_2001;);
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001;

Method 3:- Drop partition and update indexes
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001 UPDATE INDEXES;

ADD/MODFY partition

You can use ALTER TABLE ADD PARTITION, MODIFY PARTITION statement on a table

For ex:-
SQL>ALTER TABLE EMP MODIFY PARTITION BY RANGE (JOIN_DATE)
(PARTITION YEAR_2001 VALUES LESS THAN (to_date('01-JAN-2002','dd-mon-yyyy')),
PARTITION YEAR_2002 VALUES LESS THAN (to_date('01-JAN-2003','dd-mon-yyyy')));

SQL> ALTER TABLE EMP ADD PARTITION YEAR_2003 VALUES LESS THAN ( '01-JAN-2004' ) ;

Please refer oracle documentation for detailed PARTITIONS options and methods..etc

Thanks

http://oracleracexpert.com, Oracle ACE

Webinar: Cloning an Oracle Home or Oracle Install

This webinar helps to Clone Oracle Home or Oracle Install while migrating the databases.

Date and time: Dec 15th 2022 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• What is Cloning
• When Cloning useful
• Different methods of Cloning
• How to perform Cloning
• References
• 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

SSL Connection Error: SSL is required but the server doesn’t support it.

When users using connecting MYSQL using MySQL Workbench they received following error

By default MySQL Workbench, use SSL and users may receive above error. It is recommended to use certificates to avoid above error. In case, if you do not have the certificates then you can follow below steps to skip the certificate and connect to the MySQL Database

 



Option 1:

• Open MySQL workbench
• Right click on MySQL instance and select "Edit Connection"
• Go to the "SSL" tab under Connection Method
• Choose "If Available" instead of "Required" in the drop-down for the "Use SSL"
• Click the "Test Connection" and you should connect without errors.

Option 2:

• 
  
• Right click on MySQL instance and select "Edit Connection"
• Go to “Advanced” tab and enter "useSSL=0" in the 'Others' tab
• Click the "Test Connection" and you should connect without errors

Option 3:

 MySQL Workbench version 8.0.26 and below doesn’t use SSL Options by default

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Failed to connect to MySQL at with user root

When users connecting to MySQL using Workbench or from command prompt they may receive the error when they don’t have access to connect from the machine.

It is mainly due to security setup that allows to connect to “root” from specific IP address or localhost



In this scenario, ask your admins to create a new user account and setup the required access instead of using “root”

If the root account is not configure to authenticate with a password, you may need below command

MySQL> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';


If user need to login as root, you they can follow the steps to grant access to local host and or IP Address

MySQL> GRANT ALL ON [DB_Name].* to 'root'@'127.0.0.1' IDENTIFIED BY '[PASSWORD]';
MySQL> GRANT ALL ON [DB_Name].* to 'root'@ ‘IP Address’ IDENTIFIED BY '[PASSWORD]';

Instead of using “GRANT ALL ON”, use required privileges only.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

ORA-14552: Cannot Perform a DDL Commit or Rollback inside a query or DML

You will receive this error when you use COMMIT or ROLLBACK and make sure you don’t use DML in a function to avoid this error.

In case if you need to use COMMIT or ROLLBACK then change your function as autonomous transaction by using “PRAGMA AUTONOMOUS_TRANSACTION" in your function

For Ex:-
CREATE OR REPLACE FUNCTION Function_Autonomous
return number
as
v_number number;
pragma autonomous_transaction;
BEGIN
…………
END;
/

14552, 00000, "cannot perform a DDL, commit or rollback inside a query or DML "
*Cause: DDL operations like creation tables, views etc. and transaction
control statements such as commit/rollback cannot be performed
inside a query or a DML statement.

*Action: Ensure that the offending operation is not performed or
use autonomous transactions to perform the operation within
the query/DML operation.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

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;  

In case if you need a View DDL, you can run below command;
SQL>select DBMS_METADATA.GET_DDL('VIEW',<View_Name>’) from dual;
or
SQL>select DBMS_METADATA.GET_DDL('VIEW','<view_name>','<schema_name>') from DUAL;

You can run below command to generate the DDL statements of a SCHEMA
set pages 30000
set linesize 1000
set lines 500

SQL> SELECT DBMS_METADATA.GET_DDL('USER','<schema_name>') FROM dual;

You can run below command to generate DDL statements for more than one schema

SQL> SELECT DBMS_METADATA.GET_DDL('USER',U.USERNAME) FROM DBA_USERS U WHERE USERNAME IN ('USER1','USER2');

You can run below command to generate DDL statement of the System Grant/ role granted/object granted to a schema owner

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema_name>') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema_name>') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',<Schema_Name>’) from dual;

You can run below command to generate DDL statement of the role
SQL> SELECT DBMS_METADATA.GET_DDL('ROLE','RESOURCE') from dual;

You can also use SQL Developer to get the DDL script for a specific object.

You can refer oracle documentation for more details on DBMS_METADATA

Thanks & Regards

http://oracleracexpert.com

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  

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

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

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

http://oracleracexpert.com, Oracle ACE

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

http://oracleracexpert.com, Oracle ACE

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

Automatic Indexing in Oracle 19c using DBMS_AUTO_INDEX

Oracle 19c introduced new features automatic indexing, this feature creates and drops without dba intervention and takes care of index management

The DBMS_AUTO_INDEX package used to manage the automatic indexing feature. This feature evaluates the need for new indexes, creates when needed and drops then when they are no longer needed. It supports both single and multi-column index

Enable/Disable AUTO Index

To enable automatic indexing run below command
SQL> dbms_auto_index.configure ('AUTO_INDEX_MODE', 'IMPLEMENT');

To turn off or disable automatic indexing run below command
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');

To turn on automatic indexing, but new indexes remain invisible run below command
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');

Note: The invisible Index feature introduced in Oracle 11g, these indexes are maintained like other indexes but ignored by the optimizer unless the he OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE

For ex:- SQL> create index INDX_01 on EMP (emp_name) invisible;

Auto Index Tablespace Management

The automatic indexes by default created in the default permanent tablespace, but you can you can specify a tablespace using the AUTO_INDEX_DEFAULT_TABLESPACE property

SQL> exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','TBS_AUTO_INDX');

Run below command with NULL to return using the default permanent tablespace.
SQL> exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

User can specify threshold in percentage using below command. In this case only 60% of the tablespace is used for Auto Index
SQL> exec dbms_auto_index.configure ('AUTO_INDEX_SPACE_BUDGET', '60');

The autocreated indexes will be named with prefix SYS_AI. To find out these indexes there is a called “AUTO” added in DBA_INDEXES where you will see the value as “YES”

Automatic Index Management at Schema Level

You can control the SCHEMAS that’s should participate in Automatic Indexing feature by using the AUTO_INDEX_SCHEMA property.

SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', ‘EMP’, allow => TRUE);

If ALLOW parameter is set to TRUE, then specified schema will be added to inclusion list and you can clear the inclusion list using below command. 

SQL>  exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => TRUE);

User can manually add the SCHEMAS to the exclusion list by setting ALLOW parameter to FALSE. SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => FALSE);

User can clear the exclusion list by running below command 

SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => FALSE);

Users can also use AUTO_INDEX_EXCLUDE_SCHEMA property to exclude specific schema.
SQL> exec dbms_auto_index.configure ('AUTO_INDEX_EXCLUDE_SCHEMA', ‘EMP’);

The manually created unused indexes never deleted by the automatic indexing process and they can be deleted using AUTO_INDEX_RETENTION_FOR_MANUAL. But user needs to mention after how many days the unused manual indexes can be dropped.

SQL> exec dbms_auto_index.configure ('AUTO_INDEX_RETENTION_FOR_AUTO', '50')

Views: Oracle offers several Views associated with automatic Indexing features

 
DBA_AUTO_INDEX_CONFIG - configuration settings related to automatic indexes
DBA_AUTO_INDEX_EXECUTIONS - The history of Automatic Indexing task executions
DBA_AUTO_INDEX_STATISTICS - Statistics related to automatic indexes
DBA_AUTO_INDEX_VERIFICATIONS- stats about PLAN_HASH_VALUE, AUTO_INDEX_BUFFER_GETS
DBA_AUTO_INDEX_IND_ACTIONS - Actions performed on automatic indexes
DBA_AUTO_INDEX_SQL_ACTIONS - Actions performed on SQL statements to verify automatic indexes

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Create Recovery Catalog for RMAN

Recovery catalog schema used by RMAN to store one or more Oracle databases metadata. This info is already stored in control file of each Target Database and using Recovery catalog provides redundancy. In case, if target control file and all backups are lost then you can still get the info from Recovery catalog.

RMAN Recovery catalog also helps as centralized metadata location for all your databases and it helps easily report from once location. In addition, you can store metadata history longer than Control file.

You must register database in order to store metadata in Recovery catalog. it is highly recommended to use enable ARCHIVELOG mode for Recovery Catalog database

· Recovery Catalog metadata has following database file info
· Data file and archived redo log backup sets and pieces
· Data file and Archive log copies
· Database structure such as tablespaces and data files
· Stored scripts
· RMAN configuration settings

Creating Recovery Catalog

1. Connect to Recovery catalog DB as admin and create the Schema.

SQL> CREATE USER RCAT IDENTIFIED BY password
DEFAULT TABLESPACE RCATTBS
TEMPORARY TABLESPACE temp;

2. The RECVEROY_CATALOG_OWNER role is required to maintain and query, so grant the role to the Schema

SQL> GRANT RECOVERY_CATALOG_OWNER TO RCAT;

3. Connect to the database as the recovery catalog owner and run CREATE CATALOG command to create the catalog.

RMAN> CREATE CATALOG

You can also specify tableaspace name for the catalog.
RMAN> CREATE CATALOG TABLESPACE RCATTBS;

4. Connect to recovery Catalog and Register Target Database using below nelow command

$ rman TARGET / CATALOG RCAT@RCATDB;
RMAN> REGISTER DATABASE;

Examples for Data file copy, Archive log backup and backup pieces
RMAN>CATALOG DATAFILECOPY '/bkp/dbfile/testuser01.dbf';
RMAN>CATALOG ARCHIVELOG '/arc/arc_DB_1_559.dbf', '/arc/arc_DB_1_560.dbf',
RMAN>CATALOG BACKUPPIECE '/disk1/backups/backup_820.bkp';

Catalog multiple backup files in a directory by using one single command
RMAN> CATALOG START WITH '/bkp/dbfile/';

Refer below link for Virtual Private Catalogs
https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/managing-recovery-catalog.html#GUID-DA362C55-5B94-4AB8-A8A1-163BF08FE594

Thanks & Regards,

http://oracleracpert.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

Renaming or moving Oracle Control files and redo log files

When renaming Oracle control files and redo log files user needs to be very careful and must take latest database backup to restore in case of any user mistakes.

Control File: The current location of the control files can be queried from the V$CONTROLFILE view

SQL> select NAME from v$controlfile;
NAME
--------------------------------------------------
/oracle/TDB/controlfile/TDB01.ctl
/oracle/TDB/controlfile/TDB02.ctl
/oracle/TDB/controlfile/TDB03.ctl

To rename the control file location we must alter control_files parameter in the spfile/pfile. Follow below steps

•  Alter the control_files parameter

ALTER SYSTEM SET control_files='/dbf1/TDB/controlfile/TDB01.ctl', '/dbf2/TDB/controlfile/TDB02.ctl','/dbf3/TDB/controlfile/TDB03.ctl' SCOPE=SPFILE;

• Shutdown the database and copy or move the files to new location

SQL> SHUTDOWN IMMEDIATE
SQL> ! mv /oracle/TDB/controlfile/TDB01.ctl /dbf1/TDB/controlfile/TDB01.ctl
SQL> ! mv /oracle/TDB/controlfile/TDB02.ctl /dbf2/TDB/controlfile/TDB02.ctl
SQL> ! mv /oracle/TDB/controlfile/TDB03.ctl /dbf3/TDB/controlfile/TDB03.ctl

• Startup the database and verify new location

SQL> Startup
SQL> select NAME from v$controlfile;
NAME
--------------------------------------------------
/dbf1/TDB/controlfile/TDB01.ctl
/dbf2/TDB/controlfile/TDB02.ctl'
/dbf3/TDB/controlfile/TDB03.ctl'

Redo log: The current redo log files location can be queried from the V$logfile view

SQL> SELECT member FROM v$logfile;

MEMBER
---------------------------------------------------------------------
/oracle/TDB/TDB11.rdo
/oracle/TDB/TDB12.rdo
/oracle/TDB/TDB21.rdo
/oracle/TDB/TDB22.rdo

Follow the below steps to move or rename a Redo log file

• Shutdown the Db and rename the file at operating system

SQL> Shutdown Immediate
SQL> ! /oracle/TDB/TDB11.rdo /dbf1/TDB/redo/TDB11.rdo
SQL> ! /oracle/TDB/TDB12.rdo /dbf1/TDB/redo/TDB12.rdo
SQL> ! /oracle/TDB/TDB21.rdo /dbf1/TDB/redo/TDB21.rdo
SQL> ! /oracle/TDB/TDB22.rdo /dbf1/TDB/redo/TDB22.rdo

• Start the database in mount mode and ALTER DATABASE RENAME FILE

SQL> Startup mount
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB11.rdo’ to '/dbf1/TDB/redo/TDB11.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB12.rdo’ to ‘/dbf1/TDB/redo/TDB12.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB21.rdo’ to ‘/dbf1/TDB/redo/TDB21.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB22.rdo’ to ‘/dbf1/TDB/redo/TDB22.rdo’;

• Open the database and verify

SQL> Alter database open;
SQL> SELECT member FROM v$logfile;

MEMBER
---------------------------------------------------------------------
/oracle/TDB/TDB11.rdo
/oracle/TDB/TDB12.rdo
/oracle/TDB/TDB21.rdo
/oracle/TDB/TDB22.rdo

We can also DROP and RECREATE the redo in different location. But make sure the group STATUS should be “INACTIVE” in order to drop.

• You can check the redo status from the V$log view

SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE

• Drop and recreate the redo log group with new location

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 (‘/dbf1/TDB/redo/TDB21.rdo’, ‘/dbf1/TDB/redo/TDB22.rdo’) SIZE 100M;

• Switch the log file to change the current redo and recreate the other redo groups

SQL> Alter system switch log file;
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 (‘/dbf1/TDB/redo/TDB11.rdo’, ‘/dbf1/TDB/redo/TDB12.rdo’) SIZE 100M;

Thanks & Regards.

http://oracleracexpert.com, Oracle ACE

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

The ORA-20011, can be found in alert.log and this error occurs when DBMS_STATS:GATHER_STATS_JOB gathering stats

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

You identify the issue set the following trace events
SQL> alter session set events '10384 trace name context forever , level 16384';

After research found that object truncate and starts are gathering at same time. In case of partition set granularity to ‘PARTITION’

DB level
SQL> exec dbms_stats.set_global_prefs(pname=>'GRANULARITY',pvalue=>'PARTITION')

Table level
SQL> exec dbms_stats.set_table_prefs(ownname=>'Owner_Name',tabname=>'<Table_Name>',pname=>'GRANULARITY',pvalue=>'PARTITION')

Run the gather stats after DDL or DML have been completed.

Also note that “ORA-08103 object no longer exists” error occurs when another user has deleted object since the operation began. In case of incomplete recovery of the object, the work around would be to remove references to the object or delete the object.

When using Global temporary table with ON COMMIT DELETE ROWS options, may encounter ORA-08103: object no longer exists when commit statement that followed right after the delete statement.

In this case recreation of global temporary table with ON COMMIT PRESERVE ROWS clause will helps to safely fetch data

There are several bugs related to ORA-8103 error, check oracle support for more details.

Bug 13618170

Bug 5523799
Bug 5637976

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE

ORA-39346: data loss in character set conversion for object TABLE_STATISTICS

When doing export/import using data pump encountered below error. This happens when invalid or corrupt characters are stored in the database.

When using export/import instead of data pump, no errors reported.

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"ORCL"."TABLE1"
ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"ORCL"."TABLEMAT"
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Sun Apr 11 12:05:25 2021 elapsed 0 00:20:13

After research found that this is a known issue due to Bug 13696958 .

The fix for Bug 13696958 

added warning messages in 12.1 datapump import for characters that couldn't be converted. Previously, a fix for Bug 16173664 was made available to report the affected object in the import log.

Workaround/fix:-

Needs to apply interim patch 21342624 
and execute post-install step:

$ datapatch

If the patch 21342624 installed then impdp log will report the objects that are experiencing data loss during characterset conversion. If the fix then character data is checked even when the client and database character sets are the same, to prevent input of invalid character data.

That being said the fix for Bug 21342624 does not stop the error messages or repair any bad data on the source database

In case of any further issues , patch questions or concerns, please contact Oracle Support.

Refer Oracle support notes: -

Unpublished Bug 21342624 - DATA LOSS DUE TO CHARACTER SET NOT SHOWING CORRECT SET OF OBJECTS DURING IMPORT.

Note 1317012.1 - How To Use MOS Patch Planner To Check And Request The Conflict Patches?

Thanks & 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