Archive for the ‘Oracle’ Category

Newer Entries »

Automatic failover with Oracle DataGuard (Fast-Start Failover)

Wednesday, December 5th, 2007

This post will demonstrate beautiful software… Oracle DataGuard :)

Quick intro for non-Oracle people out there… Oracle DataGuard is High Availability solution for Oracle Database. For thousands pages of documentation, concept guides, troubleshooting, HOWTOs about it please visit docs.oracle.com ;)

Some facts:
1) Max Availability mode (requirrement of Fail-Start Failover)
2) Flashback for database is on (also req.)
3) Physical standby
4) All configured from CLI (sqlplus and dgmgrl; no OEM)
5) Oracle version: 10gR2 EE(10.2.0.1)
6) All done on single host(2 instances + 1 observer + 1 listener)

Ok, let’s start observer (element which tests instances and decides when to failover to secondary database):

[oracle@xeno ~]$ export ORACLE_SID=xeno1
[oracle@xeno ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/abc
Connected.
DGMGRL> show configuration verbose;
Configuration
Name: DGxeno
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
xeno1 - Primary database
xeno3 - Physical standby database
- Fast-Start Failover target
Fast-Start Failover
Threshold: 30 seconds
Observer: xeno.localdomain
Current status for "DGxeno":
SUCCESS
DGMGRL> START OBSERVER
<-- session hangs...

From another terminal we will insert some data:

[oracle@xeno ~]$ echo $ORACLE_SID
xeno1
[oracle@xeno ~]$ sqlplus vnull/abc
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 4 18:48:39 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
SQL> insert into dgtest values ('30000000');
1 row created.
SQL> commit;
Commit complete.
SQL>

Great, now we are going to prepare small holocaust for our xeno1 database… we just simply instant SIGKILL all processes of oracle xeno1:

[root@xeno ~]$ for P in `ps auxw | awk '/ora_[0-9a-z]+_xeno1/ { print $2 }' `; do kill -9 $P; done

Simple and efficient! ;)

Screenshot from DGMGRL observer:
DGMGRL observer failover

Now we can see from alert_xeno3.log the following:

[..]
<-- simulated failure of xeno1!
Tue Dec 4 19:01:19 2007
RFS[14]: Possible network disconnect with primary database
Tue Dec 4 19:01:19 2007
RFS[15]: Possible network disconnect with primary database
Tue Dec 4 19:01:19 2007
RFS[13]: Possible network disconnect with primary database
<-- failover starts!
Tue Dec 4 19:02:45 2007
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
Tue Dec 4 19:02:45 2007
Terminal Recovery: Stopping real time apply
Tue Dec 4 19:02:45 2007
MRP0: Background Media Recovery cancelled with status 16037
Tue Dec 4 19:02:45 2007
Errors in file /u01/app/oracle/admin/xeno3/bdump/xeno3_mrp0_7206.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2316040
Tue Dec 4 19:02:45 2007
Errors in file /u01/app/oracle/admin/xeno3/bdump/xeno3_mrp0_7206.trc:
ORA-16037: user requested cancel of managed recovery operation
Tue Dec 4 19:02:45 2007
MRP0: Background Media Recovery process shutdown (xeno3)
Tue Dec 4 19:02:46 2007
Terminal Recovery: Stopped real time apply
Tue Dec 4 19:02:46 2007
Attempt to do a Terminal Recovery (xeno3)
Tue Dec 4 19:02:46 2007
Media Recovery Start: Managed Standby Recovery (xeno3)
Managed Standby Recovery not using Real Time Apply
Terminal Recovery timestamp is '12/04/2007 19:02:46'
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 95 redo required
Terminal Recovery: /u04/oracle/xeno3/standby_redo01.log
Identified End-Of-Redo for thread 1 sequence 95
Tue Dec 4 19:02:46 2007
Incomplete recovery applied all redo ever generated.
Recovery completed through change 2316041
Tue Dec 4 19:02:46 2007
Media Recovery Complete (xeno3)
Terminal Recovery: successful completion
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Resetting standby activation ID 3036789101 (0xb501b96d)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
Tue Dec 4 19:02:51 2007
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
Tue Dec 4 19:02:51 2007
ALTER DATABASE SWITCHOVER TO PRIMARY (xeno3)
If media recovery active, switchover will wait 900 seconds
Standby terminal recovery start SCN: 2316040
SwitchOver after complete recovery through change 2316041
Online log /u04/oracle/xeno3/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u04/oracle/xeno3/redo02.log: Thread 1 Group 2 was previously cleared
Standby became primary SCN: 2316039
Converting standby mount to primary mount.
Tue Dec 4 19:02:51 2007
Switchover: Complete - Database mounted as primary (xeno3)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
Tue Dec 4 19:02:51 2007
ARC6: STARTING ARCH PROCESSES
Tue Dec 4 19:02:51 2007
ALTER SYSTEM SET standby_archive_dest='' SCOPE=BOTH SID='xeno3';
Tue Dec 4 19:02:51 2007
ARC7: Becoming the 'no SRL' ARCH
Tue Dec 4 19:02:51 2007
ALTER SYSTEM SET log_archive_dest_1='location="/u04/oracle/xeno3/arch"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)' SCOPE=BOTH SID='xeno3';
Tue Dec 4 19:02:51 2007
ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH SID='xeno3';
Tue Dec 4 19:02:51 2007
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
Tue Dec 4 19:02:51 2007
Completed: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
Tue Dec 4 19:02:51 2007
ALTER DATABASE OPEN
Tue Dec 4 19:02:51 2007
Assigning activation ID 3036795877 (0xb501d3e5)
LGWR: Primary database is in MAXIMUM AVAILABILITY mode
Tue Dec 4 19:02:51 2007
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
Tue Dec 4 19:02:51 2007
ARCa: Archival started
ARC6: STARTING ARCH PROCESSES COMPLETE
ARCa started with pid=13, OS id=7969
LNSb started with pid=29, OS id=7971
Error 12514 received logging on to the standby
Tue Dec 4 19:02:58 2007
Errors in file /u01/app/oracle/admin/xeno3/bdump/xeno3_lgwr_6565.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Tue Dec 4 19:02:58 2007
LGWR: Error 12514 verifying archivelog destination LOG_ARCHIVE_DEST_2
Tue Dec 4 19:02:58 2007
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Continuing...
Thread 1 advanced to log sequence 97
LGWR: Waiting for ORLs to be archived...
LGWR: ORLs successfully archived
Thread 1 opened at log sequence 97
Current log# 2 seq# 97 mem# 0: /u04/oracle/xeno3/redo02.log
Successful open of redo thread 1
Tue Dec 4 19:03:01 2007
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Dec 4 19:03:01 2007
SMON: enabling cache recovery
Tue Dec 4 19:03:02 2007
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Tue Dec 4 19:03:02 2007
SMON: enabling tx recovery
Tue Dec 4 19:03:02 2007
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=29, OS id=7973
Tue Dec 4 19:03:03 2007
LOGSTDBY: Validating controlfile with logical metadata
Tue Dec 4 19:03:03 2007
LOGSTDBY: Validation complete
Tue Dec 4 19:03:04 2007
Completed: ALTER DATABASE OPEN
[..]


[oracle@xeno ~]$ export ORACLE_SID=xeno3
[oracle@xeno ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/abc
Connected.
DGMGRL> show configuration;
Configuration
Name: DGxeno
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
xeno1 - Physical standby database (disabled)
- Fast-Start Failover target
xeno3 - Primary database
Current status for "DGxeno":
Warning: ORA-16608: one or more databases have warnings
DGMGRL> show database xeno3;
Database
Name: xeno3
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
xeno3
Current status for "xeno3":
Warning: ORA-16817: unsynchronized Fast-Start Failover configuration
DGMGRL> show database xeno1;
Database
Name: xeno1
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: ONLINE
Instance(s):
xeno1
Current status for "xeno1":
Error: ORA-16661: the standby database needs to be reinstated
DGMGRL>

We should check now our data:

[oracle@xeno ~]$ export ORACLE_SID=xeno3
[oracle@xeno ~]$ sqlplus vnull/abc
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 4 19:16:17 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
SQL> select * from dgtest;
ID
----------
[..]
777
30000000 <--- our data
[..]

DataGuard saved the day :)

