Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for January, 2011

Move all datafiles including system to different directory / Mount point

Posted by Hendry chinnapparaj on January 24, 2011

Steps

———–

1) Shutdown database after creating pfile

2) copy/move datafiles, control files to new directory

3) edit init file, change the location for controlfiles

4) Rename / relocate datafile

5) open the database

 

SQL> select name from v$datafile;

 

NAME

——————————————————————————–

/u04/oradata/TEST/system01.dbf

/u04/oradata/TEST/undotbs01.dbf

/u04/oradata/TEST/sysaux01.dbf

/u04/oradata/TEST/users01.dbf

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

cpsoa1:/u04/oradata/TEST> cp -p system01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p undotbs01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p sysaux01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p users01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p temp01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> pwd

/u04/oradata/TEST

 

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  524288000 bytes

Fixed Size                  2085296 bytes

Variable Size             163581520 bytes

Database Buffers          352321536 bytes

Redo Buffers                6299648 bytes

Database mounted.

 

SQL> Alter database rename file ‘/u04/oradata/TEST/system01.dbf’ to ‘/u02/oradata/TEST/system01.dbf’;

 

Database altered.

 

SQL> Alter database rename file ‘/u04/oradata/TEST/undotbs01.dbf’ to ‘/u02/oradata/TEST/undotbs01.dbf’;

 

Database altered.

 

SQL>  Alter database rename file ‘/u04/oradata/TEST/sysaux01.dbf’ to ‘/u02/oradata/TEST/sysaux01.dbf’;

 

Database altered.

 

SQL> Alter database rename file ‘/u04/oradata/TEST/users01.dbf’ to ‘/u02/oradata/TEST/users01.dbf’;

 

Database altered.

 

SQL> Alter database rename file ‘/u04/oradata/TEST/temp01.dbf’ to ‘/u02/oradata/TEST/temp01.dbf’;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> select name from v$datafile;

 

NAME

——————————————————————————–

/u02/oradata/TEST/system01.dbf

/u02/oradata/TEST/undotbs01.dbf

/u02/oradata/TEST/sysaux01.dbf

/u02/oradata/TEST/users01.dbf

 

SQL> select name from v$tempfile;

 

NAME

——————————————————————————–

/u02/oradata/TEST/temp01.dbf

 

control files move

———————–

SQL> show parameter control

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

control_file_record_keep_time        integer     7

control_files                        string      /u04/oradata/TEST/control01.ct

l, /u04/oradata/TEST/control02

.ctl, /u04/oradata/TEST/contro

l03.ctl

 

 

SQL> show parameter spfile

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

spfile                               string      /u01/oracle/product/10.2/db_1/

dbs/spfileTEST.ora

 

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Vi initTEST.ora

*.control_files=’/u02/oradata/TEST/control01.ctl’,’/u02/oradata/TEST/control02.ctl’,’/u02/oradata/TEST/control03.ctl’

 

cpsoa1:/u04/oradata/TEST> mv control01.ctl /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> mv control02.ctl /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> mv control03.ctl /u02/oradata/TEST/

 

SQL> select name from v$controlfile;

 

NAME

——————————————————————————–

/u02/oradata/TEST/control01.ctl

/u02/oradata/TEST/control02.ctl

/u02/oradata/TEST/control03.ctl

 

Advertisements

Posted in Uncategorized | Leave a Comment »

Move Oracle10gR2 – 10.2.0.4.0 Database from Veritas File System to JFS2

Posted by Hendry chinnapparaj on January 24, 2011

Hi

I have tested this successfully, no issues

– Just shutdown the database residing in veritas file system 10.2.0.4.0 and aix 5.3

– ftp all the files including datafiles, redo, standby, pwd, initfile , spfile etc,. to the JFS2 – AIX 5.3

– just startup the database

– all should be fine and easy if the directories / mount points are the same on both servers.

Shalom

Posted in Admin | Leave a Comment »

ORA-06512: at “SYS.OLAPIHISTORYRETENTION”, line 1

Posted by Hendry chinnapparaj on January 24, 2011

problem

—————–

SQL> startup pfile=initTEST.ora
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  2083760 bytes
Variable Size             100664400 bytes
Database Buffers          205520896 bytes
Redo Buffers                6303744 bytes
Database mounted.
ORA-03113: end-of-file on communication channel

The alert log has

replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=1220658
Mon Jan 24 16:56:35 2011
Errors in file /u01/oracle/admin/TEST/udump/test_ora_2367508.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
ORA-06512: at “SYS.OLAPIHISTORYRETENTION”, line 1
ORA-06512: at line 15
Mon Jan 24 16:56:35 2011

and the trace file has

Instance name: TEST
Redo thread mounted by this instance: 1
Oracle process number: 14
Unix process pid: 2367508, image: oracle@uatu009 (TNS V1-V3)

