Blog

Off-Box network virtualization in Oracle Cloud Infrastructure

Oracle Cloud Infrastructure has implemented one of its kind: Off-box network virtualization. As the name suggests, it pulls out the network and IO virtualization out of the software stack and puts it in the network. Basically you can have physical, dedicated hosts with no hypervisor overhead, noisy neighbours or shared resources with a full software-defined layer 3 network topology. You can enjoy the benefit of running bare metal hosts side-by-side with any class of systems like Virtual Machines(VMs) to Engineered Systems such as Exadata sharing the same set of APIs – meaning you can enjoy best of both worlds where benefitting from Exadata hardware(Infiniti Band) and software(smart scan, flash cache, columnar compression) features for your application along with cloud native security and governance capabilities of a layer 3 virtual cloud network.

The major benefits of Off-box network virtualisation is increase of network performance and higher level of security by providing isolation hence in case of any unforeseen attacks it affects single virtual network without touching other virtual networks.

To determine the time for the until time clause for RMAN duplicate/recovery

Connect to production server and source the DB home.

 

1. Export the NLS date format in PROD

 

export NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’

 

2. Connect to production database(PROD)

 

rman target /

list backup of controlfile;

 

Pick the time of the latest controlfile time.

 

Sample Output:

 

RMAN> list backup of controlfile;

 

From the above output it helps to determine the control file which need to be copied over to DEV/TEST server.

 

3. To determine the point in time recover run the archive log backup command.

 

Please use the below command on PROD to retrieve that and the latest time stamp from Next Time column of the last archive log present in the backup being used.

 

RMAN> list backup of archivelog all;

 

 

 

Step 4: connect to RMAN (Target Environment)

 

Sample clone:

 

rman auxiliary / log=/u01/backups/RMAN/rman_instance_rec.log

 

DUPLICATE DATABASE TO DEV

UNTIL TIME “TO_DATE(’31-JAN-2014 00:00:28′,’DD-MON-YYYY HH24:MI:SS’)”

BACKUP LOCATION ‘/u01/backups/RMAN/PROD’

nofilenamecheck

;

 

Note: Following command automatically restore, recover, open database and also recreate the TEMP tablespace.

 

 

Error Message:

 

channel ORA_AUX_DISK_4: SID=626 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 09/06/2012 17:50:45

 

RMAN-05501: aborting duplication of target database

RMAN-06617: UNTIL TIME (06-SEP-12) is ahead of last NEXT TIME in archived logs (06-SEP-12)

 

RMAN> exit

 

If we run into issue in providing the point in time recovery, try to readjust the value to next lower sequence number from the archive log list and match it to that respective timing.

 

Error Fix: How to find the exact time to mention.

 

Description:

The below command will simulate the restore process from available backup. It won’t touch the data file

 

RMAN> Restore database PREVIEW;

 

PREVIEW tells about

1. At which SCN media recovery will start

2. AT which SCN recovery required to clear the datafile fuzziness.

3. What all archivelogs are required to clear this fuzziness.

4. Also it reports the missing archivelogs under backup.

 

Based on scn you can find until which archive log is required and based on SCN you can find the time of recover

Tracing to Capture any Database Timeouts on Client(s) and Server Side

Consider below tracing on Client and Server Side to capture any database errors, especially if you donot see any errors on DB alert logs and application team is complaining of database related timeouts in their application logs

 

1. Remove all entrees for TRACE_ from the client SQLNET.ORA

2. Place the below in the Client SQLNET.ORA making sure you change the file path for TRACE_DIRECTORY_CLIENT= to a location your user can write too.

TRACE_UNIQUE_CLIENT = ON
TRACE_LEVEL_CLIENT=16
TRACE_TIMESTAMP_CLIENT=TRUE
TRACE_DIRECTORY_CLIENT=/home/oracle/client_trace
TRACE_FILE_CLIENT=CL.trc
DIAG_ADR_ENABLED=OFF #<===Use this only for 11g and newer clients

3. Make sure to turn on SERVER side TRACE here on the Database side, Making sure you change the file path for TRACE_DIRECTORY_SERVER= to a location your user can write too.
Place the below in the Server side sqlnet.ora

TRACE_UNIQUE_SERVER = ON
TRACE_LEVEL_SERVER=16
TRACE_TIMESTAMP_SERVER=TRUE
TRACE_DIRECTORY_SERVER=/Path here/
TRACE_FILE_SERVER=SR.trc
DIAG_ADR_ENABLED=OFF #<===Use this only for 11g and newer clients

