Thursday 30 June 2011

Rman duplicate cloning - skip tablespace

Rman duplicate cloning - skip tablespace

Rman duplicate cloning - skip tablespace

Sometimes client asked to clone the database for testing purpose or new environment setup.
Normally we well knew about RMAN cloning.
Also they told we don’t want XYZ schema data for new database.
Normally in my environment each schema user has individual data tablespace & index tablespace.
I will plan to skip the XYZ schema associated tablespaces while cloning the database.

I have tested this scenario in my test server.

Environment Details:

Operating system: Windows XP service pack 2
Database Version: 11.1.0.7 (32 bit)
Source database name: che
Clone database name: skipdb

How its work?


RMAN before restore the datafiles its skip the mentioned skip tablespace belonging datafiles.That datafiles are offline before restore operation is performed. After recovery, its remove the skip tablespace information from current control file.

1.Login into CHE database

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
-------------------- ------------------------------------------------------------
USERS D:\ORACLE\APP\PRODUCT\ORADATA\CHE\USERS01.DBF
UNDOTBS1 D:\ORACLE\APP\PRODUCT\ORADATA\CHE\UNDOTBS01.DBF
SYSAUX D:\ORACLE\APP\PRODUCT\ORADATA\CHE\SYSAUX01.DBF
SYSTEM D:\ORACLE\APP\PRODUCT\ORADATA\CHE\SYSTEM01.DBF

----created new tablespaces

SQL> create tablespace raja_data
2 datafile 'D:\ORACLE\APP\PRODUCT\ORADATA\CHE\raja_data01.dbf' size 100m;

Tablespace created.

SQL> create tablespace raja_indx
2 datafile 'D:\ORACLE\APP\PRODUCT\ORADATA\CHE\raja_indx01.dbf' size 50m;

Tablespace created.

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
-------------------- ------------------------------------------------------------
USERS D:\ORACLE\APP\PRODUCT\ORADATA\CHE\USERS01.DBF
UNDOTBS1 D:\ORACLE\APP\PRODUCT\ORADATA\CHE\UNDOTBS01.DBF
SYSAUX D:\ORACLE\APP\PRODUCT\ORADATA\CHE\SYSAUX01.DBF
SYSTEM D:\ORACLE\APP\PRODUCT\ORADATA\CHE\SYSTEM01.DBF
RAJA_DATA D:\ORACLE\APP\PRODUCT\ORADATA\CHE\RAJA_DATA01.DBF
RAJA_INDX D:\ORACLE\APP\PRODUCT\ORADATA\CHE\RAJA_INDX01.DBF

6 rows selected.

----created new user


SQL> create user raja identified by raja
2 default tablespace RAJA_DATA
3 quota unlimited on RAJA_DATA
4 quota unlimited on RAJA_INDX;

User created.

SQL> grant connect, resource, dba to raja;

Grant succeeded.

----created new objects

SQL> create table raja.objects as select * from dba_objects;

Table created.

SQL> create index raja.object_id_indx on objects(object_id) tablespace raja_indx;

Index created.

SQL> col segment_name for a20
SQL> col tablespace_name for a20
SQL> select segment_name,segment_type,tablespace_name from dba_segments where owner in ('RAJA');

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- ------------------ --------------------
OBJECT_ID_INDX INDEX RAJA_INDX
OBJECTS TABLE RAJA_DATA

2.Backup the target database ( CHE database)

C:\>set oracle_sid=che


Recovery Manager: Release 11.1.0.7.0 - Production on Wed May 5 13:13:19 2010

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

RMAN> connect target sys@che

target database Password:
connected to target database: CHE (DBID=3458403522)

RMAN> backup database plus archivelog;


Starting backup at 05-MAY-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=5 STAMP=717585117
input archived log thread=1 sequence=2 RECID=6 STAMP=717933744
input archived log thread=1 sequence=3 RECID=7 STAMP=720282824
input archived log thread=1 sequence=4 RECID=8 STAMP=718183666
input archived log thread=1 sequence=5 RECID=9 STAMP=718204428
channel ORA_DISK_1: starting piece 1 at 05-MAY-10
channel ORA_DISK_1: finished piece 1 at 05-MAY-10
piece handle=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACKUPSET\2010_05_05\O1_MF_ANNNN_TAG20100
505T131350_5Y28FQJ3_.BKP tag=TAG20100505T131350 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=2 STAMP=716895745
input archived log thread=1 sequence=2 RECID=3 STAMP=716895747
input archived log thread=1 sequence=3 RECID=4 STAMP=716895750
channel ORA_DISK_1: starting piece 1 at 05-MAY-10
channel ORA_DISK_1: finished piece 1 at 05-MAY-10
piece handle=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACKUPSET\2010_05_05\O1_MF_ANNNN_TAG20100
505T131350_5Y28FYY8_.BKP tag=TAG20100505T131350 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 05-MAY-10

Starting backup at 05-MAY-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\ORACLE\APP\PRODUCT\ORADATA\CHE\SYSTEM01.DBF
input datafile file number=00002 name=D:\ORACLE\APP\PRODUCT\ORADATA\CHE\SYSAUX01.DBF
input datafile file number=00005 name=D:\ORACLE\APP\PRODUCT\ORADATA\CHE\RAJA_DATA01.DBF
input datafile file number=00004 name=D:\ORACLE\APP\PRODUCT\ORADATA\CHE\USERS01.DBF
input datafile file number=00006 name=D:\ORACLE\APP\PRODUCT\ORADATA\CHE\RAJA_INDX01.DBF
input datafile file number=00003 name=D:\ORACLE\APP\PRODUCT\ORADATA\CHE\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 05-MAY-10
channel ORA_DISK_1: finished piece 1 at 05-MAY-10
piece handle=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACKUPSET\2010_05_05\O1_MF_NNNDF_TAG20100
505T131406_5Y28G74D_.BKP tag=TAG20100505T131406 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 05-MAY-10
channel ORA_DISK_1: finished piece 1 at 05-MAY-10
piece handle=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACKUPSET\2010_05_05\O1_MF_NCSNF_TAG20100
505T131406_5Y28J9K2_.BKP tag=TAG20100505T131406 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-MAY-10

