My Blog

October 16, 2008

can you take a backup from solaris and use it on windows NT?

Filed under: Oracle — by enotebook @ 10:06 am

I was asked such a question – can you take a backup from solaris and use it on windows NT? Of course, the answer is NO. why?
We can use rman convert only for Os with same endian format. Oracle has a v$ view holding such information.
SQL> select platform_name, endian_format from v$transportable_platform;
PLATFORM_NAME ENDIAN_FORMAT
———————————————————- ————–
Solaris OE (32-bit) Big
Solaris OE (64-bit) Big
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
AIX-Based Systems (64-bit) Big
HP-UX (64-bit) Big
HP Tru64 UNIX Little
HP-UX IA (64-bit) Big
Linux IA (64-bit) Little
HP Open VMS Little
Microsoft Windows IA (64-bit) Little
IBM zSeries Based Linux Big
Linux 64-bit for AMD Little
Apple Mac OS Big
Microsoft Windows 64-bit for AMD Little
Solaris Operating System (x86) Little
Solaris is big endian and windows is small endian and hence rman convert will not work in this scenario.
Simple summary:
1) Microsoft and linux are little endian,
2) IBM Soalris and Apple are big endian
3) HP tru and open are little endian but 64 bit are BIG endian

October 15, 2008

ORA-07445 with ORA-03113: end-of-file on communication channel

Filed under: Oracle — by enotebook @ 5:21 pm

I was encountering an error when executing a SQL query on a development server, which is ORA-03113: end-of-file on communication channel. After the investigation i found it’s caused by an Oracle bug – bug No. 4517291. Here is the process to solve this problem.

$ tail -n 10 alert_orcl.log
Completed: alter tablespace test add datafile
‘/u01/oradata/orcl/test02.dbf’ size 20M
Wed Oct 15 04:39:49 2008
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_25786.trc:
ORA-07445: exception encountered: core dump
[Address not mapped to object] [0x0] [] []
Wed Oct 15 04:40:14 2008
Thread 1 advanced to log sequence 5
Current log# 1 seq# 5 mem# 0: /u01/oradata/orcl/redo01.log
Wed Oct 15 04:41:24 2008
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_25788.trc:
ORA-07445: exception encountered: core dump
[Address not mapped to object] [0x0] [] []
$ cd ../udump/
$ ls -alth |grep *25788*
-rw-r—– 1 oracle oinstall 2.6M Oct 15 04:41 orcl_ora_25788.trc
$ head -n 100 *25788*
/u01/app/oracle/admin/orcl/udump/orcl_ora_25788.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 –
Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10g/db
System name: Linux
Node name: LinuxOra.andy.com
Release: 2.6.9-55.ELsmp
Version: #1 SMP Fri Apr 20 17:03:35 EDT 2007
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 25788, image: oracle@LinuxOra.andy.com]oracle@LinuxOra.andy.com (TNS V1-V3)
*** 2008-10-15 04:41:24.389
*** SERVICE NAME:(SYS$USERS) 2008-10-15 04:41:24.388
*** SESSION ID:(148.278) 2008-10-15 04:41:24.388
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to
object), addr: 0x0, PC: [0x95450df, evaopn2()+209]
Registers:
%eax: 0x0000065c %ebx: 0xb72cf65c %ecx: 0xb72c8b34
%edx: 0x2bbb0210 %edi: 0x09545081 %esi: 0x00000000
%esp: 0xbfffc130 %ebp: 0xbfffc2ec %eip: 0x095450df
%efl: 0x00210246
evaopn2()+198 (0x95450d4) add $8,%esp
evaopn2()+201 (0x95450d7) mov 0xffffffd8(%ebp),%ebx
evaopn2()+204 (0x95450da) jmp 0x9545072
evaopn2()+206 (0x95450dc) mov 0x28(%edx),%esi
> evaopn2()+209 (0x95450df) mov (%esi),%edi
evaopn2()+211 (0x95450e1) movzw 0x4(%esi),%esi
evaopn2()+215 (0x95450e5) mov (%edi,%ecx),%edi
evaopn2()+218 (0x95450e8) mov %esi,0xfffffff0(%ebp)
evaopn2()+221 (0x95450eb) mov %edi,0xfffffff8(%ebp)
*** 2008-10-15 04:41:24.391
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump
[Address not mapped to object] [0x0] [] []
Current SQL statement for this session:
select * from ( select user_id, displ, created, profile from t where
user_id = 5 and displ in (0,1) order by displ desc, created desc)
where rownum <= 15
—– Call Stack Trace —–
calling call entry argument values
in hex
location type point (? means dubious
value)
——————– ——– ——————– —————-
————
ksedst()+27 call ksedst1() 1 ? 1 ?
ksedmp()+557 call ksedst() 1 ? 8164444 ?
B7488F40 ?

