Sridhar Ramireddy's Blog

Virtual indexes in Oracle 10g.

2009-07-27T07:50:00.001-07:00

Virtual indexes in Oracle 10g.

07:05:15 orcl>
07:05:20 orcl>
07:05:20 orcl>
07:05:21 orcl>
07:05:21 orcl>
07:05:25 orcl> Let?s describe the employees table and review the columns and datatypes.
07:05:25 orcl>
07:05:25 orcl> desc hr.employees;
?Name??????????????????????????????? ?????????????????????????????????????Null???? Type
?------------------------------------------------------------------------ -------- -------------------------------------------------
?EMPLOYEE_ID??????????????????????????????????????????????????????NUMBER(6)
?FIRST_NAME????????????????????????????????????????????????????????VARCHAR2(20)
?LAST_NAME??????????????????????????????????????????????????????????NOT NULL VARCHAR2(25)
?EMAIL???????????????????????????????????? ???????????????????????????????NOT NULL VARCHAR2(25)
?PHONE_NUMBER?????????????????????????????????????????????????VARCHAR2(20)
?HIRE_DATE???????????????????????????????????????????????????????????NOT NULL DATE
?JOB_ID??????????? ???????????????????????????????????????????????????????NOT NULL VARCHAR2(10)
?SALARY??????????????????????????????????????????????????????????????????NUMBER(8,2)
?COMMISSION_PCT????????????????????????????????????????????????NUMBER(2,2)
?MANAGER_ID?????????????????????????????????????????????????????????NUMBER(6)
?DEPARTMENT_ID??????????????????????????????????????????????????NUMBER(4)

Since I am using this table for other testing, let?s build a copy to use for our virtual index testing.

07:34:55 orcl> create table hr.employees2 as select * from hr.employees;

Table created.

We?ll duplicate a few rows in the table.

07:35:15 orcl> insert into hr.employees2 select * from hr.employees;
107 rows created.

Duplicating rows to pump up the volume in our test table.

07:36:17 orcl> r
? 1* insert into hr.employees2 select * from hr.employees2

178048 rows created.

Activating SQL*PLUS Autotrace to produce an Explain Plan.

07:50:38 orcl> set autotrace traceonly exp stat

Building a regular B-Tree index on the EMPLOYEEES2 table.? Notice that Autotrace does not produce Explain Plans for DDL statements.

07:52:19 orcl> create index hr.emp2_emp_id on hr.employees2 (employee_id);

Index created.

Running the statement using the Autotrace option to generate the access path.? Please note
that the optimizer has chosen the EMP2_EMP_ID index we just built.

07:54:52 orcl> ed
Wrote file afiedt.buf

? 1? select employee_id, a.department_id, b.department_name
? 2? from
? 3? hr.departments b,? hr.employees2 a
? 4? where
? 5? a.department_id = b.department_id
? 6* and employee_id = 203
07:55:14 ??7? /

Execution Plan
----------------------------------------------------------?????????????????????????????????????????????????????????????????????????
Plan hash value: 1466647341??????????????????????????????????????????????????????????????????????? ?????????????????????????????????
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
--------------------------------------------------------------------------------------------???????????????????????????????????????
| Id? | Operation??????????????????? | Name??????? | Rows? | Bytes | Cost (%CPU)| Time???? |???????????????????????????????????????
--------------------------------------------------------------------------------------------???????????????????????????????????????
|?? 0 | SELECT STATEMENT???????????? |???????????? |???? 1 |??? 25 |???? 5?? (0)| 00:00:01 |???????????????????????????????????????
|?? 1 |? NESTED LOOPS??????????????? |???????????? |???? 1 |??? 25 | ????5?? (0)| 00:00:01 |???????????????????????????????????????
|?? 2 |?? TABLE ACCESS BY INDEX ROWID| EMPLOYEES2? |???? 1 |???? 9 |???? 4?? (0)| 00:00:01 |???????????????????????????????????????
|*? 3 |??? INDEX RANGE SCAN????????? | EMP2_EMP_ID |???? 1 |?????? |???? 3?? (0)| 00:00:01 |???????????????????????????????????????
|?? 4 |?? TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |???? 1 |??? 16 |???? 1?? (0)| 00:00:01 |???????????????????????????????????????
|*? 5 |??? INDEX UNIQUE SCAN???????? | DEPT_ID_PK? |???? 1 |?????? |???? 0?? (0)| 00:00:01 |???????????????????????????????????????
--------------------------------------------------------------------------------------------???????????????????????????????????????
?????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????

Dropping the original index.

07:55:17 orcl> drop index hr.emp2_emp_id;

Index dropped.

Building our virtual index using the NOSEGMENT clause.

07:59:12 orcl> create index hr.emp2_emp_id_virtual on hr.employees2(employee_id) nosegment;

Index created.

Setting the hidden startup parameter "_use_nosegment_indexes" to TRUE so that our
session will recognize our new virtual index.

08:00:09 orcl> alter session set "_use_nosegment_indexes" = true;

Running our statement again to see if it will use our new virtual index.? Check outthe access path below.? The optimizer has chosen our virtual index.

? 1? select employee_id, a.department_id, b.department_name
? 2? from
? 3? hr.departments b,? hr.employees2 a
? 4? where
? 5? a.department_id = b.department_id
? 6* and employee_id = 203

Execution Plan
----------------------------------------------------------??????????????????? ??????????????????????????????????????????????????????
Plan hash value: 2516110069????????????????????????????????????????????????????????????????????????????????????????????????????????
??????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????
----------------------------------------------------------------------------------------------------???????????????????????????????
| Id? | Operation??????????????????? | Name?????????????? ?| Rows? | Bytes | Cost (%CPU)| Time???? |???????????????????????????????
----------------------------------------------------------------------------------------------------???????????????????????????????
|?? 0 | SELECT STATEMENT???????????? |????????? ???????????|???? 1 |??? 25 |???? 3?? (0)| 00:00:01 |???????????????????????????????
|?? 1 |? NESTED LOOPS??????????????? |???????????????????? |???? 1 |??? 25 |???? 3?? (0)| 00:00:01 |???????????????????????????????
|?? 2 |?? TABLE ACCESS BY INDEX ROWID| EMPLOYEES2????????? |???? 1 |???? 9 |???? 2?? (0)| 00:00:01 |???????????????????????????????
|*? 3 |??? INDEX RANGE SCAN????????? | EMP2_EMP_ID_VIRTUAL |???? 1 |?????? |???? 1?? (0)| 00:00:01 |???????????????????????????????
|?? 4 |?? TABLE ACCESS BY INDEX ROWID| DEPARTMENTS???????? |???? 1 |??? 16 |???? 1?? (0)| 00:00:01 |???????????????????????????????
|*? 5 |??? INDEX UNIQUE SCAN???????? | DEPT_ID_PK????????? |???? 1 |?????? |???? 0?? (0)| 00:00:01 |???????????????????????????????
----------------------------------------------------------------------------------------------------???????????????????????????????
??????

Setting the "_use_nosegment_indexes? to FALSE.? Note that the optimizer did NOTchoose the virtual index.

08:01:09 orcl> alter session set "_use_nosegment_indexes" = false;

Session altered.

08:01:33 orcl> select employee_id, a.department_id, b.department_name
08:01:47?? 2? from
08:01:47?? 3? hr.departments b,? hr.employees2 a
08:01:47?? 4? where
08:01:47?? 5? a.department_id = b.department_id
08:01:47?? 6? and employee_id = 203;

Execution Plan
----------------------------------------------------------??????????????????????????????? ??????????????????????????????????????????
Plan hash value: 2641883601????????????????????????????????????????????????????????????????????????????????????????????????????????
??????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????
--------------------------------------------------------------------------------------------???????????????????????????????????????
| Id? | Operation??????????????????? | Name??????? | Rows? | Bytes | Cost (%CPU)| Time???? |???????????????????????????????????????
--------------------------------------------------------------------------------------------???????????????????????????????????????
|?? 0 | SELECT STATEMENT???????????? |???????????? |???? 1 |??? 25 |?? 818?? (3)| 00:00:10 |???????????????????????????????????????
|?? 1 |? NESTED LOOPS??????????????? |???????????? |???? 1 |??? 25 |?? 818?? (3)| 00:00:10 |???????????????????????????????????????
|*? 2 |?? TABLE ACCESS FULL????????? | EMPLOYEES2 ?|???? 1 |???? 9 |?? 817?? (3)| 00:00:10 |???????????????????????????????????????
|?? 3 |?? TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |???? 1 |??? 16 |???? 1?? (0)| 00:00:01 |???????????????????????????????????????
|*? 4 |??? INDEX UNIQUE SCAN???????? | DEPT_ID_PK? |???? 1 |?????? |???? 0?? (0)| 00:00:01 |???????????????????????????????????????
--------------------------------------------------------------------------------------------?

Building another virtual index using the NOSEGMENT clause.? Note that Oracle returns an
error stating that the column is already indexed.

08:17:23 orcl>? create index hr.emp3_emp_id_virtual on hr.employees2(employee_id) nosegment;
create index hr.emp3_emp_id_virtual on hr.employees2(employee_id) nosegment
????????????????????????????????????????????????????? *
ERROR at line 1:
ORA-01408: such column list already indexed

Reactivating our session to begin recognizing virtual indexes.?

08:19:01 orcl> alter session set "_use_nosegment_indexes" = true;
Session altered.

Building a regular B-TREE index on the same column.? Although Oracle didn?t allow us to create twovirtual indexes on the same column, it did allow us to create one standard index and one virtual indexon the same column.08:19:12 orcl> create index hr.emp2_emp_id_non_virtual on hr.employees2(employee_id);Index created.

Running the statement again.? The optimizer chose the virtual index over the standard indexcontaining data!?? Interesting!
1? select employee_id, a.department_id, b.department_name
? 2? from
? 3? hr.departments b,? hr.employees2 a
? 4? where
? 5? a.department_id = b.department_id
? 6* and employee_id = 203