Starting backup at 05-MAY-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=10 STAMP=718204515
channel ORA_DISK_1: starting piece 1 at 05-MAY-10
channel ORA_DISK_1: finished piece 1 at 05-MAY-10
piece handle=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACKUPSET\2010_05_05\O1_MF_ANNNN_TAG20100
505T131515_5Y28JCRW_.BKP tag=TAG20100505T131515 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-MAY-10

3.Configure the network files.

Listener configuration:


SID_LIST_LISTENER11G=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=che)
(SID_NAME=che)
(ORACLE_HOME=D:\Oracle\app\product\11.1.0\db_1)
)
(SID_DESC=
(GLOBAL_DBNAME=skipdb)
(SID_NAME=skipdb)
(ORACLE_HOME=D:\Oracle\app\product\11.1.0\db_1)
)
)
)

TNS configuration:


CHE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = che)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = che)
)
)


SKIPDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = che)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = skipdb)
)
)


4.Create the instance using oradim utility ( applicable for windows platform)

C:\>oradim -new -sid skipdb
Instance created.

5.Create a password file for skipdb database using orapwd utility

C:\>orapwd file=D:\Oracle\app\product\11.1.0\db_1\dbs\orapwSKIPDB password=skipdbadmin entries=20

6.Create the init. ora file for skipdb database.

Che database: We create the pfile for skipdb from che database & edit the parameter.

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string D:\ORACLE\APP\PRODUCT\11.1.0\D
B_1\DATABASE\SPFILECHE.ORA

SQL> create pfile='D:\Oracle\app\product\admin\skipdb\pfile\initskipdb.ora'
from spfile;

File created.

Init parameter change “skipdb” instead of che & use to start the Skipdb.

skipdb.__db_cache_size=130023424
skipdb.__java_pool_size=12582912
skipdb.__large_pool_size=4194304
skipdb.__oracle_base='D:\Oracle\app\product'#ORACLE_BASE set from environment
skipdb.__pga_aggregate_target=192937984
skipdb.__sga_target=348127232
skipdb.__shared_io_pool_size=0
skipdb.__shared_pool_size=184549376
skipdb.__streams_pool_size=8388608
*.audit_file_dest='D:\Oracle\app\product\admin\skipdb\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='D:\Oracle\app\product\oradata\skipdb\control01.ctl','D:\Oracle\app\product\oradata\skipdb\control02.ctl','D:\Oracle\app\product\oradata\skipdb\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='skipdb'
*.diagnostic_dest='D:\Oracle\app\product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=skipdbXDB)'
*.log_archive_dest='D:\Oracle\app\product\archive\skipdb'
*.log_archive_start=TRUE
*.memory_target=538968064
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTBS1'
db_file_name_convert = ('D:\ORACLE\APP\PRODUCT\ORADATA\CHE','D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB')
log_file_name_convert = ('D:\ORACLE\APP\PRODUCT\ORADATA\CHE','D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB')


7.Create the appropriate folder for skipdb database (admin/oradata folders etc..)


8.Startup the clone database in mount stage


C:\>set oracle_sid=skipdb

C:\>sqlplus

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 28 14:21:08 2010

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

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1348508 bytes
Variable Size 398462052 bytes
Database Buffers 130023424 bytes
Redo Buffers 5828608 bytes

9.Connect the target & auxiliary database using RMAN

C:\>rman target sys/cheadmin@che

Recovery Manager: Release 11.1.0.7.0 - Production on Wed May 5 13:49:04 2010

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

Connected to target database: CHE (DBID=3458403522)

RMAN> connect auxiliary sys/skipdbadmin@skipdb