Go to Oracle metalink and search the realated information, i found this article – Doc ID: NOTE:421013.1 Summary Of Bugs Containing ORA- 07445 , then follow the guide to identify the root problem. In the end i downloaded patch p4517291 and apply it, problem got resolved.

Restore database with damaging data files,control files and redo logs

Filed under: Oracle — by enotebook @ 12:06 pm

Think about a scenario – One day your database crashed, all the data files, controls files and redo logs(in case you didn’t multiplex them anyway) are damaged, fortunately you do have all the archived logs on hand and with a backup set of data files that you even don’t know when they have been backed-up, is your database able to be restored? This post does the test and gives you a glance on restoration process of oracle database.

SQL> startup
ORACLE instance started.
Total System Global Area 247463936 bytes
Fixed Size 1218772 bytes
Variable Size 79693612 bytes
Database Buffers 163577856 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

Ensure database is running on archive mode.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> select name from v$tablespace;
NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
SQL> select name from v$datafile;
NAME
——————————————————————————–
/u01/oradata/orcl/system01.dbf
/u01/oradata/orcl/undotbs01.dbf
/u01/oradata/orcl/sysaux01.dbf
/u01/oradata/orcl/users01.dbf

Have the data files to be backed-up.
SQL> alter tablespace system begin backup;
Tablespace altered.
SQL> alter tablespace undotbs1 begin backup;
Tablespace altered.
SQL> alter tablespace sysaux begin backup;
Tablespace altered.
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> host
[oracle@LinuxOra oradata]$ cd /u01/oradata/
[oracle@LinuxOra oradata]$ ls
orcl
[oracle@LinuxOra oradata]$ mkdir orclbak
[oracle@LinuxOra oradata]$ cp orcl/* orclbak/
[oracle@LinuxOra oradata]$ exit
exit
SQL> alter tablespace system end backup;
Tablespace altered.
SQL> alter tablespace undotbs1 end backup;
Tablespace altered.
SQL> alter tablespace sysaux end backup;
Tablespace altered.
SQL> alter tablespace users end backup;
Tablespace altered.

Archive the logs
SQL> alter system switch logfile;
System altered.

Database is running and has being accepted lots of user’s transaction. Here we create a tablespace and a table.
SQL> create tablespace test datafile ‘/u01/oradata/orcl/test.dbf’ size 10M;
Tablespace created.
SQL> create table t tablespace test as select * from v$datafile;
Table created.
SQL> select count(*) from t;
COUNT(*)
———-
5

SQL> alter system switch logfile;
System altered.

It’s a good idea to always backup the control file to trace after any changes applied to database structure.
SQL> alter database backup controlfile to trace;
Database altered.

Find out the trace file and see its content, you might need to use it to re-create control file.
SQL> select spid from v$process where addr in (select s.paddr from v$session s, v$mystat m where s.sid = m.sid and rownum < 2);
SPID
————
24697
SQL> shutdown immediate
Database closed.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
[oracle@LinuxOra oradata]$ cd /u01/app/oracle/admin/orcl/udump/
$ ls |grep *24697*
orcl_ora_24697.trc
$ pwd
/u01/app/oracle/admin/orcl/udump
$ more 100 orcl_ora_24697.trc

Control file re-creation scripts
$ cd /u01/oradata
[oracle@LinuxOra oradata]$ touch ctl.sql
[oracle@LinuxOra oradata]$ vi ctl.sql
CREATE CONTROLFILE REUSE DATABASE “ORCL” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/oradata/orcl/redo01.log’ SIZE 50M,
GROUP 2 ‘/u01/oradata/orcl/redo02.log’ SIZE 50M,
GROUP 3 ‘/u01/oradata/orcl/redo03.log’ SIZE 50M
— STANDBY LOGFILE
DATAFILE
‘/u01/oradata/orcl/system01.dbf’,
‘/u01/oradata/orcl/undotbs01.dbf’,
‘/u01/oradata/orcl/sysaux01.dbf’,
‘/u01/oradata/orcl/users01.dbf’
CHARACTER SET WE8ISO8859P1
;

Simulate damaging data files and control files (move them)
[oracle@LinuxOra oradata]$ ls
ctl.sql orcl orclbak

Lost data files.
[oracle@LinuxOra oradata]$ mv orcl orcl_latest

Restore backed-up data files to specified location.
[oracle@LinuxOra oradata]$ mv orclbak/ orcl
[oracle@LinuxOra oradata]$ ls
ctl.sql orcl orcl_latest
[oracle@LinuxOra oradata]$
[oracle@LinuxOra oradata]$ cd orcl
$ ls
control01.ctl control02.ctl control03.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

Damage control files.
$ mv control01.ctl control01.ctl.latest
$ mv control02.ctl control02.ctl.latest
$ mv control03.ctl control03.ctl.latest
$ ls
control03.ctl.latest redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl.latest control01.ctl.latest redo02.log sysaux01.dbf temp01.dbf users01.dbf
$
$ exit
exit

Database cannot be mounted since losing of control files. they can be re-create, content in ctl.sql is from the trace file as showed above.
SQL> @/u01/oradata/ctl.sql;
Control file created.
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

After re-creating control files we can open database, but it needs recovery.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/u01/oradata/orcl/system01.dbf’

SQL> recover database using backup controlfile until cance;
ORA-00277: illegal option to the UNTIL recovery flag CANCE

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 887850 generated at 10/14/2008 14:59:50 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2008_10_14/o1_mf_1_1_%u_.arc
ORA-00280: change 887850 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 887946 generated at 10/14/2008 15:02:30 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2008_10_14/o1_mf_1_2_%u_.arc
ORA-00280: change 887946 for thread 1 is in sequence #2
ORA-00278: log file
‘/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2008_10_14/o1_mf_1_1_4h9v67
29_.arc’ no longer needed for this recovery

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 5: ‘/u01/oradata/orcl/test.dbf’

ORA-01112: media recovery not started

ORA-01111: name for data file 5 is unknown – rename to correct file
ORA-01110: data file 5: ‘/u01/app/oracle/product/10g/db/dbs/UNNAMED00005’
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01111: name for data file 5 is unknown – rename to correct file
ORA-01110: data file 5: ‘/u01/app/oracle/product/10g/db/dbs/UNNAMED00005’

ORA-01112: media recovery not started

After applying all the archived redo logs, we can see test tablespace has been restored, but the file name is incorrect since re-creatation of control file, for continue to restore data
base, oracle writes a UNAMED style filename to describe the data file in control file, rename it to correct file as error message showed.
SQL> alter database create datafile ‘/u01/app/oracle/product/10g/db/dbs/UNNAMED00005’ as ‘/u01/oradata/orcl/test.dbf’ reuse;
Database altered.

Open database with using resetlogs option.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from t
2 ;
COUNT(*)
———-
5

Done.

October 8, 2008

Encountered error when using Mysql ODBC client connects to server

Filed under: Mysql — by enotebook @ 3:50 pm

I actually got this error before while using Mysql ODBC client connects to Mysql server, however i did not note it down at once, so when i face the same problem i even don’t know how to fix it, surely it has been solved after doing some research, here i place a post as note.
Mysql ODBC driver: MySQL ODBC 3.51 Driver
Mysql Server version: MySQL Community Server 5.0.51a
Error message: Client does not support authentication protocol requested by server; consider upgrading MySQL client
MySQL 5.0 uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients, when using an older client to connect the server, you may get with the error message.
To solve the problem, it needs to issue a command to reset the password to pre-4.1 style for each user that needs to use a pre-4.1 client program like following:
mysql> SET PASSWORD FOR
-> [email=’some_user’@’some_host’] = OLD_PASSWORD(‘newpwd’);
Alternatively, use UPDATE and FLUSH PRIVILEGES:
mysql> UPDATE mysql.user SET Password = OLD_PASSWORD(‘newpwd’)
-> WHERE Host = ‘some_host’ AND User = ‘some_user’;
mysql> FLUSH PRIVILEGES;

Done.

Blog at WordPress.com.