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 May, 2011

ORA-01113: file 1 needs media recovery during open reset logs

Posted by Hendry chinnapparaj on May 16, 2011

Problem

———–

When you’re cloning a database, get the ora error below during open resetlogs

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: ‘E:\ORADATA\LN3D\SYSTEM01.DBF’

Solution

———

Copy the redolog from the source database where the cold backup was done to the target database redo directory

And then

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 23735596903 generated at 05/16/2011 14:35:45 needed for

thread 1

ORA-00289: suggestion : D:\ORACLE\ORA92\RDBMS\ARC52958.001

ORA-00280: change 23735596903 for thread 1 is in sequence #52958

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

d:\oracle\oradata\ln3d\redo01.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

Advertisements

Posted in Clone or Duplicate | Leave a Comment »

ORA-16086: standby database does not contain available standby log files

Posted by Hendry chinnapparaj on May 10, 2011

Alert log shows the errors below and log gap detected for the dataguard environment

PRIMARY DATABASE SERVER

————————————-

Mon May  9 11:36:43 2011

LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16086)

LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned

LGWR: Error 16086 creating archivelog file ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=drpserver)(PORT=1521)

))(CONNECT_DATA=(SERVICE_NAME=lmsprd_js_XPT)(INSTANCE_NAME=lmsprd)(SERVER=dedicated)))’

Mon May  9 11:36:43 2011

Errors in file /u01/oracle/admin/lmsprd/bdump/lmsprd_lgwr_36962468.trc:

ORA-16086: standby database does not contain available standby log files

Mon May  9 11:36:43 2011

Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED

LGWR: Failed to archive log 3 thread 1 sequence 25059 (16086)

Mon May  9 11:36:47 2011

Thread 1 advanced to log sequence 25059 (LGWR switch)

Current log# 3 seq# 25059 mem# 0: /u50/oradata/lmsprd/redo_3a.log

Current log# 3 seq# 25059 mem# 1: /u51/oradata/lmsprd/redo_3b.log

Mon May  9 11:42:21 2011

STANDBY DATABASE SERVER

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

SQL> select group#,sequence#,bytes,used,archived,status from v$standby_log;

GROUP#  SEQUENCE#      BYTES       USED ARC STATUS

———- ———- ———- ———- — ———-

14          0 2147483648        512 NO  UNASSIGNED

15          0 2147483648        512 NO  UNASSIGNED

16          0 2147483648        512 NO  UNASSIGNED

17          0 2147483648        512 YES UNASSIGNED

Trace file at Standby Server

—————————

rfxdgid:0x0

rfxogapflg:0x0

rfsarb.kccicrls:0:79191975

rfsarb.kccicrlc:662481912

Error 16086 creating standby archive log file at host ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=drpserver)

(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=lmsprd_js_XPT)(INSTANCE_NAME=lmsprd)(SERVER=dedicated)))’

*** 2011-05-09 12:24:55.631 62692 kcrr.c

LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16086)

*** 2011-05-09 12:24:55.631 62692 kcrr.c

LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned

*** 2011-05-09 12:24:55.631 62692 kcrr.c

LGWR: Error 16086 creating archivelog file ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=drpserver)(PORT=1521)

))(CONNECT_DATA=(SERVICE_NAME=lmsprd_js_XPT)(INSTANCE_NAME=lmsprd)(SERVER=dedicated)))’

ORA-16086: standby database does not contain available standby log files

*** 2011-05-09 12:24:55.631 60970 kcrr.c

kcrrfail: dest:2 err:16086 force:0 blast:1

Receiving message from LNSb

*** 2011-05-09 12:24:55.637 57269 kcrr.c

Making upidhs request to LNSb (ocis 0x1104a3cf8). Begin time is <05/09/2011 12:24:51> and NET_TIMEOUT <180> seconds

NetServer pid:11927790

*** 2011-05-09 12:24:59.637 57441 kcrr.c

upidhs done status 0

*** 2011-05-09 12:30:07.969

Oracle Fix Recommended