connected to auxiliary database: SKIPDB (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO SKIPDB SKIP TABLESPACE RAJA_DATA, RAJA_INDX;

Starting Duplicate Db at 05-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=152 device type=DISK
Datafile 5 skipped by request
Datafile 6 skipped by request

contents of Memory Script:
{
set until scn 1000208;
set newname for datafile 1 to
"D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.DBF";
set newname for datafile 3 to
"D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01.DBF";
set newname for datafile 4 to
"D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.DBF";
restore
clone database
skip tablespace "RAJA_INDX",
"RAJA_DATA" ;
}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 05-MAY-10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSTEM01.DB
F
channel ORA_AUX_DISK_1: restoring datafile 00002 to D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.DB
F
channel ORA_AUX_DISK_1: restoring datafile 00003 to D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01.D
BF
channel ORA_AUX_DISK_1: restoring datafile 00004 to D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.DBF

channel ORA_AUX_DISK_1: reading from backup piece D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACK
UPSET\2010_05_05\O1_MF_NNNDF_TAG20100505T131406_5Y28G74D_.BKP
channel ORA_AUX_DISK_1: piece handle=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\BACKUPSET\2010_05
_05\O1_MF_NNNDF_TAG20100505T131406_5Y28G74D_.BKP tag=TAG20100505T131406
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 05-MAY-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "SKIPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=718206667 file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.
DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=718206667 file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01
.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=718206667 file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.D
BF

contents of Memory Script:
{
set until scn 1000208;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 05-MAY-10
using channel ORA_AUX_DISK_1
datafile 5 not processed because file is offline
datafile 6 not processed because file is offline


starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file D:\ORACLE\APP\PRODUCT\FLASH_REC
OVERY_AREA\CHE\ARCHIVELOG\2010_05_05\O1_MF_1_6_5Y28JC3X_.ARC
archived log file name=D:\ORACLE\APP\PRODUCT\FLASH_RECOVERY_AREA\CHE\ARCHIVELOG\2010_05_05\O1_MF_1_6
_5Y28JC3X_.ARC thread=1 sequence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 05-MAY-10

contents of Memory Script:
{
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 535662592 bytes

Fixed Size 1348508 bytes
Variable Size 201329764 bytes
Database Buffers 327155712 bytes
Redo Buffers 5828608 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "SKIPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
set newname for tempfile 1 to
"D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.DBF";
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01.DBF";
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.DBF";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\TEMP01.DBF in control file

cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.DBF RECID=1 STAMP=718206692

cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01.DBF RECID=2 STAMP=718206692

cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.DBF RECID=3 STAMP=718206693

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=718206692 file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.
DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=718206692 file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01
.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=718206693 file name=D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.D
BF

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
# drop offline and skipped tablespaces
sql clone 'drop tablespace "RAJA_INDX" including contents cascade constraints';
# drop offline and skipped tablespaces
sql clone 'drop tablespace "RAJA_DATA" including contents cascade constraints';

}
executing Memory Script

sql statement: drop tablespace "RAJA_INDX" including contents cascade constraints

sql statement: drop tablespace "RAJA_DATA" including contents cascade constraints
Finished Duplicate Db at 05-MAY-10


10.Verify the skipdb database.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- ----------
SKIPDB READ WRITE

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\Oracle\app\product\archive\skipdb
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
--------------- ------------------------------------------------------------
SYSTEM D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSTEM01.DBF
SYSAUX D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\SYSAUX01.DBF
UNDOTBS1 D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\UNDOTBS01.DBF
USERS D:\ORACLE\APP\PRODUCT\ORADATA\SKIPDB\USERS01.DBF

SQL> select username,account_status from dba_users where username='RAJA';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
RAJA OPEN

SQL> select segment_name,segment_type,tablespace_name from dba_segments where owner in ('RAJA');

no rows selected

Other scenario:

A user accidentally dropped a table in production database. We have a proper RMAN backup.

Normally we follow the below steps for recovery:

1. Offline the datafiles/tablespace belonging to table.
2. Restore the datafiles/tablespace.
3. Recover the datafiles.

For this we need an outage for the application. So we using prior backup to clone the database & export the table and import into production. Our database is very huge database. So restoration is very difficult.

That time we used skip tablespace feature. Restore the corresponding tablespace, system, undo & sysaux tablespaces. To reduce the restoration downtime & avoid the space issue



I Hope this article helped to you. Suggestions are welcome.

Wednesday 15 June 2011

Setup VNC Server in Solaris Sparc

Steps to Follow
The following steps are performed on the Solaris 10 update 5 system and the remote system which will be accessing the VNC desktop (the remote system does NOT have to be Solaris 10 update 5)
NOTE: This procedure configures a session that can be accessed via HTTP in a browser such as Mozilla, FireFox, Internet Explorer, etc.

ON THE SERVER (the system you wish to gain remote access to)
=============================================
1. ensure that Solaris 10 Update 5 is installed on the system:

root@solarishost33# more /etc/release
Solaris 10 5/08 s10s_u5wos_10 SPARC
Copyright 2008 Sun Microsystems, Inc. All Rights Reserved.
Use is subject to license terms.
Assembled 24 March 2008

2. ensure that the $PATH variable includes /usr/X11/bin and /usr/openwin/bin:

root@solarishost33# echo $PATH
/usr/bin:/usr/sbin:/usr/ucb:/etc:/usr/platform/sun4u/sbin:/usr/lib/nis:/usr/l
n:/export/install/bin:/usr/X11/bin:/usr/openwin/bin:.

3. execute the vncserver(1) command:

/usr/bin/vncserver -httpd

(screen output)

You will require a password to access your desktops.

Password:
Password must be at least 6 characters - try again
Password:
Verify:

New '10.30.21.33:1 (root)' desktop is10.30.2133:1

Starting applications specified in //.vnc/xstartup
Log file is //.vnc/10.30.21.33:1.log

ON THE CLIENT (the system from which you are making the connection to the server)
===========================================================


1. on remote system browse to:

http://(SERVER):(port started)

(where SERVER is the system where /usr/bin/vncserver -httpd was run and "port started" is 5800 plus the ID specified vncserver startup message on SERVER)

e.g. if the vncserver startup messages says:

"New '10.30.21.33:1 (root)' desktop is10.30.2133:1"

then the URL for the browser window is:

http://10.30.21.33:5801/

in this case since the desktop was started on "1:" vncserver will start on port 5800

"http://10.30.21.33:5801" in a browser to see a VNC remote desktop to SERVER.

2. The "VNC Viewer: Connection Details" window will open. Click "OK" and enter the password that was set in #3 from the "ON THE SERVER" section.

3. A simple session with a terminal window will appear.


Cheers!!

Thursday 9 June 2011

OVM-2008 The Server Pool Master

OVM-2008 The Server Pool Master (192.168.1.101) has been registered with some other pool, and can not register it again.
# service ovs-agent stop
# rm -rf /etc/ovs-agent/db
# service ovs-agent start

OVM-1011 OVM Manager communication with 192.168.1.101 for operation Pre-check cluster root for Server Pool failed:

[root@HemCloud OVS]# cd /opt
[root@HemCloud opt]# ls
oracle ovs-agent-2.2 ovs-agent-2.3 ovs-agent-latest
[root@HemCloud opt]# ovs-agent-2.3/utils/repos.py -n /dev/sda3
[ NEW ] 2988a0bf-77ec-4b98-b896-4e2a6a9a07d6 => /dev/sda3
[root@HemCloud opt]# ovs-agent-2.3/utils/repos.py -l
[ ] 2988a0bf-77ec-4b98-b896-4e2a6a9a07d6 => /dev/sda3
[root@HemCloud opt]# ovs-agent-2.3/utils/repos.py -r 2988a0bf-77ec-4b98-b896-4e2a6a9a07d6
[ R ] 2988a0bf-77ec-4b98-b896-4e2a6a9a07d6 => /dev/sda3

then after add your existing vm from /running_pool .....

Job done!!

Configuring Oracle Binary Permissions


Oracle Database uses several binary files. The most important is the executable oracle in UNIX and Linux flavors and oracle.exe in Windows.
Note the permission on these files. For instance, in UNIX, you may see something like this.
# cd $ORACLE_HOME/bin
# ls -l oracle
-rwsr-s--x 1 oracle oinstall 69344968 Jun 10 14:05 oracle

The permissions (the same in all relevant Oracle versions) are the default. Let's see what they mean. (If you are familiar with the UNIX permissions, you can skip this subsection and proceed to the subsection "Two-Task Architecture.")

The first position indicates the type of the file. In UNIX, everything—regular files, directories, and devices—is considered a file. This is a true file, hence the first position shows "-." Had it been a directory, this position would have shown "d"; in the case of a character special device, it would have shown "c," and so on.
The second position onward shows the permissions given on the file. The permissions are shown in blocks of three, indicating the status for the Read, Write, and Execute respectively. The first three positions show the permissions for the owner, the next three show the permissions given to the group the file belongs to, and the last three show the permissions provided to all others.
Position12345678910
Value-rwsr-s--x
    OwnerGroupOther

In each permission set, the permissions are shown as either a value or "-." If a "-" appears in the place, it indicates that the permission is not granted on that privilege. For instance, in the above case, note the sixth position, indicating that the Write permission for the Group is set to "-," which indicates that the group "dba" (the group the file belongs to) cannot write to this file. If the permission is granted, then the value is set to the corresponding letter. Again, in the above example, the Read permission for the Group (denoted by the fifth position) shows "r," indicating that the group "dba" can read this file.

Note the last three positions, which indicate the permissions for the Others (not the owner, oracle, or the users belonging to the group dba). From the permissions, you can see that Others can simply execute this file but not read it or write to it.
This explains "r," "w," and "x"—for Read, Write and Execute, respectively—but what about the character "s" in the place where there should have been an "x"? This is an interesting twist to the Execute privileges. The presence of this "s" on the permission above indicates that this program is setuid enabled. When the program runs, regardless of who runs it, it will run as the user who owns it, i.e. oracleThis is one way in which the program can be owned by Oracle software but run by anyone who would connect to it. Thus, the program can operate under the privileges of oracle and not the user who runs it, which makes it possible to open database files and so on.
Two-Task Architecture. Recall how Oracle Database processes operate, by decoupling the user process from the server process. If you don't remember this completely, I highly recommend rereading the first few chapters of the Oracle Database 10g Concepts Manual. Below is a highly distilled version of the interaction, which merely lays the foundation for understanding the permissions; it's not a substitute for reviewing the contents of the manual.
When a user connects to an Oracle database—say, with SQL*Plus—Oracle creates a new process to service this user's program. This new process is called the Oracle server process, which differs from the user's process (sqlplus, sqlplus.exe, TOAD.exe, or whatever it else it may be). This server process interacts with the memory structures such as the System Global Area (SGA) and reads from the datafiles; if the data is not found in the buffer cache in the SGA, and so on. Under no circumstances is the user's process (sqlplus) allowed to directly interact with the Oracle database datafiles. Because there are two processes (the user process and the server process) working in tandem to get the work done, this is sometimes known as two-task architecture. If a user process does something potentially disruptive, such as violating the memory management in the host machine, the Oracle database itself is not affected and the damage is limited to the user's process.
(Note that the above applies to Oracle connections in a dedicated server environment. In a multithreaded server environment, this model is a little different in the sense that a single server process can service more than one user process. It's still two-task, but instead of a 1:1 relation between the server and user processes, it's 1:many.)
The server processes are run under the user who owns the Oracle software. Here's an example. Suppose the user logs into the database using SQL*Plus:
$ sqlplus arup/arup

After this, if you search for this process:

$ ps -aef|grep sqlplus

it shows this:

oracle 6339 6185 0 13:06 pts/0 00:00:00 sqlplus

This, of course, assumes that no other SQL*Plus sessions have been running on the server.

Note the process id (6339). Now if you search that process ID:
$ ps -aef|grep 6339

You will get two processes:

oracle 6339 6185 0 13:06 pts/0 00:00:00 sqlplus
oracle 6340 6339 0 13:06 ? 00:00:00 oracleDBA102
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

The first one you've seen before (the process for the SQL*Plus session). The second one—process ID 6340—is the server process that Oracle creates for the user. Note the Parent Process ID of the process; it's 6339, which is the process ID of the SQL*Plus session.

The process name is oracleDBA102 (DESCRIPTION=(LOCAL=YES) (ADDRESS=(PROTOCOL=beq))), which tells you several things. First, the presence of the clause LOCAL=YES indicates that this process started due to another process that is running locally, on the same server as the database itself. It also shows PROTOCOL=beq, which means that the connection was made through a bequeath connection.
You can also find the information about the server process from the dynamic views:
select spid
from v$session s, v$process p
where s.sid = (select sid from v$mystat where rownum <2)
and p.addr = s.paddr;

The value returned by the above query is the process ID of the server process. This is the only way to get the process ID if the client process is on a different server, such as someone running SQL*Plus on a laptop connecting to the database.

Now, assume that the user connects through a slightly modified manner. Instead of connecting directly on the server, she uses the TNS string. Assume that your TNS string looks like this (on the server oradba):
DBA102 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradba)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBA102)
)
)