Execution Plan
----------------------------------------------------------?????????????????????????????????????????????????????????????????????????
Plan hash value: 2516110069????????????????????????????????????????????????????????????????????????????????????????????????????????
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
----------------------------------------------------------------------------------------------------???????????????????????????????
| Id? | Operation??????????????????? | Name??????????????? | Rows? | Bytes | Cost (%CPU)| Time???? |???????????????????????????????
----------------------------------------------------------------------------------------------------???????????????????????????????
|?? 0 | SELECT STATEMENT???????????? |???????????????????? |???? 1 |??? 25 |???? 3?? (0)| 00:00:01 |????????????????????? ??????????
|?? 1 |? NESTED LOOPS??????????????? |???????????????????? |???? 1 |??? 25 |???? 3?? (0)| 00:00:01 |???????????????????????????????
|?? 2 |?? TABLE ACCESS BY INDEX ROWID| EMPLOYEES2????????? |???? 1 |???? 9 |???? 2?? (0)| 00:00:01 |??????????? ????????????????????
|*? 3 |??? INDEX RANGE SCAN????????? | EMP2_EMP_ID_VIRTUAL |???? 1 |?????? |???? 1?? (0)| 00:00:01 |???????????????????????????????
|?? 4 |?? TABLE ACCESS BY INDEX ROWID| DEPARTMENTS???????? |???? 1 |??? 16 |???? 1?? (0)| 00:00:01 |? ??????????????????????????????
|*? 5 |??? INDEX UNIQUE SCAN???????? | DEPT_ID_PK????????? |???? 1 |?????? |???? 0?? (0)| 00:00:01 |???????????????????????????????
----------------------------------------------------------------------------------------------------???????????????????????????????
?????

Setting the _use_nosegment_indexes parameter to false to turn it off for our session.

08:20:00 orcl>? alter session set "_use_nosegment_indexes" =false;
Session altered.

Running the statement again.? The optimizer chose the standard index over the virtual index.It looks like Oracle will use the virtual index if you have the parameter set to TRUE.

08:20:31 orcl> select employee_id, a.department_id, b.department_name
08:20:41?? 2? from
08:20:41?? 3? hr.departments b,? hr.employees2 a
08:20:41?? 4? where
08:20:41?? 5? a.department_id = b.department_id
08:20:41?? 6? and employee_id = 203;
Execution Plan
----------------------------------------------------------?????????????????????????????????????????????????????????????????????????
Plan hash value: 4005350841??????????????????????????????????????????????????????????????????????????????????????????????????? ?????
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
--------------------------------------------------------------------------------------------------------???????????? ???????????????
| Id? | Operation??????????????????? | Name??????????????????? | Rows? | Bytes | Cost (%CPU)| Time???? |???????????????????????????
--------------------------------------------------------------------------------------------------------?? ?????????????????????????
|?? 0 | SELECT STATEMENT???????????? |???????????????????????? |???? 1 |??? 25 |???? 5?? (0)| 00:00:01 |???????????????????????????
|?? 1 |? NESTED LOOPS??????????????? |???????????????????????? |???? 1 |??? 25 |???? 5?? (0)| 00:00:01 |???????????????????????????
|?? 2 |?? TABLE ACCESS BY INDEX ROWID| EMPLOYEES2????????????? |???? 1 |???? 9 |???? 4?? (0)| 00:00:01 |???????????????????????????
|*? 3 |??? INDEX RANGE SCAN????????? | EMP2_EMP_ID_NON_VIRTUAL |???? 1 |?????? |???? 3 ??(0)| 00:00:01 |???????????????????????????
|?? 4 |?? TABLE ACCESS BY INDEX ROWID| DEPARTMENTS???????????? |???? 1 |??? 16 |???? 1?? (0)| 00:00:01 |???????????????????????????
|*? 5 |??? INDEX UNIQUE SCAN???????? | DEPT_ID_PK????????????? |???? 1 |???? ??|???? 0?? (0)| 00:00:01 |???????????????????????????
--------------------------------------------------------------------------------------------------------???????????????????????????
?????

Rebuilding the virtual index.? Notice that the error message states that
you can not alter a ?fake? index.

08:21:03 orcl> alter index hr.emp2_emp_id_non_virtual rebuild;
Index altered.
08:21:39 orcl> alter index hr.emp2_emp_id_virtual rebuild;
alter index hr.emp2_emp_id_virtual rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index

Executing DBMS_STATS to gather statistics on both the virtual and standard index.?? I haverun tests with statistics and without and it does seem to affect virtual index access paths.
?
08:21:55 orcl>
08:21:57 orcl>
08:21:57 orcl> exec dbms_stats.gather_index_stats('HR', 'EMP2_EMP_ID_NON_VIRTUAL');
PL/SQL procedure successfully completed.
08:23:10 orcl> exec dbms_stats.gather_index_stats('HR', 'EMP2_EMP_ID_VIRTUAL');
PL/SQL procedure successfully completed.

Looking for information on indexes built on the EMPLOYEES2 table.? Oracle returns a row for the standard index but not the virtual index.

08:20:31 orcl> select index_name, last_analyzed from dba_indexes where
? 2* table_name = 'EMPLOYEES2'
INDEX_NAME???????????????????? LAST_ANAL???????????????????????????????????????????????????????????????????????????????????????????
------------------------------ ---------????????????????????? ??????????????????????????????????????????????????????????????????????
EMP2_EMP_ID_NON_VIRTUAL??????? 31-MAY-07???????????????????????????????????????????????????????????????????????????????????????????

Determining f we can find the virtual index in DBA_SEGMENTS.? No success.

08:26:09 orcl> select segment_name, segment_type from dba_segments where segment_name like 'EMP2%';
SEGMENT_NAME???????? SEGMENT_TYPE??????????????????????????????????????????????????????????????????????????????????????????????? ???
-------------------- ------------------????????????????????????????????????????????????????????????????????????????????????????????
EMP2_EMP_ID_NON_VIRT INDEX???????????????????????????????????????????????????????????????????????????????????????????? ?????????????
UAL????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???

Looking for the the virtual index in DBA_OBJECTS.? Finally, we find some sort of evidence that the
virtual index exists in the database!
??????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
08:30:21 orcl> col object_name for a30
08:30:29 orcl> r
? 1? select object_name, object_type, created, status, temporary
? 2* from dba_objects where object_name like 'EMP2%'
OBJECT_NAME??????????????????? OBJECT_TYPE???????? CREATED?? STATUS? T??????????????????????????????? ??????????????????????????????
------------------------------ ------------------- --------- ------- -?????????????????????????????????????????????????????????????
EMP2_EMP_ID_NON_VIRTUAL??????? INDEX?????????????? 31-MAY-07 VALID?? N????????????????????? ????????????????????????????????????????
EMP2_EMP_ID_VIRTUAL??????????? INDEX?????????????? 31-MAY-07 VALID?? N?????????????????????????????????????????????????????????????
??????????????????????????????????????????????????????????????????????????????????
08:31:01 orcl> spool off

Verifying Physical Standby

2009-06-29T12:17:00.000-07:00

Verifying physical standby:

-- on Standby --
Alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS';SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
--

select database_role, open_mode, switchover_status from v$database ;

Detailed Approach to check physical standby issues:

Steps:
1. Physical standby database archivelogs
Count the existing archived logs in the standby database:
select count(*) from v$archived_log;
Then archive the current log on the primary database:
alter system archive log current;
Then, count the archived logs on the standby database again. It should have been increased by one.
2. Level of synchronization
To see if there is a problem with the archiving destinations (such as that destionation that transmits logs), quere v$archive_dest_status on primary and standby.
set lines 120
select dest_id, status, error from v$archive_dest_status;
select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status where archived_thread# <> 0;
3. Show received logs
On a physical standby database, the received archived redo logs can be displayed like this:
select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log;
4. Show applied archived redo log
select thread#, sequence#, first_change#, next_change# from v$log_history;
5. Show messages
Use v$dataguard_status to display messages. dest_id refers to what is configured with log_archive_dest_n.
select message from v$dataguard_status where dest_id = 2;
The query was executed on the primary database, and it shows that the destination 2 cannot deliver its logs.
ARCH: Error 12535 Creating archive log file to 'to_standby' ARCH: Error 12535 Creating archive log file to 'to_standby' ARCH: Error 12535 Creating archive log file to 'to_standby'
Broken network connections:
If the network connection is broken, the standby database writes RFS: Possible network disconnect with primary database. The primary
database writes: Network asynch I/O wait error 3114 log 3 service 'to_standby'.

6. Archived logs that are not transmitted
The following query assumes that the archives are locally stored on destination 1 and sent to the remote server on destionation 2.
set lines 120
select substr(local.name,1,50) "Archive Name", case when remote.sequence# is null then 'NOT TRANSMITTED' else 'transmitted' end, local.sequence#, local.thread#from (select * from v$archived_log where dest_id = 1) local left join (select * from v$archived_log where dest_id = 2) remote on local.sequence# = remote.sequence# and local.thread# = remote.thread# order by local.sequence#;

6. Is standby database performing managed recovery
select process, status from v$managed_standby;
If there is a MRP or a MRP0 process, then the database is performing managed recovery.

7. Monitoring the recovery process
select process, status, thread#, sequence#, block#, blocks from v$managed_standby;

Steps to rename a RAC database

2009-06-29T12:14:00.001-07:00

