Sunday, March 30, 2014

12c RAC - Conversion of Standard Cluster to Oracle Flex ASM Cluster


Step 1 : Login as 'oracle' user and execute 'asmca'


Step-2 : Check the disk groups and availability 



Step 3 : Choose option to convert to 'Oracle Flex ASM Cluster'





Step 4 : Execute the following script - conversion process will starts from here




Step 5 : Check the Cluster Mode and Cluster State after logging in to ASMCMD prompt.

[oracle@racnroll1 ~]$ asmcmd

ASMCMD> showversion
ASM version         : 12.1.0.1.0

ASMCMD> showclusterstate
Normal

ASMCMD> showclustermode
ASM cluster : Flex mode enabled

Tuesday, March 25, 2014

Oracle RMAN in Oracle Database 12c


Feature-1: Table Recovery From Backupa. This is the simple method. Simple RECOVER TABLE command to recover one or more tables/partitions (most recent or older version) from an RMAN backup

Feature-2: Cross-Platform Backup & Restore
a. Cross-platform Transportable Database : Same endian, Tablespaces : Same or across endians
b. New 12c Syntax: FOR TRANSPORT / TO PLATFORM

Feature-3: Active Duplicate
a. One-Command Database Cloning method
b. Create clone or standby database as of the current time
c. utilizes source (TARGET) database channels to copy data files and archived logs to clone (AUXILIARY) database server – eliminates backup staging area

Feature-4: Consolidate and Protect Data
RMAN wih Oracle Multitenancy

Friday, March 21, 2014

Published Oracle Technical Article in World Famous Site "Oracle Technology Network (OTN)"


Article-15 :  Oracle RAC 12c:"Flex ASM" e "Flex Cluster"

Article-15 URL : http://www.oracle.com/technetwork/pt/articles/database-performance/oracle-rac-12c-flex-asm-cluster-2172079-ptb.html




Thursday, March 13, 2014

Published Oracle Technical Article in World Famous Site "Oracle Technology Network (OTN)"


Article-12 : Oracle Exadata Database Machine: Seguridad a nivel de ASM y de Base de Datos (Parte 1)
 

Article-13 : Oracle Exadata Database Machine: Seguridad a nivel de ASM y de Base de Datos (Parte 2)
 

Article-14 : Oracle Exadata Database Machine: Seguridad a nivel de ASM y de Base de Datos (Parte 3)

Article-12 URL : http://www.oracle.com/technetwork/es/articles/database-performance/seguridad-asm-base-de-datos-parte1-2166616-esa.html


Article-13 URL : http://www.oracle.com/technetwork/es/articles/database-performance/seguridad-asm-base-datos-parte2-2167445-esa.html


Article-14 URL : http://www.oracle.com/technetwork/es/articles/database-performance/seguridad-asm-base-datos-parte3-2167449-esa.html



Published Oracle Technical Article in World Famous Site "Oracle Technology Network (OTN)"


Article-10 Title : Oracle Exadata Database Machine: Segurança a nível de ASM e de Banco de Dados (Parte 2)

Article-11 Title : Oracle Exadata Database Machine: Segurança a nível de ASM e de Banco de Dados (Parte 3)

Article-10 URL : http://www.oracle.com/technetwork/pt/articles/database-performance/seguranca-asm-banco-dados-parte2-2166649-ptb.html

Article-11 URL : http://www.oracle.com/technetwork/pt/articles/database-performance/seguranca-asm-banco-de-dados-parte3-2166659-ptb.html

Wednesday, March 12, 2014

Reference Partitioning - Avoiding ORA-14652


Login to Pluggable Database:
============================
SQL> connect user2/oracle@192.168.56.111:1521/cdb2_pdb1;
Connected.

