Changing timezone in Oracle 11g RAC

In order to change timezone of all processes running under control of Oracle 11g RAC (alter log dates and other relevant information), you should edit on the each node of the cluster file CRS_HOME/crs/install/s_crsconfig_HOSTNAME_env.txt and change variable TZ. For an instance:

1. Stop all RAC processes on the node:

/etc/init.d/ohasd stop

2. vi /crs/crs/install/s_crsconfig_xxx_env.txt

3. Comment out old timezone definition
#TZ=GMT+03:00

and add new one

TZ=Europe/Minsk

4. Start your RAC stack on the node

/etc/init.d/ohasd start

Another useful case of using bbed

Well, there was one oracle db of version 10.2.0.5.1  without any backup. Suddenly, all members of all redo log files were lost. It was extrimely important to recover the database to operable state.

Possible variants for recover are:
1. Using one of the following parameters or its combination

_corrupted_rollback_segments
_allow_resetlogs_corruption
_allow_read_only_corruption

2. Using bbed

Let discuss about second variant.

I forgot to say there ware dozens of uncommitted transactions at the moment of crashing.

So,

a) Make copy of  the database remains to the safe place.

b) using bbed, write to the headers of each datafile the following (example of the system tablespace)

m /x 0000 dba 1,1 offset 138
m /x 0000 dba 1,1 offset 16
m /x 01 dba 1,1 offset 15

c) recreate controlfile using trace script with resetlogs option

d) recover database until cancel
cancel
alter database open resetlogs ;

e) at this point the database can open, but in my case there were lots of ora-00600 [2662] errors, for example

ORA-00600: internal error code, arguments: [2662], [291], [3742542507], [291], [3742542553], [30094534]

It testifyed the presense of data blocks in the database with scn higher then scn of resetlog operation. In this case convert 6th argument to DBA, access the block and correct right scn (change 5th to 3th) using bbed, for example

m /x 13 dba 8,65 offset 16382
m /x 9d dba 8,65 offset 16383
m /x 13 dba 8,65 offset 8
m /x 9d dba 8,65 offset 9

Repeat all steps again, starting with point (b)

f) And finally, the database was opened. Of couse, I had made backup of the data, using transportable tablespaces.

Today the db works without issues.

That's all

Real example of using bbed

Situation
------------------

There is some confusion, but I'll try to explain...

It's necessary to import tablespace, where dumpfile and datafile of tablespace is not from one set.
(original datafile, which prepared to import, was lost and database, from which might to run export again, also was lost (control files, redo files and datafiles were rewritten), but accidentally datafile, which previously imported from export dump, not rewritten). There are no any backups or copyes of interested datafile. There were no structural and changes in the schema in exported tablespace or in tablespace.

Solution
-----------------

So, we have database which know nothing about dafafile from dump, dumpfile from exp set and dafafile from previous state database (which contains different from dumpfile scn values).

Import uses procedures from package sys.dbms_plugts (from dumpfile)

-- import begins
EXECTRP sys.dbms_plugts.beginImport ('10.2.0.5.0',152,'2000',13,'Linux x86 64-bit',2304718,2942799951,1,0,0,0);
EXECTRP sys.dbms_plugts.checkCompType('COMPATSG','10.2.0.5.0');
-- check that user exists
EXECTRP sys.dbms_plugts.checkUser('XXX');
-- plugging tablespace and datafile
EXECTRP sys.dbms_plugts.beginImpTablespace('XXXX',37,'SYS',1,0,16384,1,1236469596676,1,2147483645,4,64,4,0,1,0,4,2517399282,1,33,8657726773,NULL,287,3813661688,NULL,NULL);
EXECTRP sys.dbms_plugts.checkDatafile(NULL,2517399282,22,6400,37,27,4194302,6400,1236469277014,1236469596676,1236469119757,113246210,NULL,NULL,NULL);

.................

-- import ends
EXECTRP sys.dbms_plugts.endImport;

In order to perform import its necessary to correct scn values in dumpfile, anyway error will occur
ORA-19721: Cannot find datafile with absolute file number 22 in tablespace XXXX

Need to correct :
- beginImpTablespace (clean_scn,1236469596676) ;
- checkDatafile(creation_scn,1236469277014;checkpoint_scn(coincide with beginImpTablespace.clean_scn,1236469596676);reset_scn,1236469119757)

To figure out scn value, use bbed:

1.

BBED> set filename 'FILE'

2.

BBED> map
File: FILE
Block: 1 Dba:0x00000000
------------------------------------------------------------
Data File Header

struct kcvfh, 676 bytes @0

ub4 tailchk @16380

3.

BBED> p kcvfh

struct kcvfh, 676 bytes @0
....................................
struct kcvfhcrs, 8 bytes @100
ub4 kscnbas @100 0xe38a4aac
ub2 kscnwrp @104 0x011f
....................................
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0xe387d20b
ub2 kscnwrp @120 0x011f
....................................
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0xe3d23120
ub2 kscnwrp @488 0x011f
....................................

Here:

kcvfhcrs - creation_scn
kcvfhrls - reset_scn
kcvfhckp - checkpoing_scn


scn evaluated as kscnwrp+kscnbas and after that transform to decimal number :

creation_scn : 011fe38a4aac ---> 1236473105068
reset_scn : 011fe387d20b ---> 1236472943115
checkpoint_scn : 011fe3d23120 ---> 1236477817120

4.

Edit dumpfile with new scn values

EXECTRP sys.dbms_plugts.beginImpTablespace('XXXX',37,'SYS',1,0,16384,1,1236477817120,1,2147483645,4,64,4,0,1,0,4,2517399282,1,33,8657726773,NULL,287,3813661688,NULL,NULL);
EXECTRP sys.dbms_plugts.checkDatafile(NULL,2517399282,22,6400,37,27,4194302,6400,1236473105068,1236477817120,1236472943115,113246210,NULL,NULL,NULL);

5.

Run imp

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in CL8MSWIN1251 character set and AL16UTF16 NCHAR character set
import server uses RU8PC866 character set (possible charset conversion)
export client uses RU8PC866 character set (possible charset conversion)
. importing XXX's objects into XXX
. . importing table ...
.......................
Import terminated successfully without warnings.

6.

change tablespace status

SQL> alter tablespace xxxx read write ;

Tablespace altered.

7.

That's all

In other situations, it may be necessary change DBID, number of blocks and other parameters.