Steps to Rename RAC Database
Assumption:
Two Node RAC; Database name orcl and Instance orcl1 and orcl2 running on HostA and HostB
Name to be changed: Databse - Prod and instances prod1 and prod2
Steps:
a. From the instance orcl1, Backup the control file to trace. (Alter database backup controlfile to trace; )
b. Create pfile from the spfile (Create pfile=<’path/filename’> from spfile; )
c. Check the online redo-log files name, path and size on each nodes.
d. Take the complete backup of the database (To be towards safer side ).
———————————————————————————————
1. ) Shutdown the database using srvctl
$ srvctl stop database -d orcl
2. ) On one of the nodes, say on HostA,
Edit the pfile ( Created earlier), and change the following parameters.
Cluster_database=False
db_name=prod
Also, change all the occurances of orcl to prod.., Change the path of the Control_files..etc., as required.
Save the file as $ORACLE_HOME/dbs/initprod1.ora
3. ) Startup the database to nomount state by using the pfile.
4. ) Edit the Control file script , which should look as below. Make sure the script contains online redo-logs of instance orcl1 only. Run this script from SQL prompt in nomount state.
SQL> CREATE CONTROLFILE set DATABASE “PROD“ RESETLOGS NOARCHIVELOGMAXLOGFILES 192MAXLOGMEMBERS 3MAXDATAFILES 1024MAXINSTANCES 32MAXLOGHISTORY 292LOGFILEGROUP 1 ‘/u03/oradata/redo01.log’ SIZE 50M,GROUP 2 ‘/u03/oradata/redo02.log’ SIZE 50MDATAFILE‘/u03/oradata/system01.dbf’,‘/u03/oradata/undotbs01.dbf’,‘/u03/oradata/sysaux01.dbf’,‘/u03/oradata/users01.dbf’,‘/u03/oradata/example01.dbf’,‘/u03/oradata/undotbs02.dbf’CHARACTER SET WE8ISO8859P1;
ALTER DATABASE ADD LOGFILE THREAD 2GROUP 3 ‘/u03/oradata/redo03.log’ SIZE 50M REUSE,GROUP 4 ‘/u03/oradata/redo04.log’ SIZE 50M REUSE;
ALTER DATABASE ENABLE PUBLIC THREAD 2;
ALTER DATABASE OPEN RESETLOGS;
5. ) Change the init.ora parameter Cluster_database=False back to Cluster_database=True.
6.) Create spfile from pfile and place it in the shared location as it was earlier.
SQL> Create spfile=’/spfileprod.ora’ from pfile;
Shutdown immediate and start the instance with spfile
7. ) Start the second instance on on NodeB with the new spfile. (From sql prompt)
8. ) Remove the ORCL instance from CRS.
srvctl remove database -d orcl
9. ) Register PROD isntance with the CRS.
srvctl add database -d prod -o $ORACLE_HOMEsrvctl add instance -d prod -i prod1 -n NodeAsrvctl add instance -d prod -i prod2 -n NodeB
10. ) Change the instance names in the tnsnames.ora and listener.ora files.

Online Redefinition

2009-06-29T12:12:00.000-07:00

online redefinition:

-- Table_name: DPO
-- Step 1. Backup DDL and confirm online_redef.-- Extract table DDL and save it as a backup.-- Confirm that the table is a candidate for online reorganization.

exec dbms_redefinition.can_redef_table('CW','DPO',DBMS_REDEFINITION.CONS_USE_PK);

-- Step 2. Build Interim table and make sure to "enable Row Movement". (add “_2” to the table_name)

CREATE TABLE CW.DPO_2 ( CWORDERID VARCHAR2(16 BYTE), ORDER_NUMBER VARCHAR2(30 BYTE), ORDER_VERSION VARCHAR2(2 BYTE), ORDER_EXPEDITE VARCHAR2(1 BYTE), ORDER_TYPE_SERVICE VARCHAR2(4 BYTE), RELATEDORDERTYPE VARCHAR2(4 BYTE), BILL_NAME VARCHAR2(25 BYTE), BILL_SECONDARY_NAME VARCHAR2(25 BYTE), ORDER_CREATION_DATE DATE, QCCREFLOWCOUNT VARCHAR2(3 BYTE) ) TABLESPACE OMS_DATA PCTFREE 40 PARTITION BY RANGE (ORDER_CREATION_DATE) ( PARTITION P_DPO_AUG_2007 VALUES LESS THAN (to_date('01-SEP-2007','DD-MON-YYYY')) TABLESPACE OMSE_AUG_2007_DATA, PARTITION P_DPO_SEP_2007 VALUES LESS THAN (to_date('01-OCT-2007','DD-MON-YYYY')) TABLESPACE OMSE_SEP_2007_DATA, PARTITION P_DPO_NOV_2008 VALUES LESS THAN (to_date('01-DEC-2008','DD-MON-YYYY')) TABLESPACE OMSE_NOV_2008_DATA, PARTITION P_DPO_DEC_2008 VALUES LESS THAN (to_date('01-JAN-2009','DD-MON-YYYY')) TABLESPACE OMSE_DEC_2008_DATA, PARTITION P_OVERFLOW VALUES LESS THAN (MAXVALUE) TABLESPACE OMSE_OVERFLOW_DATA ) NOCOMPRESS NOCACHE NOPARALLEL MONITORING ENABLE ROW MOVEMENT;

-- Step 3. Start Redefinition

begin dbms_redefinition.start_redef_table('CW','DPO','DPO_2'); end; /

-- Step 4. Automatically rebuild all table dependencies (indexes, triggers and so forth)

exec dbms_redefinition.copy_table_dependents('CW','DPO','DPO_2', TRUE, TRUE, TRUE, FALSE );
-- Step 5. Sync Interim table

exec dbms_redefinition.sync_interim_table('CW','DPO','DPO_2');

-- Step 6. Complete the online re-organization and make sure the grants are given to users/Roles.

begin dbms_redefinition.finish_redef_table('CW','DPO','DPO_2'); end; /

GRANT SELECT ON CW.DPO TO E073813; GRANT SELECT ON CW.DPO TO OMSE_RO; GRANT SELECT ON CW.DPO TO OMSE_EVM_READ;
-- Step 7. Drop the interim table

drop table CW.DPO_2;

Moving datafiles between ASM Diskgroups - Version 10g.

2009-06-29T12:11:00.001-07:00

RMAN is the prefered tool to move datafiles from one Diskgroup to another, or, to move files from traditional mount points(filesystem) to ASM diskgroups. Alternatively, DBMS_FILE_TRANSFER can also be used for this purpose.

RMAN is critical to Automatic Storage Management and is responsible for tracking the ASM filenames and for deleting obsolete ASM files. Since ASM files cannot be accessed through normal operating system interfaces, RMAN is the preferred means of copying ASM files.

Lets take 2 different scenarioes, 1. move a datafile from one diskgroup to another and second one is to move the datafile from filesystem mount point to ASM diskgroup.

Note: The steps provided here assume that the database is open and in Archivelog mode and not for system or sysaux datafiles.
For SYSTEM and SYSAUX datafiles, please see my next post.

Steps:

1) Identify the data file to be moved.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES:

FILE_NAME
-------------------
+DATA/orcl/datafile/sysaux01.dbf
+DATA/orcl/datafile/undotbs01.dbf
+DATA/orcl/datafile/system01.dbf
/oradb/u0001/orcl/test_tbsp.dbf <=== Move to DATA Diskgroup.
+REDO/orcl/datafile/users.256.565313879 <=== Move to DATA Diskgroup.

2) Identify the diskgroup on to which the file has to be moved.

SQL> SELECT GROUP_NUMBER, NAME FROM V$ASM_DISKGROUP;

GROUP_NUMBER NAME
------------ ---------
1 DATA
2 REDO

3) Take the datafiles offline.

SQL> ALTER DATABASE DATAFILE '+REDO/orcl/datafile/users.256.565313879' OFFLINE;
SQL> ALTER DATABASE DATAFILE '/oradb/u0001/orcl/test_tbsp.dbf' OFFLINE;

4) Now Copy the files to ASM Diskgroup DATA using RMAN.

$ rman target /

connected to target database: ORCL (DBID=1089529226)

RMAN> COPY DATAFILE '+REDO/orcl/datafile/users.256.565313879' TO '+DATA';

Starting backup at 03-AUG-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+REDO/orcl/datafile/users.256.565313879
output filename=+DATA/orcl/datafile/users.259.565359071 tag=TAG20050803T121109 recid=2 stamp=565359071
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-AUG-05

RMAN> COPY DATAFILE '/oradb/u0001/orcl/test_tbsp.dbf' TO '+DATA';

Starting backup at 03-AUG-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/oradb/u0001/orcl/test_tbsp.dbf
output filename=+DATA/orcl/datafile/test_tbsp.260.675369081 tag=TAG20050803T121109 recid=2 stamp=675369081
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-AUG-05

5) Rename the file to point to new location.

RMAN> run
{
set newname for datafile '+REDO/orcl/datafile/users.256.565313879' to '+DATA/orcl/datafile/users.259.565359071' ;
set newname for datafile '/oradb/u0001/orcl/test_tbsp.dbf' to '+DATA/orcl/datafile/test_tbsp.260.675369081' ;
switch datafile all;
}

OR use alter database and then switch datafile.

SQL> alter database rename file '+REDO/orcl/datafile/users.256.565313879' to '+DATA/orcl/datafile/users.259.565359071' ;
SQL> alter database rename file '/oradb/u0001/orcl/test_tbsp.dbf' to '+DATA/orcl/datafile/test_tbsp.260.675369081' ;

6) If needed, recover the files.

SQL> RECOVER DATAFILE '+DATA/orcl/datafile/users.259.565359071' ;
SQL> RECOVER DATAFILE '+DATA/orcl/datafile/test_tbsp.260.675369081' ;

7) Bring the files online.

SQL> ALTER DATABASE DATAFILE '+DATA/orcl/datafile/users.259.565359071' ONLINE;
SQL> ALTER DATABASE DATAFILE '+DATA/orcl/datafile/test_tbsp.260.675369081' ONLINE;

8) Verify the newlocation of the files.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
-------------------
+DATA/orcl/datafile/users.259.565359071
+DATA/orcl/datafile/sysaux01.dbf
+DATA/orcl/datafile/undotbs01.dbf
+DATA/orcl/datafile/system01.dbf
+DATA/orcl/datafile/test_tbsp.260.675369081'

9) Delete the file from its original location

ASM:SQL> ALTER DISKGROUP REDO DROP FILE '+REDO/orcl/datafile/users.256.565313879 ' ;

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

