Some Best Practices in Configuring your RMAN Backup with Exadata

1-Do not use internal parameters _backup_ksfq_bufsz and _backup_ksfq_bufcnt if your database is newer than 11.2.0.2. These parameters are set automatically after this release

2-Try to use between 2 and 8 channels per instance while backing up your database

3-For incremental backups, use FILESPERSET 1. It will be better when restoring single files

4-Create a service to use in your backups. For example, BAKUP service_name.

Best Regards,
Paulo Portugal

12.1.2.3.0 Exadata - Quorum Disks - Increase the availability of your Voting and CRS Files

If you have a QH of EH and want to increase the availability of your voting and css files, you can do so by multiplexing them in DB Nodes using a new feature called QUORUM Disks.

All the steps necessary are described here:

https://docs.oracle.com/cd/E50790_01/doc/doc.121/e51951/db_server.htm#DBMMN22700


They are done using the quorumdiskmgr found at /opt/oracle.SupportTools

1. # /opt/oracle.SupportTools/quorumdiskmgr --create --config --owner=grid --group=oinstall --network-iface-list="ib0, ib1"

Best Regard,
Paulo Portugal

Thew db_performance_profile parameter - IORM Exadata 12.1.2.1.0 and above

The new PROFILE type of IORM on versions above 12.1.2.1.0 make things simple:

CellCLI> ALTER IORMPLAN DBPLAN=(
(name=dimond, share=10, limit=100, type=profile),
(name=gold, share=5, limit=60, type=profile),
(name=silver, share=1, limit=20, type=profile))


Go to your database and run this change:

SQL> alter system set db_performance_profile=gold scope=spfile;


With that we do not need to specify/change plan directives every time we create new databases.

Best Regards,
Paulo Portugal

Increasing your Capacity-on-Demand on Database Servers - Exadata

If you want to increase the number of licensed cores in your DB Servers you can use this command below:

resourceControl -capacity_on_demand {display | disable | enable}
[-cores number_of_cores] [-force]


Remember that you cannot decrease once you increase this number.

Best Regards,
Paulo Portugal

Exadata I/O Instance Capping

A great feature to use in newer versions of Exadata is I/O Latency Capping. Together with this configuration example "CellCLI> ALTER CELL iotimeoutthreshold = '5s'"  you can told your Exa to point to a block mirror that is in a faster disk. Do not set it before checking your I/O information in AWR and other tools/commands. Best Regards,


Paulo Portugal

Installing the Backup Module of ZDLRA is simple

1-Download the file at http://www.oracle.com/technetwork/database/availability/oracle-zdlra-backup-module-2279224.html


2-Unzip and install using this command example that you can find in README.txt file

% java -jar ra_install.jar  -dbUser joe -dbPass Oracle
   -serviceName DBS.REGRESS.RDBMS.DEV.US.ORACLE.COM
   -host slc08uth.us.oracle.com
  -walletDir $ORACLE_HOME/dbs/ra_wallet  -libDir $ORACLE_HOME/lib/
  -proxyHost www-proxy.smallcompany.com
Recovery Appliance Install Tool, build 2014-08-07
Recovery Appliance credentials are valid.
Recovery Appliance wallet created in directory /orclhome/dbs/ra_wallet.
Recovery Appliance initialization file /orclhome/dbs/rat1.ora created.
Downloading Recovery Appliance Software Library from file ra_linux64.zip.
Download complete.
Extracted file /orclhome/lib/libra.so

That's it.

You finished this step.

Best Regards,
Paulo Portugal

The new restore control file restore from service

Now we can restore a control file from service using a simple command:

RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE SOURCEMASTER;

Best Regards,

Paulo Portugal

Creating SPARSE Diskgroup - Exadata Snapshot Clones

create diskgroup DG_SPARSE
  normal redundancy 
  disk 'o/*/SPARSE_*'
  attribute 
    'compatible.asm'          = '12.1.0.2',
    'compatible.rdbms'        = '12.1.0.2',
    'cell.smart_scan_capable' = 'true',
    'cell.sparse_dg'          = 'allsparse',
    'au_size'                 = '4M';

Using Rapid Home Provisioning Tool on DB12c

This tool can be used to create rapid clone providing clients, add and delete roles for users, add delete and import images and others.

Examples:

--Check actual images:

rhpctl query image

--Get more details of certain image:

rhpctl query image -image MYDB12

--Get workingcopy details
rhpctl query workingcopy -image MYDB12


Best Regards,
Paulo Portugal

The right I/O tool to test disk performance - VDBENCH

Thanks to Henk Vandenbergh for developing this great tool.

Simple to use and a lot of resources and options. Works in almost all OS:

This example below was used in my mac laptop.


pportugal@dhcp-adc-twvpn-3-vpnpool-10-154-105-192 [] /Users/pportugal/Downloads/vdbench50403> ./vdbench -t


Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Vdbench distribution: vdbench50403 Mon May 04  9:22:37 MDT 2015
For documentation, see 'vdbench.pdf'.