Posted In: Oracle, cluster | No Comments »

Oracle’s EM Grid Control on CentOS5/RHEL5: libdb.so.2 issue and resolution

Sunday, November 25th, 2007

CentOS5 and RHEL5 comes without libdb.so.2 library (old Sleepycat database software). The problem is that there is no RPM for this library in the repositories (newest is only compat-db-4.3 which provides libdb4-3.so). Oracle’s EM installer fails about libdb.so.2 with:

libdb.so.2 missing on CentOS5/RHEL5

Resolution which worked for me was to install Redhat 7.3 db1 package(!). Binary compability seems to work:

[root@oemgc ~]# wget ftp://fr.rpmfind.net/linux/redhat/7.3/en/os/i386/RedHat/RPMS/db1-1.85-8.i386.rpm
--12:32:23-- ftp://fr.rpmfind.net/linux/redhat/7.3/en/os/i386/RedHat/RPMS/db1-1.85-8.i386.rpm
=> `db1-1.85-8.i386.rpm'
Resolving fr.rpmfind.net... 194.199.20.114
Connecting to fr.rpmfind.net|194.199.20.114|:21... connected.
Logging in as anonymous ... Logged in!
==> SYST ... done. ==> PWD ... done.
==> TYPE I ... done. ==> CWD /linux/redhat/7.3/en/os/i386/RedHat/RPMS ... done.
==> SIZE db1-1.85-8.i386.rpm ... 42581
==> PASV ... done. ==> RETR db1-1.85-8.i386.rpm ... done.
Length: 42581 (42K)
100%[=============================================================>] 42,581 230K/s in 0.2s
12:32:25 (230 KB/s) - `db1-1.85-8.i386.rpm' saved [42581]
[root@oemgc ~]# rpm -Uhv db1-1.85-8.i386.rpm
warning: db1-1.85-8.i386.rpm: Header V3 DSA signature: NOKEY, key ID db42a60e
Preparing... ########################################### [100%]
1:db1 ########################################### [100%]
[root@oemgc ~]# rpm -ql db1
/usr/bin/db1_dump185
/usr/lib/libdb.so.2
/usr/lib/libdb1.so.2
/usr/share/doc/db1-1.85
/usr/share/doc/db1-1.85/LICENSE
/usr/share/doc/db1-1.85/README
/usr/share/doc/db1-1.85/changelog
[root@oemgc ~]#

Posted In: Linux, Oracle | 3 Comments »

My engineering work…

Wednesday, August 15th, 2007

As of May I’m very busy architecting & implementing cluster for Java Enterprise Edition on comodity hardware (mainly x86_32 based) for my engineering work – to obtain BEng title. Our subject is:
“Web service based on scalable and highly available J2EE application cluster”. We have team consisting of 4 persons in which I’m responsible for all kind of systems/hardware scaling/clusters/load balancing/databases/networking/tunning everything :) . What kind of portal we are creating is to be decided by developers (it will likely be some kind of Web 2.0 portal).
Rest of the team is dedicated to J2EE programming. We are mainly playing with technology.
Currently rock-solid base core cluster architecture looks like this:

Cluster architecture

We are utilizing:

  • Load balancers: Linux Virtual Servers with DirectRouting on CentOS5 (configured as a part of Redhat Cluster Suite)
  • Database: Oracle10g R2
  • Middleware: JBOSS 4.2.0 (EJB3) running in a cluster based on JGroups + Hibernate(JPA) + JBOSS Cache
  • Frontend: Apache2 webservers with Solaris Network Cache Accelerator and AJP proxy to JBOSS servers
  • Solaris Jumpstart to setup new systems really fast with our selfwritten application in PHP for maintaing systems.
  • NFS for providing static content for web servers from Oracle server (yay! dedicated NetApp would be great! ;) )
  • LDAP to synchronize admins accounts inside cluster.
  • SNMPv2(LVS,OSes,JBOSS,Oracle) to monitor everything with single (selfwritten) Java application which graphs everything in realtime.