For System and Sysaux an approach similar to the one given below can be used.

A. Create a Copy of datafile in target Diskgroup:

RMAN> backup as copy tablespace system format '+DATA';
RMAN> backup as copy tablespace sysaux format '+DATA';

B. Then shutdown the database on all nodes and restart one instance to a mounted state

RMAN> shutdown immediate;
RMAN> startup mount;

C. switch the datafiles to the copy

RMAN> switch tablespace system to copy;
RMAN> switch tablespace sysaux to copy;

3. Recover the changes made to these tablespaces;

RMAN> recover database;

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

Moving/Multiplexing control files when ASM is involved.

Assume there is only one controlfile: +DATA_01/db_name/controlfile/current.256.636907509, and you want to move and multiplex this to other diskgroups (DATA_01, DATA-03 and DATA_04), then an approach similar to one given below can be used.

First Backup spfile or create a pfile and do a show parameters control_file on sqlplus.

1) shutdown database on all nodes; ensure no instance is mounting the database.

2) startup nomount one instance

3) alter system set control_files=
'+DATA_02/db_name/controlfile/control01.ctl',
'+DATA_03/db_name/controlfile/control02.ctl',
'+DATA_04/db_name/controlfile/control03.ctl' scope=spfile sid='*';

4) rman target /

5) restore controlfile from '+DATA_01/db_name/controlfile/current.256.636907509';

OR

restore controlfile to '+DATA_02/db_name/controlfile/control01.ctl' from '+DATA_01/db_name/controlfile/current.256.636907509';

Repeat for other controlfiles

restore controlfile to '+DATA_03/db_name/controlfile/control02.ctl' from '+DATA_01/db_name/controlfile/current.256.636907509';
restore controlfile to '+DATA_04/db_name/controlfile/control03.ctl' from '+DATA_01/db_name/controlfile/current.256.636907509';

6) shutdown (immediate)

7) srvctl start database -d

DataGaurd configuration

2009-06-29T12:10:00.000-07:00

Data Guard Configuration Steps.

Here are the steps on how to configure Dataguard for an existing Primary - Physical Standby. Assume you have a healthy Primary and Standy databases running on 2 different servers.

Primary host name : primary_server, ORACLE_SID on Primary : PRIM
Standby host name : secondary_server, ORACLE_SID on Standby host: STBY

Prerequisites:

1. Both primary and Standby exists and working fine without issues.
2. DB_BROKER_START init.ora parameter

On both primary and standby sites, change the initialization parameter, dg_broker_start to enable the Data guard broker.

SQL> alter system set dg_broker_start=TRUE scope=both ;

3. SPFILE. Make sure both the instances are started with SPFILE.

4. TNS/Net parameters: Please have the TNS entries of primary, Standby databases on both the servers, we assume they are PRIM, STBY for Primary and Standby respectively.

Creating the configuration:

On primary site, start command line interface of the Dataguard Broker.(dgmgrl)

>dgmgrl
DGMGRL> connect sys/passwd@PRIM

DGMGRL> create configuration 'prim_dg_config' as primary database is 'PRIM' connect identifier is PRIM ;

Configuration "prim_dg_config" created with primary database "PRIM"

DGMGRL> add database 'STBY' as connect identifier is STBY maintained as physical ;

Database "TDL2Y" added

DGMGRL> show configuration ;

Configuration Name: prim_dg_config

Enabled: NO

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases: PRIM - Primary database

STBY - Physical standby database
Current status for "prim_dg_config":DISABLED

DGMGRL> enable configuration ;

Enabled.

DGMGRL> show configuration

Configuration Name: prim_dg_config
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases: PRIM - Primary database
STBY - Physical standby database Current status for "prim_dg_config":DISABLED

Current status for "prim_dg_config":

Warning: ORA-16607: one or more databases have failed

DGMGRL> show database 'PRIM' ;

Database Name: TDL2P

Role: PRIMARY

Enabled: YES

Intended State: ONLINE

Instance(s): TDL2P
Current status for "TDL2P":
Error: ORA-16778: redo transport error for one or more databases

-- SET the LOCAL_LISTENER='' on both primary and standby fixed.

DGMGRL> show database 'TDL2P' ;

Database Name: TDL2P Role: PRIMARY Enabled: YES Intended State: ONLINE Instance(s): TDL2P
Current status for "TDL2P":
Warning: ORA-16610: command 'EDIT DATABASE TDL1Y SET PROPERTY' in progress

-- wait, for 2 min

DGMGRL> show database 'TDL2P' ;

Database Name: TDL2P Role: PRIMARY Enabled: YES Intended State: ONLINE Instance(s): TDL2P
Current status for "TDL2P":
SUCCESS

alter system set log_archive_dest_2 = 'SERVICE=TDL2Y ARCH DELAY=5' scope=both ;alter system set log_archive_dest_state_2='enable' scope=both ;

Switchover:

Primary is PRIM and standby STBY.

Connect to primary_server.

> dgmgl
DGMGRL> switchover to 'STBY' ;
Performing switchover NOW,

please wait...

Operation requires shutdown of instance "PRIM" on database "PRIM"

Shutting down instance "TDL1P"...ORA-01109: database not open

Database dismounted.ORACLE instance shut down.Operation requires shutdown of instance "TDL1Y" on database "TDL1Y"Shutting down instance "TDL1Y"...ORA-01109: database not open

Database dismounted.ORACLE instance shut down.Operation requires startup of instance "TDL1P" on database "TDL1P"Starting instance "TDL1P"...Unable to connect to databaseORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.You are no longer connected to ORACLEPlease connect again.Unable to start instance "TDL1P"You must start instance "TDL1P" manuallyOperation requires startup of instance "TDL1Y" on database "TDL1Y"You must start instance "TDL1Y" manuallySwitchover succeeded, new primary is "TDL1Y"
DGMGRL>

Failover:

check the configuration on 2 nodes.

tidlprdb01cdc(TDL1P) /tidl01/u0001/oracle/admin/TDL1P/bdump>dgmgrlDGMGRL for Solaris: Version 10.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.DGMGRL> connect sys@TDL1PPassword:Connected.DGMGRL>DGMGRL>DGMGRL>DGMGRL> show configuration ;
Configuration Name: tdb1p_dg_config Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: TDL1P - Primary database TDL1Y - Physical standby database
Current status for "tdb1p_dg_config":SUCCESS

tidlprdb02cdp(TDL1Y) /tidl02/u0002/oracle/admin/TDL1Y/bdump>dgmgrlDGMGRL for Solaris: Version 10.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.DGMGRL> connect sys@TDL1YPassword:Connected.DGMGRL> show configuration ;
Configuration Name: tdb1p_dg_config Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: TDL1P - Primary database TDL1Y - Physical standby database
Current status for "tdb1p_dg_config":SUCCESS
Primary is TDLP and standby TDL1Y. Connect to TDL1P server.

DGMGRL> failover to 'TDL1Y' ;

----

Issue #1:

Note:
If the TCP port numbers are different from default (1521), then set the LOCAL_LISTENER parameter on each database (primary and Standby to the respective listeners)

Other wise you get an error as
--
ORA-12541: TNS:no listenerPING[ARC0]: Heartbeat failed to connect to standby '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=ddtdrdb01cdc)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SLAB_XPT.TWCABLE.COM)(INSTANCE_NAME=SLAB)(SERVER=dedicated)))'. Error is 12541.
--

Issue #2:

DGMGRL> connect sys@TDL1YPassword:Connected.DGMGRL>DGMGRL>DGMGRL> show configuration ;
Configuration Name: tdl1p_dg_config Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: TDL1P - Physical standby database TDL1Y - Primary database
Current status for "tdl1p_dg_config":Warning: ORA-16608: one or more databases have warnings

>oerr ora 1660816608, 00000, "one or more databases have warnings"// *Cause: The Data Guard broker detected a warning status for one or// more databases.// *Action: Locate the database(s) with a warning status and correct it.

How to check ->
DGMGRL> show database "TDL1Y" ;
Database Name: TDL1Y Role: PRIMARY Enabled: YES Intended State: ONLINE Instance(s): TDL1Y
Current status for "TDL1Y":SUCCESS

DGMGRL> show database "TDL1P" ;
Database Name: TDL1P Role: PHYSICAL STANDBY Enabled: YES Intended State: ONLINE Instance(s): TDL1P
Current status for "TDL1P":Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

RESOLUTION: Remove the standby database and re-add to the configuration.
DGMGRL> remove database "TDL1P" ;Removed database "TDL1P" from the configuration
DGMGRL> add database 'TDL1P' as connect identifier is TDL1P maintained as physical ;Database "TDL1P" added
DGMGRL>DGMGRL> show configuration ;
Configuration Name: tdl1p_dg_config Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: TDL1Y - Primary database TDL1P - Physical standby database (disabled)
Current status for "tdl1p_dg_config":SUCCESS

Issue #3:

ORA-16047: DGID mismatch between destination setting and standby

Resolution:

Check log_archive_config.
on Primary: it should be set to Standby as
alter system set log_archive_config = 'dg_config=(TDL1Y)' scope=both ;

On Standby:
alter system set log_archive_config='dg_config=(TDL1P)' scope=both ;

check local_listener
As Mentioned in Issue#1, if the port numbers are not the default 1521, then, the local_listener need to be set to the correct names.

Activate a Physical Standby database

2009-06-29T12:09:00.000-07:00

Activate Standby database:

1. Stop redo log transport and redo apply.

On Primary,

alter system set log_archive_dest_stat1_ ='DEFER' scope=both ;

on Standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Activate the standby database.

SQL> select open_mode, database_role from v$database ;

OPEN_MODE DATABASE_ROLE---------- ----------------MOUNTED PHYSICAL STANDBY

SQL>SQL>SQL> alter database activate standby database ;

Database altered.

SQL> startup mount force ;
ORACLE instance started.
Total System Global Area 524288000 bytesFixed Size 2031352 bytesVariable Size 188743944 bytesDatabase Buffers 327155712 bytesRedo Buffers 6356992 bytesDatabase mounted.
SQL> alter database set standby database to maximize performance ;

