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!