Blog

How to Exclude Tablespaces from RMAN Backup

You can use “CONFIGURE EXCLUDE FOR TABLESPACE” to exclude tablespaces from RMAN  backup command “BACKUP DATABASE”.

To exclude tablespace TEST_TBS from RMAN backup:

$ rman target / catalog rman/passwd@rman

RMAN>CONFIGURE EXCLUDE FOR TABLESPACE TEST_TBS;

Tablespace TEST_TBS will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete


RMAN> show exclude ;
RMAN configuration parameters for database with db_unique_name RACTEST are:
CONFIGURE EXCLUDE FOR TABLESPACE 'TEST_TBS';

RMAN>

To exclude PDB tablespace TEST_TBS  from RMAN backup:

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE RACTESTPDB:TEST_TBS;

Tablespace RACTESTPDB:TEST_TBS will be excluded from future whole 
database backups. 
new RMAN configuration parameters are successfully stored. 
starting full resync of recovery catalog
full resync complete

RMAN>show exclude ;
RMAN configuration parameters for database with db_unique_name RACTEST are:
CONFIGURE EXCLUDE FOR TABLESPACE 'TEST_TBS';
CONFIGURE EXCLUDE FOR TABLESPACE 'RACTESTPDB:TEST_TBS';

The following rman backup will excluded the above mentioned tablespaces:

RMAN> BACKUP DATABASE;

The excluded tablespace can be backed up by explicitly specifying them in a BACKUP command or by specifying the NOEXCLUDE option:

RMAN> BACKUP DATABASE NOEXCLUDE;

RMAN> BACKUP TABLESPACE TEST_TBS;

To disable the exclusion for RMAN backups:

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE TEST_TBS CLEAR;

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE RACTESTPDB:TEST_TBS CLEAR;

RMAN> show exclude;

 

ORA-00603 ORA-27504 ORA-27300 ORA-27301 ORA-27302 ORA-603 in ASM alert log file

There are following error messages in ASM alert log file:

Errors in file /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_m000_39281.trc (incident=198012):
ORA-00603: ORACLE server session terminated by fatal error
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:sendmsg failed with status: 105
ORA-27301: OS failure message: No buffer space available
ORA-27302: failure occurred at: sskgxpsnd2
opidrv aborting process M000 ospid (39281) as a result of ORA-603
2019-03-12T01:00:18.919871+11:00
Process m000 died, see its trace file

+ASM1_m000_39281.trc:

...
..
.
SKGXP:[7f620a621930.0]{0}: SKGXPVFYNET: Socket self-test could not verify successful transmission of 32768 bytes (mtype 61).
SKGXP:[7f620a621930.1]{0}: The network is required to support UDP protocol sends of this size. Socket is bound to 169.254.174.228.
SKGXP:[7f620a621930.2]{0}: phase 'send', 0 tries, 100 loops, 13590 ms (last)
struct ksxpp * ksxppg_ [0x7f620a68a770, 0x7f6204ff1350) = 0x7f6204ff1348
...
..
.

Workaround

1) Shrink database instance SGA size to give more memory back to OS.
After OS gets more available memory, the issue is gone.

Physical memory utilisation( %):

Capture

OR

2) Oracle has reported this issue in Doc ID 2041723.1. by changing the MTU of the loopback interface,  and changing the value of kernel parameter min_free_kbytes.

a)  Lower MTU to 16436 by adding following to /etc/sysconfig/network-scripts/ifcfg-lo.

MTU=16436

Then restart the network service.

# systemctl restart network.service

b) Increase the value of vm.min_free_kbytes to 0.4%of the total physical memory of the server. This can be done by adding following to /etc/sysctl.conf.

  vm.min_free_kbytes = 2097152

and then run below command to make it effective:

#sysctl -p

PRCD-1334 PRCR-1065 CRS-2974 when stopping mgmtdb

Try to stop mgmtdb, and relocate mgmtdb to another node with below errors:

$ srvctl status mgmtdb
Database is enabled
Instance -MGMTDB is running on node racnode1

$ srvctl stop mgmtdb
PRCD-1334 : failed to stop database _mgmtdb
PRCR-1065 : Failed to stop resource ora.mgmtdb
CRS-2974: unable to act on resource 'ora.mgmtdb' on server 'racnode1' 
because that would require stopping or relocating resource 'ora.chad' 
but the -force option was not specified

Just relocate mgmtdb, it works.

$ srvctl relocate mgmtdb -node racnode2


$ srvctl status mgmtdb
Database is enabled
Instance -MGMTDB is running on node racnode2

How to Check Oracle Hidden Parameters ?

As we know, “show parameter” doesn’t show hidden parameters values in Oracle database version before 18c. Instead, the below sql script will list hidden parameters.

SQL> show parameter report

NAME                                 TYPE        VALUE
---------------------------------- ----------- --------------
optimizer_adaptive_reporting_only    boolean     FALSE

SQL>show parameter "_report_capture_cycle_time"
SQL>
set pagesize 120
set linesize 150
col Parameter format a40
col Description format a40
col "Session Value" format a10
col "Instance Value" format a10

SELECT 
       a.ksppinm  "Parameter", 
       a.ksppdesc "Description", 
       b.ksppstvl "Session Value",  
       c.ksppstvl "Instance Value" 
FROM 
       x$ksppi a, 
      x$ksppcv b, 
      x$ksppsv c 
WHERE  
      a.indx = b.indx 
AND   a.indx = c.indx 
AND   a.ksppinm LIKE  '/_report_capture%'  escape '/'  
ORDER BY 1;

Parameter                                Description                              Session Va Instance V
---------------------------------------- --------------------------------
_report_capture_cycle_time               Time (in sec) between two cycles of repo 60         60
                                         rt capture daemon
_report_capture_dbtime_percent_cutoff    100X Percent of system db time daemon is 50         50
...
..
.

From 18c, it is easier to show a hidden parameter as showing a normal parameter:

SQL> show parameter allow_insert_with

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
_allow_insert_with_update_check      boolean     TRUE
SQL>

ERROR: function dblink(unknown, unknown) does not exist

From one of PostgreSQL AWS RDS instance, try to access tables of remote RDS instance with below command:

testuser=>select * from dblink('dbname=testrds port=5555
        host=testrds.rds.amazonaws.com 
        user=testuser password=passwd', 'SELECT cust_id, cust_name 
     from iamesh.customer' ) as cust ( id float, name varchar(50));

ERROR: function dblink(unknown, unknown) does not exist

LINE 1: select * from dblink('dbname=testrds port=5555 host=testrds.

HINT: No function matches the given name and argument types. 
     You might need to add explicit type casts.
SQL state: 42883
Character: 15

Show current installed extensions:

testuser=> select * from pg_extension;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10       | 11           | f              | 1.0        |           |
(1 rows)

Show available extensions, dblink extension is available to be installed:

testuser>SELECT * FROM pg_available_extensions order by 1;

        name             | default_version | installed_version |                                                       comment
-------------------------+-----------------+-------------------+----
...
..
dblink                   |1.2              |
plpgsql                  |1.0              |1.0
...
..
.

We can see dblink extension is NOT installed yet. To create “dblink” extension:

testuser>create extension dblink; 

ERROR: permission denied to create extension "dblink"
HINT: Must be superuser to create this extension.

Logon as super user, and retry again.

rdsdba=> create extension dblink;
CREATE EXTENSION

Try to access remote tables of remote instance again, and it is successful.

testuser=>select * from dblink('dbname=testrds port=5555
host=testrds.rds.amazonaws.com 
user=testuser password=passwd', 'SELECT cust_id, cust_name 
from iamesh.customer' ) as cust ( id float, name varchar(50));

     id     |                name
------------+-------------------------------------
 2001873700 | James Bond
 2001873800 | David Lee
...
..
.