Now the user connects (on the same server, oradba) as follows:

sqlplus arup/arup@dba102

Check the process ID from the dynamic views:

SQL> select spid
2 from v$session s, v$process p
3 where s.sid = (select sid from v$mystat where rownum <2)
4 and p.addr = s.paddr
5 /

 


 

SPID
------------
6428

The process ID is 6428. Search for this on the server:

$ ps -aef|grep sqlplus | grep -v grep
oracle 6426 6185 0 13:20 pts/0 00:00:00 sqlplus

Now when you search for the server process on the database server:

$ ps -aef|grep 6426 | grep -v grep
oracle 6426 6185 0 13:20 pts/0 00:00:00 sqlplus

you don't see the server process. There is no child process of the user process 6426. But you know from the dynamic performance views that the server process is 6428, so what is the parent process of that?

$ ps -aef|grep 6428 | grep -v grep
oracle 6428 1 0 13:20 ? 00:00:00 oracleDBA102 (LOCAL=NO)

The parent process is 1. But why isn't it 6426?

To understand the answer, you have to understand how different Oracle server processes are created. In the first case, when the user did not use a TNS connect string, the connection was routed directly to the database without going to the listener first. The database created a user process and then handed the control of the process to the process owner, a process known as bequeathing—hence the term bequeath process, which showed up in the process name.
In the second case, when the user was still on the same server but connected through the listener, the listener created the process for the user—which is known as forking. Similarly, if the user process were running on a different machine (such as a laptop), the connection would have to be made to the listener and the listener would have created the process. The process was created by a remote server, hence the process name contains the clause LOCAL=NO. Even if the SQL*Plus session was running on the same server, the fact that it was a non-bequeath connection made it a non-LOCAL one.
(Note: Depending on the OS, you may not see the parent ID of the server process in the same way that you see the SQL*Plus session in bequeath connections. In some cases, even though the connection is bequeath, the parent ID will show as 1. Therefore, don't rely on the parent ID to determine what type of server process it is; use the process name instead.)
Now that you understand the two-task model, let's see if you get the salient point in this discussion. The database creates and runs the server process, not the user who started the client process such as SQL*Plus. The server process uses the executable oracle or oracle.exe, so only the Oracle software owner, named orasoft (named so as to avoid confusion with the term "oracle," which is the name of the executable), should have privileges to execute them—no one else. So why do you need permissions for the others?
The short answer is, you don't. You can remove the unnecessary permissions by issuing this command:
$ chmod 4700 $ORACLE_HOME/bin/oracle

After executing the command, the permissions will look like this:

-rws------ 1 orasoft oinstall 248754168 Oct 8 07:11 oracle

Now we can move on to strategy—via the SUID bit. In this case the SUID bit is set to ON (indicated by rws permissions for the owner).

Strategy 
Because you don't need anyone other than the Oracle software owner (orasoft, in this case) to run the Oracle executable, you should remove the SUID bit from the executable and make it accessible by only the owner—no one else:

$ chmod 0700 $ORACLE_HOME/bin/oracle

The permissions now look like this:

-rwx------ 1 orasoft oinstall 248754168 Oct 8 07:11 oracle

Implications

This is a major change, and it's important that you understand its impact. When a user (not the Oracle software owner) on the server tries a local connection, the executable oracle is run on his behalf as if the user orasoft is running it. Because the server process will open the datafiles (owned by orasoft), either it must run as orasoft or the user must have permissions to open the datafiles.

For example, suppose the UNIX user ananda logs in to the same server the database is on and connects locally:
$ sqlplus arup/arup

The user will immediately get an error:

ERROR:
ORA-12546: TNS:permission denied

 

Enter user-name:

The reason why is very simple: you removed the SUID permission on the file oracle. When the user executes a local connection, he essentially tries to run the executable oracle, but because the SUID is not set, it's not tried as user orasoft but rather as ananda. As user ananda does not have permission to run this file, it will not be executed—hence the ORA-12546 error.

So, how can ananda connect to the database? There are two options. One is to make all the user processes run on a different server than the database server itself—thus there are no bequeath connections to the database, only non-LOCAL ones. Because the non-LOCAL connections go through the listener process and the listener spawns a server process for them, the server process is owned by orasoft (the Oracle software owner) and not by the user who is running the client process. There is no permission to issue.
Alternatively, if you must run some user processes on the database server itself, you can connect through the listener with
$ sqlplus arup/arup@dba102

which has the same effect as a user connecting from outside the server. Now only the user who owns the Oracle software (in this case, orasoft) can connect to the database through a bequeath connection.

DBAs with individual OS IDs will not be able to shut down or start up the database using the command connect / as sysdba, even if they belong to group dba. They can do so with
$ sqlplus /nolog
SQL> connect sys/Password_of_SYS@dba102 as sysdba

Yes, this approach makes use of the SYS password, but that's a better practice compared to / as sysdba. A much better practice yet is to create Oracle userids for individual DBAs: 

connect ANANDA/Password_of_ANANDA@dba102 as sysdba

A favorite hacker trick is to get into the server using any account and then try to force into the database. (A typical "loose door" is the user "nobody.") Even if the hacker does not get into the database, he can create a denial-of-service attack by buffer overflow of the oracle executable. If the ability to execute the file is removed, then the effectiveness of the attack is severely limited. At the same time, as you saw, you have not removed any functionality from legitimate users. Most users connect to the database using the listener anyway, and they will not be affected much.

Action Plan
See if any other user on the system makes a bequeath connection. You can accomplish this by

• Simply asking
• Searching for processes on the server to see if you find something as obvious as SQL*Plus
• Checking the column MACHINE of V$SESSION:
select program
from v$session
where machine = '<machine_name>';

If something comes up, you can identify the exact program running by turning on auditing (which you will learn about in the subsequent phases of this article series)and capturing any program coming from the server.

Action

IF no programs connect from the server, THEN
  Change the permissions of the oracle executable($O_H/bin/oracle)
chmod 0700 $ORACLE_HOME/bin/oracle
ELSIF some program connects from the server
  Change the connection from UserID/Password to UserID/Password@Connect_String
END IF
IF you frequently connect from shell scripts as sqlplus / as sysdba THEN
  Change it to use DBAUser/Password@Connect_String
END IF

How To Configure Data Guard Broker

Configuring Data Guard Broker:

NOTE: You should have your physical standby already setup.

How to Setup Data Guard

1.) Check parameter DG_BROKER_START on primary:

SQL> show parameter DG_BROKER_START

NAME TYPE VALUE

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

dg_broker_start boolean FALSE

2.) Set DG_BROKER_START to true on primary:

SQL> alter system set DG_BROKER_START=true scope=both;

System altered.

3.)Check DG_BROKER_START on standby side:

SQL> show parameter DG_BROKER_START

NAME TYPE VALUE

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

dg_broker_start boolean FALSE

4.) Set DG_BROKER_START to true on standby:

SQL> alter system set DG_BROKER_START=true scope=both;

System altered.

5.) Define a service for the listener that includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME:

Update the listener.ora file on primary and standby

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl.abc.com)

(ORACLE_HOME = /opt/app/oracle/10.2)

(SID_NAME = orcl)

)

(SID_DESC =

(GLOBAL_DBNAME = orcl_DGMGRL.abc.com) -->_DGMGRL.

(ORACLE_HOME = /opt/app/oracle/10.2)

(SID_NAME = orcl)

)

)

6.) Now to create the data guard broker configurations:

[oracle@APP3 admin]$ dgmgrl

DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle

Connected.

DGMGRL> create configuration 'DBTEST'

> as primary database is 'orcl'

> connect identifier is orcl;

Configuration "DBTEST" created with primary database "orcl"

DGMGRL>