13:13:52.968 Created output directory '/Users/pportugal/Downloads/vdbench50403/output'
13:13:53.137 input argument scanned: '-f/var/folders/r5/v8vgh1v10z52rfn4b4nlsd8h0000gp/T/parmfile'
13:13:53.305 Starting slave: /Users/pportugal/Downloads/vdbench50403/vdbench SlaveJvm -m localhost -n localhost-10-151021-13.13.52.936 -l localhost-0 -p 5570
13:13:53.698 All slaves are now connected
13:13:53.805 Vdbench will attempt to expand a disk file if the requested file size is a multiple of 1mb
13:13:53.805 lun=/var/folders/r5/v8vgh1v10z52rfn4b4nlsd8h0000gp/T/quick_vdbench_test does not exist or is too small. host=localhost
13:13:55.002 Starting RD=SD_format; I/O rate: Uncontrolled MAX; elapsed=(none); For loops: threads=2 iorate=max
13:13:55.164 All sequential workloads on all slaves are done.
13:13:55.164 This triggers end of run inspite of possibly some non-sequential workloads that are still running.

Oct 21, 2015  interval        i/o   MB/sec   bytes   read     resp     read    write     resp     resp queue  cpu%  cpu%
                             rate  1024**2     i/o    pct     time     resp     resp      max   stddev depth sys+u   sys
13:13:56.088         1     320.00    40.00  131072   0.00    0.606    0.000    0.606   21.419    1.845   0.2   NaN   NaN
13:13:56.099   avg_1-1       0.00     0.00       0   0.00    0.000    0.000    0.000    0.000    0.000   0.0   NaN   NaN
13:13:57.001 Starting RD=rd1; I/O rate: 100; elapsed=5; For loops: None

Oct 21, 2015  interval        i/o   MB/sec   bytes   read     resp     read    write     resp     resp queue  cpu%  cpu%
                             rate  1024**2     i/o    pct     time     resp     resp      max   stddev depth sys+u   sys
13:13:58.011         1      74.00     0.07    1024  51.35    0.028    0.025    0.031    0.126    0.018   0.0   NaN   NaN
13:13:59.007         2     105.00     0.10    1024  53.33    0.029    0.025    0.033    0.073    0.013   0.0   NaN   NaN
13:14:00.007         3     105.00     0.10    1024  49.52    0.026    0.022    0.029    0.052    0.012   0.0   NaN   NaN
13:14:01.006         4     107.00     0.10    1024  51.40    0.026    0.023    0.029    0.067    0.013   0.0   NaN   NaN
13:14:02.022         5      96.00     0.09    1024  54.17    0.027    0.025    0.029    0.081    0.012   0.0   NaN   NaN
13:14:02.046   avg_2-5     103.25     0.10    1024  52.06    0.027    0.024    0.030    0.081    0.012   0.0   NaN   NaN
13:14:03.002 Vdbench execution completed successfully. Output directory: /Users/pportugal/Downloads/vdbench50403/output

pportugal@dhcp-adc-twvpn-3-vpnpool-10-154-105-192 [] /Users/pportugal/Downloads/vdbench50403>




Best Regards,
Paulo Portugal

Enabling New Flash Cache Compression on Exadata Cells

The flash cards F40 and F80 support flash cache compression and uncompressed tables for example can reach a 4 times compression ratio.

To enable compression follow these steps:

1-Flush all the flash cache in cell

 CellCLI> alter flashcache all flush

2-Drop the flashcache

CellCLI> drop flashcache all

3-Drop all flash log

  CellCLI> dorp flashlog all

4-Drop all cell flash cache disks

  CellCLI> drop celldisk all flashdisk

5-On Cell that you want to enable flash cache compression run:

CellCLI> alter cell flashCacheCompress = TRUE

6-Create cell disk with flash disks

  CellCLI> create celldisk all flashdisk

7-Create flash log

  CellCLI> create flashlog all

8-Finally create flashcache

  CellCLI> create flashcache all

Best Regards,
Paulo Portugal

ps: for X3 run
CellCLI> alter cell flashCacheCompX3Support = TRUE

Create Sparse Grid Disks on Exadata Virtualized

Exadata Virtualized is there and one very nice feature is the Sparse Grid Disks.

With SGD you can create a disk that allocate space dynamically. So you can have a virtual size can be much bigger than the actual physical size. 

Example of how to create a Sparse Grid Disk and Sparse Disk Group:

create griddisk S_GD1 ... virtualsize=100g;

create diskgroup ... S_DG1 'spare grid disks here'  ;


Best Regards,
Paulo Portugal

Restore only a PDB is simple - Test done in Oracle Cloud

If you dropped a table and want to restore to a restore point for example just run these commands below:

    set decryption identified by oracle;
    alter pluggable database MYPDB close;
    restore pluggable database MYPDB to restore point myrestorepoint;
    recover pluggable database MYPDB to restore point myrestorepoint;
    alter pluggable database MYPDB open resetlogs;

I tested it using a cloud database and a cloud database backup in Oracle Cloud. Everything works perfectly.


Best Regards,
Paulo Portugal

OISP and ZFS for Database 12c

