Oracle Masters http://www.oraclemasters.in Oracle FAQ, Tips And Tricks, Resources, Certifications....Anything Oracle! Fri, 20 Aug 2010 14:33:30 +0000 en hourly 1 http://wordpress.org/?v=3.0.1 How To Check Whether Physical Standby is in Sync with the Primary or Not? http://www.oraclemasters.in/?p=1255 http://www.oraclemasters.in/?p=1255#comments Fri, 20 Aug 2010 14:32:52 +0000 tdomf_2a078 http://www.oraclemasters.in/?p=1255 Hi all,

The following tips will help you in monitoring sync between primary and standby databases.

How To Check Whether Physical Standby is in Sync with the Primary or Not?

1. Check for GAP on standby
2. Check redo received on standby
3. Check redo applied on standby

Solution
Execute following queries:

A. On Primary

?Download text
1
2
3
4
5
6
7
8
9
10

SQL> SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
 
Thread     Last Sequence Generated
---------- -----------------------
1          19
2          13
3          11

B. On Physical Standby

?Download text
1
2
3
4
5
6
7
8
9
10
11
12
13

SQL> 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;
 
Thread     Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1          19                     19                    0
2          13                     13                    0
3          11                     11                    0

C. On Physical Standby

?Download text
1
2

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected

Now perform following checks:

1. Check for GAP

If query “C” returns any row then this means there are some archive log missing on standby.
Example:

?Download text
1
2
3
4
5

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
 
Thread     Low Sequence High Sequence
---------- ------------ -------------
1          8            9

This example shows sequence 8 and 9 from thread 1 are missing on standby, Hence standby is not in sync with the primary.
If query “C” does not returns any row and output is “no row selected” than this means there is no archive gap on standby.

2. Check for redo received on standby

Compare value of “Last Sequence Generated” in query “A” with “Last Sequence Received” in query “B” for all threads.
If both values are same than this means that standby has received the last sequence generated on primary.
If both values are not same then there are some archives missing on standby, Hence standby is not in sync with the primary.

Example:

If “Last Sequence Generated” in query “A” shows value 25 for thread 1 and “Last Sequence Received” in query “B” shows value 20 for thread 1 than this means sequence 21 to 25 are missing on standby. Hence standby is not in sync with the primary.

3. Check for redo applied on standby

If value of “Difference” in query “B” is 0 than this means all the redo received on primary is applied on standby. Hence we can says standby is in sync with primary.
If value of “Difference” in query “B” is not 0 than this means all the redo received on primary is not applied on standby. Hence we can says standby is not in sync with primary

Cheers,
Sakthivel G

]]> http://www.oraclemasters.in/?feed=rss2&p=1255 1 Snapshot Standby – Oracle 11g and Oracle 10g examples http://www.oraclemasters.in/?p=1243 http://www.oraclemasters.in/?p=1243#comments Sat, 14 Aug 2010 15:40:08 +0000 Ramesh Menon http://www.oraclemasters.in/?p=1243

Let us consider a scenario, where the application owner wants to perform read/write testing using production database. This is not possible in a real environment as you cannot play with production data. Oracle, provides a feature to use the standby database in read write mode, so that application users can perform tests, and then revert the standby to physical standby status.

This feature is possible in both Oracle 10g and Oracle 11g. Oracle 11g calls it a snapshot standby and can be performed directly. Oracle creates a Restore Point internally.


This article will demonstrate both Oracle 10g and Oracle 11g methods.

  1. Oracle 10g Steps
  2. Oracle 11g Steps

Steps in Oracle 10g

  1. Set FRA parameters
  2. Create a guranteed restore point
  3. Activate standby database
  4. Perform read/write testing
  5. Revert to original standby


Set FRA parameters

?Download text
1
2
3
4
5
6
7
8
9

SQL> alter system set db_recovery_file_dest_size=1G;
 
System altered.
 
SQL> alter system set db_recovery_file_dest='/fra';
 
System altered.
 
SQL>


Create a guranteed restore point

?Download text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

SQL> ho df -gh /fra
Filesystem             size   used  avail capacity  Mounted on
/dev/dsk/c1t6d0s0      7.9G    48M   7.7G     1%    /fra
 
SQL> create restore point pre_activate guarantee flashback database;
 
Restore point created.
 
SQL>
 
 
SQL> set lines 132
SQL> col time format a32
SQL>  select name, time, storage_size,GUARANTEE_FLASHBACK_DATABASE from V$restore_point;
 
NAME                                               TIME                             STORAGE_SIZE GUA
-------------------------------------------------- -------------------------------- ------------ ---
PRE_ACTIVATE                                       14-AUG-10 07.59.35.000000000 PM       8192000 YES
 
1 row selected.
 
SQL>


Activate the standby database

?Download text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

SQL> alter database activate standby database;
 
Database altered.
 
SQL> select name, open_mode, controlfile_type from V$database;
 
NAME                                               OPEN_MODE            CONTROL
-------------------------------------------------- -------------------- -------
PROD10G                                             MOUNTED              CURRENT
 
1 row selected.
 
SQL> alter database open;
 
Database altered.
 
SQL> select name, open_mode, controlfile_type from V$database;
 
NAME                                               OPEN_MODE            CONTROL
-------------------------------------------------- -------------------- -------
PROD10G                                            READ WRITE           CURRENT
 
1 row selected.


Perform testing

?Download text
1
2
3

SQL> create table test as select * from v$datafile;
 
Table created.


Revert to standby setup

?Download text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
 
Total System Global Area  217157632 bytes
Fixed Size                  2209600 bytes
Variable Size             159385792 bytes
Database Buffers           50331648 bytes
Redo Buffers                5230592 bytes
Database mounted.
SQL>  flashback database to restore point PRE_ACTIVATE;
 
Flashback complete.
 
SQL> alter database convert to physical standby;
 
Database altered.
 
SQL> shut immediate;
ORA-01507: database not mounted
 
 
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area  217157632 bytes
Fixed Size                  2209600 bytes
Variable Size             159385792 bytes
Database Buffers           50331648 bytes
Redo Buffers                5230592 bytes
Database mounted.
SQL> select name, open_mode, controlfile_type from V$database;
 
NAME                                               OPEN_MODE            CONTROL
-------------------------------------------------- -------------------- -------
PROD10G		                                    MOUNTED              STANDBY
 
1 row selected.
 
SQL>

Steps in Oracle 11g

Though the same steps as Oracle 10g can be performed on 11g, Oracle introduces a new command as shown below

Converting to snapshot standby

?Download text
1
2

sql> select name, open_mode, controlfile_type from V$database
sql> alter database convert to snapshot standby

Excerpt from alert log file while this command is processed

?Download text
1
2
3
4
5
6
7
8
9
10
11
12
13

Sat Aug 14 19:49:00 2010
alter database convert to snapshot standby
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_08/14/2010 19:49:00
krsv_proc_kill: Killing 4 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 235449
Sat Aug 14 19:49:19 2010
Standby became primary SCN: 235447
Sat Aug 14 19:49:19 2010
Setting recovery target incarnation to 4
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby

Quering v$restore_point

?Download text
1
2
3
4
5
6
7
8
9

sql> col name format a50
sql> select name from v$restore_point
NAME
--------------------------------------------------
SNAPSHOT_STANDBY_REQUIRED_08/14/2010 19:49:00
 
1 row selected.
 
SQL>

Reverting

?Download text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
 
Total System Global Area  217157632 bytes
Fixed Size                  2209600 bytes
Variable Size             159385792 bytes
Database Buffers           50331648 bytes
Redo Buffers                5230592 bytes
Database mounted.
SQL>  alter database convert to physical standby;
 
Database altered.
 
SQL>

Excerpt from alert log file while this command is processed

?Download text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

