Wednesday, July 6, 2016

Steps to rebuild of existing standby database after failover

 

Steps to quickly rebuild of existing standby database:


There are situations where you will have to rebuild your existing standby database as a result of  various situations like primary db was restored from backup with open reset logs.


1. Disable log shipping to standby database (that you want to rebuild "alter system set log_archive_dest_state_2=defer").

2. Take full bakup from PRIMARY DB.

3. Take standby controlfile backup.

4. Copy backup and standby control file to standby server.

5. Drop datalafiles and controlfiles on standby Database.

6. Copy new standby control files to all controlfile locations.

7. Mount standby Database

8. Restore standby database.

8.  Enable log shipping to standby database(alter system set log_archive_dest_state_2=enable).

9. Recover managed standby database (on standby).

Friday, June 10, 2016

Create Temporary Tables in Oracle


Global Temporary Tables in Oracle


Temporary tables are useful in applications where a result set is to be buffered, perhaps because it is constructed by running multiple DML operations. For example, consider the following:

A Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.

During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped.

This statement creates a temporary table that is transaction specific:

NOTE : Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.

*********************************************************************************
HERE is an example to create a global temporary table with on commit DELETE ROWS :
*********************************************************************************

sql>  CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;
     
     
   
sql>  insert into ADMIN_WORK_AREA values (sysdate,sysdate+ 1,'A');

1 row inserted.


sql> select * from ADMIN_WORK_AREA;

commit;

Commit complete.

sql> select * from ADMIN_WORK_AREA;


NOTE: records in this temp table will be deleted upon commit. This is equivalent to truncating table on commit.

*********************************************************************************
HERE is an example to create a global temporary table with on commit PRESERVE ROWS :
*********************************************************************************


sql>  CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT PRESERVE ROWS;
     
           
sql>  insert into ADMIN_WORK_AREA values (sysdate,sysdate+ 1,'A');

1 row inserted.

1 row inserted.

sql>  select * from ADMIN_WORK_AREA;

commit;

Commit complete.

sql>  select * from ADMIN_WORK_AREA;


NOW exit the session and login back and select the table.

sql>  select * from ADMIN_WORK_AREA;

table is empty

NOTE: records (rows) in this temp table will be deleted upon session exit only, as long as you are using same session you can see these rows.
This is equivalent to truncating table on session exit.


Inserting Data into table with DML Error Logging (catching errors whiles inserting data into table)


 Inserting Data with DML Error Logging:



When you load a table using an INSERT statement with sub query, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature.

To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.


DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements.

--------------------------------------------------------
--  DDL for Table ATEST1
--------------------------------------------------------


  CREATE TABLE "ATOORPU"."ATEST1"
   (    "ID" NUMBER constraint ATEST1_PK PRIMARY KEY,
    "TDATE" DATE,
    "AMOUNT" VARCHAR2(20 BYTE),
    "ORD_NO" NUMBER
   ) ;