One big reason to upgrade your database to 12c version if you have ZFS is that with ZFS you will have the benefit of having Oracle Intelligent Storage Protocol. Oracle ZFS have 5 categories that could take care of different kind of database files and deals differently with each one making than work faster using different parameter values behind the scenes.

For more information check Oracle Documentation regarding ZFS and OISP.

Best Regards,
Paulo Portugal

Errors RMAN-03002 and RMAN-06571 While running SWITCH DATABASE TO COPY on Standby Database

If you are doing a rollforward of a standby database and in the middle of the process got errors like below:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 06/26/2015 23:08:53
RMAN-06571: datafile 301 does not have recoverable copy


Don't worry, you just need to catalog the datafile in question using this command below:

catalog datafilecopy '+DG_EBS_DATA/bwebspr_glt_p7/datafile/apps_ts_tx_idx.584';


You can also use this query below to build the command to you quickly:

select 'catalog datafilecopy '''||name||''';' from v$datafile where file#=301;



After doing that for all problematic datafiles you can finish your rollforward process.


Best Regards,
Paulo Portugal

RMAN-03002 ORA-19504 ORA-17502 ORA-15001 ORA-15040

I was trying to restore a standby control file to create a new standby database and got this error:

RMAN> run {
allocate channel c1 device type disk;
restore standby controlfile from '/home/orab2b/copia_ctl_stby.ctl';
release channel c1;
}
2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=36 instance=BWB2BPR1 device type=DISK

Starting restore at 20-MAY-15

released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/20/2015 22:49:50
ORA-19504: failed to create file "+FRA/bwb2bpr/controlfile/control01.ctl"
ORA-17502: ksfdcre:3 Failed to create file +FRA/bwb2bpr/controlfile/control01.ctl
ORA-15001: diskgroup "FRA" does not exist or is not mounted
ORA-15040: diskgroup is incomplete
ORA-15040: diskgroup is incomplete
ORA-19600: input file is control file  (/home/orab2b/copia_ctl_stby.ctl)
ORA-19601: output file is control file  (+FRA/bwb2bpr/controlfile/control01.ctl)

To fox that, run this command below as GRID_HOME owner:

[bwdp0248-+ASM1]/u01/app/11.2.0.4/grid/bin>  ./setasmgidwrap o=/u01/app/oracle/product/11.2.0.4/dbb2b/bin/oracle  --HERE USE YOUR ORACLE RDBMS HOME

This fix my problem.

Best Regards,

Paulo Portugal

CellCLI> list cell CELL-01519: Cannot talk to the Management Server (MS). Error: ; nested exception is: HTTP transport error: javax.xml.soap.SOAPException: java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Bad response: 404 Not Found CellCLI> exit

Hi guys,

I already got problems when I set my cell virtual machine with memory less than 1GB and also when I commented out the "127.x.x.x"  ip address in /etc/hosts file. But this error below was new for me.

To fix that in your TEST virtual machine of Exadata Cell, just do these following steps:

1-Stop the MS service



2-Redeploy the MS oc4j:


3-Start the MS processes:



Best Regards,
Paulo Portugal

Great White Paper for 12c In-Memory Advisor

In this link below you can find a great white paper for In Memory Advisor:

http://www.oracle.com/technetwork/database/manageability/info/twp-in-memory-advisor-bp-2430474.pdf


Best Regards,
Paulo Portugal

Using DBMS_PASSTHROUGH to run command in SQL Server from Oracle

This simple example below update a table in SQL Server from Oracle:

declare
   l_num_rows number;
begin
  l_num_rows := dbms_hs_passthrough.execute_immediate@TMMASTER ('USE ORAIntegration_V2_test');
  commit;
  execute immediate 'alter session close database link TMMASTER';
  commit;
 
  l_num_rows := dbms_hs_passthrough.execute_immediate@TMMASTER ('UPDATE trans SET    a=333 WHERE  a=222 ');
  commit;
 
--  l_num_rows := dbms_hs_passthrough.execute_immediate@TMMASTER ('COMMIT TRANSACTION');
  commit;


  execute immediate 'alter session close database link TMMASTER';
  commit;
end;



select * from trans@TMMASTER;


Best Regards,
Paulo Portugal

The new Oracle Exadata Deployment Assistant - Fev 2015 - Elastic Rack, OVM, COD

Already including X5, Zero Data Loss and Super Cluster configuration options:

http://www.oracle.com/technetwork/database/exadata/oeda-download-2076737.html

The new OEDA have options to install Exadata, Zero Data Loss and Super Cluster

 

With Elastic option you can customize your Exadata (19 DB Nodes and 3 Cells for example) The maximum number os cell+db nodes is 23.

  The Hardware Options now have the Elastic Rack with Flash Disks (HP disks does not exists anymore) or HC Disks.

  You can choose to have a Virtualized Exadata or all physical db nodes. Also you can choose COD to save money on your software licenses.

 

Here you see one RAC with 19 nodes and 3 cells.  This is only one from many possibilities:

 

At the end, your template shows something like that:

Best Regards,
Paulo Portugal