RFS[2]: Identified database type as 'snapshot standby': Client is LGWR ASYNC pid 2541
RFS[2]: Opened log for thread 1 sequence 40 dbid -1085733228 branch 726956820
Archived Log entry 46 added for thread 1 sequence 40 rlc 726956820 ID 0xbf4a13e2 dest 2:
RFS[2]: Opened log for thread 1 sequence 41 dbid -1085733228 branch 726956820
Sat Aug 14 19:54:55 2010
 alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (PROD11R2)
krsv_proc_kill: Killing 2 processes (all RFS)
Flashback Restore Start
Flashback Restore Complete
Stopping background process RVWR
Deleted Oracle managed file /oraarch11g/PROD11R2/arch/PROD11R2_STBY/flashback/o1_mf_66f7o57r_.flb
Guaranteed restore point  dropped
Clearing standby activation ID 3209312565 (0xbf4a3935)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 104857600;
Completed:  alter database convert to physical standby
 
SQL> shut immediate;
ORA-01507: database not mounted
 
 
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
 
Total System Global Area  217157632 bytes
Fixed Size                  2209600 bytes
Variable Size             159385792 bytes
Database Buffers           50331648 bytes
Redo Buffers                5230592 bytes
Database mounted.
 
SQL> select name, open_mode, controlfile_type from v$database;
 
NAME                                               OPEN_MODE            CONTROL
-------------------------------------------------- -------------------- -------
PROD11R2                                           MOUNTED              STANDBY
 
1 row selected.
]]> http://www.oraclemasters.in/?feed=rss2&p=1243 0 11g Physical Standby database creation http://www.oraclemasters.in/?p=1226 http://www.oraclemasters.in/?p=1226#comments Sat, 14 Aug 2010 13:32:10 +0000 Ramesh Menon http://www.oraclemasters.in/?p=1226

Creating a physical standby database on Oracle 11gR2

This article is a simple example to setup a standby database in Oracle 11g. The standby database environment will be used in later articles for configuring dataguard broker and logical standby database creation

Steps

  1. Setup the primary database
    1. Create bare minimum intialization parameter file
    2. Create database
    3. Create spfile
  2. Create the standby database
    1. Setup initialization parameter file for Standby database
    2. Create password file on production and standby
    3. Convert standby to use spfile
    4. Setup networking for archive transfer
    5. Create a backup on production and restore it to the standby
    6. Create a standby controlfile and copy it to the standby server
    7. Mount the standby database and test log transfer and application

The following are the details of the databases that I will create

?Download configuration.txt
1
2
3
4
5
6
7
8
9
10
11
12

				Source Database			Standby Database
IP Address			192.168.1.221			192.168.1.222
Hostname			solaris-si-node-01		solaris-si-node-02
SID				PROD11R2			PROD11R2
DB_UNIQUE_NAME			PROD11R2_LIVE			PROD11R2_STBY
Listener			LISTENER_PROD			LISTENER_STBY
Listener Port			1234				1234
TnsNames Entry			PROD11R2_LIVE			PROD11R2_STBY
Partitions			/oradata11g/PROD11GR2/data01	/oradata11g/PROD11GR2/data01
				/oradata11g/PROD11GR2/data02	/oradata11g/PROD11GR2/data02
				/oradata11g/PROD11GR2/data03	/oradata11g/PROD11GR2/data03			
				/oraarch11g/PROD11GR2/arch	/oraarch11g/PROD11GR2/arch

Setup the primary database


Create bare minimum intialization parameter file

We will create the entries for this database in both LIVE and STANDBY ROLES, so that we do not have to create multiple initialization parameter files

?Download initPROD11R2.ora
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

	db_name=PROD11R2
	db_unique_name=PROD11R2_LIVE
	control_files=('/oradata11g/PROD11R2/data01/control/control01.ctl','/oradata11g/PROD11R2/data02/cont
	rol/control02.ctl','/oradata11g/PROD11R2/data03/control/control03.ctl')
	diagnostic_dest=/ora11g/app/ora11g/product/11.2.0/dbhome_1/log
	undo_tablespace=UNDO_TBS
	undo_management=AUTO
 
	log_archive_dest_1='LOCATION=/oraarch11g/PROD11R2/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
	log_archive_dest_2='SERVICE=PROD11R2_STBY VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)'
	log_archive_format='PROD11R2_%t_%s_%r.dbf'
 
	# Standby related parameters
	standby_file_management=AUTO
	#standby_archive_dest='/oraarch11g/PROD11R2/arch/'		Deprecated
 
	fal_server=PROD11R2_STBY
	fal_client=PROD11R2_LIVE


Create the database

?Download createdb.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

	$ export ORACLE_SID=PROD11R2	
 
	create database
	user sys  identified by oracle
	user system  identified by oracle
	logfile
	group 1 ('/oradata11g/PROD11R2/data01/redo/redo01a.log','/oradata11g/PROD11R2/data02/redo/redo01b.lo
	g') size 100M,
	group 2 ('/oradata11g/PROD11R2/data02/redo/redo02a.log','/oradata11g/PROD11R2/data03/redo/redo02b.lo
	g') size 100M,
	group 3 ('/oradata11g/PROD11R2/data03/redo/redo03a.log','/oradata11g/PROD11R2/data01/redo/redo03b.lo
	g') size 100M
	DATAFILE '/oradata11g/PROD11R2/data01/oradata/system_01.dbf' size 500M AUTOEXTEND ON MAXSIZE 1000M
	EXTENT MANAGEMENT LOCAL
	SYSAUX DATAFILE '/oradata11g/PROD11R2/data01/oradata/sysaux_01.dbf' size 200M AUTOEXTEND ON MAXSIZE
	500M
	UNDO TABLESPACE UNDO_TBS
	DATAFILE '/oradata11g/PROD11R2/data01/oradata/undo_tbs_01.dbf' size 100M
	DEFAULT TEMPORARY TABLESPACE TEMP
	TEMPFILE '/oradata11g/PROD11R2/data01/oradata/temp_01.dbf' size 100M
 
	$ sqlplus "/ as sysdba"
 
	SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 13 20:26:26 2010
 
	Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
	Connected to an idle instance.
 
	SQL> startup nomount
	ORACLE instance started.
 
	Total System Global Area  217157632 bytes
	Fixed Size                  2209600 bytes
	Variable Size             159385792 bytes
	Database Buffers           50331648 bytes
	Redo Buffers                5230592 bytes
	SQL> exit
	Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
	With the Partitioning, OLAP, Data Mining and Real Application Testing options
	$ pwd
	/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs
	$ sqlplus "/ as sysdba"
 
	SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 13 20:26:58 2010
 
	Copyright (c) 1982, 2009, 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> @crdb
 
	Database created.
 
	SQL> @?/rdbms/admin/catalog.sql
	SQL> @?/rdbms/admin/catproc.sql
	</p>


Convert the database to SPFILE based startup and enable archivelog mode

?Download createspfile.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

	SQL> create spfile from pfile;
	File created.
 
	SQL> shut immediate;
	Database closed.
	Database dismounted.
	ORACLE instance shut down.
	SQL> exit
	Disconnected from 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 mount
	ORACLE instance started.
 
	Total System Global Area  217157632 bytes
	Fixed Size                  2209600 bytes
	Variable Size             159385792 bytes
	Database Buffers           50331648 bytes
	Redo Buffers                5230592 bytes
	Database mounted.
 
	SQL>  alter system set log_archive_dest_1='LOCATION=/oraarch11g/PROD11R2/arch/ VALID_FOR=(ALL_LOGFILES, ALL_ROLES)';
 
	System altered.
 
	SQL> alter system set log_archive_dest_2='SERVICE=PROD11R2_STBY VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)'
	SQL> /
 
	System altered.
 
 
 
	SQL> alter system set log_archive_format='PROD11R2_%t_%s_%r.dbf' SCOPE=SPFILE;
 
	System altered.
 
 
	SQL> alter database archivelog;
 
	Database altered.
 
	SQL> shut immediate;
	ORA-01109: database not open
 
 
	Database dismounted.
	ORACLE instance shut down.
	SQL> startup
	ORACLE instance started.
 
	Total System Global Area  217157632 bytes
	Fixed Size                  2209600 bytes
	Variable Size             159385792 bytes
	Database Buffers           50331648 bytes
	Redo Buffers                5230592 bytes
	Database mounted.
	Database opened.
	SQL>

In the above example, we have set the log_archive_dest_2 to the DR Service, which will be created in the later section

Setup the standby database


Setup initialization parameter file for Standby database

?Download initPROD11R2.ora
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

	db_name=PROD11R2
	db_unique_name=PROD11R2_STBY
	control_files=('/oradata11g/PROD11R2/data01/control/control01.ctl','/oradata11g/PROD11R2/data01/cont
	rol/control02.ctl','/oradata11g/PROD11R2/data01/control/control03.ctl')
	diagnostic_dest=/ora11g/app/ora11g/product/11.2.0/dbhome_1/log
	undo_tablespace=UNDO_TBS
	undo_management=AUTO
 
	log_archive_dest_1='LOCATION=/oraarch11g/PROD11R2/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
	log_archive_dest_2='SERVICE=PROD11R2_LIVE VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)'
	log_archive_format='PROD11R2_%t_%s_%r.dbf'
 
	# Standby related parameters
	standby_file_management=AUTO
	#standby_archive_dest='/oraarch11g/PROD11R2/arch/'		
 
	fal_server=PROD11R2_LIVE
	fal_client=PROD11R2_STBY


Create password file on production and standby

?Download text
1

	 orapwd file=orapwdPROD11R2 password=oracle entries=5


Convert standby to use spfile

?Download text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

	$ sqlplus "/ as sysdba"
 
	SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 14 10:30:24 2010
 
	Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
	Connected to an idle instance.
 
	SQL> startup nomount
	ORACLE instance started.
 
	Total System Global Area  217157632 bytes
	Fixed Size                  2209600 bytes
	Variable Size             159385792 bytes
	Database Buffers           50331648 bytes
	Redo Buffers                5230592 bytes
	SQL> create spfile from pfile;
 
	File created.
 
	SQL> shut immediate
	ORA-01507: database not mounted
 
 
	ORACLE instance shut down.
	SQL> startup nomount;
	ORACLE instance started.
 
	Total System Global Area  217157632 bytes
	Fixed Size                  2209600 bytes
	Variable Size             159385792 bytes
	Database Buffers           50331648 bytes
	Redo Buffers                5230592 bytes
	SQL>


Setup networking for archive transfer

You can setup the listener.ora and tnsnames.ora on production and standby as follows

?Download listenertnsnames.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

 
	On Production database server
 
	Listener
	LISTENER_PROD11R2 =
	  (DESCRIPTION_LIST =
	    (DESCRIPTION =
	      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
	    )
	    (DESCRIPTION =
	      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1234))
	    )
	  )
 
	SID_LIST_LISTENER_PROD11R2 =
	  (SID_LIST =
	    (SID_DESC =
	      (GLOBAL_DBNAME = PROD11R2)
	      (ORACLE_HOME = /ora11g/app/ora11g/product/11.2.0/dbhome_1)
	      (SID_NAME = PROD11R2)
	    )
	  )
 
 
	Tnsnames
	PROD11R2_LIVE =
	  (DESCRIPTION =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1234))
	    (CONNECT_DATA =
	      (SERVER = DEDICATED)
	      (SID=PROD11R2)
	    )
	  )
 
	PROD11R2_STBY =
	  (DESCRIPTION =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1234))
	    (CONNECT_DATA =
	      (SERVER = DEDICATED)
	      (SID=PROD11R2)
	    )
	  )