—————————-

Increase the log file size or add more redo logs

Workaround / FIX

——————–

Disable and enable Dataguard broker from the Primary

Dgmgrl sys/*****

Show configuration

Disable configuration

Enable configuration

After disabling / enabling dataguard broker  configuration

select group#,sequence#,bytes,used,archived,status from v$standby_log;

GROUP#  SEQUENCE#      BYTES       USED ARC STATUS

———- ———- ———- ———- — ———-

14      25072 2147483648   10079744 YES ACTIVE

15          0 2147483648        512 NO  UNASSIGNED

16          0 2147483648        512 NO  UNASSIGNED

17          0 2147483648        512 YES UNASSIGNED

Check the Log Gap for Data guard – run the Log Gap check Job.

Posted in Uncategorized | Leave a Comment »

Clone Oracle9i Database on same Windows Server

Posted by Hendry chinnapparaj on May 10, 2011

Summary :-

                      
The following steps must be on Source -Production instance/Server

Step 1. Shutdown database in normal mode and start it up in restricted mode.

                        Step 2. Take the backup of control file

                        Step 3. Shutdown database again  in normal mode.                    

Step 4. Copy init parameter file ,control file script  and all the database file on the destination server/location, once all the files are successfully copied, you may startup the database normally.

                        The following step must be on destination – Clone instance/server

                        Step 5. Edit init parameter file and control file script.

                        Step 6.  New Environment setup .

                        Step 7. Connect with sqlplus and recreate control file

                        Step 8. Open the database in resetlogs.

                        Step 9. Shutdown the database in normal mode

                        Step 10. Take the cold backup and start the database in archive/non archive mode.

Details :-


Step 1 – 3 on the Source Server ( Server A)

SVRMGR> connect internal/oracle

Connected.

SVRMGR> shutdown ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SVRMGR> startup restrict

ORACLE instance started.

Total System Global Area                         57124108 bytes

Fixed Size                                          70924 bytes

Variable Size                                    40198144 bytes

Database Buffers                                 16777216 bytes

Redo Buffers                                        77824 bytes

Database mounted.

Database opened.

SVRMGR> alter database backup controlfile to trace;

Statement processed.

SVRMGR>

SVRMGR> show parameter user_dump_dest

NAME                                TYPE    VALUE

———————————– ——- ————————-

user_dump_dest                      string E:\ora816\admin\ora816\udump

In Windows

D:\oracle\admin\ora816\udump

Note :- Backup control file will generate in user dump destination as above. Check for the latest Ora<xxxxx>.trc .  Rename this file to Ctrl.sql

 

SVRMGR> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Step 4: Copy all the parameter files (initsid.ora, configsid.ora ),  Control files,  Ctrlprod.sql ( created as above) and all data files  to clone server . It is good to follow the OFA. If you are  cloning a  database on the same machine then  create a different directory structure ( as OFA) for cloning instance and copied all the required files of primary instance here.

 

    Step 4a : On Unix  ( Let us say your cloning instance name is clone)

If you had followed OFA , your initsid.ora  parameters files would be in  $ORACLE_BASE/admin/clone/pfile/initclone.ora

and configsid.ora would be in  $ORACLE_BASE/admin/clone/pfile/configclone.ora.

Create a symbolic link

Change the working directory to $ORACLE_HOME/dbs and create a symbolic link

$ ln –s $ORACLE_BASE/admin/clone/pfile/initclone.ora  initclone.ora

 

Step 5 : On destination server

Change the following parameter in the initsid.ora of the cloning instance/server

 

InitSid.ora (initora816.ora) of the Source/production server.
InitSid.ora (initclone.ora) of the

destination/clone server.

db_name = “ora816”instance_name = ora816

service_names = ora816

control_files = (“e:\ora816\oradata\ora816\control01.ctl”, “f:\ora816\oradata\ora816\control02.ctl”, “g:\ora816\oradata\ora816\control03.ctl”)

Db_name= “clone”Instance_name= clone

Service_name = clone

Control_file = (“C:\clone\control01.ctl”,”D:\clone\control02.ctl”)

The other parameter which is required to change is user_dump_dest, background_dump_dest,log_archive_dest

 

 

Oraxxxx.trc is copied from source/production instance to destination/clone instance and renamed it as ctrlclone.sql .

 

 Edit ctrlclone.sql as follows.

 

Oraxxxx.trc in user dump dest of Source/Production instance Ctrlclone.sql
STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “ORA816” NORESETLOGS ARCHIVELOG

MAXLOGFILES 32

MAXLOGMEMBERS 2

MAXDATAFILES 32

MAXINSTANCES 16

MAXLOGHISTORY 1815

LOGFILE

GROUP 1 ‘E:\ORA816\ORADATA\ORA816\REDO03.LOG’  SIZE 1M,

GROUP 2 ‘E:\ORA816\ORADATA\ORA816\REDO02.LOG’  SIZE 1M,

GROUP 3 ‘E:\ORA816\ORADATA\ORA816\REDO01.LOG’  SIZE 1M

DATAFILE

‘E:\ORA816\ORADATA\ORA816\SYSTEM01.DBF’,

‘E:\ORA816\ORADATA\ORA816\RBS01.DBF’,

‘E:\ORA816\ORADATA\ORA816\TEMP01.DBF’,

‘E:\ORA816\ORADATA\ORA816\TOOLS01.DBF’,

‘E:\ORA816\ORADATA\ORA816\INDX01.DBF’,

‘E:\ORA816\ORADATA\ORA816\DR01.DBF’,

‘E:\ORA816\DATABASE\NGAR5ORA81601.DBF’,

‘E:\ORA816\DATABASE\RBSTEST01.DBF’

CHARACTER SET WE8ISO8859P1

;

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.

RECOVER DATABASE

# All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

# Database can now be opened normally.

ALTER DATABASE OPEN;

/* Delete everything upto startup nomount */STARTUP NOMOUNT pfile=f:\clone\initclone.ora

