Archive

Archive for May, 2012

Temporary Tablesapce (TEMP) Full

May 31, 2012 Leave a comment

Fixing Procedure.

Turn it off and on. The temporary space are released.

1. Check current free space

SVRMGR>  select ‘FILE’,tablespace_name,sum(bytes) from dba_free_space
2> group by tablespace_name;
‘FIL TABLESPACE_NAME                SUM(BYTES)
—- —————————— ———-
FILE INDX                            167636992
FILE NETPG_DATA                     1012871168
FILE NETPG_INDEX                     497352704
FILE RBS                             801107968
FILE SYSTEM                           90705920
FILE TEMP                            117174272
FILE TOOLS                             8384512
FILE USERS                           514584576
FILE VIDEO                          5940887552
9 rows selected.

2. Turn it on/off

SVRMGR> alter tablespace temp offline;
Statement processed.
SVRMGR> alter tablespace temp online;
Statement processed.

3. Check free space now

SVRMGR> select ‘FILE’,tablespace_name,sum(bytes) from dba_free_space
2>  group by tablespace_name;
‘FIL TABLESPACE_NAME                SUM(BYTES)
—- —————————— ———-
FILE INDX                            167636992
FILE NETPG_DATA                     1012871168
FILE NETPG_INDEX                     497352704
FILE RBS                             801107968
FILE SYSTEM                           90705920
FILE TEMP                           1594617856 <–
FILE TOOLS                             8384512
FILE USERS                           514584576
FILE VIDEO                          5940887552
9 rows selected.
SVRMGR>

Categories: Administration

InnoDB: Unable to lock ./ibdata1, error: 37

May 14, 2012 1 comment

Today, I have start mysql with a central NAS storage. The error messages file show message following

InnoDB: Unable to lock ./ibdata1, error: 37
120514 16:41:29  InnoDB: Operating system error number 37 in a file operation.
InnoDB: Error number 37 means ‘No locks available’.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: Error in creating or opening ./ibdata1
120514 16:41:29 InnoDB: Could not open or create data files.
120514 16:41:29 InnoDB: If you tried to add new data files, and it failed here,
120514 16:41:29 InnoDB: you should now edit innodb_data_file_path in my.cnf back
120514 16:41:29 InnoDB: to what it was, and remove the new ibdata files InnoDB created
120514 16:41:29 InnoDB: in this failed attempt. InnoDB only wrote those files full of
120514 16:41:29 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
120514 16:41:29 InnoDB: remove old data files which contain your precious data!
120514 16:41:29 [ERROR] Plugin ‘InnoDB’ init function returned error.
120514 16:41:29 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
120514 16:41:29 [ERROR] Unknown/unsupported storage engine: InnoDB
120514 16:41:29 [ERROR] Aborting
120514 16:41:29 [Note] /usr/sbin/mysqld: Shutdown complete

Solution: Add nolock option into nfs mount entry in /etc/fstab as below and restart netfs. The mysql can starts.

wtt.isilon.local:/ifs/wtt/dbdata                /dbdata  nfs     rsize=65536,wsize=65536,nolock

 

Categories: mySQL troubleshooting

RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file

May 2, 2012 1 comment

After restoring spfile and controlfile from the backup, I start the database instance in nomount, the below message shows

RMAN> startup nomount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/02/2012 11:41:14
RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925

Since I restore the database backup into another 11gR2 cluster server, I forget create audit files for it. After creating the files and directories as below, it works

[oracle@wcsvmuatdb01 cspro]$  mkdir adump dpdump hdump pfile
[oracle@wcsvmuatdb01 cspro]$ rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Wed May 2 11:52:53 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CSPRO (not mounted)

Categories: RMAN

RMAN-04014: startup failed: ORA-00119: invalid specification for system parameter REMOTE_LISTENER

May 2, 2012 Leave a comment

I restore a  11gR2 database into a testing 11gR2 cluster database, after restoring the spfile from backup and starting it up by RMAN, I get the messages below

RMAN> startup nomount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/02/2012 11:23:56
RMAN-04014: startup failed: ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name ‘scan.csportal.com:1521’

Since the testing server can look DNS up, I have added the scan.csportal.com into /etc/hosts. The problem is workaround.

#SCAN Entry
10.193.42.100   scan.springbroad.com   scan <– this testing db scan IP
10.193.42.100   scan.csportal.com <– the restoring database

 

Regards /Jeff Wong

 

Categories: RMAN