Database altered.

SQL> alter database open ;

Database altered.

SQL> , database_role from v$database ;

OPEN_MODE DATABASE_ROLE---------- ----------------READ WRITE PRIMARY

Change the DBID and/or DBNAME using utility "nid".

startup mount
nid target=sys/password@OLD_DB_NAME dbname=NEW_DB_NAME

Example:

>nid target=sys/password@TDL2P dbname=TDL2P

DBNEWID: Release 10.2.0.3.0 - Production on Tue Nov 11 14:14:13 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database ADMQA (DBID=345737326)
Connected to server version 10.2.0
Control Files in database: +TDL2P_REDO_01/tdl2p/controlfile/current.257.669824119 +TDL2P_REDO_02/tdl2p/controlfile/current.256.669824119 +TDL2P_REDO_01/tdl2p/controlfile/current.263.669824121
Change database ID and database name ADMQA to TDL2P? (Y/[N]) => Y
Proceeding with operationChanging database ID from 345737326 to 1974265142Changing database name from ADMQA to TDL2P Control File +TDL2P_REDO_01/tdl2p/controlfile/current.257.669824119 - modified Control File +TDL2P_REDO_02/tdl2p/controlfile/current.256.669824119 - modified Control File +TDL2P_REDO_01/tdl2p/controlfile/current.263.669824121 - modified Datafile +TDL2P_DATA_01/tdl2p/datafile/system.259.669824149 - dbid changed, wrote new name Datafile +TDL2P_DATA_01/tdl2p/datafile/undots1.258.669824141 - dbid changed, wrote new name Datafile +TDL2P_DATA_01/tdl2p/datafile/undots1.262.669824141 - dbid changed, wrote new name Datafile +TDL2P_DATA_01/tdl2p/datafile/users.261.669824141 - dbid changed, wrote new name Datafile +TDL2P_DATA_01/tdl2p/datafile/admiral_data.260.669824149 - dbid changed, wrote new name Datafile +TDL2P_DATA_01/tdl2p/datafile/admiral_index.256.669824141 - dbid changed, wrote new name Datafile +TDL2P_DATA_01/tdl2p/datafile/hp_dbspi.264.669824141 - dbid changed, wrote new name Datafile +TDL2P_DATA_01/tdl2p/datafile/sysaux.257.669824141 - dbid changed, wrote new name Datafile +TDL2P_DATA_01/tdl2p/tempfile/temp.263.670514641 - dbid changed, wrote new name Control File +TDL2P_REDO_01/tdl2p/controlfile/current.257.669824119 - dbid changed, wrote new name Control File +TDL2P_REDO_02/tdl2p/controlfile/current.256.669824119 - dbid changed, wrote new name Control File +TDL2P_REDO_01/tdl2p/controlfile/current.263.669824121 - dbid changed, wrote new name Instance shut down
Database name changed to TDL2P.Modify parameter file and generate a new password file before restarting.Database ID for database TDL2P changed to 1974265142.All previous backups and archived redo logs for this database are unusable.Database is not aware of previous backups and archived logs in Recovery Area.Database has been shutdown, open database with RESETLOGS option.Succesfully changed database name and ID.DBNEWID - Completed succesfully.

2. Modify init.ora with the new_database_parameters like db_name, dump destinations, instance_name, db_unique_name etc.

3. Mount the database.

>r sqlsqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Dec 14 07:29:29 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: / as sysdbaConnected to an idle instance.

SQL> startup mountORACLE instance started.
Total System Global Area 629145600 bytesFixed Size 2032160 bytesVariable Size 352329184 bytesDatabase Buffers 268435456 bytesRedo Buffers 6348800 bytesORA-01103: database name 'TDL1P' in control file is not 'ADMIRAL'

Ignore the above error, as it is due to db_name is not being modified after "nid" command.
SQL> alter system set db_name='TDL2P' scope=spfile ;

System altered.

SQL> shutdown immediateORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mountORACLE instance started.
Total System Global Area 629145600 bytesFixed Size 2032160 bytesVariable Size 352329184 bytesDatabase Buffers 268435456 bytesRedo Buffers 6348800 bytesDatabase mounted.

SQL> alter database open resetlogs ;
Database altered.

Rebuilding a Corrupted OCR.

2009-06-29T12:08:00.000-07:00

STEPS:

Shutdown the Oracle Clusterware stack on all the nodes using command crsctl stop crs as root user.
Backup the entire Oracle Clusterware home.
Execute /install/rootdelete.sh on all nodes
Execute /install/rootdeinstall.sh on the node which is supposed to be the first node
The following commands should return nothing
ps -e | grep -i 'ocs[s]d'
ps -e | grep -i 'cr[s]d.bin'
ps -e | grep -i 'ev[m]d.bin'
Execute /root.sh on first node
After successful root.sh execution on first node Execute root.sh on the rest of the nodes of the cluster
Run vipca --
backup listener.ora and tnsnames.ora from asm_home and db_home and remove the original files.
From asm_home, Add the listener using netca. This may give errors if the listener.ora contains the entries already. If this is the case, move the listener.ora to /tmp from the $ORACLE_HOME/network/admin or from the $TNS_ADMIN directory if the TNS_ADMIN environmental is defined and then run netca. Add all the listeners that were added earlier.
Add the database to the OCR using the appropriate srvctl add database command as the user who owns the database. Please ensure that this is not run as root user
Add ASM, DB, Instance using approproate srvctl add commands.
using dbca, create the services or use srvctl add commands.
execute cluvfy stage -post crsinst -n node1,node2 ### Please ensure to replace node1,node2 with the node names of the cluster

1. Rebuilding OCR

node1(CRS) /home/oracle/cronnode1 >sudo su -root@node1:/ ] ls /esb02/u0001/oracle/product/crs/install/root*.sh/esb02/u0001/oracle/product/crs/install/rootdeinstall.sh
/esb02/u0001/oracle/product/crs/install/rootdeletenode.sh/esb02/u0001/oracle/product/crs/install/rootdelete.sh
root@node1:/ ] /esb02/u0001/oracle/product/crs/install/rootdelete.shShutting down Oracle Cluster Ready Services (CRS):Stopping resources.Error while stopping resources. Possible cause: CRSD is down. << issue with CRSStopping CSSD.Unable to communicate with the CSS daemon.Shutdown has begun. The daemons should exit soon.Checking to see if Oracle CRS stack is down...Oracle CRS stack is not running.Oracle CRS stack is down now.Removing script for Oracle Cluster Ready servicesUpdating ocr file for downgradeCleaning up SCR settings in '/etc/oracle/scls_scr'

root@node2:/ ] /esb02/u0001/oracle/product/crs/install/rootdelete.shShutting down Oracle Cluster Ready Services (CRS):Stopping resources.Error while stopping resources. Possible cause: CRSD is down.Stopping CSSD.Unable to communicate with the CSS daemon.Shutdown has begun. The daemons should exit soon.Checking to see if Oracle CRS stack is down...Oracle CRS stack is not running.Oracle CRS stack is down now.Removing script for Oracle Cluster Ready servicesUpdating ocr file for downgradeCleaning up SCR settings in '/etc/oracle/scls_scr'

root@node1:/ ] /esb02/u0001/oracle/product/crs/install/rootdeinstall.shRemoving contents from OCR mirror device2560+0 records in.2560+0 records out.Removing contents from OCR device2560+0 records in.2560+0 records out.

root@node1:/ ] ls /esb02/u0001/oracle/product/crs/root.sh/esb02/u0001/oracle/product/crs/root.shroot@node1:/ ]root@node1:/ ]root@node1:/ ] /esb02/u0001/oracle/product/crs/root.shWARNING: directory '/esb02/u0001/oracle/product' is not owned by rootWARNING: directory '/esb02/u0001/oracle' is not owned by rootWARNING: directory '/esb02/u0001' is not owned by rootChecking to see if Oracle CRS stack is already configured
Setting the permissions on OCR backup directorySetting up NS directoriesOracle Cluster Registry configuration upgraded successfullyWARNING: directory '/esb02/u0001/oracle/product' is not owned by rootWARNING: directory '/esb02/u0001/oracle' is not owned by rootWARNING: directory '/esb02/u0001' is not owned by rootSuccessfully accumulated necessary OCR keys.Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.node : node 1: node1 node1-priv node1node 2: node2 node2-priv node2Creating OCR keys for user 'root', privgrp 'system'..Operation successful.Now formatting voting device: /dev/oracle/vote_disk_01Now formatting voting device: /dev/oracle/vote_disk_02Now formatting voting device: /dev/oracle/vote_disk_03Format of 3 voting devices complete.Startup will be queued to init within 30 seconds.Adding daemons to inittabAdding daemons to inittabExpecting the CRS daemons to be up within 600 seconds.CSS is active on these nodes. node1CSS is inactive on these nodes. node2Local node checking complete.Run root.sh on remaining nodes to start CRS daemons.
root@node1:/ ] ps -eaf| grep d.bin oracle 1532014 1 0 21:39:14 - 0:00 /esb02/u0001/oracle/product/crs/bin/evmd.bin root 1556588 1 0 21:39:14 - 0:00 /esb02/u0001/oracle/product/crs/bin/crsd.bin reboot oracle 1564836 1675454 0 21:39:18 - 0:00 /esb02/u0001/oracle/product/crs/bin/ocssd.bin root 1622160 1704014 0 21:39:34 pts/1 0:00 grep d.bin

