Thursday, 21 July 2011

Kill OPMN Process and restart the Instance


Causes::

You have previously started the opmn process as an incorrect user (such as root) but are now logged in as the unix user that owns the Application Server installation. The AS unix owner then experiences permission related errors communicating with the root initiated OPMN process.

It can be confirmed that this has taken place by listing the ownership of the running "opmn -d" processes:
$ ps -ef|grep 'opmn -d'|grep -v 'grep'
root 31011 1 0 09:08 ? 00:00:00 /as1013/app/oracle/product/mid101300a/opmn/bin/opmn -d
root 31012 31011 0 09:08 ? 00:00:00 /as1013/app/oracle/product/mid101300a/opmn/bin/opmn -d
Solution

To implement the solution, please execute the following steps:

1. Capture output from "ps -ef|grep 'opmn -d'|grep -v 'grep'" to identify current opmn daemon processes to be terminated.
2. Capture output from "opmnctl status -l" for future reference
- it may be necessary to manually kill processes that the current "opmn -d" processes have started
3. Log in to the UNIX account that owns the current "opmn -d" processes (this may require root privileges).
4. Use "opmnctl stopall" to stop the opmn daemon and all processes
5. Use "ps -ef|grep 'opmn -d'|grep -v 'grep'" to confirm the two "opmn -d" processes have been terminated
- use "kill -9 " on each PID to force this if necessary (again, this may need root privileges)
6. Review the PID column from the "opmnctl status -l" and confirm that each process has been stopped.
- again use "kill -9 " on each PID to force this if necessary (again, this may need root privileges)
7. For the directory of each OC4J instance that exists below $ORACLE_HOME/j2ee,
rename the "persistence" directory to "persistence.old"
8. Confirm if any files under the $ORACLE_HOME have been created under the incorrect unix account:

$ find $ORACLE_HOME -user root -print
With the exception of files below the $ORACLE_HOME/Apache/Apache/bin directory, use "chown" to reset the owner and group of each files that has changed.

For example:

$ cd $ORACLE_HOME/opmn/conf
$ ls -ld1 .*
drwx------ 3 mid101300a oinstall 4096 Sep 7 09:08 .
drwx------ 8 mid101300a oinstall 4096 May 30 09:00 ..
-r-------- 1 root root 21 Sep 7 09:08 .formfactor
Use the ownership of the directory guide the "chown" command needed:

$ chown mid101300a:oinstall .formfactor
9. Log out from the incorrect UNIX account and log back in as the UNIX user owning the AS installation.
10. Start opmn and desired managed processes:

$ opmnctl start

$ opmnctl startproc process-type=HTTP_Server
opmnctl: starting opmn managed processes...

$ opmnctl startproc process-type=home
opmnctl: starting opmn managed processes...
11. Confirm via "ps -ef|grep 'opmn -d'|grep -v 'grep' " that opmn daemon processes are owned by the expected user
12. Use "opmnctl status -l" to confirm all processes have started correctly.

Regards,

Hemesh

Friday, 15 July 2011

Solaris 10 – Change IP Address without reboot

Adding or editing the IP address on a Solaris 10 server is different from the previous versions of the OS (Solaris 9, Solaris 8 etc).

In the previous versions of the Solaris Operating System, you need to edit the /etc/hosts file and add/edit the entry for the IP address and the hostname.

Example:

192.168.1.1 sun1

However, in Solaris 10, you should edit the /etc/hosts file (a symlink to /etc/inet/hosts file) and the /etc/inet/ipnodes file and add an entry for IP address and hostname.

Once done, restart the Network service using

# svcadm restart network/physical

or reboot the server for the changes to take effect.

Although, the /etc/inet/ipnodes files is primarily for IPv6 only, without adding an entry to the file, the IP address (IPv4) doesn’t become active. This seems to be a known problem but the good news is this is now fixed in the Solaris 10 U4 (08/07 build).

Also, ensure that the /etc/netmasks file with the network ID and the netmask.

Monday, 11 July 2011

RMAN DUPLICATE Point in Time

[oracle@testbox ~]$ RESTORE_TIME=`TZ=GMT date +%d-%m-%Y`

[oracle@testbox ~]$ echo $RESTORE_TIME

11-07-2011

[oracle@testbox ~]$ RESTORE_TIME=`TZ=GMT+24 date +%d-%m-%Y`

[oracle@testbox ~]$ echo $RESTORE_TIME

10-07-2011

[oracle@testbox ~]$ RESTORE_TIME=`TZ=GMT+48 date +%d-%m-%Y`

[oracle@testbox ~]$ echo $RESTORE_TIME

10-07-2011

[oracle@testbox ~]$ RESTORE_TIME=`TZ=GMT-48 date +%d-%m-%Y`

[oracle@testbox ~]$ echo $RESTORE_TIME

12-07-2011

[oracle@testbox ~]$ RESTORE_TIME=`date "+%d-%m-%Y"`

[oracle@testbox ~]$ echo $RESTORE_TIME

11-07-2011

[oracle@testbox ~]$ RESTORE_TIME=`date -d yesterday "+%d-%m-%Y"`

[oracle@testbox ~]$ echo $RESTORE_TIME

10-07-2011

[oracle@testbox ~]$

RESTORE_TIME=`date -d yesterday "+%d-%m-%Y"`

echo Beginning copy of $SOURCE to $DEST. Press Ctrl+C to abort within 10 secs...

sleep 10

# Delete old logs and create an automatic log for this operation

find /tmp -name /tmp/$LOGFILE* -mtime +14 -exec rm {} \; 2>/dev/null

exec > >(tee -a /tmp/$LOGFILE.$TIMESTAMP)

echo Generating RMAN script ..

cat <<EOF >> /tmp/rman-duplicate.$$

run {

set until time "to_date('$RESTORE_TIME 23:00:00','DD-MM-YYYY HH24:MI:SS')";

allocate auxiliary channel aux_01 device type disk;

duplicate target database to $DEST;

}

exit;

Friday, 1 July 2011

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!!