SQL> CREATE TABLE MAIN_TABLE (
  2  name               VARCHAR2(30),
  3  order_date         DATE,
  4  no_rows    NUMBER)
  5  PARTITION BY RANGE(no_rows)
  6  (
  7  PARTITION no_rows1 VALUES LESS THAN (100) TABLESPACE users,
  8  PARTITION no_rows2 VALUES LESS THAN (1000) TABLESPACE users,
  9  PARTITION no_rows3 VALUES LESS THAN (10000) TABLESPACE users,
 10  PARTITION no_rows4 VALUES LESS THAN (MAXVALUE) TABLESPACE users)
 11  ;
Table created.

SQL> ALTER TABLE MAIN_TABLE
          ADD CONSTRAINT pk_ref_main PRIMARY KEY (name) USING INDEX;
Table altered.

SQL> CREATE TABLE SUB_TABLE (
name             VARCHAR2(30) ,
index_col       VARCHAR2(30) ,
CONSTRAINT fk_ref_sub_main
FOREIGN KEY(name) REFERENCES main_table(name))
PARTITION BY REFERENCE(fk_ref_sub_main);
  2    3    4    5    6  PARTITION BY REFERENCE(fk_ref_sub_main)
                       *
ERROR at line 6:
ORA-14652: reference partitioning foreign key is not supported

Note: Foreign Key Column (table_name) has to be NOT NULL.

SQL> CREATE TABLE SUB_TABLE (
name             VARCHAR2(30) NOT NULL,
index_col       VARCHAR2(30),
CONSTRAINT fk_ref_sub_main
FOREIGN KEY (name) REFERENCES MAIN_TABLE(name))
PARTITION BY REFERENCE(fk_ref_sub_main);
Table created.