4. Review the DOC how to ID matching client to server trace and extract the trace files outlined (Only for concerned clients having the issue).

How to Match Oracle Net Client and Server Trace Files ( Doc ID 374116.1)

Network Tuning for Dom0 and ODAbase for high speed transfer across WAN links

From OS perspective following is the procedure.

1) Find the Bandwidth delay product (BDP) value and set the network buffer size accordingly. It is calculated as the product of the link bandwidth, and the Round Trip Time.

For example :

For a 1Gb/s network and Rount Trip Time of 0.1s, the BDP=(0.1 * 10^9)/8. On such a network, set the following parameter values under the file : /etc/sysctl.conf
net.core.rmem_max = 12500000
net.core.wmem_max = 12500000
net.ipv4.tcp_rmem = 4096 87380 12500000
net.ipv4.tcp_wmem = 4096 65536 12500000

And increase the following parameters as well :
net.core.netdev_max_backlog = 30000
net.ipv4.tcp_max_syn_backlog = 4096

And then execute the command:
# sysctl -p

 

a) For both the changes there is no need for a system reboot.

b) Post this change, it is required to monitor the output of the command : “# netstat -s” and check if the following counters are still seen to be increasing :
packets pruned from receive queue because of socket buffer overrun
times the listen queue of a socket overflowed
SYNs to LISTEN sockets ignored
packets collapsed in receive queue due to low socket buffer
TCPBacklogDrop

 

Configure on Guest VM and dom0.

Using Snapshot Standby for DR drill(s)

Snapshot Standby Database functionality (that comes with EE licenses) can be used for yearly DR drills on 11g/12c databases. Here are the technical steps used for the same. 

Convert the Physical Standby Database to Snapshot database.

Please differ the dest_2 parameter in standby server.

SQL> alter system set log_archive_dest_state_2=DEFER scope=both sid=’*’;

$sqlplus / as sysdba

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  (make sure MRP is stopped)

SQL> select flashback_on from v$database;

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

Activate the Physical Standby Database

$ sqlplus / as sysdba

SQL> select database_role,name,controlfile_type from v$database;

DATABASE_ROLE    NAME      CONTROL
—————- ——— ——-
PHYSICAL STANDBY NASCXTHZ   STANDBY

SQL> alter database convert to snapshot standby; (converting the physical standby database to snapshot database)

SQL> alter database open;
Database altered.

SQL> select database_role,name,controlfile_type,flashback_on from v$database;

DATABASE_ROLE    NAME      CONTROL FLASHBACK_ON
—————- ——— ——- ——————
SNAPSHOT STANDBY NASCXTHZ   CURRENT RESTORE POINT ONLY

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

SQL> set pages 200 lines 200
SQL> select * from v$restore_point;

Below two commands will show how much flashback data has been generated.

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
——————– ——— —————- ————– ————————
6.7014E+12 23-APR-15             1440       63750144                        0

SQL> select * from v$flashback_database_stat;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
——— ——— ————– ———- ———- ————————
23-APR-17 23-APR-17          49152     622592          0                        0

SQL> show parameter db_rec

SQL> select database_role,name,controlfile_type,flashback_on from v$database;

DATABASE_ROLE    NAME      CONTROL FLASHBACK_ON
—————- ——— ——- ——————
SNAPSHOT STANDBY NASCXTHZ   CURRENT RESTORE POINT ONLY

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

Convert the Snapshot Database to Physical Standby database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  830930944 bytes
Fixed Size                  2217912 bytes
Variable Size             603981896 bytes
Database Buffers          222298112 bytes
Redo Buffers                2433024 bytes
Database mounted.
SQL>

Next convert the snapshot to a physical standby.

SQL> alter database convert to physical standby;
Database altered.

SQL> select * from v$restore_point;

no rows selected

SQL> select * from v$flashback_database_log;

no rows selected

SQL> select * from v$flashback_database_stat;

no rows selected

SQL> alter database recover managed standby database disconnect from session;

Post DG Sync Validation

Run the following query on Primary DB

SQL> select thread#, max(sequence#) “Last Primary Seq Generated”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

Run the following query on Standby DB

SQL> select al.thrd “Thread”, almax “Last Seq Received”, lhmax “Last Seq Applied”
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) lh
where al.thrd = lh.thrd;

Ensure “Last Primary Seq Generated” on Primary matches “Last Seq Applied” on each Standby