DGMGRL> show configuration

Configuration

Name: DBTEST

Enabled: NO

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

orcl - Primary database

Current status for "DBTEST":

DISABLED

DGMGRL>

7.) Now add standby DB to the data broker configuration:

DGMGRL> add database 'orcl1' as

> connect identifier is orcl1

> maintained as physical;

Database "orcl1" added

DGMGRL>

DGMGRL> show configuration

Configuration

Name: DBTEST

Enabled: NO

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

orcl - Primary database

orcl1 - Physical standby database

Current status for "DBTEST":

DISABLED

8.) Now enable the configurations:

DGMGRL> enable configuration

Enabled.

DGMGRL> show configuration

Configuration

Name: DBTEST

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

orcl - Primary database

orcl1 - Physical standby database

Current status for "DBTEST":

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

DGMGRL> show database verbose orcl1

Database

Name: orcl1

Role: PHYSICAL STANDBY

Enabled: YES

Intended State: ONLINE

Instance(s):

orcl

Properties:

InitialConnectIdentifier = 'orcl1'

ObserverConnectIdentifier = ''

LogXptMode = 'ARCH'

Dependency = ''

DelayMins = '0'

Binding = 'OPTIONAL'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '180'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'MANUAL'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '3'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = '/opt/app/oracle/oradata/orcl, /home/oracle/oradata/orcl'

LogFileNameConvert = '/opt/app/oracle/oradata/orcl, /home/oracle/oradata/orcl, /opt/app/oracle/flash_recovery_area/orcl/onlinelog, /home/oracle/oradata/flash_recovery_area/orcl'

FastStartFailoverTarget = ''

StatusReport = '(monitor)'

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

HostName = 'wrpws'

SidName = 'orcl'

LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=wrpws)(PORT=1521))'

StandbyArchiveLocation = '/home/oracle/oradata/orcl/archive'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.arc'

LatestLog = '(monitor)'

TopWaitEvents = '(monitor)'

Current status for "orcl1":

Error: ORA-12545: Connect failed because target host or object does not exist

DGMGRL>

Well the problem in my case was that the hostname was not getting resolved. I added the hostname to the host file and it started working.

After this error I got error:

Current status for "orcl1":

Error: ORA-16664: unable to receive the result from a remote database

Well same problem, communication between the 2 database. Make sure that the /etc/hosts files have entries of all other servers, with name and IP address.

Always review the log_archive_dest_2 parameter, to have the right entries and service is accessible.

DGMGRL> enable configuration

Enabled.

DGMGRL> show configuration

Configuration

Name: DBTEST

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

orcl - Primary database

orcl1 - Physical standby database

Current status for "DBTEST":

SUCCESS

DGMGRL>

This should setup Data Guard Broker.

COMMANDS Available in DGMGRL:

DGMGRL> help

The following commands are available:

add Add a standby database to the broker configuration

connect Connect to an Oracle instance

create Create a broker configuration

disable Disable a configuration, a database, or Fast-Start Failover

edit Edit a configuration, database, or instance

enable Enable a configuration, a database, or Fast-Start Failover

exit Exit the program

failover Change a standby database to be the primary database

help Display description and syntax for a command

quit Exit the program

reinstate Change a disabled database into a viable standby database

rem Comment to be ignored by DGMGRL

remove Remove a configuration, database, or instance

show Display information about a configuration, database, or instance

shutdown Shutdown a currently running Oracle instance

start Start Fast-Start Failover observer

startup Start an Oracle database instance

stop Stop Fast-Start Failover observer

switchover Switch roles between the primary database and a standby database

Use "help " to see syntax for individual commands

Step up Data Guard for Oracle 10g

Steps:

1.) Make sure archive log mode is enabled on your database:

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /opt/app/oracle/oradata/orcl/archive

Oldest online log sequence 108

Next log sequence to archive 109

Current log sequence 109

SQL> select name, log_mode from v$database;

NAME LOG_MODE

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

ORCL ARCHIVELOG

If archive log mode is not enabled. Please enable it using the following link.

How to enable archivelog mode in Oracle 11g database

2.) Enable force logging on the database, so that there is no problems with no logging operations in the future.

SQL> alter database force logging;

Database altered.

3.) Create password file, if you do not have one already.

[oracle@APP3 dbs]$ cd $ORACLE_HOME/dbs

[oracle@APP3 dbs]$ orapwd file=orapworcl password=oracle force=y

[oracle@APP3 dbs]$ ls -lrt orapworcl

-rw-r----- 1 oracle oinstall 1536 Sep 14 08:21 orapworcl

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP

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

SYS TRUE TRUE

4.) Create Standby Redo Logfiles on primary DB.

Current logfile:

SQL> col member a40

SQL> select a.group#,a.status,a.member,b.bytes/1024/1024 from v$logfile a,v$log b

2 where a.group#=b.group#;

GROUP# STATUS MEMBER B.BYTES/1024/1024

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

1 /opt/app/oracle/oradata/orcl/redo01.log 50

2 /opt/app/oracle/oradata/orcl/redo02.log 50

Add standby redo log groups:

SQL> alter database add standby logfile group 3 size 50M;

Database altered.

SQL> alter database add standby logfile group 4 size 50M;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

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

1 ONLINE /opt/app/oracle/oradata/ORCL/redo01.log NO

2 ONLINE /opt/app/oracle/oradata/ORCL/redo02.log NO

3 STANDBY /opt/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5bvzkzgs_.log YES

4 STANDBY /opt/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_4_5bvzl8hf_.log YES

SQL> select * from v$standby_log;

GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME

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

3 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0

4 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0

5.) Check parameter db_unique_name

SQL> show parameters unique

NAME TYPE VALUE

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

db_unique_name string orcl