?Download listenertnsnames.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

	On Standby database server
 
	Listener
	LISTENER_PROD11R2 =
	  (DESCRIPTION_LIST =
	    (DESCRIPTION =
	      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
	    )
	    (DESCRIPTION =
	      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1234))
	    )
	  )
 
	SID_LIST_LISTENER_PROD11R2 =
	  (SID_LIST =
	    (SID_DESC =
	      (GLOBAL_DBNAME = PROD11R2)
	      (ORACLE_HOME = /ora11g/app/ora11g/product/11.2.0/dbhome_1)
	      (SID_NAME = PROD11R2)
	    )
	  )
 
 
	Tnsnames
	PROD11R2_LIVE =
	  (DESCRIPTION =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1234))
	    (CONNECT_DATA =
	      (SERVER = DEDICATED)
	      (SID=PROD11R2)
	    )
	  )
 
	PROD11R2_STBY =
	  (DESCRIPTION =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1234))
	    (CONNECT_DATA =
	      (SERVER = DEDICATED)
	      (PROD11R2)
	    )
	  )


Create a backup on production and restore it to the standby

?Download backuprestore.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

	SQL> alter database begin backup;
 
	Database altered.
 
	SQL> ho
	$ cd /oradata11g/PROD11R2/data01/oradata/
	$ scp  *.* ora11g@192.168.1.222:/oradata11g/PROD11R2/data01/oradata/
	Password:
	sysaux_01.dbf        100% |*************************************************|   200 MB    00:25
	system_01.dbf        100% |*************************************************|   500 MB    00:58
	temp_01.dbf          100% |*************************************************|   100 MB    00:06
	undo_tbs_01.dbf      100% |*************************************************|   100 MB    00:06
	$
 
	SQL> alter database end backup;
 
	Database altered.
 
	SQL>


Create a standby controlfile and copy it to the standby server

?Download standbycontrolfile.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

	SQL> alter database create standby controlfile as '/tmp/control01.ctl';
 
	Database altered.
 
	SQL> exit
	Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
	With the Partitioning, OLAP, Data Mining and Real Application Testing options
	$ cd /tmp
	$ scp  control01.ctl ora11g@192.168.1.222:/oradata11g/PROD11R2/data01/control/control01.ctl
	Password:
	control01.ctl        100% |*************************************************|  7664 KB    00:00
	$ scp  control01.ctl ora11g@192.168.1.222:/oradata11g/PROD11R2/data02/control/control02.ctl
	Password:
	control01.ctl        100% |*************************************************|  7664 KB    00:00
	$ scp  control01.ctl ora11g@192.168.1.222:/oradata11g/PROD11R2/data03/control/control03.ctl
	Password:
	control01.ctl        100% |*************************************************|  7664 KB    00:00
	$


Mount the standby database and test log transfer and application

On standby database

?Download recoverstandby.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

	SQL> alter database mount;
 
	Database altered.
 
	SQL> recover standby database;
	ORA-00279: change 235449 generated at 08/14/2010 13:13:39 needed for thread 1
	ORA-00289: suggestion : /oraarch11g/PROD11R2/arch/PROD11R2_1_35_726956820.dbf
	ORA-00280: change 235449 for thread 1 is in sequence #35
 
 
	Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
	cancel
	Media recovery cancelled.
	SQL> ho ls -ltr /oraarch11g/PROD11R2/arch/PROD11R2_1_5_726956820.dbf
	-rw-r-----   1 ora11g   dba         1024 Aug 14 13:15 /oraarch11g/PROD11R2/arch/PROD11R2_1_5_726956820.dbf

If the archive logs are not being received, check the alert logs for errors.

]]> http://www.oraclemasters.in/?feed=rss2&p=1226 0 one of datafile shows recovery in my standby database http://www.oraclemasters.in/?p=1219 http://www.oraclemasters.in/?p=1219#comments Thu, 12 Aug 2010 08:02:08 +0000 tdomf_2a078 http://www.oraclemasters.in/?p=1219 Hi,

One of my datafile in standby database shows recover mode.
can any one help me how to solve this issue.

I have created one table in one tablespace in production database, today i have applied the archive files in standby database, after that i have opened the standby database in read only mode to check the table. But the table is
corrupted saying block corrupted at block Num 3.

regards

]]> http://www.oraclemasters.in/?feed=rss2&p=1219 1 Duplicate active database over network using RMAN – 11g http://www.oraclemasters.in/?p=1198 http://www.oraclemasters.in/?p=1198#comments Tue, 13 Jul 2010 10:23:55 +0000 Ramesh Menon http://www.oraclemasters.in/?p=1198

