ORA-12560: TNS:protocol adapter error (Windows)

ORA-12560: TNS:protocol adapter error:



This error often strikes anytime then i list out what to do
1. Check networking wires
2. Check ping
3. Ensure listener is started
4.. Oracle variables configured correctly

In Windows OS:
---------------
C:\Users\Home>set ORACLE_HOME=F:\app\Home2\product\11.2.0\dbhome_1

C:\Users\Home>SET ORACLE_SID=ORCL

C:\Users\Home>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 3 23:32:18 2011

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

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

C:\Users\Home>oradim -start -sid XE

C:\Users\Home>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 4 00:08:27 2011

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 64086720 bytes
Fixed Size 137492 bytes
Variable Size 21313364 bytes
Database Buffers 41930400 bytes
Redo Buffers 556464 bytes
Database mounted.
Database opened.

SQL> conn sys@orcl as sysdba
Enter password:
Connected.
SQL>

Your suggestions and queries are always warm welcomed.  

Status of Primary & Standby server

-----run on  primary
spool 'C:\dg_Primary_output.log'
--spool dg_Primary_output.log
set feedback off
set trimspool on
set line 500
set pagesize 50
column name for a30
column display_value for a30
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4
col PROTECTION_MODE for a20
col RECOVERY_MODE for a20
col db_mode for a15
SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name;
col name for a10
col DATABASE_ROLE for a10
SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE,switchover_status from v$database;
select thread#,max(sequence#) from v$archived_log group by thread#;
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
col severity for a15
col message for a70
col timestamp for a20
select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2;
select ds.dest_id id
, ad.status
, ds.database_mode db_mode
, ad.archiver type
, ds.recovery_mode
, ds.protection_mode
, ds.standby_logfile_count "SRLs"
, ds.standby_logfile_active active
, ds.archived_seq#
from v$archive_dest_status ds
, v$archive_dest ad
where ds.dest_id = ad.dest_id
and ad.status != 'INACTIVE'
order by
ds.dest_id;
column FILE_TYPE format a20
col name format a60
select    name
,    floor(space_limit / 1024 / 1024) "Size MB"
,    ceil(space_used  / 1024 / 1024) "Used MB"
from    v$recovery_file_dest
order by name;
spool off





----run on standby 

spool 'C:\dg_standby_output.log'
--spool dg_standby_output.log
set feedback off
set trimspool on
set line 500
set pagesize 50
set linesize 200
column name for a30
column display_value for a30
col value for a10
col PROTECTION_MODE for a15
col DATABASE_Role for a15
SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name;
col name for a10
col DATABASE_ROLE for a10
SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE from v$database;
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
select process, status,thread#,sequence# from v$managed_standby;
col name for a30
select * from v$dataguard_stats;
select * from v$archive_gap;
col name format a60
select    name
,    floor(space_limit / 1024 / 1024) "Size MB"
,    ceil(space_used  / 1024 / 1024) "Used MB"
from    v$recovery_file_dest
order by name;
spool off

Find any NUMBER or TEXT from all DB

 These are the repository from where i can consult. Thanks for using this.

-----------------------------------------------------------------------------------------------------------------
Below Pl/Sql to find any NUMBER from all DB
-----------------------------------------------------------------------------------------------------------------
set serveroutput on size unlimited;

DECLARE
   v_id     NUMBER         := 1001249;
   v_sql1   VARCHAR (1000);
   cnt      NUMBER (10);
BEGIN
   FOR t IN (SELECT column_name, table_name
               FROM user_tab_columns
              WHERE column_name LIKE '%_ID' and data_type='NUMBER')
   LOOP
      v_sql1 :=
            'SELECT COUNT('
         || t.column_name
         || ') FROM '
         || t.table_name
         || ' WHERE '
         || t.column_name
         || ' = '
         || v_id;
      EXECUTE IMMEDIATE v_sql1
                   INTO cnt;
      IF (cnt > 0)
      THEN
         DBMS_OUTPUT.put_line ('..' || cnt);
         DBMS_OUTPUT.put_line ('..' || v_sql1);
      END IF;
   END LOOP;
END;
/




----------------------------------------------------------------------------------------------------------------
Below Pl/Sql to find any TEXT from all DB
---------------------------------------------------------------------------------------------------------------


SET serveroutput ON size unlimited;
DECLARE
  v_id   VARCHAR (1000) := '% is Invo%';
  v_sql1 VARCHAR (1000);
  cnt    NUMBER (10);