As this is basic configuration with database as an single point of failure, in Septemer I’m going to setup DataGuard for Oracle. Also I’m testing more advanced scale up. Currently I’m in process of setting up Solaris Cluster with Oracle RAC 10gR2 implemented on iSCSI storage provided by third node based on Solaris Nevada with iSCSI target to test Transparent Application Failover. I’ve been scratching my head over this one for awhile now. Yeah, it is real hardcore… more over that’s not the end of the story – Disaster Recovery with some other interesting bits of technology is going to be implemented later on… all on x86_32 comodity hardware :) Also we are going to put C-JDBC(Sequoia project) under stress…

Posted In: J2EE, Linux, Oracle, SAN, Solaris, cluster | 2 Comments »

SQLPLUS: COPY FROM … TO … – link

Sunday, March 18th, 2007

SQLPLUS: COPY FROM … TO … Czyli cos o czym pojecia dotad nie mialem – nawet nie wiedzialem o istnieniu czegos takiego…

Posted In: Oracle | No Comments »

Krotka notka o perspektywach z uprawnieniami w Oracle

Sunday, March 18th, 2007
  • DBA_SYS_PRIVS – system privileges granted to users and roles
  • DBA_TAB_PRIVS – this view shows privileges, procedures, packages and functions
  • DBA_COL_PRIVS – this view tracks grants on columns
  • DBA_ROLE_PRIVS – this shows who (or what roles) a role has been granted to

Posted In: Oracle | No Comments »

Entropia niszczy dane Oracle, part#2

Sunday, March 18th, 2007

Post z dnia: 07/03/2007

Exterminowanie datafile przez sily natury AKA /dev/chaos:
oracle@xeno:/u04/data/vn2$ dd if=/dev/urandom of=users02.dbf bs=1M count=5

Po probie INSERT do tabeli ktora byla w tamtym pliku w alert.logu mozna wyczytac ze:
Wed Mar 7 20:36:08 2007
Hex dump of (file 5, block 10) in trace file /u04/admin/vn2/udump/vn2_ora_6088.trc
Corrupt block relative dba: 0x0140000a (file 5, block 10)
Bad header found during buffer read
[..]
Reread of rdba: 0x0140000a (file 5, block 10) found same corrupted data
Wed Mar 7 20:36:08 2007
Corrupt Block Found
TSN = 4, TSNAME = USERS
RFN = 5, BLK = 10, RDBA = 20971530
OBJN = 10235, OBJD = 10235, OBJECT = DANE1, SUBOBJECT =
SEGMENT OWNER = VNULL, SEGMENT TYPE = Table Segment

Do rzeczy ( sprawdzenie integralnosci ):
oracle@xeno:/u04/data/vn2$ dbv file=users02.dbf
[..duzo smiecia..]
Total Pages Examined : 639
[..]
Total Pages Marked Corrupt : 639

Ale oczywiscie inne przestrzenie tabel i to co jest w SGA ladnie dziala.
Shutdown immediate nie dziala tak wiec abort:

SQL> shutdown abort;
SQL> startup;
[..]
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/u04/data/vn2/users02.dbf'
ORA-01251: Unknown File Header Version read for file number 5

Czyli teraz baza jest w trybie mount, odlaczamy uszkodzony plik:

SQL> alter database datafile '/u04/data/vn2/users02.dbf' offline;
SQL> alter database open;

W tym momencie uzytkownicy moga sie logowac i tworzyc nowe tabele ( trafia do datafile users01.dbf ), jednakze dane z tabeli sa dalej niedostepne z uwagi ze byla w users02.dbf!

A teraz przywracamy z hotbackupu recznie gdyz “nie czuje” jeszcze RMANa i za duzo tam magii ;)

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database datafile '/u04/data/vn2/users02.dbf' offline drop;
oracle@xeno:/u04/data/vn2$ cp /backup/users02.dbf .
SQL> alter database open;
SQL> recover datafile '/u04/data/vn2/users02.dbf';
SQL> alter database datafile '/u04/data/vn2/users02.dbf' online;

Hura, dane sa aczkolwiek nalezaloby jeszcze zrobic RECOVERy ;)