This post is a demonstration of duplicating an Oracle database to another server over the network. The article is for the scenario where ALL directory structures are the same on both the source and the destination database.

The setup information are as follows

?View Code LANGUAGE
1
2
3
4
5
6
7
8
9
10
11
12

Details		Source					Destination
OS		SunOS 5.10 Generic_141445-09 x86 64 bit	SunOS 5.10 Generic_141445-09 x86 64 bit
Hostname	solaris-si-node-01			solaris-si-node-02
IP Address	192.168.1.221				192.168.1.222
Oracle Home	/ora11g/app/ora11g/product/11.2.0/db_1	/ora11g/app/ora11g/product/11.2.0/db_1
SPFILE	$ORACLE_HOME/dbs/spfilePRODDBR2.ora		$ORACLE_HOME/dbs/spfilePRODDBR2.ora
Database Name	PRODDB2					PRODDB2
Data Location	/oradata11g/oradata/PRODDBR2/		/oradata11g/oradata/PRODDBR2/
Archive Location	/fra/PRODDBR2/arch		/fra/PRODDBR2/arch
Listener	LISTENER				LISTENER_DEST
Listener Port	1521					1521
Connect String	PRODDB2					PRODDB2_DEST

The steps involved are as follows

  1. Setup the source database
  2. Setup the auxiliary database
  3. Use RMAN to create duplicate database

Setup the source database

The source database should be configured to have connectivity to the auxiliary database via the listener. This can be configured by setting the tnsnames.ora entry for the auxiliary database. (This Listener needs to be created on the auxiliary database as seen later in this article).

?Download tnsnames.ora
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

	# tnsnames.ora Network Configuration File: /ora11g/app/ora11g/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
	# Generated by Oracle configuration tools.
 
	PRODDBR2 =
	  (DESCRIPTION =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))
	    (CONNECT_DATA =
	      (SERVER = DEDICATED)
	      (SERVICE_NAME = PRODDBR2)
	    )
	  )
 
	PRODDBR2_DEST =
	  (DESCRIPTION =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))
	    (CONNECT_DATA =
	      (SERVER = DEDICATED)
	      (SERVICE_NAME = PRODDBR2)
	    )
	  )

Setup the auxiliary database

In this section, we setup the auxiliary database. The following are the things that we need to take care of

  • Generate a password file

    • You could copy the password file from the source database or create a new one

    ?Download pwcreate.txt
    1
2
3
4

    $ scp orapwPRODDBR2 ora11g@solaris-si-node-02:/ora11g/app/ora11g/product/11.2>
	Password:
	orapwPRODDBR2        100% |*****************************|  1536       00:00
	$
  • Create directory structure same as that on production

    • Ensure that you create the directory structure same as the production server. Any location in the spfile MUST exist on the target

    ?Download text
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

    	audit_file_dest=/ora11g/app/ora11g/admin/PRODDBR2/adump
	background_dump_dest=/ora11g/app/ora11g/diag/rdbms/proddbr2/PRODDBR2/trace
	core_dump_dest=/ora11g/app/ora11g/diag/rdbms/proddbr2/PRODDBR2/cdump
	user_dump_dest=/ora11g/app/ora11g/diag/rdbms/proddbr2/PRODDBR2/trace
 
	control_files=/oradata11g/oradata/PRODDBR2/control01.ctl, /ora11g/app/ora11g/flash_recovery_area/PRODDBR2/control02.ctl
	diagnostic_dest=/ora11g/app/ora11g
 
 
	mkdir -p /ora11g/app/ora11g/admin/PRODDBR2/adump
	mkdir -p /ora11g/app/ora11g/diag/rdbms/proddbr2/PRODDBR2/trace
	mkdir -p /ora11g/app/ora11g/diag/rdbms/proddbr2/PRODDBR2/cdump
	mkdir -p /ora11g/app/ora11g/diag/rdbms/proddbr2/PRODDBR2/trace
	mkdir -p /oradata11g/oradata/PRODDBR2/
	mkdir -p /ora11g/app/ora11g/flash_recovery_area/PRODDBR2/

    Also create the directories for your datafiles and redo logfiles

    ?Download tnsnames.ora
    1

    	mkdir -p /oradata11g/oradata/PRODDBR2/ (in my case, it is already created earlier)
  • Create a bare minimum initialization parameter file

    • Add db_name=PRODDBR2 to initPRODDBR2.ora in $ORACLE_HOME/dbs. Since the SPFILE will be brought from the production, and the directory structure is the same, the other parameters can be ignored

  • Create the listener for remote logins

    • Create and start the listener for this instance

    ?Download listener.ora
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

    # Generated by Oracle configuration tools.
 
SID_LIST_LISTENER_DEST =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PRODDBR2)
      (ORACLE_HOME = /ora11g/app/ora11g/product/11.2.0/dbhome_1)
      (SID_NAME = PRODDBR2)
    )
  )
 
LISTENER_DEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))
    )
  )
 
ADR_BASE_LISTENER = /ora11g/app/ora11g
  • Startup in nomount mode
    • ?Download tnsnames.ora
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

    $ sqlplus "/ as sysdba"
 
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 13 16:08:36 2010
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup nomount;
ORACLE instance started.
 
Total System Global Area  217157632 bytes
Fixed Size                  2209600 bytes
Variable Size             159385792 bytes
Database Buffers           50331648 bytes
Redo Buffers                5230592 bytes
SQL> exit

Use RMAN to create duplicate database

Now that all the settings have been made, we can connect to the target database and auxiliary database as follows and start the duplication process.

?Download duplicate.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320

 
$ hostname
solaris-si-node-01
$ export ORACLE_SID=PRODDBR2
$ rman target /
 
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 13 16:23:25 2010
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: PRODDBR2 (DBID=2607472656)
 
RMAN> connect auxiliary sys@proddbr2_dest
 
auxiliary database Password:
connected to auxiliary database (not started)
 
RMAN> DUPLICATE TARGET DATABASE TO PRODDBR2
FROM ACTIVE DATABASE
PASSWORD FILE
SPFILE
NOFILENAMECHECK; 
Starting Duplicate Db at 13-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
 
contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/orapwPRODDBR2' auxiliary format
 '/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/orapwPRODDBR2'   targetfile
 '/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/spfilePRODDBR2.ora' auxiliary format
 '/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/spfilePRODDBR2.ora'   ;
   sql clone "alter system set spfile= ''/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/spfilePRODDBR2.ora''";
}
executing Memory Script
 
Starting backup at 13-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
Finished backup at 13-JUL-10
 
sql statement: alter system set spfile= ''/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/spfilePRODDBR2.ora''
 
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PRODDBR2'' comment=
 ''duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
 
sql statement: alter system set  db_name =  ''PRODDBR2'' comment= ''duplicate'' scope=spfile
 
Oracle instance shut down
 
connected to auxiliary database (not started)
Oracle instance started
 
Total System Global Area    1085640704 bytes
 
Fixed Size                     2210208 bytes
Variable Size                687867488 bytes
Database Buffers             385875968 bytes
Redo Buffers                   9687040 bytes
 
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PRODDBR2'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''PRODDBR2'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/oradata11g/oradata/PRODDBR2/control01.ctl';
   restore clone controlfile to  '/ora11g/app/ora11g/flash_recovery_area/PRODDBR2/control02.ctl' from
 '/oradata11g/oradata/PRODDBR2/control01.ctl';
   alter clone database mount;
}
executing Memory Script
 
sql statement: alter system set  db_name =  ''PRODDBR2'' comment= ''Modified by RMAN duplicate'' scope=spfile
 
sql statement: alter system set  db_unique_name =  ''PRODDBR2'' comment= ''Modified by RMAN duplicate'' scope=spfile
 
Oracle instance shut down
 
Oracle instance started
 
Total System Global Area    1085640704 bytes
 
