Categories
Blog

ASM Scripts

I have added some more handy ASM Scripts.
Click Here

Categories
Blog

ORA-30012: undo tablespace ‘UNDOTBS01′ does not exist or of wrong type

I was recently recovering a backup of a standby database, the standby was from a primary that was a RAC with 2 nodes.  I had the following error:

SQL> startup

ORACLE instance started.

Total System Global Area 3170893824 bytes

Fixed Size                  2043968 bytes

Variable Size            2030047168 bytes

Database Buffers         1124073472 bytes

Redo Buffers               14729216 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-30012: undo tablespace ‘UNDOTBS01’ does not exist or of wrong type

Process ID: 19872

Session ID: 1218 Serial number: 3

Solution

[oracle@eux3300392 dbs]$ vi initps82live.ora

undo_management=MANUAL

SQL> startup

ORACLE instance started.

Total System Global Area 6313463808 bytes

Fixed Size                  2264976 bytes

Variable Size            1677721712 bytes

Database Buffers         4613734400 bytes

Redo Buffers               19742720 bytes

Database mounted.

Database opened.

SQL>

4. create a new undo tablespace, e.g.:

CREATE UNDO TABLESPACE “UNDOTBS3” DATAFILE ‘+DATA’ SIZE 10880M AUTOEXTEND ON NEXT 5120K MAXSIZE 32767M

5. shutdown the database

6. change UNDO_MANAGEMENT=AUTO in your init file and set UNDO_TABLESPACE=UNDOTBS (or whatever name you specified during the undo tablespace creation)

7. connect “/ as sysdba” and startup the database using this init file (as in step 2).

Now the database will open using the new undo tablespace and it would have created new undo segments in this tablespace.

 
 

Categories
Blog

Adding New Redo Log Groups

An error occurs when deleting a logfile group after you have added new groups.
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance
 
Solution
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
Then just check by doing the following:
SQL> select member
from v$logfile;
 

Categories
Blog

Cannot mark instance UNNAMED_INSTANCE as enabled

 

I was recently recovering a backup of a standby database, the standby was from a primary that was a RAC with 2 nodes.  I had the following error:

 

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

 

Solution

 

Edit the .ora file add the following:

*._no_recovery_through_resetlogs=TRUE

Save

 

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

 

SQL> startup mount;

 

ORACLE instance started.

Total System Global Area 6313463808 bytes

Fixed Size                  2264976 bytes

Variable Size            1677721712 bytes

Database Buffers         4613734400 bytes

Redo Buffers               19742720 bytes

Database mounted.

 

SQL> alter database open resetlogs;

Database altered.

 

shutdown and remove the line added above and restart instance

 
 

Categories
Blog

Basic IOPS

I was asked about IOPS, so that a new server could be sized, this could help.

set linesize 100
 set head off
SELECT 'Number of Small Reads :'||
sum(decode(name,'physical read total IO requests',value,0)-
decode(name,'physical read total multi block requests',value,0)),
'Number of Small Writes:'||
sum(decode(name,'physical write total IO requests',value,0)-
decode(name,'physical write total multi block requests',value,0)),
'Number of Large Reads :'||
sum(decode(name,'physical read total multi block requests',value,0)),
'Number of Large Writes:'||
sum(decode(name,'physical write total multi block requests',value,0)),
'Total Bytes Read :'||
sum(decode(name,'physical read total bytes',value,0)),
'Total Bytes Written :'||
sum(decode(name,'physical write total bytes',value,0))
FROM gv$sysstat
/

Your results might look something like:

Number of Small Reads :106883
Number of Small Writes:205903
Number of Large Reads :2791
Number of Large Writes:40298
Total Bytes Read :2009381888
Total Bytes Written :4188587008

Remember that statistics contained in the GV$ views are cumulative and therefore should be sampled. You then subtract your first statistics from the last statistics to give you the throughput.

Number of Small Reads :333474
Number of Small Writes:500211
Number of Large Reads :8010
Number of Large Writes:142981
Total Bytes Read :5586081648
Total Bytes Written :22232604961

It is now easy to calculate the IOPS for small and large I/O and also MBPS. The following calculations will assist in determining the workload for the current database system.
Small Read IOPS =(333474-106883)/(10*60) = 377 IOPS
Small Write IOPS =(500211-205903)/(10*60) = 490 IOPS
Total Small IOPS =(226591+294308)/(10*60) = 868 IOPS
I/O Percentage of Reads to Writes = 44:56
Large Read IOPS =(8010-2791) /(10*60) = 8 IOPS
Large Write IOPS =(142981-40298)/(10*60) = 171 IOPS
Total Large IOPS =(5219+102683) /(10*60) = 179 IOPS
I/O Percentage of Reads to Writes = 4:96
Total MBPS Read =((5586081648-2009381888) /(10*60))/1048576= 5 MBPS
Total MBPS Written =((22232604961-4188587008)/(10*60))/1048576= 28 MBPS
Total MBPS =((3576699760+18044017953)/(10*60))/1048576= 34 MBPS
We could conclude from these figures that this is more of a OLTP as there is more writes occurring.

Categories
Blog

How to recreate database dbconsole

Steps to recreate database dbconsole
1) emca -deconfig dbcontrol db -repos drop
2) Normally you would run emca -reconfig all db -repos recreate but we want to specify a listener home

$ emca -config dbcontrol db -repos recreate -cluster -LISTENER_OH /opt/app/oracle/product/10.2/asm_1
STARTED EMCA at 13-Nov-2009 11:18:11
 EM Configuration Assistant, Version 10.2.0.1.0 Production
 Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
 Database unique name: shello1
 Listener port number: 1521
 Cluster name: crs
 Password for SYS user: warrier
 Password for DBSNMP user: warlord
 Password for SYSMAN user: warlord
 Email address for notifications (optional):
 Outgoing Mail (SMTP) server for notifications (optional):
 ASM ORACLE_HOME [ /opt/app/oracle/product/10.2/db_1 ]: /opt/app/oracle/product/10.2/asm_1
 ASM port [ 1521 ]:
 ASM user role [ SYSDBA ]:
 ASM username [ SYS ]:
 ASM user password: warlord
 -----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /opt/app/oracle/product/10.2/db_1
Database instance hostname ................ shelled.net
 Listener port number ................ 1521
 Cluster name ................ crs
 Database unique name ................ shello1
 Email address for notifications ...............
 Outgoing Mail (SMTP) server for notifications ...............
 ASM ORACLE_HOME ................ /opt/app/oracle/product/10.2/asm_1
 ASM port ................ 1521
 ASM user role ................ SYSDBA
 ASM username ................ SYS
-----------------------------------------------------------------
 Do you wish to continue? [yes(Y)/no(N)]: Y

This will recreate the dbconsole.