*** ACTION NAME:() 2011-01-24 16:56:34.754
*** MODULE NAME:(sqlplus@uatu009 (TNS V1-V3)) 2011-01-24 16:56:34.754
*** SERVICE NAME:(SYS$USERS) 2011-01-24 16:56:34.754
*** SESSION ID:(160.3) 2011-01-24 16:56:34.754
kwqmnich: current time::  8: 56: 34
kwqmnich: instance no 0 check_only flag 1
kwqmnich: initialized job cache structure
Error in executing triggers on database startup
*** 2011-01-24 16:56:35.565
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
ORA-06512: at “SYS.OLAPIHISTORYRETENTION”, line 1
ORA-06512: at line 15

 

Solution

—————-

query for the triggers that fires when the database startsup and disable them

SQL> SELECT TRIGGER_NAME FROM DBA_TRIGGERS WHERE TRIGGERING_EVENT LIKE ‘%STARTUP%’
2  ;

TRIGGER_NAME
——————————
AURORA$SERVER$STARTUP
OLAPISTARTUPTRIGGER

SQL> alter trigger AURORA$SERVER$STARTUP disable;

Trigger altered.

SQL> alter trigger OLAPISTARTUPTRIGGER disable;

Trigger altered.

OLAP Trigger may not be needed in this case for us

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  2083760 bytes
Variable Size             100664400 bytes
Database Buffers          205520896 bytes
Redo Buffers                6303744 bytes
Database mounted.
Database opened.

 

No errors

 

Posted in ORA- ERRORS relating to ORACLE DATABASE | Leave a Comment »

ORA-38754: FLASHBACK DATABASE not started; required redo log is not available

Posted by Hendry chinnapparaj on January 19, 2011

Problem

—————

When trying to do flashback in a data guard environment in 10g.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3321888768 bytes
Fixed Size                  2087680 bytes
Variable Size            2013267200 bytes
Database Buffers         1291845632 bytes
Redo Buffers               14688256 bytes
Database mounted.
SQL> flashback database to restore point primary_jan17;
flashback database to restore point primary_jan17
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38761: redo log sequence 57 in thread 1, incarnation 3 could not be
accessed

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     108
Next log sequence to archive   111
Current log sequence           111

Cause

———-

The required archived logs were already backedup to tape / deleted from disk , hence not available for flashback.

Solution

—————

Restore the archive logs from tape

RMAN> restore archivelog from sequence 40;

or

RMAN> restore archivelog from time ‘sysdate – 3’;

Starting restore at 19-01-2011 14:22:55
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

archive log thread 1 sequence 63 is already on disk as file /u03/oradata/flash_recovery_area/CPSOA1_JS/archivelog/2011_01_17/o1_mf_1_63_1EfFdyHyY_.arc
archive log thread 1 sequence 64 is already on disk as file /u03/oradata/flash_recovery_area/CPSOA1_JS/archivelog/2011_01_17/o1_mf_1_64_1EfLwYh1X_.arc
archive log thread 1 sequence 65 is already on disk as file /u03/oradata/flash_recovery_area/CPSOA1_JS/archivelog/2011_01_17/o1_mf_1_65_1EfMEfwdz_.arc
archive log thread 1 sequence 66 is already on disk as file /u03/oradata/flash_recovery_area/CPSOA1_JS/archivelog/2011_01_17/o1_mf_1_66

channel ORA_SBT_TAPE_1: starting archive log restore to default destination
channel ORA_SBT_TAPE_1: restoring archive log
archive log thread=1 sequence=37
channel ORA_SBT_TAPE_1: reading from backup piece tpm2930c_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=tpm2930c_1_1 tag=TAG20110116T161052
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:02:25
channel ORA_SBT_TAPE_1: starting archive log restore to default destination
channel ORA_SBT_TAPE_1: restoring archive log
archive log thread=1 sequence=38
channel ORA_SBT_TAPE_1: reading from backup piece trm293n4_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=trm293n4_1_1 tag=TAG20110116T162300
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_SBT_TAPE_1: starting archive log restore to default destination
channel ORA_SBT_TAPE_1: restoring archive log
archive log thread=1 sequence=39

SQL> flashback database to restore point primary_jan17;

Flashback complete.

SQL> alter database open resetlogs;

Also do the flashback on the standby database

SQL> flashback database to restore point stby_jan17;

SQL> Recover managed standby database disconnect from session

 

and enable the dataguard configuration

 

Shalom

Posted in FLASHBACK | Leave a Comment »

ORA-12705: Cannot access NLS data files or invalid environment specified

Posted by Hendry chinnapparaj on January 7, 2011

PROBLEM

————–

The following error occurred when trying to connect to sql*plus to run a script.

ORA-12705: Cannot access NLS data files or invalid environment specified

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jan 7 10:09:37 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

Solution

————

check the NLS_LANG unix env setting

export NLS_LANG=”AMERICAN_AMERICA.US7ASCII”

In the above case, the “A” was missing – no more errors after adding the”A” for the AMERICAN.

Posted in ORA- ERRORS relating to ORACLE DATABASE | Leave a Comment »