6.) Add standby related entried to Primary database:

SQL> create pfile='/home/oracle/initprim.ora' from spfile;

Sample init.ora from Primary:

orcl.__db_cache_size=2097152000

orcl.__java_pool_size=16777216

orcl.__large_pool_size=16777216

orcl.__shared_pool_size=536870912

orcl.__streams_pool_size=0

*.audit_file_dest='/opt/app/oracle/admin/orcl/adump'

*.background_dump_dest='/opt/app/oracle/admin/orcl/bdump'

*.compatible='10.2.0.3.0'

*.control_files='/opt/app/oracle/oradata/orcl/control01.ctl','/opt/app/oracle/oradata/orcl/control02.ctl','/opt/app/oracle/oradata/orcl/control03.ctl'

*.core_dump_dest='/opt/app/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.db_unique_name='orcl'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.log_archive_dest_1='location=/opt/app/oracle/oradata/orcl/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'

*.open_cursors=300

*.pga_aggregate_target=823132160

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=2684354560

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/opt/app/oracle/admin/orcl/udump'

db_unique_name=orcl

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl1)'

*.LOG_ARCHIVE_DEST_2='SERVICE=ORCL1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl1'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=3

DB_FILE_NAME_CONVERT='/u01/oradata/orcl','/opt/app/oracle/oradata/orcl'

LOG_FILE_NAME_CONVERT='/u01/oradata/orcl/archive',/opt/app/oracle/oradata/orcl/archive','/u01/oradata/flash_recovery_area/orcl','/opt/app/oracle/flash_recovery_area/orcl/onlinelog'

FAL_SERVER=orcl1

FAL_CLIENT=orcl

Copy the init.ora and make necessary changes to the file to be used at standby side. Changes like location of various files, FAL_SERVER, FAL_CLIENT etc.

Sample init.ora in Standby DB:

orcl.__db_cache_size=2097152000

orcl.__java_pool_size=16777216

orcl.__large_pool_size=16777216

orcl.__shared_pool_size=536870912

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/oradata/orcl/adump'

*.background_dump_dest='/u01/oradata/orcl/bdump'

*.compatible='10.2.0.3.0'

*.control_files='/u01/oradata/orcl/control01.ctl','/u01/oradata/orcl/control02.ctl','/u01/oradata/orcl/control03.ctl'

*.core_dump_dest='/u01/oradata/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest='/u01/oradata/flash_recovery_area/orcl'

*.db_recovery_file_dest_size=2147483648

*.db_unique_name='orcl1'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.log_archive_dest_1='location=/u01/oradata/orcl/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'

*.open_cursors=300

*.pga_aggregate_target=823132160

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=2684354560

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/oradata/orcl/udump'

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl1)'

*.LOG_ARCHIVE_DEST_2='SERVICE=ORCL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=3

DB_FILE_NAME_CONVERT='/opt/app/oracle/oradata/orcl','/u01/oradata/orcl'

LOG_FILE_NAME_CONVERT='/opt/app/oracle/oradata/orcl/archive','/u01/oradata/orcl/archive','/opt/app/oracle/flash_recovery_area/orcl/onlinelog','/u01/oradata/flash_recovery_area/orcl'

FAL_SERVER=orcl

FAL_CLIENT=orcl1

7.) Shutdown the primary database. Use the newly created pfile to startup nomount the database. Then create a spfile for the database. Mount the database and create a standby controlfile.

Shutdown the database and take a cold back of the database, all files including the redo log files. You can also create a standby DB from hot backup.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount pfile='/home/oracle/pfileorcl.ora'

ORACLE instance started.

Total System Global Area 2684354560 bytes

Fixed Size 2086352 bytes

Variable Size 570427952 bytes

Database Buffers 2097152000 bytes

Redo Buffers 14688256 bytes

SQL> create spfile from pfile='/home/oracle/pfileorcl.ora';

File created.

Meanwhile I also received the error:

create spfile from pfile='/home/oracle/pfileregdb.ora'

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [kspsetpao1], [1753], [1700], [*],

[user_dump_dest], [33], [], []

Note: This error usually comes when the syntax of the pfile is wrong somewhere, please fix the pfile and try again. It worked for me.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2684354560 bytes

Fixed Size 2086352 bytes

Variable Size 570427952 bytes

Database Buffers 2097152000 bytes

Redo Buffers 14688256 bytes

Database mounted.

Database opened.

8.) Shutdown the database again and take a cold backup of all files.

9.) Create standby control file.

SQL> startup mount

ORACLE instance started.

Total System Global Area 2684354560 bytes

Fixed Size 2086352 bytes

Variable Size 570427952 bytes

Database Buffers 2097152000 bytes

Redo Buffers 14688256 bytes

Database mounted.

--Then mount and create a standby controlfile.

SQL> alter database create standby controlfile as 'standby.ctl';

Database altered.

-- Open the primary read write.

SQL> alter database open;

Database altered.

10.) Transfer all the file from the cold backup from Primary to Standby server. Also copy the password file from primary to standby.

Also copy the standby controlfile created in step 9 and copy if with the right name and location on standby server.

I use SFTP for transferring the files.

11.) Add entries for the primary db and standby DB in both primary and standby servers. i.e. primary server should have its own (orcl) and standby server (orcl1) tns entry.

12.) Copy the pfile from step 6 for standby DB.

Now try to nomount the standby database with the new pfile.

[oracle@dbtest dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 15 04:57:32 2009

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/pfilestbregdb.ora';

ORACLE instance started.

Total System Global Area 1694498816 bytes

Fixed Size 1219784 bytes

Variable Size 402654008 bytes

Database Buffers 1275068416 bytes

Redo Buffers 15556608 bytes

13.) Create spfile from pfile.

SQL> create spfile from pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/pfilestbregdb.ora';

File created.

14.) Shutdown the DB and do a startup mount.

SQL>startup mount;

15.) Start REDO apply process:

SQL> alter database recover managed standby database disconnect from session;

OR

SQL> alter database recover managed standby database nodelay disconnect parallel 8;

16.) Verification.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

To check archive gap:

SELECT thread#, low_sequence#, high_sequence# from V$archive_gap;

for RAC

SELECT thread#, low_sequence#, high_sequence# from gv$archive_gap;

To stop redo apply:

alter database recover managed standby database cancel;

17.) Check alert log files and verify that you did not receive any error.

18.) Switch some logfiles on the Primary and check if the same are getting applied to the standby.

on Primary:

SQL> alter system switch logfile;

on standby:

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

Thanks should be it, your Physical Standby DB should be working fine.

Wednesday 8 June 2011

Understand AWR Report Term

AWR report is broken into multiple parts.

1)Instance information:-
This provides information the instance name , number,snapshot ids,total time the report was taken for and the database time during this elapsed time.

Elapsed time= end snapshot time – start snapshot time
Database time= Work done by database during this much elapsed time( CPU and I/o both add to Database time).If this is lesser than the elapsed time by a great margin, then database is idle.Database time does not include time spend by the background processes.

2)Cache Sizes : This shows the size of each SGA region after AMM has changed them. This information
can be compared to the original init.ora parameters at the end of the AWR report.

3)Load Profile: This important section shows important rates expressed in units of per second and
transactions per second.This is very important for understanding how is the instance behaving.This has to be compared to base line report to understand the expected load on the machine and the delta during bad times.

4)Instance Efficiency Percentages (Target 100%): This section talks about how close are the vital ratios like buffer cache hit, library cache hit,parses etc.These can be taken as indicators ,but should not be a cause of worry if they are low.As the ratios cold be low or high based in database activities, and not due to real performance problem.Hence these are not stand alone statistics, should be read for a high level view .

5)Shared Pool Statistics: This summarizes changes to the shared pool during the snapshot
period.

6)Top 5 Timed Events :This is the section which is most relevant for analysis.This section shows what % of database time was the wait event seen for.Till 9i, this was the way to backtrack what was the total database time for the report , as there was no Database time column in 9i.

7)RAC Statistics :This part is seen only incase of cluster instance.This provides important indication on the average time take for block transfer, block receiving , messages ., which can point to performance problems in the Cluster instead of database.

8)Wait Class : This Depicts which wait class was the area of contention and where we need to focus.Was that network, concurrency, cluster, i/o Application, configuration etc.

9)Wait Events Statistics Section: This section shows a breakdown of the main wait events in the
database including foreground and background database wait events as well as time model, operating
system, service, and wait classes statistics.

10)Wait Events: This AWR report section provides more detailed wait event information for foreground
user processes which includes Top 5 wait events and many other wait events that occurred during
the snapshot interval.

11)Background Wait Events: This section is relevant to the background process wait events.

12)Time Model Statistics: Time mode statistics report how database-processing time is spent. This
section contains detailed timing information on particular components participating in database
processing.This gives information about background process timing also which is not included in database time.

13)Operating System Statistics: This section is important from OS server contention point of view.This section shows the main external resources including I/O, CPU, memory, and network usage.

14)Service Statistics: The service statistics section gives information services and their load in terms of CPU seconds, i/o seconds, number of buffer reads etc.

15)SQL Section: This section displays top SQL, ordered by important SQL execution metrics.

a)SQL Ordered by Elapsed Time: Includes SQL statements that took significant execution
time during processing.

b)SQL Ordered by CPU Time: Includes SQL statements that consumed significant CPU time
during its processing.

c)SQL Ordered by Gets: These SQLs performed a high number of logical reads while
retrieving data.

d)SQL Ordered by Reads: These SQLs performed a high number of physical disk reads while
retrieving data.

e)SQL Ordered by Parse Calls: These SQLs experienced a high number of reparsing operations.

f)SQL Ordered by Sharable Memory: Includes SQL statements cursors which consumed a large
amount of SGA shared pool memory.

g)SQL Ordered by Version Count: These SQLs have a large number of versions in shared pool
for some reason.

16)Instance Activity Stats: This section contains statistical information describing how the database
operated during the snapshot period.

17)I/O Section: This section shows the all important I/O activity.This provides time it took to make 1 i/o say Av Rd(ms), and i/o per second say Av Rd/s.This should be compared to the baseline to see if the rate of i/o has always been like this or there is a diversion now.

18)Advisory Section: This section show details of the advisories for the buffer, shared pool, PGA and
Java pool.

19)Buffer Wait Statistics: This important section shows buffer cache waits statistics.

20)Enqueue Activity: This important section shows how enqueue operates in the database. Enqueues are
special internal structures which provide concurrent access to various database resources.

21)Undo Segment Summary: This section gives a summary about how undo segments are used by the database.
Undo Segment Stats: This section shows detailed history information about undo segment activity.

22)Latch Activity: This section shows details about latch statistics. Latches are a lightweight
serialization mechanism that is used to single-thread access to internal Oracle structures.The latch should be checked by its sleeps.The sleepiest Latch is the latch that is under contention , and not the latch with high requests.Hence run through the sleep breakdown part of this section to arrive at the latch under highest contention.

23)Segment Section: This portion is important to make a guess in which segment and which segment type the contention could be.Tally this with the top 5 wait events.

Segments by Logical Reads: Includes top segments which experienced high number of
logical reads.

Segments by Physical Reads: Includes top segments which experienced high number of disk
physical reads.

Segments by Buffer Busy Waits: These segments have the largest number of buffer waits
caused by their data blocks.

Segments by Row Lock Waits: Includes segments that had a large number of row locks on
their data.

Segments by ITL Waits: Includes segments that had a large contention for Interested
Transaction List (ITL). The contention for ITL can be reduced by increasing INITRANS storage
parameter of the table.

24)Dictionary Cache Stats: This section exposes details about how the data dictionary cache is
operating.

25)Library Cache Activity: Includes library cache statistics which are needed in case you see library cache in top 5 wait events.You might want to see if the reload/invalidations are causing the contention or there is some other issue with library cache.

26)SGA Memory Summary:This would tell us the difference in the respective pools at the start and end of report.This could be an indicator of setting minimum value for each, when sga)target is being used..

27)init.ora Parameters: This section shows the original init.ora parameters for the instance during
the snapshot period.