Fixed Size                     2210208 bytes
Variable Size                687867488 bytes
Database Buffers             385875968 bytes
Redo Buffers                   9687040 bytes
 
Starting backup at 13-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/snapcf_PRODDBR2.f tag=TAG20100713T162459 RECID=5 STAMP=724263900
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 13-JUL-10
 
Starting restore at 13-JUL-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
 
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 13-JUL-10
 
database mounted
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
 
contents of Memory Script:
{
   set newname for datafile  1 to
 "/oradata11g/oradata/PRODDBR2/system01.dbf";
   set newname for datafile  2 to
 "/oradata11g/oradata/PRODDBR2/sysaux01.dbf";
   set newname for datafile  3 to
 "/oradata11g/oradata/PRODDBR2/undotbs01.dbf";
   set newname for datafile  4 to
 "/oradata11g/oradata/PRODDBR2/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/oradata11g/oradata/PRODDBR2/system01.dbf"   datafile
 2 auxiliary format
 "/oradata11g/oradata/PRODDBR2/sysaux01.dbf"   datafile
 3 auxiliary format
 "/oradata11g/oradata/PRODDBR2/undotbs01.dbf"   datafile
 4 auxiliary format
 "/oradata11g/oradata/PRODDBR2/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
Starting backup at 13-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oradata11g/oradata/PRODDBR2/system01.dbf
output file name=/oradata11g/oradata/PRODDBR2/system01.dbf tag=TAG20100713T162509
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oradata11g/oradata/PRODDBR2/sysaux01.dbf
output file name=/oradata11g/oradata/PRODDBR2/sysaux01.dbf tag=TAG20100713T162509
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oradata11g/oradata/PRODDBR2/undotbs01.dbf
output file name=/oradata11g/oradata/PRODDBR2/undotbs01.dbf tag=TAG20100713T162509
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oradata11g/oradata/PRODDBR2/users01.dbf
output file name=/oradata11g/oradata/PRODDBR2/users01.dbf tag=TAG20100713T162509
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-JUL-10
 
sql statement: alter system archive log current
 
contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/fra/PRODDBR2/arch/1_7_724236691.dbf" auxiliary format
 "/fra/PRODDBR2/arch/1_7_724236691.dbf"   ;
   catalog clone archivelog  "/fra/PRODDBR2/arch/1_7_724236691.dbf";
   switch clone datafile all;
}
executing Memory Script
 
Starting backup at 13-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=7 RECID=5 STAMP=724264160
output file name=/fra/PRODDBR2/arch/1_7_724236691.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 13-JUL-10
 
cataloged archived log
archived log file name=/fra/PRODDBR2/arch/1_7_724236691.dbf RECID=5 STAMP=724264163
 
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=724264163 file name=/oradata11g/oradata/PRODDBR2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=724264163 file name=/oradata11g/oradata/PRODDBR2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=724264163 file name=/oradata11g/oradata/PRODDBR2/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=724264163 file name=/oradata11g/oradata/PRODDBR2/users01.dbf
 
contents of Memory Script:
{
   set until scn  924147;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script
 
executing command: SET until clause
 
Starting recover at 13-JUL-10
using channel ORA_AUX_DISK_1
 
starting media recovery
 
archived log for thread 1 with sequence 7 is already on disk as file /fra/PRODDBR2/arch/1_7_724236691.dbf
archived log file name=/fra/PRODDBR2/arch/1_7_724236691.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 13-JUL-10
 
contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''PRODDBR2'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
 
database dismounted
Oracle instance shut down
 
connected to auxiliary database (not started)
Oracle instance started
 
Total System Global Area    1085640704 bytes
 
Fixed Size                     2210208 bytes
Variable Size                687867488 bytes
Database Buffers             385875968 bytes
Redo Buffers                   9687040 bytes
 
sql statement: alter system set  db_name =  ''PRODDBR2'' comment= ''Reset to original value by RMAN'' scope=spfile
 
sql statement: alter system reset  db_unique_name scope=spfile
 
Oracle instance shut down
 
connected to auxiliary database (not started)
Oracle instance started
 
Total System Global Area    1085640704 bytes
 
Fixed Size                     2210208 bytes
Variable Size                687867488 bytes
Database Buffers             385875968 bytes
Redo Buffers                   9687040 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PRODDBR2" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1  SIZE 50 M ,
  GROUP  2  SIZE 50 M ,
  GROUP  3  SIZE 50 M
 DATAFILE
  '/oradata11g/oradata/PRODDBR2/system01.dbf'
 CHARACTER SET WE8MSWIN1252
 
 
contents of Memory Script:
{
   set newname for tempfile  1 to
 "/oradata11g/oradata/PRODDBR2/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/oradata11g/oradata/PRODDBR2/sysaux01.dbf",
 "/oradata11g/oradata/PRODDBR2/undotbs01.dbf",
 "/oradata11g/oradata/PRODDBR2/users01.dbf";
   switch clone datafile all;
}
executing Memory Script
 
executing command: SET NEWNAME
 
renamed tempfile 1 to /oradata11g/oradata/PRODDBR2/temp01.dbf in control file
 
cataloged datafile copy
datafile copy file name=/oradata11g/oradata/PRODDBR2/sysaux01.dbf RECID=1 STAMP=724264213
cataloged datafile copy
datafile copy file name=/oradata11g/oradata/PRODDBR2/undotbs01.dbf RECID=2 STAMP=724264213
cataloged datafile copy
datafile copy file name=/oradata11g/oradata/PRODDBR2/users01.dbf RECID=3 STAMP=724264213
 
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=724264213 file name=/oradata11g/oradata/PRODDBR2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=724264213 file name=/oradata11g/oradata/PRODDBR2/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=724264213 file name=/oradata11g/oradata/PRODDBR2/users01.dbf
 
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script
 
database opened
Finished Duplicate Db at 13-JUL-10
 
RMAN>

This article is a simple demonstration of RMAN’s duplicate over network feature. Oracle 11g provides various other options with duplicate command. These include duplicate database when directory structures are different, point in time duplication, incomplete (partial) duplication, duplicate for standby etc. Oracle documentation is the best place for beginers to start, in order to understand these features.

This option of duplicate opens the destination or auxiliary database in resetlogs mode.

]]> http://www.oraclemasters.in/?feed=rss2&p=1198 0 Query tuning http://www.oraclemasters.in/?p=1187 http://www.oraclemasters.in/?p=1187#comments Wed, 09 Jun 2010 00:35:37 +0000 niket http://www.oraclemasters.in/?p=1187

Optimizer is not using index when i am giving hint to the query which contains bind variables

It is generating its own plan

The query is

?Download text
1
2
3
4
5
6
7
8
9
10

SELECT b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,
       b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,
       b.charge_code, b.charge_type, price_inclusive_of_taxes,
       b.pricing_entity_id, b.party_slaccount
  FROM bt_billing_charges b
 WHERE b.entity_id = :b3
   AND NVL (b.invoice_no, 0) = 0
   AND b.paid_status = 'N'
   AND b.cp_code = NVL (:b2, b.cp_code)
   AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no);

Now execution plan is
============

?Download text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

Plan Table
============
----------------------------------------------------------------+--------------------------------
---+
| Id  | Operation                      | Name                   | Rows  | Bytes | Cost  | Time
   |
----------------------------------------------------------------+--------------------------------
---+
| 0   | SELECT STATEMENT               |                        |       |       |   539 |
   |
| 1   |  CONCATENATION                 |                        |       |       |       |
   |
| 2   |   FILTER                       |                        |       |       |       |
   |
| 3   |    TABLE ACCESS BY INDEX ROWID | BT_BILLING_CHARGES     |     1 |    88 |   269 |  00:00:
04 |
| 4   |     INDEX RANGE SCAN           | IDX_BT_BILLING_ENTITYID|   807 |       |     8 |  00:00:
01 |
| 5   |   FILTER                       |                        |       |       |       |
   |