CREATE CONTROLFILE set DATABASE “CLONE” resetlogs noarchivelog

MAXLOGFILES 32

MAXLOGMEMBERS 2

MAXDATAFILES 32

MAXINSTANCES 16

MAXLOGHISTORY 1815

LOGFILE

GROUP 1 ‘F:\CLONE\ORA816\REDO03.LOG’

SIZE 1M,

GROUP 2 ‘F:\CLONE\ORA816\REDO02.LOG’

SIZE 1M,

GROUP 3 ‘F:\CLONE\ORA816\REDO01.LOG’

SIZE 1M

DATAFILE

‘F:\CLONE\ORA816\SYSTEM01.DBF’,

‘F:\CLONE\ORA816\RBS01.DBF’,

‘F:\CLONE\ORA816\TEMP01.DBF’,

‘F:\CLONE\ORA816\TOOLS01.DBF’,

‘F:\CLONE\ORA816\INDX01.DBF’,

‘F:\CLONE\ORA816\DR01.DBF’,    ‘F:\CLONE\DATABASE\NGAR5ORA81601.DBF’,

‘F:\CLONE\DATABASE\RBSTEST01.DBF’

CHARACTER SET WE8ISO8859P1

;

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.

#RECOVER DATABASE

# All logs need archiving and a log switch is needed.

#ALTER SYSTEM ARCHIVE LOG ALL;

# Database can now be opened normally.

#ALTER DATABASE OPEN;

STEP 6 :-

 

ON NT :- You have to create a windows NT services as follows.

 

C:\>oradim -new -sid clone -srvc oracleserviceClone -intpwd oracle -startmode auto -pfile f:\clone\initclone.ora

 

ON Unix :-

         

(A)   (A)    Change the working directory to /etc and edit the oratab file  to put the entry for CLOBE instance.

 (B)   Setup login profile for the Oracle user having dba group.

                        ORACLE_SID=CLONE

                         Export ORACLE_SID

         Or  .oraenv

               

           

 

 