--------------------------------------------------------
INSERT SOME VALUES INTO TEST TABLE
--------------------------------------------------------


Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (1,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (2,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (3,to_date('01-MAR-16','DD-MON-RR'),null,100);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (4,to_date('01-MAR-16','DD-MON-RR'),'100',200);

--------------------------------------------------------
CREATE ERROR LOG TABLE USING THE DBMS PACKAGE :
--------------------------------------------------------


EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('ATEST1', 'ERR_ATEST1');   -- ATEST1 source table and ERR_ATEST1 error log table


Error Logging Restrictions and Caveats:

  • Oracle Database logs the following errors during DML operations:
  • Column values that are too large
  • Constraint violations (NOT NULL, unique, referential, and check constraints)
  • Errors raised during trigger execution
  • Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
  • Partition mapping errors
Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

--------------------------------------------------------
-- This will generate some insert errors
--------------------------------------------------------


INSERT INTO ATEST1
  SELECT ID+3,TDATE,AMOUNT,ORD_NO
  FROM ATEST1
  WHERE id > 1
  LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9; 

--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.


Note:
 
If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.

--------------------------------------------------------
-- This will generate some update errors
--------------------------------------------------------


update ATEST1 set ID=3 where ID>5 LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;

--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.


--------------------------------------------------------
LETS CHECK THE ERROR MESSAGES RECORDED:
--------------------------------------------------------


select * from ERR_ATEST1;

Wednesday, June 8, 2016

USING SELECT 'X' in query/sub-queries



USING SELECT 'X' in query/sub-queries.



--------------------------------------------------------
--  DDL for Table TAB1
--------------------------------------------------------

  CREATE TABLE "ATEST"."TAB1"
   (    "ID" NUMBER,
    "NAME" VARCHAR2(20 BYTE)
   ) ;

Insert into ATEST.TAB1 (ID,NAME) values (1,'AAA');
Insert into ATEST.TAB1 (ID,NAME) values (2,'BBB');
Insert into ATEST.TAB1 (ID,NAME) values (3,'EEE');
Insert into ATEST.TAB1 (ID,NAME) values (4,'FFF');


--------------------------------------------------------
--  DDL for Table TAB2
--------------------------------------------------------

  CREATE TABLE "ATEST"."TAB2"
   (    "ID" NUMBER,
    "NAME" VARCHAR2(20 BYTE)
   ) ;

Insert into ATEST.TAB2 (ID,NAME) values (1,'CCC');
Insert into ATEST.TAB2 (ID,NAME) values (2,'DDD');


Get records that exits in TAB1 and not in TAB2 using select 'X' :


select * from TAB1 f where not exists (select 'X' from TAB2 where id=f.id);
ID    NAME
--    ---- 
4    FFF
3    EEE

IN the above query we get output of all the records from TAB1 that doesnt match with TAB2 ID's.
Hence we do not get the records with ID's 1 & 2 as they only exits in TAB1.
This is just like using "select * from TAB1 f where not exists (select ID from TAB2 where id=f.id);"


Get records that exits in TAB1 and in TAB2 using select 'X' :


select * from TAB1 f where exists (select 'X' from TAB2 where id=f.id);

ID    NAME
--    ---- 
1    AAA
2    BBB

IN the above query we get output of all the records from TAB1 that exist with same ID in TAB2 .
Hence we get only records with ID 1 & 2 as they exists in both TABLES.
This is just like using "select * from TAB1 f where exists (select ID from TAB2 where id=f.id);"

Thursday, April 21, 2016

validate_con_names: PDB$SEED is not open catconInit: Unexpected error returned by validate_con_names Unexpected error encountered in catconInit; exiting


[oracle@Linux03 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d

$ORACLE_HOME/rdbms/admin -b /tmp/utl32k_output utl32k.sql
catcon: ALL catcon-related output will be written to /tmp/utl32k_output_catcon_19470.lst
catcon: See /tmp/utl32k_output*.log files for output generated by scripts
catcon: See /tmp/utl32k_output_*.lst files for spool files, if any
validate_con_names: PDB$SEED is not open
catconInit: Unexpected error returned by validate_con_names
Unexpected error encountered in catconInit; exiting

[oracle@Linux03 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 21 09:27:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       MOUNTED
PDBORCL       MIGRATE
PDBORCL2       MIGRATE
PDBORCL1       MIGRATE

SQL> startup;

ORA-01081: cannot start already-running ORACLE - shut it down first

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

CHECK THE STATUS OF PLUGGABLE DATABASE.

SQL> STARTUP;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       MOUNTED
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       MOUNTED

WE NEED TO START PDB$SEED PLUGGABLE DATABASE in UPGRADE STATE FOR THAT 

SQL> SHUTDOWN IMMEDIATE;


Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP UPGRADE;

ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       MIGRATE
PDBORCL       MIGRATE
PDBORCL2       MIGRATE
PDBORCL1       MIGRATE

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@Linux03 admin]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin

[oracle@Linux03 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d
$ORACLE_HOME/rdbms/admin -b /tmp/utl32k_output utl32k.sql
catcon: ALL catcon-related output will be written to /tmp/utl32k_output_catcon_19826.lst
catcon: See /tmp/utl32k_output*.log files for output generated by scripts
catcon: See /tmp/utl32k_output_*.lst files for spool files, if any
catcon.pl: completed successfully

ALTER SYSTEM SET max_string_size=extended ORA-02097: parameter cannot be modified because specified value is invalid


MAX_STRING_SIZE PARAMETER:


The extended data types functionality is controlled using the MAX_STRING_SIZE initialization parameter. The default value is STANDARD, which restricts the maximum sizes to the traditional lengths. Setting the parameter value to EXTENDED allows for the new maximum lengths.

This is a pre-requisite for enabling Extended Data Types in Oracle Database 12c. Which helps us to increase the max size of varchar data type from 4000 bytes to 32767 bytes.

[oracle@Linux03 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 21 09:10:02 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE;
ALTER SYSTEM SET max_string_size=extended;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET max_string_size=extended
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified


SQL> ALTER SYSTEM SET max_string_size=extended;
ALTER SYSTEM SET max_string_size=extended
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified


SQL> show parameter max_string_size

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size     string STANDARD

SQL> alter system set max_string_size=EXTENDED scope=spfile;

System altered.

SQL> show parameter max_string_size

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size     string STANDARD
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.

SQL> show parameter max_string_size

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size     string EXTENDED

Tuesday, April 5, 2016

CREATE ORACLE DATABASE SERVICES/SERVICE_NAME

dbms_service.create_service :  

We can call dbms_service.create_service procedure to create new service names , then start these service names for user connections. The procedure dbms_service.create_service requires the service name and service network name, use the service name to manage the service name. 


oracle@LINUX201:[~] $ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:17:50 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select SERVICE_ID,name from V$SERVICES;

SERVICE_ID NAME
---------- ----------------------------------------------------------------
         4 ORCL
         1 SYS$BACKGROUND
         2 SYS$USERS

************************************************************

LEST CREATE A NEW DB SERVICE 

************************************************************


SAMPLE :

BEGIN

DBMS_SERVICE.CREATE_SERVICE(service_name=>'QPDEV',

 network_name=>'QPDEV.WORLD'); (or)  network_name=>'QPDEV');

 END;

 /


SQL> begin
 dbms_service.create_service('ORCLTEST','ORCLTEST');
end;
/

PL/SQL procedure successfully completed.

SQL> select SERVICE_ID,name from V$SERVICES;

SERVICE_ID NAME
---------- ----------------------------------------------------------------
         4 ORCL
         1 SYS$BACKGROUND
         2 SYS$USERS

SQL> begin
 DBMS_SERVICE.START_SERVICE('ORCL');
end;
/  2    3    4

PL/SQL procedure successfully completed.

************************************************************

CHECK IF THE SERVICE IS CREATED AND STARTED

************************************************************


SQL> select SERVICE_ID,name from V$SERVICES;

SERVICE_ID NAME
---------- ----------------------------------------------------------------
         7 ORCLTEST
         4 ORCL
         1 SYS$BACKGROUND
         2 SYS$USERS

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

oracle@LINUX201:[~] $ cd $ORACLE_HOME/network/admin

oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ ls
samples  shrept.lst  tnsnames.ora

************************************************************

LETS ADD THIS NEW SERVICE IN TO TNSNAMES FILE:

************************************************************


oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX201.world.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLTEST)
    )
  )

************************************************************

LETS TEST IT :

************************************************************



oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ tnsping ORCLTEST

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-APR-2016 13:21:31

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX201)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLTEST)))
OK (10 msec)
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ sqlplus atoorpu@ORCLTEST

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:21:46 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:


You can also use more advanced features like failover parameters like below

Examples


DBMS_SERVICE.CREATE_SERVICE('ORCL','ORCL.WORLD');

DECLARE
   params dbms_service.svc_parameter_array;
   BEGIN
      params('FAILOVER_TYPE')            :='TRANSACTION';
      params('REPLAY_INITIATION_TIMEOUT'):=1800;
      params('RETENTION_TIMEOUT')        :=86400;
      params('FAILOVER_DELAY')           :=10;
      params('FAILOVER_RETRIES')         :=30;
      params('commit_outcome')           :='true';
      params('aq_ha_notifications')      :='true';
      DBMS_SERVICE.MODIFY_SERVICE('GOLD',params);
   END;