SQL> insert into main_table values ('ORACLE','12-MAR-2014',1);
insert into main_table values ('ORACLE','12-MAR-2014',1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

SQL> connect sys/oracle@cdb2 as sysdba
Connected.

SQL> show parameter _segment_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
rollback_segments                    string

SQL> alter session set container=CDB2_PDB1;
Session altered.

SQL> sho user
USER is "SYS"

SQL> alter user user2 quota unlimited on users;
User altered.

SQL> connect user2/oracle@192.168.56.111:1521/cdb2_pdb1;
Connected.

SQL> insert into main_table values ('ORACLE','12-MAR-2014',1);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from main_table;

NAME                           ORDER_DAT    NO_ROWS
------------------------------ --------- ----------
ORACLE                         12-MAR-14          1

SQL> insert into sub_table values ('ORACLE',1);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from sub_table;

NAME                           INDEX_COL
------------------------------ ------------------------------
ORACLE                         1


Create Users in Oracle 12c - Avoiding ORA-65096


Login into Container Database - (CDB2)
=================================
First, we have to decide if the new user will be considered type COMMON_USERS or LOCAL_USERS.
For a common user, the c## prefix must be used in the user creation

SQL> connect sys/oracle@cdb2 as sysdba
Connected.

SQL> create user user1 identified by oracle default tablespace users;
create user user1 identified by oracle default tablespace users
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> create user c##user1 identified by oracle default tablespace users;
User created.

SQL> grant connect,resource to c##user1;
Grant succeeded.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select PDB from v$services;

PDB
------------------------------
CDB2_PDB1
CDB$ROOT
CDB$ROOT
CDB$ROOT
CDB$ROOT

Login into Pluggable Database - (CDB2_PDB1)
======================================
SQL> alter session set container=CDB2_PDB1;
Session altered.

SQL> show con_name

CON_NAME
------------------------------
CDB2_PDB1

SQL> alter pluggable database open;
Pluggable database altered.

SQL> create user user2 identified by oracle;
User created.

SQL> grant connect,resource to user2;
Grant succeeded.

SQL> connect user2/oracle@192.168.56.111:1521/cdb2_pdb1;
Connected.

Monday, March 10, 2014

Article-9 : Published Oracle Technical Article in World Famous Site "Oracle Technology Network (OTN)"


 Article-9 : Published Oracle Technical Article in World Famous Site "Oracle Technology Network (OTN)"

Article Title : Oracle Exadata Database Machine: “Write-Back Flash Cache”

Article URL : http://www.oracle.com/technetwork/pt/articles/database-performance/exadata-write-back-flash-cache-2164875-ptb.html



Thursday, March 6, 2014

Diagnosing and Repairing Failures with Data Recovery Advisor - RMAN


Diagnosing and Repairing Failures with Data Recovery Advisor

Data Recovery Advisor is an Oracle Database tool that automatically diagnoses data failures, determines and presents appropriate repair options, and executes repairs at the user's request.

The easiest way to determine problems that your database is encountering is to use the LIST FAILURE command.

Step-1 : Check LIST FAILURE

RMAN> LIST FAILURE;

List of Database Failures
=================

Failure ID Priority   Status       Time Detected  Summary
---------- -------- ---------     --------------------------------------------------------------
862          HIGH     OPEN      16-DEC-13     One or more non-system datafiles are missing

RMAN>

If one or more failures exist, then you should typically use LIST FAILURE to show information about the failures and then use ADVISE FAILURE in the same RMAN session to obtain a report of your repair options.

Step-2 : Check ADVISE FAILURE

RMAN> ADVISE FAILURE;

List of Database Failures
=================

Failure ID Priority    Status    Time Detected    Summary
---------- -------- --------- -------------       ---------------------------------------------
862          HIGH     OPEN      16-DEC-13     One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
===================
no manual actions available

Optional Manual Actions
=================
1. If file +DATA/orcl/datafile/users.259.818891057 was unintentionally renamed or moved, restore it

Automated Repair Options
==================
Option Repair Description
------   ------------------
1      Restore and recover datafile 4
 
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_452126040.hm


If ADVISE FAILURE suggests manual repairs, then try these first. If manual repairs are not possible,
or if they do not repair all failures, then you can use REPAIR FAILURE to automatically fix failures suggested in the most recent
ADVISE FAILURE command in your current RMAN session.

Execute REPAIR FAILURE PREVIEW which previews the first repair options displayed by the previous ADVISE FAILURE command in the RMAN session.

Step-3 : Check REPAIR FAILURE PREVIEW

RMAN> REPAIR FAILURE PREVIEW;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_452126040.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';

Step-4 : Check REPAIR FAILURE

RMAN> REPAIR FAILURE;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_452126040.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting restore at 16-DEC-13
using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=6 STAMP=834322042 file name=+RECO/orcl/datafile/users.290.834321573
destination for restore of datafile 00004: +DATA/orcl/datafile/users.259.818891057
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=+DATA/orcl/datafile/users.259.834324019 RECID=0 STAMP=0
Finished restore at 16-DEC-13

Starting recover at 16-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: +DATA/orcl/datafile/users.259.834324019
channel ORA_DISK_1: reading from backup piece +RECO/orcl/backupset/2013_12_16/nnndn1_incr_update_0.306.834322071
channel ORA_DISK_1: piece handle=+RECO/orcl/backupset/2013_12_16/nnndn1_incr_update_0.306.834322071 tag=INCR_UPDATE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 39 is already on disk as file +RECO/orcl/archivelog/2013_12_16/thread_1_seq_39.281.834323373
archived log for thread 1 with sequence 40 is already on disk as file +RECO/orcl/archivelog/2013_12_16/thread_1_seq_40.283.834323373
archived log file name=+RECO/orcl/archivelog/2013_12_16/thread_1_seq_39.281.834323373 thread=1 sequence=39
archived log file name=+RECO/orcl/archivelog/2013_12_16/thread_1_seq_40.283.834323373 thread=1 sequence=40
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-DEC-13

sql statement: alter database datafile 4 online
repair failure complete

Do you want to open the database (enter YES or NO)? yes
database opened

RMAN>

Saturday, March 1, 2014