STEP 7 :- Create controlfile as follows :-
 
Check in the initclone.ora REMOTE_LOGIN_PASSWORDFILE is set to
   exclusive or shared.  If this is set, then a valid passwordfile

   should exist in ORACLE_HOME/dbs or created using orapwd  as

 

orapwd file=d:\oracle\ora92\database\pwdclone.ora password=oracle entries=1 
 

  sqlplus /nolog

connect sys/** as sysdba

or

SVRMGR> @f:\clone\ctrlclone.sql

ORACLE instance started.

Total System Global Area                         57123804 bytes

Fixed Size                                          70620 bytes

Variable Size                                    40198144 bytes

Database Buffers                                 16777216 bytes

Redo Buffers                                        77824 bytes

Statement processed.

D:\ORACLE\ORA92\DATABASE

STEP 8 :- Open the database in resetlog mode as follows.

SVRMGR> alter database open resetlogs;

Statement processed.

STEP 9 :- Shutdown the database in Normal mode

SVRMGR> connect internal/oracle

Connected.

SVRMGR> shutdown ;

Database closed.

Database dismounted.

ORACLE instance shut down.

STEP 10 :- Take the cold backup and start the database in archive/non archive mode.

Posted in Clone or Duplicate | Leave a Comment »

Configure dbconsole manually

Posted by Hendry chinnapparaj on May 5, 2011

PROBLEM

————-

Database sid = icutest

Hostname=icu009

icutest:/u01/oracle/product/10.2/db_1/bin> ./emctl status dbconsole

OC4J Configuration issue. /u01/oracle/product/10.2/db_1/oc4j/j2ee/OC4J_DBConsole_icu009.finwest.com_icutest not found.

icutest:/u01/oracle/product/10.2/db_1/bin> ./emctl start dbconsole

OC4J Configuration issue. /u01/oracle/product/10.2/db_1/oc4j/j2ee/OC4J_DBConsole_icu009.finwest.com_icutest not found.

icutest:/u01/oracle/product/10.2/db_1/bin>

SOLUTION

————-

icutest:/u01/oracle/product/10.2/db_1/bin> ./emca -config dbcontrol db

STARTED EMCA at May 5, 2011 1:34:22 PM

EM Configuration Assistant, Version 10.2.0.1.0 Production

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

Enter the following information:

Database SID: icutest

Listener port number: 1521

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

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

You have specified the following settings

Database ORACLE_HOME ……………. /u01/oracle/product/10.2/db_1

Database hostname ……………. icu009.finwest.com

Listener port number ……………. 1521

Database SID ……………. icutest

Email address for notifications ……………

Outgoing Mail (SMTP) server for notifications ……………

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

Do you wish to continue? [yes(Y)/no(N)]: y

May 5, 2011 1:34:50 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /u01/oracle/product/10.2/db_1/cfgtoollogs/emca/icutest/emca_2011-05-05_01-34-22-PM.log.

May 5, 2011 1:35:05 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Securing Database Control (this may take a while) …

May 5, 2011 1:35:49 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Database Control secured successfully.

May 5, 2011 1:35:49 PM oracle.sysman.emcp.util.DBControlUtil startOMS

INFO: Starting Database Control (this may take a while) …

May 5, 2011 1:37:27 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: Database Control started successfully

May 5, 2011 1:37:27 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: >>>>>>>>>>> The Database Control URL is https://icu009.greatwest.com:5503/em <<<<<<<<<<<

Enterprise Manager configuration completed successfully

FINISHED EMCA at May 5, 2011 1:37:27 PM

icutest:/u01/oracle/product/10.2/db_1/bin> ./emctl status dbconsole

Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0

Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.

https://icu009.finwest.com:5503/em/console/aboutApplication

Oracle Enterprise Manager 10g is running.

——————————————————————

Logs are generated in directory /u01/oracle/product/10.2/db_1/icu009.finwest.com_icutest/sysman/log

https://icu009.greatwest.com:5503/em/console/logon/logon

Posted in Uncategorized | Leave a Comment »