| 6   |    TABLE ACCESS BY INDEX ROWID | BT_BILLING_CHARGES     |     1 |    88 |   269 |  00:00:
04 |
| 7   |     INDEX RANGE SCAN           | IDX_BT_BILLING_ENTITYID|   807 |       |     8 |  00:00:
01 |
----------------------------------------------------------------+--------------------------------
---+
Predicate Information:
----------------------
2 - filter(:B1 IS NULL)
3 - filter((NVL("B"."INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N' AND "B"."PAYMODE_TRANS_NO" IS NOT
 NULL AND "B"."CP_CODE"=NVL(:B2,"B"."CP_CODE")))
4 - access("B"."ENTITY_ID"=TO_NUMBER(:B3))
5 - filter(:B1 IS NOT NULL)
6 - filter((NVL("B"."INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N' AND "B"."CP_CODE"=NVL(:B2,"B"."CP
_CODE") AND "B"."PAYMODE_TRANS_NO"=:B1))
7 - access("B"."ENTITY_ID"=TO_NUMBER(:B3))

Now i am passing hint to the query

?Download text
1
2
3
4
5
6
7
8
9
10

SELECT /*+ INDEX (b BT_BILLING_CHARGES_I4) */ b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,
       b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,
       b.charge_code, b.charge_type, price_inclusive_of_taxes,
       b.pricing_entity_id, b.party_slaccount
  FROM bt_billing_charges b
 WHERE b.entity_id = :b3
   AND NVL (b.invoice_no, 0) = 0
   AND b.paid_status = 'N'
   AND b.cp_code = NVL (:b2, b.cp_code)
   AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no)

But still it is using same plan.

Now when i removed bind variables with values then it is using index

?Download text
1
2
3
4
5

SELECT /*+ INDEX (b BT_BILLING_CHARGES_I4) */ B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTER
NAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B
.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM IMCL420.BT_BILLI
NG_CHARGES B WHERE B.ENTITY_ID=12345263 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS='N' AND B.CP_
CODE=NVL(0,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(0,B.PAYMODE_TRANS_NO)

and exec plan is

?Download text
1
2
3
4
5
6
7
8
9
10
11
12
13

Plan Table
============
-------------------------------------------------+-----------------------------------+
| Id  | Operation         | Name                 | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT  |                      |       |       |     5 |           |
| 1   |  INDEX RANGE SCAN | BT_BILLING_CHARGES_I4|     1 |    88 |     5 |  00:00:01 |
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("B"."ENTITY_ID"=12345263 AND NVL("INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N')
1 - filter((NVL("INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N' AND TO_NUMBER("B"."CP_CODE")=NVL(0,TO
_NUMBER("B"."CP_CODE")) AND "B"."PAYMODE_TRANS_NO"=NVL(0,"B"."PAYMODE_TRANS_NO")))

so my question is how to force query containing bind variables to use index

Kindly guide me

Thanks

Regards

Niket

]]> http://www.oraclemasters.in/?feed=rss2&p=1187 9 10g rac http://www.oraclemasters.in/?p=1183 http://www.oraclemasters.in/?p=1183#comments Thu, 03 Jun 2010 07:27:18 +0000 tdomf_2a078 http://www.oraclemasters.in/?p=1183 hi ramesh

Can u explain about master and slave nodes. I have 4 node rac, if master is down , which node will take over as master out of 3 alive nodes.

2. can I mannually switch master and slave manually.

]]> http://www.oraclemasters.in/?feed=rss2&p=1183 1 Oracle 9i binary installation. http://www.oraclemasters.in/?p=1179 http://www.oraclemasters.in/?p=1179#comments Thu, 27 May 2010 14:52:14 +0000 srini http://www.oraclemasters.in/?p=1179 We faced this error during the oracle 9I binary installation during (silent installation) on solaris 5.10 (While applying the patch set (9.2.0.6.0))

erver: /tmp/dumps/ship.patch.drop7/Disk1 $ ./runInstaller -silent -responseFile /tmp/dumps/ship.patch.drop7/Disk1/response/patchset.rsp
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be 5.8 or 5.9. Actual 5.10
Failed <<<<

Exiting Oracle Universal Installer, log for this session can be found at /ora9i/oracle/oraInventory/logs/installActions2010-05-27_03-09-17PM.log

This error we rectified by editing the file
oraparamsilent.ini
oraparam.ini
By default these files were present on Disk1/install folder of the patch set dumps.

We added solaris 5.10 entry in to the above mentioned files under column

[Certified Versions]
Solaris=5.8,5.9,5.10

We gone through the installation.

Regards

Srinivas

]]> http://www.oraclemasters.in/?feed=rss2&p=1179 0 Recover physical standby database after loss of archive log – roll forward http://www.oraclemasters.in/?p=1166 http://www.oraclemasters.in/?p=1166#comments Wed, 19 May 2010 15:14:25 +0000 Ramesh Menon http://www.oraclemasters.in/?p=1166

This tutorial simulates a scenario, where in you are unable to recover your standby database because of a corrupted archive log file. The premise is that the archive log is corrupted on both production and standby, and we do not have a backup of this archive log.

The tutorial shows an example of how we can use RMAN incremental backup to recover the standby in such a scenario.

?Download text
1
2
3
4
5
6
7
8
9

Source 
 DBNAME   PRODDB 
 Oracle Home  /u01/ora10g
 Archive Dest  /u02/PRODDB/arch
 
Destination 
 DBNAME   PRODDB
 Oracle Home  /u01/ora10g
 Archive Dest  /u02/PRODDB/arch
  1. Synch primary and standby. Defer application of logs on standby.
  2. Let us now create some dummy tables and switch the logfile on primary. This is the log file that would be needed for recovery
    3. ?Download text
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

    {PRIMARY} /u01/ora10g/backup $ sqlplus
 
SQL*Plus: Release 10.2.0.3.0 - Production on Wed May 19 12:37:34 2010
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
Enter user-name: demo/demo
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
SQL> select * from tab;
 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
OBJLIST                        TABLE
 
SQL> create table object_list as select * from dba_objects        
  2  union select * from dba_objects;
 
Table created.
 
SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/PRODDB/arch/
Oldest online log sequence     59
Next log sequence to archive   61
Current log sequence           61
SQL> alter system switch logfile;
 
System altered.
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/PRODDB/arch/
Oldest online log sequence     60
Next log sequence to archive   62
Current log sequence           62
SQL>
  3. Get the current SCN of standby database
    4. ?Download text
    1
2
3
4
5
6
7

     STBYHOST(PRODDB)SQL >select current_scn from v$database;
 
                      CURRENT_SCN
 --------------------------------
                    7765466164256
 
 STBYHOST(PRODDB)SQL >
  4. To find out which is the next archive log the standby database needs for recovery, run the recover command on the standby database without applying the archive logs
    5. ?Download text
    1