root@node2:/ ] /esb02/u0001/oracle/product/crs/root.shWARNING: directory '/esb02/u0001/oracle/product' is not owned by rootWARNING: directory '/esb02/u0001/oracle' is not owned by rootWARNING: directory '/esb02/u0001' is not owned by rootChecking to see if Oracle CRS stack is already configured
Setting the permissions on OCR backup directorySetting up NS directoriesOracle Cluster Registry configuration upgraded successfullyWARNING: directory '/esb02/u0001/oracle/product' is not owned by rootWARNING: directory '/esb02/u0001/oracle' is not owned by rootWARNING: directory '/esb02/u0001' is not owned by rootclscfg: EXISTING configuration version 3 detected.clscfg: version 3 is 10G Release 2.Successfully accumulated necessary OCR keys.Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.node : node 1: esbprdb01cdc esbprdb01cdc-priv esbprdb01cdcnode 2: node2 node2-priv node2clscfg: Arguments check out successfully.
NO KEYS WERE WRITTEN. Supply -force parameter to override.-force is destructive and will destroy any previous clusterconfiguration.Oracle Cluster Registry for cluster has already been initializedStartup will be queued to init within 30 seconds.Adding daemons to inittabAdding daemons to inittabExpecting the CRS daemons to be up within 600 seconds.CSS is active on these nodes. esbprdb01cdc node2CSS is active on all nodes.Waiting for the Oracle CRSD and EVMD to startOracle CRS stack installed and running under init(1M)Running vipca(silent) for configuring nodeappsThe given interface(s), "en6" is not public. Public interfaces should be used to configure virtual IPs.

root@node1:/ ] /esb02/u0001/oracle/product/crs/bin/vipcaroot@node1:/ ] /esb02/u0001/oracle/product/crs/bin/crs_stat -tName Type Target State Host------------------------------------------------------------ora....cdc.gsd application ONLINE ONLINE esbp...1cdcora....cdc.ons application ONLINE ONLINE esbp...1cdcora....cdc.vip application ONLINE ONLINE esbp...1cdcora....cdc.gsd application ONLINE ONLINE esbp...2cdcora....cdc.ons application ONLINE ONLINE esbp...2cdcora....cdc.vip application ONLINE ONLINE esbp...2cdc
Backups listener.ora and tnsnames.ora from both ASM and ORACLE_HOME locations.
node1(+ASM1)> cd /esb02/u0001/oracle/product/10.2.0/asm/network/adminnode1(+ASM1)> mv listener.ora listener.ora.bkpnode1(+ASM1)> mv tnsnames.ora tnsnames.ora.bkpnode1(+ASM1)> cd /esb02/u0001/oracle/product/10.2.0/db_1/network/admin
node1(+ASM1)> mv listener.ora listener.ora.bkpnode1(+ASM1)> mv tnsnames.ora tnsnames.ora.bkpnode1(+ASM1)> cd /esb02/u0001/oracle/product/10.2.0/db_1node2(+ASM1)> cd /esb02/u0001/oracle/product/10.2.0/asm/network/adminnode2(+ASM1)> mv listener.ora listener.ora.bkpnode2(+ASM1)> mv tnsnames.ora tnsnames.ora.bkpnode2(+ASM1)> cd /esb02/u0001/oracle/product/10.2.0/db_1/network/admin
node2(+ASM1)> mv listener.ora listener.ora.bkpnode2(+ASM1)> mv tnsnames.ora tnsnames.ora.bkpnode2(+ASM1)> cd /esb02/u0001/oracle/product/10.2.0/db_1

>ps -eaf| grep tnslsnr
oracle 549064 1 0 21:28:41 - 0:00 /esb02/u0001/oracle/product/10.2.0/asm/bin/tnslsnr LISTENER_node1 -inherit oracle 1036452 1507512 0 21:56:25 pts/1 0:00 grep tnslsnr
>kill -9 549064

node1(+ASM1)> /esb02/u0001/oracle/product/10.2.0/asm/bin/netca

Oracle Net Services Configuration:Configuring Listener:LISTENERDefault local naming configuration complete.node1...node2...Listener configuration complete.node1... Deleted listener: LISTENER_node1node2... Deleted listener: LISTENER_node2Oracle Net Services configuration successful. The exit code is 0

>crs_srat -t
Name Type Target State Host------------------------------------------------------------ora....DC.lsnr application ONLINE ONLINE esbp...1cdcora....cdc.gsd application ONLINE ONLINE esbp...1cdcora....cdc.ons application ONLINE ONLINE esbp...1cdcora....cdc.vip application ONLINE ONLINE esbp...1cdcora....DC.lsnr application ONLINE ONLINE esbp...2cdcora....cdc.gsd application ONLINE ONLINE esbp...2cdcora....cdc.ons application ONLINE ONLINE esbp...2cdcora....cdc.vip application ONLINE ONLINE esbp...2cdc
ADD ASM, DB and OTHER SERVICES
>srvctl add asm -n node1 -i +ASM1 -o /esb02/u0001/oracle/product/10.2.0/asm>srvctl add asm -n node2 -i +ASM2 -o /esb02/u0001/oracle/product/10.2.0/asm

>srvctl add database -d BPELP -o /esb02/u0001/oracle/product/10.2.0/db_1>srvctl add database -d UDDIP -o /esb02/u0001/oracle/product/10.2.0/db_1>srvctl add database -d DPWRP -o /esb02/u0001/oracle/product/10.2.0/db_1
>srvctl add instance -d BPELP -i BPELP1 -n node1>srvctl add instance -d BPELP -i BPELP2 -n node2
>srvctl add instance -d UDDIP -i UDDIP1 -n node1>srvctl add instance -d UDDIP -i UDDIP2 -n node2
>srvctl add instance -d DPWRP -i DPWRP1 -n node1>srvctl add instance -d DPWRP -i DPWRP2 -n node2

>cluvfy stage -post crsinst -n node1,node2

Performing post-checks for cluster services setup
Checking node reachability...Node reachability check passed from node "node1".
Checking user equivalence...User equivalence check passed for user "oracle".
Checking Cluster manager integrity...
Checking CSS daemon...Daemon status check passed for "CSS daemon".
Cluster manager integrity check passed.
Checking cluster integrity...
Cluster integrity check passed
Checking OCR integrity...
Checking the absence of a non-clustered configuration...All nodes free of non-clustered, local-only configurations.
Uniqueness check for OCR device passed.
Checking the version of OCR...OCR of correct Version "2" exists.
Checking data integrity of OCR...Data integrity check for OCR passed.
OCR integrity check passed.
Checking CRS integrity...
Checking daemon liveness...Liveness check passed for "CRS daemon".
Checking daemon liveness...Liveness check passed for "CSS daemon".
Checking daemon liveness...Liveness check passed for "EVM daemon".
Checking CRS health...CRS health check passed.
CRS integrity check passed.
Checking node application existence...
Checking existence of VIP node application (required)Check passed.
Checking existence of ONS node application (optional)Check passed.
Checking existence of GSD node application (optional)Check passed.
Post-check for cluster services setup was successful.

>crs_stat -tName Type Target State Host------------------------------------------------------------ora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.BPELP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.DPWRP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.UDDIP.db application OFFLINE OFFLINEora....SM1.asm application OFFLINE OFFLINEora....DC.lsnr application ONLINE ONLINE esbp...1cdcora....cdc.gsd application ONLINE ONLINE esbp...1cdcora....cdc.ons application ONLINE ONLINE esbp...1cdcora....cdc.vip application ONLINE ONLINE esbp...1cdcora....SM2.asm application OFFLINE OFFLINEora....DC.lsnr application ONLINE ONLINE esbp...2cdcora....cdc.gsd application ONLINE ONLINE esbp...2cdcora....cdc.ons application ONLINE ONLINE esbp...2cdcora....cdc.vip application ONLINE ONLINE esbp...2cdc >srvctl start asm -n node1>crs_stat -t <<- ASM came online.Name Type Target State Host------------------------------------------------------------ora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.BPELP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.DPWRP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.UDDIP.db application OFFLINE OFFLINEora....SM1.asm application ONLINE ONLINE esbp...1cdcora....DC.lsnr application ONLINE ONLINE esbp...1cdcora....cdc.gsd application ONLINE ONLINE esbp...1cdcora....cdc.ons application ONLINE ONLINE esbp...1cdcora....cdc.vip application ONLINE ONLINE esbp...1cdcora....SM2.asm application OFFLINE OFFLINEora....DC.lsnr application ONLINE ONLINE esbp...2cdcora....cdc.gsd application ONLINE ONLINE esbp...2cdcora....cdc.ons application ONLINE ONLINE esbp...2cdcora....cdc.vip application ONLINE ONLINE esbp...2cdc>srvctl start asm -n node2

>crs_stat -tName Type Target State Host------------------------------------------------------------ora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.BPELP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.DPWRP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.UDDIP.db application OFFLINE OFFLINEora....SM1.asm application ONLINE ONLINE esbp...1cdcora....DC.lsnr application ONLINE ONLINE esbp...1cdcora....cdc.gsd application ONLINE ONLINE esbp...1cdcora....cdc.ons application ONLINE ONLINE esbp...1cdcora....cdc.vip application ONLINE ONLINE esbp...1cdcora....SM2.asm application ONLINE ONLINE esbp...2cdcora....DC.lsnr application ONLINE ONLINE esbp...2cdcora....cdc.gsd application ONLINE ONLINE esbp...2cdcora....cdc.ons application ONLINE ONLINE esbp...2cdcora....cdc.vip application ONLINE ONLINE esbp...2cdc

>srvctl start database -d BPELPPRKP-1001 : Error starting instance BPELP1 on node node1CRS-0215: Could not start resource 'ora.BPELP.BPELP1.inst'.PRKP-1001 : Error starting instance BPELP2 on node node2CRS-0215: Could not start resource 'ora.BPELP.BPELP2.inst'.

