Oracle System Statistiken

System Statistiken sammeln

Die aktuellen System Statistiken werden gespeichert in der folgenden Tabelle:

select * from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

Bevor man neue Statistiken sammelt, sollte man die aktuellen Werte aus der sys.aux_stats$ Tabelle löschen. Ansonsten versucht die Datenbank die aktuell in der Tabelle vorhandenen Werte und die neuen gesammelten Werte zu mergen.

Löschen der aktuellen Werte:

exec dbms_stats.delete_system_stats();

Dabei werden die aktuellen Werte aus der sys.aux_stats$ Tabelle entfernt und in eine andere Tabelle gespeichert, damit die Werte für einen eventuellen Restore in der Zukunft trotzdem noch verfügbar sind.

Neue Werte können dann wie folgt gesammelt werden:

exec dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL',interval=> 120);

D.h., dass die Datenbank 120 Minuten lang die Statistiken ermittelt und am Ende die besten Werte übernimmt.

Um einen Überblick über die Historie aller Sammlungen/Löschungen zu erhalten, kann man folgende Query benutzen:

select target,operation,status,start_time,end_time from dba_optstat_operations where operation= 'gather_system_stats' or operation = 'delete_system_stats' order by start_time asc;

Wiederherstellen der Statistiken

Um rauszufinden, wie „viel“ maximal man in der Vergangenheit zurückgehen kann, kann man folgende Query ausführen:

select dbms_stats.get_stats_history_availability from dual;

Die Query wird das maximal in der Vergangenheit verfügbare Datum anzeigen, zu dem man die System-Statistiken zurücksetzen kann.

Ein Beispiel:

Die Query liefert zurück:

10.10.15 06:37:49

D.h. bis zu dem angegeben Zeitpunkt, kann ich zum Wiederherstellen irgendein Zeitpunkt zwischen dem jetzigen, aktuellen Zeitpunkt und den maximalen Punkt in der Vergangenheit wählen. Die Datenbank weis für jede Millisekunde von jetzt bis zu dem maximalen Zeitpunkt in der Vergangenheit, welche System-Statistiken Werte zu der Millisekunde aktiv waren!

Nun kommen wir zum eigentlichen Restore. Zuerst sollte man wieder erstmal die aktuellen Werte aus der sys.aux_stats$ Tabellelöschen:

exec dbms_stats.delete_system_stats();

Nun kann der Restore erfolgen:

exec dbms_stats.restore_system_stats(to_date('09.11.2015 12:00:16','dd.mm.yyyy hh24:mi:ss'));

Man kann als Parameter also genau den Zeitpunkt angeben, zu dem man „springen möchte“. Wenn man z.B. also Statistiken gesammelt hat am 15.10.2015 16:00:00 und dann wieder neue Statistiken gesammelt hat am 20.10.2015 18:00:00 und nach dem ermitteln der Werte des zweiten Sammelns, möchte man zurück zu den Werten, die man beim ersten Mal gesammelt hatte, dann muss man für die restore_system_stats Funktion nicht unbedingt genau den Zeitpunkt „15.10.2015 16:00:00“ angeben, sondern man kann irgendeinen Zeitpunkt angeben, an dem die gewünschten Werte aktuell/aktiv waren, das heißt bis zum 20.10.2015 18:00:00. Das wäre z.b. 16.10.2015 17:55:02, oder 19.10.2015 02:20:23, oder gar 20.10.2015 17:59:59.

How to install Oracle Example Schemas

Hello,

I have been looking everywhere for a simple way to install the HR, OE, etc... sample schemas. If you do not have the files already somewhere on your system, this is a pretty good way to get these schemas:

1. Download the Oracle Examples from:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Choose your version and operating system and click on "See All". On the next page select and download the "Examples".

2. Copy the archive to your system

3. Extract the archive, you will have an "examples" folder now.

4.  Go to examples/stage/Components/oracle.rdbms.companion/11.2.0.1.0/1/DataFiles

There you find a lot of filegroupX.jar files.

5. For the specific schema you want, extract the filegroupX.jar file:

9 - BI
10 - HR
11 - IX
12 - mksample.sql
13 - OE
14 - PM
15 - SH

For example, for the HR schema, do: unzip filegroup10.jar

This will create a "demo" folder.