BTW:  To zapytanie wyswietla pliki w ktorych sa obiekty intersujace nas obiekty danego usera:

SELECT segment_name, d.name AS datafile FROM dba_segments s JOIN v$datafile d ON (s.header_file=file#) WHERE s.owner=’<schema>’;

Posted In: Oracle | No Comments »

umyslnie niszczenie Oracle – i juz nie jest takie “unbreakable” ;]

Sunday, March 18th, 2007

Post z dnia: 06/03/2007

ORACLE_SID jest ustawione na vn2

# symulacja calkowitego padu dysu na ktorym jest spfile
oracle@xeno: dbs$ dd if=/dev/urandom of=spfilevn2.ora bs=1k count=3
3+0 records in
3+0 records out
3072 bytes (3.1 kB) copied, 0.001162 seconds, 2.6 MB/s

# kill -9 <pidy_oracle> to byloby zbyt wiele pisania a efekt prawie taki sam – tzn pad bazy ;)
SQL> shutdown abort;
ORACLE instance shut down.

# elektrownia wlaczyla prad…
SQL> startup;
ORA-00600: internal error code, arguments: [733], [1157335612], [pga heap], [],[], [], [], []

No i gdyby nie bylo kopii zapasowej spfile, czy tez pfile utworzonego przez CREATE PFILE FROM SPFILE to bylaby “jazda”… ale na szczescie jest:

SQL> startup pfile='/u01/product/10.2.0/db_1/dbs/initvn2.ora';
ORACLE instance started.
[...]

Posted In: Oracle | No Comments »

Oracle – wtajemniczanie sie w mhhhroczna i tajemnicza postac DBA, odcinek n-ty…

Sunday, March 18th, 2007

Post z dnia: 26/02/2007

  • “The following statements sometimes require the use of a temporary segment: CREATE INDEX, SELECT ORDER BY, SELECT DISTINCT, SELECT GROUP BY, SELECT UNION, SELECT INTERSECT, SELECT MINUS Some unindexed joins and correlated subqueries can require use of a temporary segment.”
  • exec dbms_system.kcfrms – resetuje m.in. v$filestat i pare innych rzeczy od statystyk ( gdy TIMED_STATISTICS jest wlaczone ).
  • Oszczedzanie pamieci mode on, czyli SGA na 100MB, wlaczenie wykorzystywania O_DIRECT bonusowo takze z AIO ( mam zlinkowana ladnie binarke Enterpise z libaio.so ;) ) — troche mam malo RAMu; wzglednie duzo roznych programow rownolegle odpalonych. O_DIRECT wraz z AIO wlacza sie tak:
    1. alter system set filesystemio_options=SETALL scope=spfile;
      gdzie FILESYSTEMIO_OPTIONS = { none | setall | directIO | asynch }
    2. shutdown;
    3. startup;
    4. Sprawdzenie ( w sumie mozna by przez strace -p i odlinkowanie tablespace… ):
      oracle@xeno:~$ strace -ff -e open sqlplus / as sysdba 2> open2.log
      [..]
      SQL> startup;
      [..]
      SQL> CTRL+Z
      [1]+ Stopped strace -ff -e open sqlplus / as sysdba 2>open2.logoracle@xeno:~$ grep O_DIRECT open2.log | grep 'users01\.dbf'
      [pid 7194] open("/u03/product/10.2.0/oradata/vn1/users01.dbf", O_RDONLY|O_DIRECT|O_LARGEFILE) = 22
      [pid 7194] open("/u03/product/10.2.0/oradata/vn1/users01.dbf", O_RDWR|O_SYNC|O_DIRECT|O_LARGEFILE) = 22
      [pid 7216] open("/u03/product/10.2.0/oradata/vn1/users01.dbf", O_RDWR|O_SYNC|O_DIRECT|O_LARGEFILE) = 18
      [pid 7196] open("/u03/product/10.2.0/oradata/vn1/users01.dbf", O_RDWR|O_SYNC|O_DIRECT|O_LARGEFILE) = 25
      [pid 7200] open("/u03/product/10.2.0/oradata/vn1/users01.dbf", O_RDWR|O_SYNC|O_DIRECT|O_LARGEFILE) = 19
      [pid 7206] open("/u03/product/10.2.0/oradata/vn1/users01.dbf", O_RDWR|O_SYNC|O_DIRECT|O_LARGEFILE) = 22 <p <li <ul


      Tylko warto zastopowac cala instancje bo leci przez debugging ptrace(2) co bedzie mialo malo korzystny wplyw na wydajnosc ;) Ale dzieki takiemu trikowi powinny przestac byc moje “dzialania” na bazie buforowane dwukrotnie: przez kernel buffers i samego Oracle…

  • Stwierdzam ze nazwy perspektyw x$ sa bardzo elitarne…