2
3
4
5
6
7
8
9
10
11

     STBYHOST(PRODDB)SQL >recover standby database ;
 ORA-00279: change 7765466164257 generated at 05/19/2010 11:55:11 needed for
 thread 1
 ORA-00289: suggestion : /u02/PRODDB/arch/PRODDB_1_717173775_60.arc
 ORA-00280: change 7765466164257 for thread 1 is in sequence #60
 
 
 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 CANCEL
 Media recovery cancelled.
 STBYHOST(PRODDB)SQL >

    Archive Sequence 60 is needed for recovery. Let us now assume that this archive log is not available on disk or on tape backup.

  5. Use SCN in step 3 to take an incremental backup on disk on primary database
    6. ?Download text
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

    {PRIMARY} /u01/ora10g/backup $ export ORACLE_SID=PRODDB
{PRIMARY} /u01/ora10g/backup $ rman target /
.... 
RMAN> BACKUP INCREMENTAL FROM SCN 7765466164256 DATABASE FORMAT '/u01/ora10g/backup/proddb_standby_%U' tag 'proddb_standby';
Starting backup at 19-MAY-10
using channel ORA_DISK_1
RMAN-06755: WARNING: datafile 5: incremental-start SCN is too recent; using checkpoint SCN 7765465268142 instead
RMAN-06755: WARNING: datafile 6: incremental-start SCN is too recent; using checkpoint SCN 7765465268142 instead
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=/restorevol/BACKUPREST/PRODDB/data/PRODDB_SYSAUX_01.dbf
input datafile fno=00005 name=/restorevol/BACKUPREST/PRODDB/data/PRODDB_TS_LMTDATA_01
skipping datafile 00005 because it has not changed
input datafile fno=00006 name=/restorevol/BACKUPREST/PRODDB/data/PRODDB_TS_LMTDATA_02.dbf
skipping datafile 00006 because it has not changed
input datafile fno=00001 name=/restorevol/BACKUPREST/PRODDB/data/PRODDB_SYSTEM_01.dbf
input datafile fno=00002 name=/restorevol/BACKUPREST/PRODDB/data/PRODDB_UNDOTBS_01.dbf
input datafile fno=00004 name=/restorevol/BACKUPREST/PRODDB/data/PRODDB_TOOLS_01.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAY-10
channel ORA_DISK_1: finished piece 1 at 19-MAY-10
piece handle=/u01/ora10g/backup/proddb_standby_fjle2nl3_1_1 tag=proddb_standby comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
Finished backup at 19-MAY-10
  6. Copy backupiece to standby database server and catalog in standby controlfile
    7. ?Download text
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

    {STBYHOST} /u01/ora10g/backup/PRODDB $ scp ora10r3@PRIMARY:/u01/ora10g/backup/proddb_standby_fjle2nl3_1_1 .                
Password: 
Password: 
proddb_standby_fjle2nl3_ 100% |********************************************************************************************|  5848 KB    00:00    
{STBYHOST} /misuatdata/PRODDB $ pwd
/misuatdata/PRODDB
{STBYHOST} /u01/PRODDB $ rman target / 
 
Recovery Manager: Release 10.2.0.3.0 - Production on Wed May 19 13:04:30 2010
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
connected to target database: PRODDB (DBID=2326455671, not open)
 
RMAN> CATALOG START WITH '/u01/ora10g/backup/PRODDB/proddb_standby';           
 
searching for all files that match the pattern /u01/ora10g/backup/PRODDB/proddb_standby
 
List of Files Unknown to the Database
=====================================
File Name: /u01/ora10g/backup/PRODDB/proddb_standby_fjle2nl3_1_1
 
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
 
List of Cataloged Files
=======================
File Name: /u01/ora10g/backup/PRODDB/proddb_standby_fjle2nl3_1_1
 
RMAN> exit
 
 
Recovery Manager complete.
  7. Recover standby from RMAN
    8. ?Download text
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

    RMAN> RECOVER DATABASE NOREDO
2> ;
 
Starting recover at 19-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 devtype=DISK
datafile 5 not processed because file is read-only
datafile 6 not processed because file is read-only
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/PRODDB/oradata/data/PRODDB_SYSTEM_01.dbf
destination for restore of datafile 00002: /u02/PRODDB/oradata/data/PRODDB_UNDOTBS_01.dbf
destination for restore of datafile 00003: /u02/PRODDB/oradata/data/PRODDB_SYSAUX_01.dbf
destination for restore of datafile 00004: /u02/PRODDB/oradata/data/PRODDB_TOOLS_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/ora10g/backup/PRODDB/proddb_standby_fjle2nl3_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/ora10g/backup/PRODDB/proddb_standby_fjle2nl3_1_1 tag=proddb_standby
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished recover at 19-MAY-10
 
RMAN>
  8. Refresh controlfile of standby from production

    9. On production

    ?Download text
    1
2
3
4
5

    SQL> alter database create standby controlfile as '/tmp/proddb_stby.ctl';
 
Database altered.
 
SQL>

    On standby, copy the controlfile from production

    ?Download text
    1
2
3
4

    {STBYHOST} /u02/PRODDB/oradata/cntrl $ scp ora10r3@PRIMARY:/tmp/proddb_stby.ctl .
Password: 
proddb_stby.ctl        100% |********************************************************************************************| 14096 KB    00:01    
{STBYHOST} /u02/PRODDB/oradata/cntrl $

    Change init.ora

    ?Download text
    1

    control_files = ('/u02/PRODDB/oradata/cntrl/proddb_stby.ctl')
  9. Startup and see recovery point
    10. ?Download text
    1
2
3
4
5
6
7
8

    STBYHOST(PRODDB)SQL >recover standby database;
ORA-00279: change 7765466166167 generated at 05/19/2010 12:54:59 needed for
thread 1
ORA-00289: suggestion : /u02/PRODDB/arch/PRODDB_1_717173775_62.arc
ORA-00280: change 7765466166167 for thread 1 is in sequence #62
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    You can see that the recovery has skipped archive 60 and 61, which were part of the incremental backup.

]]> http://www.oraclemasters.in/?feed=rss2&p=1166 0 Part 4 – Oracle 10gR2 RAC Solaris 10 – Installation and configuration of RAC database http://www.oraclemasters.in/?p=1111 http://www.oraclemasters.in/?p=1111#comments Sat, 08 May 2010 06:08:40 +0000 Ramesh Menon http://www.oraclemasters.in/?p=1111

With CRS successfully installed and running, we can proceed with installation of Oracle RAC binaries and configure a RAC database. In this section we will see the screenshots for Oracle RAC database creation.

Perform Pre requisite checks

At this stage, we can run the cluvfy utility (stage is pre database installation) as follows
If you get the same errors as I get, you can ignore them as I have successfully installed and configured RAC DB. However, do not proceed on production servers in case of errors or missing patches.

?Download text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274

$ ./runcluvfy.sh stage -pre dbinst -r 10gR2 -osdba dba -n rac-10g-node-01,rac-10g-node-02 -verbose 
 
 
Performing pre-checks for database installation
 
Checking node reachability...
 
Check: Node reachability from node "rac-10g-node-01"
  Destination Node                      Reachable?
  ------------------------------------  ------------------------
  rac-10g-node-01                       yes
  rac-10g-node-02                       yes
Result: Node reachability check passed from node "rac-10g-node-01".
 
 
Checking user equivalence...
 
Check: User equivalence for user "oracle"
  Node Name                             Comment
  ------------------------------------  ------------------------
  rac-10g-node-02                       passed
  rac-10g-node-01                       passed
Result: User equivalence check passed for user "oracle".
 
Checking administrative privileges...
 
Check: Existence of user "oracle"
  Node Name     User Exists               Comment
  ------------  ------------------------  ------------------------
  rac-10g-node-02  yes                       passed
  rac-10g-node-01  yes                       passed
Result: User existence check passed for "oracle".
 
Check: Existence of group "dba"
  Node Name     Status                    Group ID
  ------------  ------------------------  ------------------------
  rac-10g-node-02  exists                    100
  rac-10g-node-01  exists                    100
Result: Group existence check passed for "dba".
 
Check: Membership of user "oracle" in group "dba" [as Primary]
  Node Name         User Exists   Group Exists  User in Group  Primary       Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rac-10g-node-02   yes           yes           yes           no            failed
  rac-10g-node-01   yes           yes           yes           yes           passed
Result: Membership check for user "oracle" in group "dba" [as Primary] failed.
 
Administrative privileges check failed.
 
Checking node connectivity...
 
 
Interface information for node "rac-10g-node-02"
  Interface Name                  IP Address                      Subnet
  ------------------------------  ------------------------------  ----------------
  e1000g0                         192.168.1.101                   192.168.1.0
  e1000g0                         192.168.1.103                   192.168.1.0
  e1000g1                         10.10.10.2                      10.0.0.0
 
 
Interface information for node "rac-10g-node-01"
  Interface Name                  IP Address                      Subnet
  ------------------------------  ------------------------------  ----------------
  e1000g0                         192.168.1.100                   192.168.1.0
  e1000g0                         192.168.1.102                   192.168.1.0
  e1000g1                         10.10.10.1                      10.0.0.0
 
 