>crs_stat -tName Type Target State Host------------------------------------------------------------ora....P1.inst application ONLINE OFFLINEora....P2.inst application ONLINE OFFLINEora.BPELP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.DPWRP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.UDDIP.db application OFFLINE OFFLINEora....SM1.asm application ONLINE ONLINE esbp...1cdcora....DC.lsnr application ONLINE ONLINE esbp...1cdcora....cdc.gsd application ONLINE ONLINE esbp...1cdcora....cdc.ons application ONLINE ONLINE esbp...1cdcora....cdc.vip application ONLINE ONLINE esbp...1cdcora....SM2.asm application ONLINE ONLINE esbp...2cdcora....DC.lsnr application ONLINE ONLINE esbp...2cdcora....cdc.gsd application ONLINE ONLINE esbp...2cdcora....cdc.ons application ONLINE ONLINE esbp...2cdcora....cdc.vip application ONLINE ONLINE esbp...2cdc
>srvctl modify instance -d BPELP -i BPELP1 -s +ASM1>srvctl modify instance -d BPELP -i BPELP2 -s +ASM2

>crs_stat -tName Type Target State Host------------------------------------------------------------ora....P1.inst application ONLINE OFFLINEora....P2.inst application ONLINE OFFLINEora.BPELP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.DPWRP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.UDDIP.db application OFFLINE OFFLINEora....SM1.asm application ONLINE ONLINE esbp...1cdcora....DC.lsnr application ONLINE ONLINE esbp...1cdcora....cdc.gsd application ONLINE ONLINE esbp...1cdcora....cdc.ons application ONLINE ONLINE esbp...1cdcora....cdc.vip application ONLINE ONLINE esbp...1cdcora....SM2.asm application ONLINE ONLINE esbp...2cdcora....DC.lsnr application ONLINE ONLINE esbp...2cdcora....cdc.gsd application ONLINE ONLINE esbp...2cdcora....cdc.ons application ONLINE ONLINE esbp...2cdcora....cdc.vip application ONLINE ONLINE esbp...2cdc>srvctl start database -d BPELPPRKP-1001 : Error starting instance BPELP1 on node node1CRS-0215: Could not start resource 'ora.BPELP.BPELP1.inst'.PRKP-1001 : Error starting instance BPELP2 on node node2CRS-0215: Could not start resource 'ora.BPELP.BPELP2.inst'.>srvctl start instance -d BPELP -i BPELP1PRKP-1001 : Error starting instance BPELP1 on node node1CRS-0215: Could not start resource 'ora.BPELP.BPELP1.inst'.>crs_stop ora.BPELP.BPELP1.instTarget set to OFFLINE for `ora.BPELP.BPELP1.inst`>crs_stop ora.BPELP.BPELP2.instTarget set to OFFLINE for `ora.BPELP.BPELP2.inst`
>crs_stat -tName Type Target State Host------------------------------------------------------------ora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.BPELP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.DPWRP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.UDDIP.db application OFFLINE OFFLINEora....SM1.asm application ONLINE ONLINE esbp...1cdcora....DC.lsnr application ONLINE ONLINE esbp...1cdcora....cdc.gsd application ONLINE ONLINE esbp...1cdcora....cdc.ons application ONLINE ONLINE esbp...1cdcora....cdc.vip application ONLINE ONLINE esbp...1cdcora....SM2.asm application ONLINE ONLINE esbp...2cdcora....DC.lsnr application ONLINE ONLINE esbp...2cdcora....cdc.gsd application ONLINE ONLINE esbp...2cdcora....cdc.ons application ONLINE ONLINE esbp...2cdcora....cdc.vip application ONLINE ONLINE esbp...2cdc>srvctl start instance -d BPELP -i BPELP1PRKP-1001 : Error starting instance BPELP1 on node node1CRS-0215: Could not start resource 'ora.BPELP.BPELP1.inst'.
>crs_stop ora.BPELP.BPELP1.instTarget set to OFFLINE for `ora.BPELP.BPELP1.inst`
--->srvctl start database -d BPELPPRKP-1001 : Error starting instance BPELP1 on node node1CRS-0215: Could not start resource 'ora.BPELP.BPELP1.inst'.PRKP-1001 : Error starting instance BPELP2 on node node2CRS-0215: Could not start resource 'ora.BPELP.BPELP2.inst'.

>srvctl start instance -d BPELP -i BPELP1PRKP-1001 : Error starting instance BPELP1 on node node1CRS-0215: Could not start resource 'ora.BPELP.BPELP1.inst'.

>crs_stop ora.BPELP.BPELP1.instTarget set to OFFLINE for `ora.BPELP.BPELP1.inst`

>crs_stop ora.BPELP.BPELP2.instTarget set to OFFLINE for `ora.BPELP.BPELP2.inst`

>crs_stat -tName Type Target State Host------------------------------------------------------------ora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.BPELP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.DPWRP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.UDDIP.db application OFFLINE OFFLINEora....SM1.asm application ONLINE ONLINE esbp...1cdcora....DC.lsnr application ONLINE ONLINE esbp...1cdcora....cdc.gsd application ONLINE ONLINE esbp...1cdcora....cdc.ons application ONLINE ONLINE esbp...1cdcora....cdc.vip application ONLINE ONLINE esbp...1cdcora....SM2.asm application ONLINE ONLINE esbp...2cdcora....DC.lsnr application ONLINE ONLINE esbp...2cdcora....cdc.gsd application ONLINE ONLINE esbp...2cdcora....cdc.ons application ONLINE ONLINE esbp...2cdcora....cdc.vip application ONLINE ONLINE esbp...2cdc

>srvctl start instance -d BPELP -i BPELP1PRKP-1001 : Error starting instance BPELP1 on node node1CRS-0215: Could not start resource 'ora.BPELP.BPELP1.inst'.

>crs_stop ora.BPELP.BPELP1.instTarget set to OFFLINE for `ora.BPELP.BPELP1.inst`

>srvctl modify database -d BPELP -p '+ESBPCDC_DATA_01/BPELP/spfileBPELP.ora' -s open
>srvctl start database -d BPELPPRKP-1001 : Error starting instance BPELP1 on node node1CRS-0215: Could not start resource 'ora.BPELP.BPELP1.inst'.PRKP-1001 : Error starting instance BPELP2 on node node2CRS-0215: Could not start resource 'ora.BPELP.BPELP2.inst'.

>vi /esb02/u0001/oracle/product/10.2.0/db_1/log/node1/racg/ora.BPELP.BPELP1.inst.log
2008-06-17 22:21:30.960: [ COMMCRS][258]clsc_connect: (1106f0ff0) no listener at (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=9011))
2008-06-17 22:21:33.352: [ RACG][1] [1233034][1][ora.BPELP.BPELP1.inst]: clsrcremaction: clsrcrpcreq message returned: 1 'SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 17 22:21:32 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: Connected to an idle instance.
SQL> ORA-00119: invalid specification for system parameter REMOTE_LISTENERORA'
2008-06-17 22:21:33.352: [ RACG][1] [1233034][1][ora.BPELP.BPELP1.inst]: end for resource = ora.BPELP.BPELP1.inst, action = start, status = 1, time = 2.430s
2008-06-17 22:34:11.683: [ COMMCRS][258]clsc_connect: (1106f0ff0) no listener at (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=9011))
2008-06-17 22:34:14.080: [ RACG][1] [991310][1][ora.BPELP.BPELP1.inst]: clsrcremaction: clsrcrpcreq message returned: 1 'SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 17 22:34:12 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: Connected to an idle instance.
SQL> ORA-00119: invalid specification for system parameter REMOTE_LISTENERORA'
2008-06-17 22:34:14.080: [ RACG][1] [991310][1][ora.BPELP.BPELP1.inst]: end for resource = ora.BPELP.BPELP1.inst, action = start, status = 1, time = 2.435s

>srvctl setenv database -d BPELP -t TNS_ADMIN=/esb02/u0001/oracle/product/10.2.0/asm/network/admin
>srvctl start database -d BPELPPRKP-1001 : Error starting instance BPELP1 on node node1CRS-0215: Could not start resource 'ora.BPELP.BPELP1.inst'.PRKP-1001 : Error starting instance BPELP2 on node node2CRS-0215: Could not start resource 'ora.BPELP.BPELP2.inst'.

>srvctl setenv database -d BPELP -t TNS_ADMIN=/esb02/u0001/oracle/product/10.2.0/db_1/network/admin
>srvctl start instance -d BPELP -i BPELP1
>srvctl start instance -d BPELP -i BPELP2>crs_stat -tName Type Target State Host------------------------------------------------------------ora....P1.inst application ONLINE ONLINE esbp...1cdcora....P2.inst application ONLINE ONLINE esbp...2cdcora.BPELP.db application ONLINE ONLINE esbp...2cdcora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.DPWRP.db application OFFLINE OFFLINEora....P1.inst application OFFLINE OFFLINEora....P2.inst application OFFLINE OFFLINEora.UDDIP.db application OFFLINE OFFLINEora....SM1.asm application ONLINE ONLINE esbp...1cdcora....DC.lsnr application ONLINE ONLINE esbp...1cdcora....cdc.gsd application ONLINE ONLINE esbp...1cdcora....cdc.ons application ONLINE ONLINE esbp...1cdcora....cdc.vip application ONLINE ONLINE esbp...1cdcora....SM2.asm application ONLINE ONLINE esbp...2cdcora....DC.lsnr application ONLINE ONLINE esbp...2cdcora....cdc.gsd application ONLINE ONLINE esbp...2cdcora....cdc.ons application ONLINE ONLINE esbp...2cdcora....cdc.vip application ONLINE ONLINE esbp...2cdc