Posted In: Oracle | No Comments »

Oracle tkprof

Sunday, March 18th, 2007

Post z dnia: 14/01/2007
Tkprof czyli bawie sie w optymalizacje zapytan w Oraclu w ramach przygotowan do kola z baz danych…

Zwlaszcza fajne jest:
exec sys.dbms_system.set_sql_trace_in_session(<ID_Sesji>, <Jej_serial_num>, true);

Posted In: Oracle | No Comments »

nieudany domowe proby ataku na Oracle…

Tuesday, March 13th, 2007

Post z dnia: 23/12/2006:

vnull@vn1> create or replace synonym sym1 for sym2;

vnull@vn1> create or replace synonym sym2 for sym1;

vnull@vn1> select * from sym1;

ORA-01775: looping chain of synonyms (DBD ERROR: error possibly near <*> indicator at char 14 in ’select * from <*>sym1′)

Kombinacje typu symA( VIEW ) -> symB ( SYNONYM ) -> symC ( SYNONYM ) -> symA … tez sie niepowiodly.

… a SQL injection w PL/SQL dziala tylko w wypadku sklejania(konkatenacji) stringu do zapytania.

Ponadto popadlem (bardzo) w naukoholizmo-pracocholizm. Enjoy! :/

Posted In: Oracle | No Comments »

zmuszanie Oracle XE expdp ( DataPump ) do dzialania…

Tuesday, March 13th, 2007

Post z dnia: 15/12/2006

mimo CREATE OR REPLACE DIRECTORY + odpowiednich grantow dla siebie ( user/schema : vnull ) dalej mi DataPump nie ruszyl:

oracle@xeno:~$ expdp vnull/BLEBLEBLE@XE schemas=vnull directory=expdp1_dir dumpfile=vnull.dmp logfile=expdpVNULL.log
[..]
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
ORA-39006: internal error
ORA-39213: Metadata processing is not available

Rozwiazaniem niby ma byc:
Connect AS SYSDBA and execute dbms_metadata_util.load_stylesheets to reload the stylesheets.
Tylko ze podczas odpalania tej procedury okazalo sie ze moja Oracle XE nie ma tych stylesheetow. Jako ze mam na rowni z XE zainstalowane Oracle Enterprise Edition to nie bylo problemow z:
oracle@xeno:~$ cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/rdbms/
oracle@xeno:~/app/oracle/product/10.2.0/server/rdbms$ ls
admin audit demo install jlib label.info log mesg public
oracle@xeno:~/app/oracle/product/10.2.0/server/rdbms$ ln -s /u01/product/10.2.0/db_1/rdbms/xml .
oracle@xeno:~/app/oracle/product/10.2.0/server/rdbms$ ls -al| grep -i xml
lrwxrwxrwx 1 oracle oinstall 34 Dec 15 15:33 xml -> /u01/product/10.2.0/db_1/rdbms/xml
oracle@xeno:~/app/oracle/product/10.2.0/server/rdbms$ sqlplus / as sysdba

[..]

SQL> execute dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.
oracle@xeno:~/app/oracle/product/10.2.0/server/rdbms$ expdp vnull/BLEBLEBLE@XE schemas=vnull directory=expdp1_dir dumpfile=vnull.dmp logfile=expdpVNULL.log

[..]

Dump file set for VNULL.SYS_EXPORT_SCHEMA_01 is:

/u01/expdp1/vnull.dmp

Job “VNULL”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 15:35:01

Posted In: Oracle | No Comments »

Newer Entries »