6. Copy the whole "human_resources" folder to ORACLE_HOME/demo/schemas/

7. Go into the $ORACLE_HOME/demo/schema/human_resources folder

7. In your SQL-Tool (SQLPLus, SQL Developer, etc..) run the hr_main.sql as SYS-User!!! out of the human_resources folder. This will install the schema for you.

Oracle import / export Grant source database grantee does not exist in target database

Hi there,

today I was confronted with the following problem:

I had to export a schema from one database and import to another database. The schema contained grants granted to some user/grantee, who only existed in the source database. These grants should be replaces by another grantee for the target database. A normal export and import was failing with the following errors:

GRANT ON ... ON "SCHEMA"."TABLE" TO "GRANTEE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'GRANTEE' does not exist

A solution for this scenario is:

Do the normal import and ignore the errors. Then do the import again with the sqlfile parameter:

impdp system/peBa4ci9 schemas=... dumpfile=xxx.dmp sqlfile=getddl.sql

When you run this import command, it will not execute an import. Rather it will only create the sql file which will contain all the DDL statements that the import consists of.

Once you have the file, open it and search for the grants DDL's that affect the user/grantee you want to replace. All DDL's for this will be at the same place such as:

GRANT DEBUG ON "SCHEMA"."TABLE1" TO "GRANTEE";
GRANT QUERY REWRITE ON "SCHEMA"."TABLE2" TO "GRANTEE";
GRANT ON COMMIT REFRESH ON "SCHEMA"."TABLE3" TO "GRANTEE";
GRANT REFERENCES ON "SCHEMA"."TABLE4" TO "GRANTEE";
GRANT UPDATE ON "SCHEMA"."TABLE5" TO "GRANTEE";
GRANT SELECT ON "SCHEMA"."TABLE6" TO "GRANTEE";

Replace the GRANTEE with the one you want to have in the target database and run the GRANT commands again on the target database so that the GRANTS are set correctly. FINISH :)

ORA-30372: fine grain access policy conflicts with materialized view

Hello, I was getting the following error when creating and refreshing a materialized view:

ORA-30372 -  "fine grain access policy conflicts with materialized view"
*Cause:    A fine grain access control procedure has applied a non-null policy
           to the query for the materialized view.

This error occurs, when you are creating/refreshing a materialized view on the base of a remote table which is located on another database where Virtual Private Database (VPD) is enabled.

The simplest workaround is to use the "USING TRUSTED CONSTRAINTS" keyword in the syntax:

ORA-01619: thread 1 is mounted by another instance

The following problem may appear when patching the Oracle Grid Infrastructure:

If so, the solution for the problem is the following. You need to find out which node of the RAC is failing to startup. Once you have found out, you need to run the following command on that node (probably in nomount/mount state, locally direct on the specific instance):

alter system set thread=4 scope=spfile sid=’orcl4';

The thread parameter should be one of the available threads in the cluster. You can find that information from the following query:

SQL> select thread#, sequence#, status from v$log;

Then you should  shutdown the instance and retry:

[oracle@dbnodt1 ~]$ srvctl start database -d dbtiso

Opatch version check passed for oracle home but Opatch version check failed Grid Infrastructure

Hi,

if you ever run into the problem, that during patching the Grid Infrastructure the OPatch version check fails although you have updated the OPatch in the GI-HOME correctly and the console says:

The opatch minimum version  check for patch <Patch> failed  <DB_HOME>
The opatch minimum version  check for patch ... failed  for ...
Opatch version check failed for oracle home ...
Opatch version  check failed
ERROR: update the opatch version for the failed homes and retry

And the logs say the following:

Opatch version check passed for oracle home  <$GI_HOME>
Opatch version  check failed

The problem probably that you have only updated the OPatch of the GI-HOME before the running the GI-Patch. You also have to update the OPatch of the DB-Home to the latest version before patching the GI-Home.

Grant user access to several tables easy way

Hi, if you want to grant a database user access (insert, select, delete, update) to several tables of a view, you can use the following PL/SQL snippet:

Grant SELECT / INSERT / UPDATE rights to user for all tables in a schema

The following loop can be used to grant a user privileges for all tables of a schema:

PRFV-5637 DNS response time could not be checked on the following nodes

If you are getting following error on the installation of the Oracle Grid Infrastructure Installation:

PRFV-5637 DNS response time could not be checked on the fokllowing nodes
- Cause: An attempt to check DNS response time for unreachable node failed on nodes specified
- Action: Make sure that "nslookup" command exists on the nodes listed and the user executing the CVU check has execute privilege for it.

The problem is strongly related to the OS-level's bind-utils package.

The solution is to do the following as the root user:

After Grid Infrastructure deinstallation: [INS-40912] Virtual host name assigned to another system on the network

If you have ever installed a Oracle Grid Infrastructure Software and deinstalled it and again tried to install it, then you might run in the error [INS-40912] Virtual host name assigned to another system on the network.

If you check to ping node2 from node1 or node1 from node2 through their virtual IP's then that will succeed. This is wrong cause the nodes should NOT be pingable BEFORE the installation of the Grid Infrastructure.

In my case an alias was created for my interface NIC with the use of the virtual IP (which came with the previous installation):

eth0:2 Link encap:Ethernet  HWaddr 00:50:56:B9:0E:AA
       inet addr:10.200.11.159  Bcast:10.200.11.255  Mask:255.255.252.0
       UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
       Interrupt:19 Base address:0x2000

So in that case just do:

ifconfig eth0:2 down

Datafiles rausfinden, die Backup benötigen in sqlplus

Falls ihr irgendwann mal in der Situation seid, dass ihr rausfinden müsst, welche Datafiles ein Backup benötigen aber ihr aus irgendeinem Grund nicht RMAN benutzen könnt, dann könnt ihr folgenden Befehl in Sqlplus ausführen:

Apex ADMIN User/Benutzer ist gesperrt

Hallo,

letztens habe ich aus versehen in APEX meinen Admin User gesperrt wegen zu vielen falschen Versuchen. Ich war dabei verschiedene Lösungen dafür zu finden und das einfachste was ich finden konnte war folgendes:

1. Geht in eurer APEX Installations-Verzeichnis (normalerweise $ORACLE_HOME/apex)
2. Loggt euch in eure Datenbank ein via sqlplus as "sysdba"
3. Und dann folgendes ausführen: @apxchpwd

Das Script entsperrt den Admin-User und Ihr müsst dann einen neues Passwort speichern.

ORA-39095: Dump file space has been exhausted

Hi there,

when exporting database objects using DataPump, the objects are managed by DataPump in a .dmp file, which you specify with the dump file parameter in the expdp command:

dumpfile=FILENAME.dmp

Now with the exprt, the specified dump file will be created and will grow continuously as the export goes on. If your file system or any other setting limits the maximum allowed size for a file and the dump file reaches that limit and there are still objects remaining to be exported, you will get following error:

ORA-39095: Dump file space has been exhausted

The solution for that problem is that instead of creating one large dump file, create several smaller dump files and set a file size limit for the dump files:

Set the  maximum file size with the following parameter to your preferred value (in this case 1 GB):

filesize = 1000M

And now specify that if the file size limit is reached by the dump file, create the next dump file:

dumpfile=file_%U.dmp

where %U will by a number that will increment with each extra dump file that is created. In this case you would get dump files as followed:

file_1.dmp
file_2.dmp
file_3.dmp
...
file_99.dmp

Note that %U specification for the dump file can expand up to 99 files. If 99 files (99* 1GB = 99 GB) have been generated before the export has completed, it will again return the ORA-39095 error. So in that case you would have to increase the file size.

RMAN-01009: syntax error: found "clone": expecting one of: "double-quoted-string, identifier, single-quoted-string"

Manchmal kann es bei beim RMAN zu folgendem Fehler kommen.. Ihr wollt eine Datenbank duplizieren mit dem folgenden Befehl:

RMAN> duplicate target database to dbsoandso nofilename check;

Aber RMAN gibt folgenden Fehler zurück:

Die Lösung ist ganz einfach, aber es kann sein, dass man nicht direkt darauf kommt. Der Fehler liegt darin, dass der Datenbank name nicht mit Hochkomatas gesetzt wurde:

RMAN> duplicate target database to dbsoandso nofilename check;

Es sollte nämlich so sein:

RMAN> duplicate target database to 'dbsoandso' nofilename check;

Oracle Datenbank User kopieren