BEGIN
  FOR t IN
  (SELECT column_name,
    table_name
     FROM user_tab_columns
    WHERE data_type IN ('CHAR', 'NCHAR', 'NVARCHAR2', 'VARCHAR2')
  )
  LOOP
    v_sql1 :='SELECT COUNT(' || t.column_name || ') FROM '|| t.table_name|| ' WHERE '|| t.column_name|| ' like '''|| v_id||'''';
    EXECUTE IMMEDIATE V_SQL1 INTO CNT;
    IF (cnt > 0) THEN
      DBMS_OUTPUT.put_line ('..' || cnt);
      DBMS_OUTPUT.PUT_LINE ('..' || V_SQL1);
    END IF;
  END LOOP;
END;
/

pl sql

========================comma seperated to rows OR used IN clause==========================



WITH T AS (SELECT &V_USERID AS USERID FROM DUAL)
SELECT TRIM(REGEXP_SUBSTR (USERID, '[^,]+', 1, LEVEL)) ID FROM T
CONNECT BY regexp_substr(USERID, '[^,]+', 1, level) is not null
/

Enter value for v_userid: '1000,10001,10002,10003,10004'

ID
-------
1000
10001
10002
10003
10004



===========================converting days \into week====================================


SELECT TRUNC(TO_DATE('sysdate'),'d'),
TRUNC(TO_DATE('sysdate'),'Y'),
7+TRUNC(TO_DATE('sysdate'),'d')-
TRUNC(TO_DATE('sysdate'),'Y') AS S,
CEIL((7+(TRUNC(TO_DATE('sysdate'),'d'))-
TRUNC(TO_DATE('sysdate'),'Y'))/7) as a FROM DUAL;


=====================================Changing Timezone=================================


set serveroutput on size 1000000;

declare
 v_sql          varchar(10000);
BEGIN
   FOR t IN (SELECT ut.table_name, utc.column_name
               FROM user_tab_columns utc join user_tables ut on utc.table_name=ut.table_name
              WHERE utc.data_type = 'DATE' and utc.table_name not like 'AD_%')
   LOOP
           v_sql:=         'UPDATE '
                        || t.table_name
                        || ' SET '
                        || t.column_name
                        || ' = TO_DATE(TO_CHAR((FROM_TZ(CAST('
                        || t.column_name
                        || ' AS TIMESTAMP), ''+5:30'') AT TIME ZONE ''&TO_TIMEZONE'')'
                        || ',''DD-MM-RRRR HH24:MI:SS''),''DD-MM-RRRR HH24:MI:SS'')';
                       
              EXECUTE IMMEDIATE v_sql;
              COMMIT;
              DBMS_OUTPUT.put_line('..'||v_sql||';');
              DBMS_OUTPUT.put_line('COMMIT;');
                       
   END LOOP;
END;


=======================================================================================

Buffer Hit Ratio

Buffer Hit Ratio

Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
round((sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0))) /
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
from v$sysstat ;


Data Dict Hit Ratio

Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

select sum(GETS),
sum(GETMISSES),
round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
from v$rowcache ;



SQL Cache Hit Ratio

Hit Ratio should be > 85%

select sum(PINS) Pins,
sum(RELOADS) Reloads,
round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
from v$librarycache;




Library Cache Miss Ratio

Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

select sum(PINS) Executions,
sum(RELOADS) cache_misses,
sum(RELOADS) / sum(PINS) miss_ratio
from v$librarycache;

Your suggestions and queries are always warm welcomed.  

Useful syntax DB

-----------------------------------ts_extent_map.sql--------------------------------------

-- -----------------------------------------------------------------------------------

-- File Name : http://www.oracle-base.com/dba/monitoring/ts_extent_map.sql

-- Author : DR Timothy S Hall

-- Description : Displays gaps (empty space) in a tablespace or specific datafile.

-- Requirements : Access to the DBA views.

-- Call Syntax : @ts_extent_map (tablespace-name) [all | file_id]

-- Last Modified: 25/01/2003

-- -----------------------------------------------------------------------------------

SET SERVEROUTPUT ON SIZE 1000000

SET FEEDBACK OFF

SET TRIMOUT ON

SET VERIFY OFF

DECLARE

l_tablespace_name VARCHAR2(30) := UPPER('&1');

l_file_id VARCHAR2(30) := UPPER('&2');

CURSOR c_extents IS

SELECT owner,

segment_name,

file_id,

block_id AS start_block,

block_id + blocks - 1 AS end_block

FROM dba_extents

WHERE tablespace_name = l_tablespace_name

AND file_id = DECODE(l_file_id, 'ALL', file_id, TO_NUMBER(l_file_id))

ORDER BY file_id, block_id;

l_block_size NUMBER := 0;

l_last_file_id NUMBER := 0;

l_last_block_id NUMBER := 0;

l_gaps_only BOOLEAN := TRUE;

l_total_blocks NUMBER := 0;

BEGIN

SELECT block_size

INTO l_block_size

FROM dba_tablespaces

WHERE tablespace_name = l_tablespace_name;

DBMS_OUTPUT.PUT_LINE('Tablespace Block Size (bytes): ' || l_block_size);

FOR cur_rec IN c_extents LOOP

IF cur_rec.file_id != l_last_file_id THEN

l_last_file_id := cur_rec.file_id;

l_last_block_id := cur_rec.start_block - 1;

END IF;

IF cur_rec.start_block > l_last_block_id + 1 THEN

DBMS_OUTPUT.PUT_LINE('*** GAP *** (' || l_last_block_id || ' -> ' || cur_rec.start_block || ')' ||

' FileID=' || cur_rec.file_id ||

' Blocks=' || (cur_rec.start_block-l_last_block_id-1) ||

' Size(MB)=' || ROUND(((cur_rec.start_block-l_last_block_id-1) * l_block_size)/1024/1024,2)

);

l_total_blocks := l_total_blocks + cur_rec.start_block - l_last_block_id-1;

END IF;

l_last_block_id := cur_rec.end_block;

IF NOT l_gaps_only THEN

DBMS_OUTPUT.PUT_LINE(RPAD(cur_rec.owner || '.' || cur_rec.segment_name, 40, ' ') ||

' (' || cur_rec.start_block || ' -> ' || cur_rec.end_block || ')');

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Total Gap Blocks: ' || l_total_blocks);

DBMS_OUTPUT.PUT_LINE('Total Gap Space (MB): ' || ROUND((l_total_blocks * l_block_size)/1024/1024,2));

END;

/

---------------------------- maxshrink.sql ----------------------------------

set verify off

column file_name format a50 word_wrapped

column smallest format 999,990 heading "Smallest|Size|Poss."

column currsize format 999,990 heading "Current|Size"

column savings format 999,990 heading "Poss.|Savings"

break on report

compute sum of savings on report

column value new_val blksize

select value from v$parameter where name = 'db_block_size'

/

select file_name,

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

ceil( blocks*&&blksize/1024/1024) currsize,

ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

/

----------------------------------AUDITING SCHEMA VISE------------------------------------

declare

v_object_schema varchar2(50) :='VIENNAPRODUCTIVE';

v_table_name varchar2(255);

v_policy_name varchar2(255);

begin

for t in (select table_name, 'POL_'||table_name as policy_name from dba_tables where owner = v_object_schema)

loop

v_table_name := t.table_name;

v_policy_name := t.policy_name;

dbms_fga.add_policy (

object_schema => v_object_schema,

object_name => v_table_name,

policy_name => v_policy_name,

statement_types => 'SELECT, INSERT, DELETE, UPDATE'

);

end loop;

end;

SQL> select object_schema, object_name, policy_name, policy_text from dba_audit_policies;

SQL> select count(1) from fga_log$;

SQL> select db_user, to_char(timestamp, 'DD-MM-rr, hh:MI:SS'), sql_text from dba_fga_audit_trail;

declare

v_object_schema varchar2(5) :='MAHIR';

v_table_name varchar2(255);

v_policy_name varchar2(255);

begin

for t in (select table_name, 'POL_'||table_name as policy_name from dba_tables where owner = v_object_schema)

loop

v_table_name := t.table_name;

v_policy_name := t.policy_name;

dbms_fga.drop_policy (

object_schema => v_object_schema,

object_name => v_table_name,

policy_name => v_policy_name,

);

end loop;

end;

---------------------------Explain plan---------------------------

explain plan for

select * from emp;

select * from table(dbms_xplan.display)

-------------------------rebuilding indexes after compression---------------

BEGIN

FOR idx IN (SELECT owner||'.'||index_name indexname FROM all_indexes WHERE owner = 'VIENNACRM')

LOOP

EXECUTE IMMEDIATE 'ALTER INDEX '||idx.indexname||' REBUILD';

END LOOP;

END ;

--------------------------spool file to o/p------------------------------

spool 'C:\spool.txt'

select * from v$instance;

select * from v$database;

show parameter spfile

--alter system checkpoint;

--alter system switch logfile;

select * from v$version;

select * from sys.registry$history order by action_time;

select comp_id, comp_name , version, status FROM dba_registry;

select distinct status from dba_data_files;

select distinct status from v$datafile;

select distinct status from dba_temp_files;

select distinct status from v$tempfile;

select * from v$log;

select * from v$logfile;

select * from v$controlfile;

select distinct status from v$backup;

select * from v$recover_file;

select * from v$datafile where name like '%MISSING%';

select * from dba_data_files where file_name like '%MISSING%';

select machine, username, osuser, count(*) from v$session group by machine, username, osuser order by machine, username, osuser;

select owner, object_type, count(*) from dba_objects where status = 'INVALID' group by owner, object_type;

select owner, object_type, object_name from dba_objects where status = 'INVALID' order by owner, object_type, object_name;

select distinct status from dba_indexes;

select owner, table_name, index_name from dba_indexes where status='UNUSABLE';

--alter system checkpoint;

--alter system switch logfile;

select * from V$DATABASE_BLOCK_CORRUPTION;

spool off;

---------------------------create pfile---------------------------------

create pfile= 'C:\pfile.ora' from spfile;

---------------------------creating DB link---------------------------------

CREATE DATABASE LINK "lga1"

CONNECT TO system

IDENTIFIED BY system

USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.147)(PORT = 1521)))(CONNECT_DATA = (SID = orcl)))'

----------------------converting timestamp to scn & viceversa--------------------

select scn_to_timestamp(884871) as timestamp from dual;

select timestamp_to_scn(to_timestamp('20/1/2012 14:24:54','DD/MM/YYYY HH24:MI:SS')) as scn from dual;

--------------------------------to view sql-----------------------------------

select sesion.sid,

sesion.username,

optimizer_mode,

hash_value,

address,

cpu_time,

elapsed_time,

sql_text

from v$sqlarea sqlarea, v$session sesion

where sesion.sql_hash_value = sqlarea.hash_value

and sesion.sql_address = sqlarea.address

and sesion.username is not null

select sesion.sid,

sql_text

from v$sqltext sqltext, v$session sesion

where sesion.sql_hash_value = sqltext.hash_value

and sesion.sql_address = sqltext.address

and sesion.username is not null

order by sqltext.piece

------------------------------changing timezone---------------------------

SELECT TO_char(sysdate,'DD-MM-RRRR HH24:MI:SS'),

FROM_TZ(CAST(to_date(TO_char(sysdate,'DD-MM-RRRR HH24:MI:SS'),'DD-MM-RRRR HH24:MI:SS')

AS TIMESTAMP), '+5:30') AT TIME ZONE '+4:00'

FROM dual

--------------------------------any dictionary table--------------------

select * from dictionary where table_name LIKE '%CONTROL%' ; - with dba user

------------------------------session & processes-------------------------

SELECT MACHINE, COUNT(*) FROM V$SESSION where status = 'ACTIVE' GROUP BY MACHINE;

select count(*) from v$processes;

-----------------------increase session & parameters-------------------

show parameter processes

show parameter sessions

show parameter transactions

A basic formula for determining these parameter values is as follows:

if processes=x

sessions=x*1.1+5

transactions=sessions*1.1

alter system set processes=500, 1000 scope=spfile;

alter system set sessions=555, 1105 scope=spfile;

alter system set transactions=610, 1216 scope=spfile;

--------------------to kill session userwise------------------------------

select inst_id,sid,serial# from gv$session where username='SCOTT';

alter system kill session '130,620,@1';

---------------------------valid the invalid objects-----------------------

EXEC DBMS_STATS.gather_database_stats;

also run the utlrp.sql script and then take the fresh export

----------------------------DBF size---------------------------------------

select file_id,sum(bytes)/1024/1024 from dba_data_files group by file_id;

------------------------------TEMP FILE SIZE----------------------------

select sum(bytes)/1024/1024 "Meg" from dba_temp_files;

------------------------------LOG SIZE---------------------------------

select sum(bytes)/1024/1024 "Meg" from v$log;

--------------------------TOTAL DB SIZE-----------------------------------

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"

from ( select sum(bytes)/1024/1024 data_size

from dba_data_files ) a,

( select nvl(sum(bytes),0)/1024/1024 temp_size

from dba_temp_files ) b,

( select sum(bytes)/1024/1024 redo_size

from sys.v_$log ) c,

( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size

from v$controlfile) d;

------------------------user & their tbsp------------------------------

select owner, tablespace_name, count(*), sum(bytes)/1024/1024 size_mb

from dba_segments

group by owner, tablespace_name

order by 4 desc;

------------------------ users on TBSP-------------------------------

select username from dba_users where default_tablespace='USERS';

-------------------table or index belongs to which DBF-----------------

Use DBA_SEGMENTS to find the table or index

Use DBA_EXTENTS to see the extents that make up the segment

and inside see FILE_ID to look at the file

it is described in DBA_DATA_FILES

----------------- datafile relates to tablespace------------------------

select tablespace_name,file_name from dba_data_files;

---------------------------details of DB------------------------------

select * from dba_objects where object_name like '%JAVA%';

select * from DBA_REGISTRY;

select * from V$OPTIONFsize

---------------------details of tbsp autoextend or not--------------------

set pagesize 100

column file_name format a32

column tablespace_name format a15

column status format a3 trunc

column t format 999,999.000 heading "Total MB"

column a format a4 heading "Aext"

column p format 990.00 heading "% Free"

SELECT df.file_name,

df.tablespace_name,

df. status,

(df.bytes/1024000) t,

(fs.s/df.bytes*100) p,

decode (ae.y,1,'YES','NO') a

FROM dba_data_files df,

(SELECT file_id,SUM(bytes) s

FROM dba_free_space

GROUP BY file_id) fs,

(SELECT file#, 1 y

FROM sys.filext$

GROUP BY file#) ae

WHERE df.file_id = fs.file_id

AND ae.file#(+) = df.file_id

ORDER BY df.tablespace_name, df.file_id;

column file_name clear

column tablespace_name clear

column status clear

column t clear

column a clear

column p clear

ttitle off

------------- if drive is full & extend TBSP----------------

create folder in d drive say oracle or anything and fire the below command

alter tablespace users add datafile 'D:\oracle\user02.dbf' size 3G;

i have assume that the folder name is oracle

alter tablespace ts_sth 
  add datafile 'c:\xx\sth_04.dbf' size 4M autoextend off;

------------------ Space of TBSP-----------------------------

set linesize 150

column tablespace_name format a20 heading 'Tablespace'

column sumb format 999,999,999

column extents format 9999

column bytes format 999,999,999,999

column largest format 999,999,999,999

column Tot_Size format 999,999 Heading 'Total| Size(Mb)'

column Tot_Free format 999,999,999 heading 'Total Free(MB)'

column Pct_Free format 999.99 heading '% Free'

column Chunks_Free format 9999 heading 'No Of Ext.'

column Max_Free format 999,999,999 heading 'Max Free(Kb)'

set echo off

PROMPT FREE SPACE AVAILABLE IN TABLESPACES

SPACE AVAILABLE IN TABLESPACES

select a.tablespace_name,sum(a.tots/1048576) Tot_Size,

sum(a.sumb/1048576) Tot_Free,

sum(a.sumb)*100/sum(a.tots) Pct_Free,

sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free

from

(

select tablespace_name,0 tots,sum(bytes) sumb,

max(bytes) largest,count(*) chunks

from dba_free_space a

group by tablespace_name

union

select tablespace_name,sum(bytes) tots,0,0,0 from

dba_data_files

group by tablespace_name) a

group by a.tablespace_name

order by pct_free;

-----------------------Size of schema-------------------------------

SELECT owner,SUM (BYTES) / (1024 * 1024 * 1024) SIZE_IN_GB

FROM dba_segments

GROUP BY owner;

------------------------autoextend on-------------------------------

alter database datafile 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF' autoextend on;

----------------------Add datafile to TBSP--------------------------

alter tablespace system add datafile 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF' size 500M autoextend on;

-----------------------------------indexes---------------------------

SELECT I.INDEX_NAME,

I.INDEX_TYPE,

I.TABLE_NAME,

IC.COLUMN_NAME,

DBMS_METADATA.GET_DDL('INDEX',I.INDEX_NAME)

FROM DBA_INDEXES I,

DBA_IND_COLUMNS IC

WHERE I.OWNER ='HIMLIVE'

AND I.INDEX_NAME = IC.INDEX_NAME

AND I.TABLE_NAME = IC.TABLE_NAME

AND I.TABLE_NAME='M_PRODUCT';

----------------------constraint------------------------------------

SELECT  p.table_name AS PARENT,
                            col.column_name,
                            p.constraint_name,
                            c.table_name AS CHILD,
                            cols.column_name,
                            c.constraint_name,
                            c.status
            FROM            user_constraints c JOIN user_constraints p
                            ON c.r_constraint_name = p.constraint_name
                            JOIN user_cons_columns col
                            ON col.owner = p.owner
                          AND p.constraint_name = col.constraint_name
                          AND col.table_name = p.table_name
                            JOIN user_cons_columns cols
                            ON cols.owner = c.owner
                          AND c.constraint_name = cols.constraint_name
                          AND cols.table_name = c.table_name
                          AND c.r_owner = p.owner
                          where c.status='DISABLED' ;

ORDER BY c.constraint_name;

---------------delete duplicate rows-----------------

DELETE FROM emp a

WHERE ROWID NOT IN (SELECT MAX (ROWID)

FROM emp b

WHERE a.empno = b.empno

GROUP BY empno);

-------------------------All views-------------------------------

SELECT TYPE_TEXT FROM ALL_VIEWS WHERE VIEW_NAME = 'SMPRODUCT_V';

------------------user_constraints---------------------------------------

select

parent.owner || '.' ||

parent.table_name "Parent table",

'is parent of' " ",

child.owner || '.' ||

child.table_name "Child table"

from

user_constraints child join user_constraints parent on

child.r_constraint_name = parent.constraint_name and

child.r_owner = parent.owner

where

child.table_name like 'AD_PINSTANCE_LOG%' escape '\' --';

-----------------------------valid/invalid objects-------------------------

select owner, object_name, object_type, status from dba_objects where status!='VALID' order by 1;

-------------------To add timestamp in export ---------------------------------

exp system/system file=d:\expdp.%date:~4,2%%date:~7,2%%date:~10,4%%time:~0,2%%time:~3,2%%time:~6,2%.dmp

https://forums.oracle.com/forums/thread.jspa?threadID=959681

-----------------------------expdp all users-----------------------------------------

SELECT 'expdp SYSTEM/system DUMPFILE=' || owner || '_%U.dmp LOGFILE=' || owner ||'.log schemas= ' || owner SQL

FROM (

SELECT owner, ROUND(SUM(size_mb)) MBytes FROM

(

SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name

FROM DBA_SEGMENTS

WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

--AND TABLESPACE_NAME LIKE 'COSTE%'

--AND SEGMENT_NAME LIKE 'OE_ORDER_LINES_ALL%'

--AND partition_name LIKE 'USAGE_FCT_NEW%'

AND OWNER <> 'SYSTEM'

--AND ROUND(bytes/(1024*1024),2) > 1000)

)

GROUP BY owner

ORDER BY MBytes DESC);

-------------------export query from 11g to 10g-------------------------

expdp system/system_password@orcl

schemas=hr

version= 10.2

dumpfile=hr_schema.dmp

logfile=hr_schemas_exp.log

---------------import query at 10g from 11g--------------------------

impdp system/system@xe

remap_schema=hr:scott

dumpfile=hr_schema.dmp

logfile=hr_schemas_imp.log --------simple query

impdp system/system_password@orcl

remap_schema=hr:scott

dumpfile=hr_schema.dmp

sqlfile=hr_schemas.sql --------sqlfile

logfile=hr_schemas_imp.log

you can use the parameter sqlfile=schema_sql.txt

this parameter is used during import statements in this case you have the sqlfile and in this case there is no actual import

you can check the sqlfile and come to know about the tablespace and users all

impdp system/system_password@orcl

remap_schema=hr:scott

REMAP_TABLESPACE=EXAMPLE:USERS ------tablespace

dumpfile=hr_schema.dmp

sqlfile=hr_schemas.sql

logfile=hr_schemas_imp.log

-------------------------check for the objects------------------------

select OBJECT_TYPE,count(*)from dba_objects where owner='LGA1' group by OBJECT_TYPE;

------------------------creating/changing path of DATA_PUMP_DIR---------------

drop directory DUMP_DIR ;

--Manually create folder dump

create or replace directory DATA_PUMP_DIR as '';

GRANT read, write ON DIRECTORY DUMP_DIR TO system;

------------------------to check path of data_pump_dir---------------

SELECT directory_path

FROM dba_directories

WHERE directory_name = 'DATA_PUMP_DIR';

emca -config dbcontrol db -repos recreate

Your suggestions and queries are always warm welcomed.  

Useful commands RMAN

------------------------to take backup as copy/image or  backupset---------------------

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY;

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO backupset;

--------------------------------check for all archives--------------------------------------

crosscheck archivelog all;

-----------------------------------skip inaccessible-----------------------------------------

backup database plus archivelog skip inaccessible ;

---------------------------------delete all archives-----------------------------------------

delete archivelog all

-----------------delete archives after backup & skip inaccessible-----------------------

RMAN> backup database plus archivelog skip inaccessible delete all input;




------------- Creating Duplicate SB database in networking----------------

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
 SPFILE
  SET DB_NAME='ORCL'
  SET DB_UNIQUE_NAME='ORCL'
  SET DB_RECOVERY_FILE_DEST='E:\app\navneet\flash_recovery_area'
  SET  CONTROL_FILES='E:\APP\NAVNEET\ORADATA\ORCL\CONTROL01.CTL',     'E:\APP\NAVNEET\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL'
  SET DB_FILE_NAME_CONVERT='E:\app\navneet\oradata\strdb\', 'E:\app\navneet\oradata\orcl\'
  SET LOG_FILE_NAME_CONVERT='E:\app\navneet\oradata\strdb\', 'E:\app\navneet\oradata\orcl\';

Oracle Data Guard with TAF

This is for my personal refference

Server1(Primary DB- orcl)

1) Install Oracle 11g on the server1

2) Enable Archive logs

3) Enable Flashback logs

4) Setup RMAN with target Database

5) Enable Force Logging

6) Create Stand By Logs

1)     Install Oracle 11g on server1.

Run the Oracle setup and get the whole s/w with database install on server1 successful with the DB name as ORCL.  And alter the force logging to yes.

Sql> alter system set force_logging =true;

It can be verified by

Sql> select force_logging from v$database;

2)    Enable Archive Logs

To set archive log, always login through SYSDBA

Now, let us check the current log mode either in "Archivelog" or "Noarchivelog":

C:\Documents and Settings\navneet>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 27 11:19:53 2012

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

Enter user-name: /as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 2296

Current log sequence 2299

OR

SQL> select log_mode from v$database;

LOG_MODE

------------

NOARCHIVELOG

We have checked that our Database is in "NOARCHIVE LOG". Below query will set the location where to save the archive logs.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=E:\ARCHIVE_LOG' SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area 535662592 bytes

Fixed Size 1375792 bytes

Variable Size 385876432 bytes

Database Buffers 142606336 bytes

Redo Buffers 5804032 bytes

Database mounted.

The below query will make database into archivelog.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE

------------

ARCHIVELOG

OR

SQL> ARCHIVE LOG LIST

Database log mode Archive Mode

Automatic archival Enabled

Archive destination E:\ARCHIVE_LOG

Oldest online log sequence 2296

Next log sequence to archive 2299

Current log sequence 2299

SQL> alter system switch logfile;

Now database is in Archivelog mode, We have verified by checking archivelogs in directory.

E:\>dir

Volume in drive E has no label.

Volume Serial Number is AA1B-C0B9

Directory of E:\

12/09/2011 09:15 AM app

03/27/2012 11:43 AM 13,952,512 ARCHIVE_LOG2ARC0000002299_0769427405.0001

03/27/2012 11:46 AM 1,536 ARCHIVE_LOG2ARC0000002300_0769427405.0001

03/28/2012 05:39 AM 50,630,144 ARCHIVE_LOG2ARC0000002301_0769427405.0001

03/29/2012 05:38 AM 40,771,072 ARCHIVE_LOG2ARC0000002302_0769427405.0001

03/29/2012 06:30 PM 40,671,232 ARCHIVE_LOG2ARC0000002303_0769427405.0001

02/17/2009 11:33 AM 23,516,968 SkypeSetupFull.exe

6 File(s) 169,543,464 bytes

1 Dir(s) 38,746,923,008 bytes free

3)   Enabling Flashback Logs

Before enable the flashback we need to take our database in archive log mode.  After enabling the archive logs now we need to set 3 paramaters for flashback are

 1 db_recovery_file_dest

2 db_recovery_file_dest_size

3 db_flashback_retention_target

 

>>This parameter "db_recovery_file_dest" is used to set the destination where we  want to store 

the location of flashback logs.  The location of flashback logs may or maybe be same as the location of archive logs, here i gave different location from archive logs.

>>The parmeter "db_recovery_file_dest_size" is used to define the size that can be used by 

flashback directory/folder provided with above parameter. This size can be calculated on the basis of transactions happened in our database and if archive logs, only when the destination of archive logs 

& flashback logs are same.

>>The parameter "db_flashback_retention_target " is used to provide time limit upto which flashback logs should be in the memory of database, So that database can be flashed up back with in time limit. Time is provided in minutes, so we set retention time 1440 which is about a day.

 >>Set flashback database parameters

alter system set db_recovery_file_dest='E:\app\navneet\FRA' scope=spfile; 

alter system set db_recovery_file_dest_size=20G scope=spfile;

alter system set db_flashback_retention_target=1440 ; 

 >> Turn flashback on for the database 

shutdown immediate;

startup mount;

alter database flashback on;

alter database open;

>>Now check if flashback is enabled 

select flashback_on from v$database;

 FLASHBACK_ON

------------------

YES

 Now Flashback is ENABLED. 

>>To disable flashback 

alter system set db_recovery_file_dest=" scope= both sid='*';

Some related queries are as follow

>> To check the flashback logs

select * from v$flashback_database_log;

>>To check the estimated flashback size

SELECT ROUND(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024) FROM V$FLASHBACK_DATABASE_LOG; 

 

>>To create a guaranteed restore point

create restore point RESTORE_POINT guarantee flashback database;

select NAME, SCN, TIME from V$RESTORE_POINT;

>>To flashback the database to specific time

flashback database to timestamp to_timestamp('dd/mm/yy HH24:MI:SS', 'DD/MM/YYYY

HH24:MI:SS');

>>To drop a restore point

select NAME, SCN, TIME from V$RESTORE_POINT; 

 drop restore point RESTORE_POINT;

The V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE views can help

to find out the current location, disk quota, space in use, space reclaimable by deleting files,

total number of files, the percentage of the total disk quota used by different types of files.

4)    Setting up RMAN

Create Recovery Catalog

CONNECT sys/password@w2k1 AS SYSDBA

-- Create tablepsace to hold repository

CREATE TABLESPACE "RMAN"

DATAFILE 'C:\ORACLE\ORADATA\W2K1\RMAN01.DBF' SIZE 6208K REUSE

AUTOEXTEND ON NEXT 64K MAXSIZE 32767M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

-- Create rman schema owner

CREATE USER rman IDENTIFIED BY rman

TEMPORARY TABLESPACE temp

DEFAULT TABLESPACE rman

QUOTA UNLIMITED ON rman;

GRANT connect, resource, recovery_catalog_owner TO rman;

Create the recovery catalog.

C:>rman catalog=rman/rman@w2k1

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to recovery catalog database

recovery catalog is not installed

RMAN> create catalog tablespace "RMAN";

recovery catalog created

RMAN> exit

Recovery Manager complete.

Register Database

Each database to be backed up by RMAN must be registered with the catalog.

C:>rman catalog=rman/rman@w2k1 target=sys/password@w2k2

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: W2K2 (DBID=1371963417)

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

5)   Enable force logging

Check whether Force logging is YES or NO.

SQL> select force_logging from v$database;

FOR

---

NO

The above  o/p shows the force logging is not enabled so Enable it by followed syntax.

SQL> alter database force logging;

Database altered.

Check again if force logging is enabled.

SQL> select force_logging from v$database;

FOR

---

YES

The above o/p shows that force logging is enabled now.

6)  Create Standby Logs

Standby redo logs are required to enable real time apply of redo data onto the standby. This standby redo logs are populated with redo information as fast as the primary redo logs, rather than waiting for the redo log to be archived and shipped to the standby.  This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.

Ensure log file sizes are identical on the primary and standby databases. The size of the current standby redo log files must exactly match the size of the current primary database online redo log files. For example, if the primary database uses two online redo log groups whose log files are 200K, then the standby redo log groups should also have log file sizes of 200K.

 Determine the appropriate number of standby redo log file groups. Logs should be equal in number. If Primary has 3 redo logs then we should made 3 or more Standby redo logs. Oracle recommends that you create a standby redo log on the primary database so that the primary database can switch over quickly to the standby role without the need for additional DBA intervention.

Syntax to create Standby Log

alter database add standby logfile group 1 ‘%ORACLE_BASE%\ORADATA\%ORACLE_SID%\REDO01.LOG’  size 50m;

Alter system set dg_broker_start=TRUE scopth = both;

Go to server 2(To create Standby DB - strdb)

Install the Oracle software only without database.  Then create an instance (STRDB) by firing the command on command prompt. The following commands you can see is how i tried.

C:\Users\Administrator>ORADIM -NEW -SID STRDB

Instance created.

Now creating password file in %Oracle_home% \db_home.

C:\Users\Administrator>ORAPWD FILE="C:\app\Administrator\product\11.2.0\dbhome_1

\database\PWDSTRDB.ora" PASSWORD=system

Create folder for Flash Area Recovery

%Oracle_base%\FRA\strdb

Create folder for Flash Area Recovery

%Oracle_base%\ORADATA\strdb

Create pfilestrdb.ora tp startup database in nomount stage, place anywhere in the Hard drive and add two lines in that file.

*.db_name='ORCL'

*.db_unique_name='STRDB'

Save & exit.

Now create listener & tns entry so that database instance can be able to talk.

Go to listner.ora, edit it by copy & pasting the same entry as mentioned below.

OLD ENTERY OF LISTNER.ORA

# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = eagle394.server4you.de)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = C:\app\Administrator

NEW ENTRY OF LISTNER.ORA

# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

     (SID_DESC =

      (SID_NAME = STRDB)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = eagle394.server4you.de)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = C:\app\Administrator

Now for TNS ENTRY

OLD TNS ENTRY

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

    (CONNECT_DATA =

      (SID = CLRExtProc)

      (PRESENTATION = RO)

    )

  )

NEW TNS ENTRY

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

STRDB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 69.64.58.18)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = STRDB)

    )

  )

ORACLR_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

    (CONNECT_DATA =

      (SID = CLRExtProc)

      (PRESENTATION = RO)

    )

  )

Now to make the network connectivity of both the DBs. Just add the servers 1’s tns entry to server2’s tns entry & vice versa.

Server1’s TNS ENTRY  after adding the server2’s tns entry

# tnsnames.ora Network Configuration File: c:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

STRDB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 69.64.58.18)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = STRDB)

    )

  )

ORACLR_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

    (CONNECT_DATA =

      (SID = CLRExtProc)

      (PRESENTATION = RO)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = pro1848.server4you.de)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

Server2’s TNS ENTRY  after adding the server1’s tns entry

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

STRDB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 69.64.58.18)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = STRDB)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = pro1848.server4you.de)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

ORACLR_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

    (CONNECT_DATA =

      (SID = CLRExtProc)

      (PRESENTATION = RO)

    )

  )

Restart the LSNRCTL by firing the command on command prompt on both servers.

cmd> LSNRCTL STOP

cmd> LSNRCTL START

Now on server 2 make the DB in nomount stage

C:\Users\Administrator>sqlplus sys/system@strdb as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 6 09:55:33 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup nomount pfile='C:\app\pfile.ora';

ORACLE instance started.

Total System Global Area 1.3696E+10 bytes

Fixed Size                  2188768 bytes

Variable Size            6878661152 bytes

Database Buffers         6777995264 bytes

Redo Buffers               37044224 bytes

SQL>

Go to server 1

Connect to Rman with orcl database to make the StandBy database with the script provided below.

C:\Users\Administrator>rman target sys/system@orcl

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jul 6 12:13:36 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1314151480)

RMAN> conn auxiliary sys/system@strdb

RMAN> DUPLICATE TARGET DATABASE

2>   FOR STANDBY

3>   FROM ACTIVE DATABASE

4>  SPFILE

5>   SET DB_NAME='ORCL'

6>   SET DB_UNIQUE_NAME='STRDB'

7>   SET DB_RECOVERY_FILE_DEST='C:\app\Administrator\FRA'

8>   SET CONTROL_FILES='C:\app\Administrator\ORADATA\STRDB\CONTROL01.CTL',     'C:\app\Administrator\FRA\STRDB\CONTROL02.CTL'

9>   SET DB_FILE_NAME_CONVERT='C:\app\Administrator\oradata\orcl\', 'C:\app\Administrator\ORADATA\STRDB\'

10>   SET LOG_FILE_NAME_CONVERT='C:\app\Administrator\oradata\orcl\', 'C:\app\Administrator\ORADATA\STRDB\';

 Script in simple form is used in RMAN
DUPLICATE TARGET DATABASE

  FOR STANDBY

  FROM ACTIVE DATABASE

 SPFILE

  SET DB_NAME='ORCL'

  SET DB_UNIQUE_NAME='STRDB'

  SET DB_RECOVERY_FILE_DEST='C:\app\Administrator\FRA'

  SET CONTROL_FILES='C:\app\Administrator\ORADATA\STRDB\CONTROL01.CTL',     'C:\app\Administrator\FRA\STRDB\CONTROL02.CTL'

  SET DB_FILE_NAME_CONVERT='C:\app\Administrator\oradata\orcl\', 'C:\app\Administrator\ORADATA\STRDB\'

  SET LOG_FILE_NAME_CONVERT='C:\app\Administrator\oradata\orcl\', 'C:\app\Administrator\ORADATA\STRDB\';

Note- All the locations provided in the script are according to the database located on the Test Servers.

Go to Server2 - strdb

This will create exact database at server 2 (STRDB) with its all redo logs and Standby logs, multiplexed control file with archive logs and flashback database. After finishing the script under RMAN, the database will be in mount stage. Database will be in the Physical standby position. It can be checked by the query

Sql> select name, db_unique_name, database_role, open_mode from v$database;

Output would be

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE

--------- ------------------------------ ---------------- --------------------

ORCL      STRDB                           PHYSICAL STANDBY MOUNTED

Check the parameter whether dg_broker_start is true or not, if not then make it true by the followed query.

Sql>Alter system set dg_broker_start=TRUE scopth = both;

Go to server 1 - orcl

Now setting up Dataguard Manager(dgmgrl) to make failover automatic.

C:\Users\Administrator>set oracle_sid=orcl

C:\Users\Administrator>dgmgrl/

DGMGRL for 64-bit Windows: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected.

The following command is used to create the configuration known as DG with primary database. Only primary database would be integrated in this configuration. StandBy database will be added in next step.

DGMGRL> CREATE CONFIGURATION dg AS

    >PRIMARY DATABASE IS orcl

    >CONNECT IDENTIFIER IS orcl;

Configuration “dg” has been configured with primary database “orcl”

Now to add standby database in the same configuration and same primary database.

DGMGRL> ADD DATABASE strdb

  >AS CONNECT IDENTIFIER IS strdb

  >MAINTAINED AS physical;

Database “strdb” added

DGMGRL> show configuration

Configuration - dg

  Protection Mode: MaxPerformance

  Databases:

    orcl  - Primary database

    strdb -  Physical standby database

Fast-Start Failover: DISABLE

Configuration Status:

DISABLE

Now the configuration is Disabled as above, to make it enable run the below command

DGMGRL>enable configuration

Enabled.

DGMGRL> show configuration

Configuration - dg

  Protection Mode: MaxPerformance

  Databases:

    orcl - Primary database

    strdb - Physical standby database

Fast-Start Failover: DISABLE

Configuration Status:

SUCCESS

Now the configuration is success after enabling. If it is not configured well then it will show error instead of success. And Fast-Start Failover is Disable which is okay for now. We can check that archive logs from Primary (orcl) database are shipping to the standby (strdb) database by going physically on their appropriate place with same sequence number. In this scenario archive log destinations are

Primary DB- C:\app\Administrator\archivelogs

Standby DB- C:\app\Administrator\archivelogs

Next step is to make Failover automatic known as FastStart Failover , for that we need to set the parameters as below:

>LogXptMode should be SYNC for both the database

Dgmgrl> edit database orcl set property ‘LogXptMode’  ='SYNC’;

Dgmgrl> edit database strdb set property ‘LogXptMode’  ='SYNC’;

 >change the protection mode to MaxAvailability

Dgmgrl> edit configuration set protection mode as maxavailability

> Standbyfile management should be Auto

Dgmgrl> edit database orcl set property StandbyFileManagement   = 'AUTO';

> FastStartFailoverTarget   should be Standby database so that it got connect o SBDB

Dgmgrl> edit database orcl set property FastStartFailoverTarget   = ‘strdb’;

Dgmgrl> edit database strdb set property FastStartFailoverTarget   = ‘orcl’;

>Now enabling the FastStart_Failover

Dgmgrl> enable FastStart_failover;

enabled

Now check the status of configuration and notice that Protection mode has been changed and Fast start failover has been Enabled.

DGMGRL> show configuration

Configuration - dg

  Protection Mode: MaxAvailability

  Databases:

    orcl - Primary database

    strdb - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:

SUCCESS

We can check by firing the command on sqlplus of Primary Database, here output shows that Standby database is synchronized with primary database, and if primary(orcl) fails then target database id STRDB which will become Primary.

SQL> select name, db_unique_name, database_role, fs_failover_current_target, fs_failover_status

from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_STATUS

--------- ------------------------------ ---------------- ------------------------------ ----------------------

ORCL               ORCL                                                 PRIMARY                                STRDB                           SYNCHRONIZED

Setting up Observer which can on another machine

Take the third machine for setting the observer and install only oracle software instead of Oracle software+database.  Copy the tns entery of strdb & orcl database & paste it on the third Machine’s tns file so that third machine would be able to contact both the servers.

STRDB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 69.64.58.18)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = STRDB)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = pro1848.server4you.de)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

Now we need to connect dataguard manager tostart the observer on this machine which will observe the primary database. Whenever Primary database goes down due to any reason then it will automatically switch STRDB to primary & make it in use with clients with in 30 seconds

C:\Users\Administrator>set oracle_sid=orcl(primary)

C:\Users\Administrator>dgmgrl/

DGMGRL for 64-bit Windows: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> start observer

Observer started

Now this window needs to be open till observer is running, if by chance this window has been closed then observer will get close itself and automatic switchover can never be performed.

Automatic Application switchover

In my simple scenario, I have one Primary Database (orcl) and one Physical Standby Database (strdb). On The challenge is now to get the connect from the client side to the right (primary) database. That is called Connect Time Failover and is achieved as follows:

First, we make sure that the client uses a tnsnames.ora with a connect descriptor that uses a SERVICE_NAME instead of a SID

MYAPP =

 (DESCRIPTION =

 (ADDRESS_LIST =

 (ADDRESS = (PROTOCOL = TCP)(HOST = 69.64.58.18)(PORT = 1521))

 (ADDRESS = (PROTOCOL = TCP)(HOST = pro1848.server4you.de)(PORT = 1521))

 )

 (CONNECT_DATA =

 (SERVICE_NAME = myapp)

 )

 )

Second, we take care that the service myapp is offered only at the right database – only on the primary. Notice that the PMON background processes of both databases must be able to communicate with the (local) listeners in order to register the service myapp. If you don’t use the listener port 1521, they can’t. You have to point to that listener port then with the initialization parameter LOCAL_LISTENER.

We create and start now the service myapp manually on the primary:

begin

 dbms_service.create_service('myapp','myapp');

end;

/

begin

 DBMS_SERVICE.START_SERVICE('myapp');

end;

/

Then we create a trigger, that ensures that this service is only offered, if the database is in the primary role:

create trigger myapptrigg after startup on database

declare

 v_role varchar(30);

begin

 select database_role into v_role from v$database;

 if v_role = 'PRIMARY' then

 DBMS_SERVICE.START_SERVICE('myapp');

 else

 DBMS_SERVICE.STOP_SERVICE('myapp');

 end if;

end;

/

The event after startup is fired, if an instance changes from status MOUNT to OPEN. If you use a logical standby, it is not fired, because the logical standby remains in status OPEN. You may use the event after db_role_change in this case. The creation of the trigger and of the service is accompanied with redo protocol (the Data Dictionary has changed) and therefore also present at strdb without any additional work to do there for the DBA. With the present setup, we have already achieved Connect Time Failover: Clients can use the same connect descriptor (myapp) to get to the right (primary) database now, regardless of switchover or failover.

But sessions that are connected to orcl are disconnected if a switchover or failover to strdb takes place. They have got to connect again then. We can change that, so that a Runtime Failover is possible, under ideal circumstances, that failover is even completely transparent to the client and proceeds without error messages. To achieve that, you don’t have to touch the tnsnames.ora on the client side. Instead, you do the following on the primary database:

begin

 dbms_service.modify_service

 ('myapp',

 FAILOVER_METHOD => 'BASIC',

 FAILOVER_TYPE => 'SELECT',

 FAILOVER_RETRIES => 200,

 FAILOVER_DELAY => 1);

end;

/

Connections to the service myapp are now automatically failed over together with the service to the new primary. Should they have done nothing during the time of the failover/switchover, or even if they had run a select statement, they will not receive any error but only notice a short interruption.

This is all done in configuration.