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

DBMS_SCHEDULER CREDENTIAL ORA-27486 privilegios insuficientes

If you create an external job and is using a credential that already exists in another schema you will face this error below if not provide the necessary privilege for the using running the job :

SQL> conn / as sysdba
SQL> exec dbms_scheduler.run_job('MY_JOB');
BEGIN dbms_scheduler.run_job('MY_JOB'); END;

*
ERROR at line 1:
ORA-27486: privilegios insuficientes
ORA-06512: em "SYS.DBMS_ISCHED", line 196
ORA-06512: em "SYS.DBMS_SCHEDULER", line 486
ORA-06512: em line 1


Fix:

You should connect with the credential owner and grant the execute on that credential to the other user:

--Connect with the credential owner:
SQL> conn edi/edi
SQL> grant execute on edi.edi_credential to sys;
--Connect back to SYS
SQL> conn / as sysdba
Connected.
SQL> exec dbms_scheduler.run_job('MY_JOB');

PL/SQL procedure successfully completed.

SQL>



Best Regards,
Paulo Portugal

The new DBMCLI utility on Exadata 12.1

For you that is used to use CELLCLI in storage nodes on Exadata, now from 12.1 release that is a new DBMCLI for database nodes also.

Like the CELLCLI the DBMCLI can be used to manage the database server. Some commands are below:

DBMCLI> LIST METRICDEFINITION DS_CPUT DETAIL

DBMCLI> LIST METRICDEFINITION WHERE name LIKE 'DS_SWAP_*' -
         ATTRIBUTES name, metricType, description


DBMCLI> LIST METRICCURRENT DS_FANS DETAIL


DBMCLI> LIST METRICCURRENT WHERE objectType = 'DBSERVER' AND                 -
         metricValue != 0 ATTRIBUTES name, metricObjectName,                  -
         metricValue, collectionTime

DBMCLI> LIST METRICHISTORY DS_TEMP WHERE alertState='critical' DETAIL

DBMCLI> LIST METRICHISTORY WHERE objectType = 'DBSERVER' AND metricValue != 0   -
         AND collectionTime > '2014-08-12T09:10:51-07:00' -ATTRIBUTES name,      -
         metricObjectName, metricValue, collectionTime


DBMCLI> ALTER ALERTHISTORY 1671443714 -
                            examinedBy="jdoe"

DBMCLI> ALTER ALERTHISTORY ALL examinedBy="jdoe"

DBMCLI> ALTER DBSERVER iaasMode="on"
DBMCLI> ALTER DBSERVER iaasIdleInUse=true, iaasReason=reason

DBMCLI> ALTER DBSERVER smtpServer='my_mail.example.com',            -
                    smtpFromAddr='john.doe@example.com',         -
                    smtpFrom='John Doe',                         -
                    smtpToAddr='jane.smith@example.com',         -
                    snmpSubscriber=((host=host1),(host=host2)),  -
                    notificationPolicy='clear',                  -
                    notificationMethod='mail,snmp'


All the documentation is here:

http://docs.oracle.com/cd/E50790_01/doc/doc.121/e51951

Best Regars,
Paulo Portugal

Find files in ASM using ASMCMD find command

This single example will find all PARAMETER FILEs that you have in your ASM instance:

ASMCMD> find --type PARAMETERFILE . *

The types that can be used are these below:

SQL> select distinct TYPE from v$asm_file;

TYPE
----------------------------------------------------------------
ARCHIVELOG
ASMPARAMETERFILE
CHANGETRACKING
CONTROLFILE
DATAFILE
DATAGUARDCONFIG
OCRFILE
ONLINELOG
PARAMETERFILE
TEMPFILE

10 rows selected.



Best Regards,
Paulo Portugal

TOTAL_MB of V$ASM_DISK shows wrong value (diff from real OS space)

If you are checking your space in V$ASM_DISK and face a problem where the TOTAL_MB is different from the real size of that disk execute the command below to workaround this problem:

alter diskgroup DG_NFE_DATA resize disk DG_NFE_DATA_0000;


Best Regards,
Paulo Portugal

Check IB Switchs on Exadata (ibqueryerrors.pl)

A quick and simple way to check the healthy of your IB Switches in Exadata is this one below:

1-Check using this command below:

ibqueryerrors.pl -rR -s PortRcvSwitchRelayErrors,PortXmitDiscards,PortXmitWait,VL15Dropped


2-If you got something line that:

  GUID 0x10e040687aa0a0 port ALL: [LinkErrorRecoveryCounter == 152] [PortRcvErrors == 5]
   GUID 0x10e040687aa0a0 port 1: [LinkErrorRecoveryCounter == 12]

and this value is not increasing a lot, you can clear these errors and continue with your life without any problem but if this number are increasing quickly or are too high (30k, 65k or more) it is better to open an SR to check the IB hardware.

3-Clear the count so when you run some tool like RACchk or the new ORAchk you will not be warned about these little errors.

[root@ex01dbadm01 ORAchk]# ibclearcounters

## Summary: 25 nodes cleared 0 errors
[root@ex01dbadm01 ORAchk]#



Best Regards,
Paulo Portugal

Getting UNDO_RETENTION best possible value quickly

With this simple query below we can check what is the recommended value of undo_retention parameter:


SYS@EXADB08 AS SYSDBA> SELECT dbms_undo_adv.best_possible_retention(SYSDATE-1/24, SYSDATE)
FROM dual;  2 

DBMS_UNDO_ADV.BEST_POSSIBLE_RETENTION(SYSDATE-1/24,SYSDATE)
-----------------------------------------------------------
                            1575436

Elapsed: 00:00:00.03
SYS@EXADB08 AS SYSDBA>



Best Regards,
Paulo Portugal