Wenn ihr mal einen DB-User duplizieren möchtet (inklusive aller Grants usw..), dann solltet ihr folgende Query ausführen. Jede Teil-Query dieser Query wird euch wiederum einen ausführbaren SQL Befehl zurückgeben. Alle zurückgegebenen Befehle zusammen bilden ein Script, das eine Kopie des Users anlegt:
 
Falsl irgendeine der obigen Befehle eine Exception liefert, bekommt ihr folgende Fehlermeldung:

Danach müsst ihr ein Suchen/Finden (Search/Replace) auf die Ausgabe ausführen und so den neuen User-Namen im Skript einführen und das Skript dann ausführen.

Oracle DataPump CONTENT parameter in API mode

Hi there,

recently there was a question in the Oracle Technology Forum (OTN) regarding using the API for DataPump.

In particular the question was concerning the CONTENT parameter which is available in normal DataPump. With the use of this keyword, you can filter what the DataPump Export unloads: data only, metadata only, or both:

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

This keyword does not exist in that form for the API version, but in the API, there are other methods that can be used to achieve the same.

For example if you want to export only the metadata of tables or schemas (so no actual rows), you can do this with the following way:

Use the  DATA_FILTER method to filter data and export only metadata:

For example:

DBMS_DATAPUMP.DATA_FILTER(h1,'INCLUDE_ROWS',0,NULL,NULL);

Use table_name and schema_name to specify which tables/schemas should be exported. If you set value = 0 in combination with INCLUDE_ROWS for name option, it disables the unloading of data by DataPump(=metadata_only).

How to copy database user on same database

If you want to copy a database user (including all the grants etc..), you should execute the following query. Each query will return you an executable SQL command. All returnings together will build a complete SQL script that will create a new user:
 
If any of the above has no output, you'll get an exception similar to this:

Then do a search and replace on the output to change the username and execute the script.

Find out datafiles that need backup in sqlplus

If you ever are in the situation that you want to find out the datafiles that need to be backuped but you cannot access RMAN for any reasons (e.g. access restrictions), you can execute the following statement in sqlplus to get the information:

How to include custom modules in Oracle RDA

Hi,

this time something really new.

For those of you who have been using the Remote Diagnostic Agent for Oracle: Have you ever wanted to create your own modules that would include your desired queries and end up in one *.htm file as illustrated in the following image:

The actions to achieve this are the following:

NOTE: This solution is only applicable with the 8.xx version of the RDA!

1. In the rda/collect/DB/ folder create your module, by creating a MYMOD.cfg and MYMOD.ctl file.

The MYMOD.cfg file is quiet straight forward and should contain the following code:
The MYMOD.ctl file is the actual file that will contain your queries. You should write and structure it like that:

Now for every information you want to query, you need to create one block with each of these components and add it to the MYMOD.ctl file:

And then following the same scheme you can add as much blocks as you want with different queries.

2.  Run the RDA in order to create an output.cfg. The output.cfg file is a config file which describes which modules have been executed. In order to include your module in the future of execution, add the following statements in the output.cfg file:

 