Check: Node connectivity of subnet "192.168.1.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  rac-10g-node-02:e1000g0         rac-10g-node-02:e1000g0         yes
  rac-10g-node-02:e1000g0         rac-10g-node-01:e1000g0         yes
  rac-10g-node-02:e1000g0         rac-10g-node-01:e1000g0         yes
  rac-10g-node-02:e1000g0         rac-10g-node-01:e1000g0         yes
  rac-10g-node-02:e1000g0         rac-10g-node-01:e1000g0         yes
  rac-10g-node-01:e1000g0         rac-10g-node-01:e1000g0         yes
Result: Node connectivity check passed for subnet "192.168.1.0" with node(s) rac-10g-node-02,rac-10g-node-01.
 
Check: Node connectivity of subnet "10.0.0.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  rac-10g-node-02:e1000g1         rac-10g-node-01:e1000g1         yes
Result: Node connectivity check passed for subnet "10.0.0.0" with node(s) rac-10g-node-02,rac-10g-node-01.
 
Suitable interfaces for the private interconnect on subnet "192.168.1.0":
rac-10g-node-02 e1000g0:192.168.1.101 e1000g0:192.168.1.103
rac-10g-node-01 e1000g0:192.168.1.100 e1000g0:192.168.1.102
 
Suitable interfaces for the private interconnect on subnet "10.0.0.0":
rac-10g-node-02 e1000g1:10.10.10.2
rac-10g-node-01 e1000g1:10.10.10.1
 
ERROR:
Could not find a suitable set of interfaces for VIPs.
 
Result: Node connectivity check failed.
 
 
Checking system requirements for 'database'...
 
Check: Total memory
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  1.5GB (1572864KB)         512MB (524288KB)          passed
  rac-10g-node-01  1.5GB (1572864KB)         512MB (524288KB)          passed
Result: Total memory check passed.
 
Check: Free disk space in "/tmp" dir
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  2.14GB (2242460KB)        400MB (409600KB)          passed
  rac-10g-node-01  1.88GB (1966732KB)        400MB (409600KB)          passed
Result: Free disk space check passed.
 
Check: Swap space
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  2.5GB (2617048KB)         512MB (524288KB)          passed
  rac-10g-node-01  2.5GB (2617048KB)         512MB (524288KB)          passed
Result: Swap space check passed.
 
Check: System architecture
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  64-bit                    64-bit                    passed
  rac-10g-node-01  64-bit                    64-bit                    passed
Result: System architecture check passed.
 
Check: Operating system version
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  SunOS 5.10                SunOS 5.10                passed
  rac-10g-node-01  SunOS 5.10                SunOS 5.10                passed
Result: Operating system version check passed.
 
Check: Operating system patch for "118345-03"
  Node Name     Applied                   Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  unknown                   118345-03                 failed
  rac-10g-node-01  unknown                   118345-03                 failed
Result: Operating system patch check failed for "118345-03".
 
Check: Operating system patch for "119961-01"
  Node Name     Applied                   Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  119961-05                 119961-01                 passed
  rac-10g-node-01  119961-05                 119961-01                 passed
Result: Operating system patch check passed for "119961-01".
 
Check: Operating system patch for "117837-05"
  Node Name     Applied                   Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  unknown                   117837-05                 failed
  rac-10g-node-01  unknown                   117837-05                 failed
Result: Operating system patch check failed for "117837-05".
 
Check: Operating system patch for "117846-08"
  Node Name     Applied                   Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  unknown                   117846-08                 failed
  rac-10g-node-01  unknown                   117846-08                 failed
Result: Operating system patch check failed for "117846-08".
 
Check: Operating system patch for "118682-01"
  Node Name     Applied                   Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  unknown                   118682-01                 failed
  rac-10g-node-01  unknown                   118682-01                 failed
Result: Operating system patch check failed for "118682-01".
 
Check: Group existence for "dba"
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  rac-10g-node-02  exists                    passed
  rac-10g-node-01  exists                    passed
Result: Group existence check passed for "dba".
 
Check: Group existence for "oinstall"
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  rac-10g-node-02  exists                    passed
  rac-10g-node-01  exists                    passed
Result: Group existence check passed for "oinstall".
 
Check: User existence for "oracle"
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  rac-10g-node-02  exists                    passed
  rac-10g-node-01  exists                    passed
Result: User existence check passed for "oracle".
 
Check: User existence for "nobody"
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  rac-10g-node-02  exists                    passed
  rac-10g-node-01  exists                    passed
Result: User existence check passed for "nobody".
 
System requirement failed for 'database'
 
Checking CRS integrity...
 
Checking daemon liveness...
 
Check: Liveness for "CRS daemon"
  Node Name                             Running
  ------------------------------------  ------------------------
  rac-10g-node-02                       yes
  rac-10g-node-01                       yes
Result: Liveness check passed for "CRS daemon".
 
Checking daemon liveness...
 
Check: Liveness for "CSS daemon"
  Node Name                             Running
  ------------------------------------  ------------------------
  rac-10g-node-02                       yes
  rac-10g-node-01                       yes
Result: Liveness check passed for "CSS daemon".
 
Checking daemon liveness...
 
Check: Liveness for "EVM daemon"
  Node Name                             Running
  ------------------------------------  ------------------------
  rac-10g-node-02                       yes
  rac-10g-node-01                       yes
Result: Liveness check passed for "EVM daemon".
 
Liveness of all the daemons
  Node Name     CRS daemon                CSS daemon                EVM daemon
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  yes                       yes                       yes
  rac-10g-node-01  yes                       yes                       yes
 
Checking CRS health...
 
Check: Health of CRS
  Node Name                             CRS OK?
  ------------------------------------  ------------------------
  rac-10g-node-02                       yes
  rac-10g-node-01                       yes
Result: CRS health check passed.
 
CRS integrity check passed.
 
Checking node application existence...
 
 
Checking existence of VIP node application
  Node Name     Required                  Status                    Comment
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  yes                       exists                    passed
  rac-10g-node-01  yes                       exists                    passed
Result: Check passed.
 
Checking existence of ONS node application
  Node Name     Required                  Status                    Comment
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  no                        exists                    passed
  rac-10g-node-01  no                        exists                    passed
Result: Check passed.
 
Checking existence of GSD node application
  Node Name     Required                  Status                    Comment
  ------------  ------------------------  ------------------------  ----------
  rac-10g-node-02  no                        exists                    passed
  rac-10g-node-01  no                        exists                    passed
Result: Check passed.
 
 
Pre-check for database installation was unsuccessful on all the nodes.
$

Install Oracle ASM Binary

Ideally, Oracle ASM and Oracle homes should be separate, to avoid conflicts when applying patches. In this section we see how to install Oracle ASM binary separately.

Start runinstaller

Runinstaller

Installation Type

ASM Home

Nodes Selection

Prerequisite Checks

Install Software Only

Summary

Progress

Run script as root

Completed

Configure ASM

This section shows how to create the ASM instance and diskgroups to be used by the database using dbca. You could also create the group using the CREATE DISKGROUP command

Start dbca

DBCA

Configure ASM

Select Nodes

ASM Parameter file

Alert Box

Listener Information

Alert Box

Create Data Diskgroup

Diskgroup creation

Post diskgroup creation

Create FRA Diskgroup

Post FRA Diskgroup creation

Install Oracle Home

Follow the same steps as installation of Oracle ASM home, but choose /oracle10g as the base directory for Oracle home

Configure RAC Database

Start DBCA

Create Database

Select Nodes

Database Type

Database Name

Enterprise Manager Configuration

Passwords

Use ASM for Database

Choose Diskgroup for datafiles

Common Location

Choose FRA

Install Sample Schemas

Service

Parameters

File Locations

Create Database

Summary

Progress

Completed

Starting Database

]]> http://www.oraclemasters.in/?feed=rss2&p=1111 2