> mv listner.ora.bkp listener.ora
> mv tnsnames.ora.bkp tnsnames.ora
>srvctl start database -d UDDIP>srvctl start database -d DPWRP
--
>chkcrs.shHA Resource Target State----------- ------ -----ora.BPELP.BPELP1.inst ONLINE ONLINE on node1ora.BPELP.BPELP2.inst ONLINE ONLINE on node2ora.BPELP.BPELP_APPS.TWCABLE.COM.BPELP1.srv ONLINE ONLINE on node1ora.BPELP.BPELP_APPS.TWCABLE.COM.BPELP2.srv ONLINE ONLINE on node2ora.BPELP.BPELP_APPS.TWCABLE.COM.cs ONLINE ONLINE on node2ora.BPELP.db ONLINE ONLINE on node2ora.DPWRP.DPWRP1.inst ONLINE ONLINE on node1ora.DPWRP.DPWRP2.inst ONLINE ONLINE on node2ora.DPWRP.DPWRP_APPS.TWCABLE.COM.DPWRP1.srv ONLINE ONLINE on node1ora.DPWRP.DPWRP_APPS.TWCABLE.COM.DPWRP2.srv ONLINE ONLINE on node2ora.DPWRP.DPWRP_APPS.TWCABLE.COM.cs ONLINE ONLINE on node2ora.DPWRP.db ONLINE ONLINE on node2ora.UDDIP.UDDIP1.inst ONLINE ONLINE on node1ora.UDDIP.UDDIP2.inst ONLINE ONLINE on node2ora.UDDIP.UDDIP_APPS.TWCABLE.COM.UDDIP1.srv ONLINE ONLINE on node1ora.UDDIP.UDDIP_APPS.TWCABLE.COM.UDDIP2.srv ONLINE ONLINE on node2ora.UDDIP.UDDIP_APPS.TWCABLE.COM.cs ONLINE ONLINE on node2ora.UDDIP.db ONLINE ONLINE on node2ora.node1.ASM1.asm ONLINE ONLINE on node1ora.node1.LISTENER_node1.lsnr ONLINE ONLINE on node1ora.node1.gsd ONLINE ONLINE on node1ora.node1.ons ONLINE ONLINE on node1ora.node1.vip ONLINE ONLINE on node1ora.node2.ASM2.asm ONLINE ONLINE on node2ora.node2.LISTENER_node2.lsnr ONLINE ONLINE on node2ora.node2.gsd ONLINE ONLINE on node2ora.node2.ons ONLINE ONLINE on node2ora.node2.vip ONLINE ONLINE on node2
--

ORACLE Installation - Record and Play

2009-06-17T19:35:00.000-07:00

If you are doing too many ORACLE installatons and having issues in bringing up Installer of GUI, tired of sowness of Oracle GUI install, here is an option you can try. Record a dry run of "oracle install" to a XML file and then edit the XML file and run the install in silent mode. This method is extremely helpful if you are installaing ORACLE into 2 different homes ( say, ORACLE_HOME for ASM and ORACLE_HOME for RDBMS), all you need to do is run ORACLE Univeral Installer upto the last page with record option and exit out without actuall install and then do a Silent mode installation.
Ex:
1. runInstaller -record -destinationFile /home/oracle/sridhar/install_db_10201
which brings up ORACLE Universal Installer and records the selections, actions like ORACLE_HOME, Database components to be e installd etc. Make sure you exit out the installer at the last step without actually installing.

2. Then run the runInstalle in Silent mode as
runInstaller -silent -responseFile /home/oracle/sridhar/install_db_10201

You can even ftp the file to another server and oracle can be installed there after modifying the .xml file with appropriate values.
--
For 10.2.0.4
runInstaller -record -destinationFile /home/oracle/sridhar/install_db_10204
runInstaller -silent -responseFile /home/oracle/sridhar/install_db_10204
--
PS: Careful though in modifying the file..

Build a Physical Standby in 10 Simple Steps.

2008-12-06T16:24:00.000-08:00

Overview:

Standby database feature is started with a release of Oracle 8.0.4 and synonyms to “Data Guard”. If needed, the standby database can assume the role of primary database and take over production in case of a Disaster. A standby database can be one of two types: a physical standby database or a logical standby database.

Physical Standby:

A physical standby database is physically identical to the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are identical.

Logical Standby:

A logical standby database is initially created as an identical copy of the primary database, but it later can be altered to have a different structure. The logical standby database is updated by executing SQL statements. This allows users to access the standby database for queries and reporting at any time.

Now let us see, how we can create a physical standby database, STBY, for a primary database, PRIM. For this standby build, here are few assumptions.

1. OS Architecture is same between primary and standby servers.
2. Oracle version is same primary and standby servers and Oracle is installed on both the servers.
3. Primary database is running on ARCIVELOG mode.
4. Automatic Storage Management (ASM) is deployed on both primary and standy servers, and ASM instance is running on both PRIMARY and STANDBY servers.
5. ASM Disk group are created on both primary and standby ASM instances and their information is as –

Primary: PRIM_DATA, PRIM_REDO, PRIM_FRA.
Standby: STBY_DATA, STBY_REDO, STBY_FRA.

Steps

Step 1. Preparation of primary database:

Before you create a standby database you must first ensure that primary database (PRIM) is properly configured.

1.1 Enable Force logging.

ALTER DATABASE FORCE LOGGING;

To Verify, select FORCE_LOGGING from v$database ;

1.2 Enable Archivelog mode on Primary database, define a local archive destination.
Ex:
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/oradb/u0001/archive' scope=both ;

If you are using FRA as your archive destination -

alter system set LOG_ARCHIVE_DEST_1='LOCATION=use_db_recovery_file_dest' scope=both ;

To verify,

show parameters log_archive_dest_1
archive log list ;

Step 2. Preparation of Standby database:

2.1 Create directories for dump destinations using OFA.

mkdir –p /oradb/u0009/oracle/admin/STBY/adump
mkdir –p /oradb/u0009/oracle/admin/STBY/bdump
mkdir –p /oradb/u0009/oracle/admin/STBY/cdump
mkdir –p /oradb/u0009/oracle/admin/STBY/udump

2.2 Create Password file with the same password of Primary database.

cd $ORACLE_HOME/dbs
orapwd password=sys_password_PRIM_database file=orapwSTBY entries=30

Step 3. Backup primary database.

3.1 Perform a Full backup to DISK or TAPE using RMAN.

RMAN>
connect target sys/password@PRIM
connect catalog user/password@CATALOG_DB
run {
allocate channel ch01 type tape/disk …. ;
allocate channel ch02 type tape/disk …. ;
backup database;
sql 'alter system archive log current' ;
backup archivelog all ;
backup current controlfile for standby ;
}

3.2 Generate a pfile to be copied to standby system.

Create pfile = ‘/export/home/oracle/Sridhar/pfile_STBY.ora’ from spfile ;

Step 4. Copy Files from Primary server to Standby server.

From Primary system, using either ftp or scp, copy the files (backupsets and pfile) created in step 3.

Step 5. Set init.ora parameters. Modify the pfile_STBY.ora for standby database.

1. Keep the same or change the memory related init.ora parameters like db_cache_size, sga_*, pga_*, etc.

2. Control_files = ** comment this parameter, RMAN will restore to the default locations. **

3. Standby_archive_dest: Specify the location of the archived redo logs that will be received from the primary database.

standby_archive_dest = '/oradb/u0009/oracle/arch‘

4. Dump Directories.

audit_file_dest ='/oradb/u0009/oracle/admin/STBY/adump’
background_dump_dest ='/oradb/u0009/oracle/admin/STBY/bdump'
user_dump_dest ='/oradb/u0009/oracle/admin/STBY/udump'
core_dump_dest ='/oradb/u0009/oracle/admin/STBY/cdump'

5. DBNAME

Db_name = PRIM
Db_unique_name = STBY

6. standby_file_management = AUTO

7. Listener/Net parameters
# set FAL_CLIENT to standby service name
# set FAL_SERVER to primary service name

fal_client = ‘STBY’
Fal_server = ‘PRIM’

7. File Destination parameters

db_create_file_dest = '+STBY_DATA'
db_create_online_log_dest_1 = '+STBY_REDO'
db_create_online_log_dest_2 = '+STBY_REDO'
db_recovery_file_dest = '+STBY_FRA'
db_recovery_file_dest_size = 100G

8. Archiving. (Optional, needed if the STANDBY is activated as PRIMARY and generating archive logs.)

log_archive_dest_1 = 'LOCATION=use_db_recovery_file_dest'
db_recovery_file_dest = '+STBY_FRA'
db_recovery_file_dest_size = 100G

Step 6. Configuring remote connections.

On both primary and standby systems, create a network service name for both primary and standby databases and test the remote connectivity.

Example:
PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(Host = primary_server)(Port = 1531))
(CONNECT_DATA = (SID = DBNAMEP))
)

STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(Host = standby_server)(Port = 1532))
(CONNECT_DATA = (SID = DBNAMEY))
)

Step 7. DEAD CONNECTION DETECTION

Enable dead connection detection by setting SQLNET.EXPIRE_TIME parameter in sqlnet.ora file on standby system.

SQLNET.EXPIRE_TIME = 5

Step 8. Restore Standby database from Backups.

8.1 Start the standby database in NOMOUNT mode.

$ sqlplus / as sysdba
SQL> create spfile from pfile=‘/export/home/oracle/sridhar/pfile_STBY.ora’ ;
SQL> Startup nomount

8.2 On standby system, run the following RMAN script to create a duplicate database.

RMAN>
connect target sys/password@PRIM
connect catalog user/password@CATALOG_DB
connect auxiliary sys/password@STBY
run {
allocate auxiliary channel ch01;
allocate auxiliary channel ch02;
duplicate target database for standby nofilenamecheck ;
}

8.3 Update Control_files parameter

Alter system set control_files = ** as per RMAN restore log from step above** scope=spfile ;

Step 9. Configure Log Apply Services.

9.1 On standby database, start the log apply services using -

alter database recover managed standby database disconnect from session ;

9.2 On Primary database, enable Archiving to physical standby database.

Alter system set log_archive_dest_2=‘SERVICE=STBY ARCH DELAY=60’ scope=both;
Alter system set log_archive_dest_state_2=enable scope=both;

9.3 On Primary database, start remote archiving.

Alter system archive log current ;

Step 10. Verifying physical standby

10.1 Identify the existing archived log on standby.

Alter session set nls_date_format= ‘DD-MON-YY HH24:MI:SS’ ;
Select sequence#, first_time, next_time, applied from v$archived_log order by 1 ;

10.2 Archive the current log on Primary database.

Alter system archive log current ;

10.3 Verify that the new archive redo log was received on standby.

Select sequence#, first_time, next_time, applied from v$archived_log order by 1 ;