Now if you rerun the RDA (with the standard command # perl rda.pl), your modules will also be executed!

APEX user admin is locked / Unlock user

Hi,

recently I had accidentally locked my admin user through multiple wrong login attempts.
I was looking for a solution for that and after exploring different ways, I have found out that the easiest option is the following:

1. Go into your apex installation directory (usually $ORACLE_HOME/apex)
2. Login to your DB via sqlplus as "sysdba"
3. Execute: @apxchpwd

The script not only changes the password for the admin user, BUT ALSO UNLOCKS IT!

ORA-39095: Dump file space has been exhausted

Wenn es darum geht einen Export unter Verwendung von datapump (expdp) auszuführen, werden die exportieren Daten in den dump files (*.dmp) abgelegt, welche ihr mit dem dump file parameter im expdp Befehl angibt::

dumpfile=FILENAME.dmp

Nun mit dem Export werden die spezifizierten dump files angelegt und sie werden logischerweise immer größer solang der Export-Prozess andauert. Wenn euer Filesystem oder irgendeine andere Einstellung eine Maximalgröße für Dateien vorgibt, kann es es zu Problemen kommen. Denn falls eure Datenbank sehr groß ist und das dump file die maximale erlaubte Größe erreicht, bekommt ihr folgende Meldung:

ORA-39095: Dump file space has been exhausted

Die Lösung dafür ist, dass anstatt dass ein dump file beim Export erzeugt wird, der Export sich auf mehrere, kleinere dump files ausbreiten soll. Dann sollte beim Export Befehl die maximale Größe für jedes dieser dump files angegeben werden, z.B. hier 1 GB:


filesize = 1000M

Auch muss beim expdp Befehl angegeben werden, dass falls es wirklich dazu kommt, dass mehrere dump files angelegt werden, wie die Benennung der Dateien stattfinden soll. Zum Beispiel, wenn ihr wollt, dass jede dump file eine Nummer am Ende bekommt:

file_1.dmp
file_2.dmp
file_3.dmp
...
file_99.dm

Dazu müsst ihr folgenden Parameter am Befehl dranhängen:

dumpfile=file_%U.dmp

Dass %U übernimmt dabei die Nummerierung.

Achtung, die inkrementelle Nummerierung geht nur bis 99.  Falls 99 Dateien (99* 1GB = 99 GB) generiert wurden, bevor der Export abgeschlossen ist, dann wird wieder der ORA-39095 Fehler kommen. In diesem Fall müsstet ihr wiederum die maximale Größe für dump files erhöhen.

Wie spielt man ein PL/SQL package in eine Oracle Datenbank über den SQL Developer ein

Hallo,

letztens hatte ich den Task bekommen einige Packages in einer Oracle Datenbank einzuspielen und mir wurde empfohlen dabei den komfortablen SQL Developer zu benutzen.

Ich ging folgendermaßen an die Aufgabe heran: Nachdem ich mit der Ziel-DB verbunden war, öffnete ich die *.pkg Datei in SQL Developer über "Datei" --> "Öffnen". Das Package öffnete sich in einem Editor. Danach wählte ich oben rechts die Datenbank auf, auf die das Package drauf sollte:

 

Naiv wie ich war, klickte ich einfach auf dem grünen Play-Button und hoffte, dass das klappen würde, aber dem war nicht so. Es öffnete sich ein neues Fenster mit dem Titel "Ziel" wählen, welches mir auch anzeigte, dass es einen Syntax-Fehler im Code gab (der Editor meckerte, dass es irgendwo im Code ein "/" geben würde, was nicht erlaubt ist). D.h. ich konnte das Package nicht einspielen.

Die einfache Lösung für das Problem war eine andere Bedienweise: Anstatt die Package-Datei über Datei/Öffnen in den SQL Developer zu öffnen, sollte man sich als allererstes über den Database Explorer mit der Ziel-DB verbinden. Dies öffnet einen normalen SQL Editor. Dort sollte man dann den Code aus der Package-Datei copy-pasten und dann "Skript ausführen" wählen. So konnte ich das Package ohne irgendwelchen Problem ausführen.

RMAN-01009: syntax error: found "clone": expecting one of: "double-quoted-string, identifier, single-quoted-string"

Some of you might get into following problem. You want to duplicate a database and your duplicate command is the following:

RMAN> duplicate target database to dbsoandso nofilename check;

But RMAN returns following error: 

The solution is easily readable from the RMAN answer, but the one or the other person might not understand the problem immediately. So the mistake you have done is that you have forgotten the commas in:

RMAN> duplicate target database to dbsoandso nofilename check;

and it should be :

RMAN> duplicate target database to 'dbsoandso' nofilename check;

Mistake of opening an PL/SQL package as file in Oracle SQL Developer

Hi,

recently I had to introduce some packages into the database of a customer. I was told to use the handy Oracle SQL Developer for that task.

After connecting to the target database, I opened the package file (*.pkg) in SQL Developer by "File"->"Open".

The package got opened and in the package editor I selected the database on which to install the package:

 

Naive as I was I just clicked on the "Execute" button and received some syntax errors.

The simple solution for that problem was, that instead of opening the whole file and then selecting the target database: First connect to the database which opens an SQL editor and in that editor copy paste the complete code of the package and then execute the whole script:

So it begins ...

Hi there,

my name is Faraz Ahmed and I have recently started working as an Oracle Database Administrator. Blogging is part of being an enthusiastic and passionate Oracle DBA and that is the reason why I am joining the Oracle Blogger Community. I hope I can share new useful insight that I will gain throughout my work experience. :)