If you are want to schedule a job in the Oracle Database to execute a certain task regularly, basically you can use one of these methods:
1. Oracle Scheduler (DBMS_SCHEDULER):
DBMS_SCHEDULER is a powerful feature that you can use to schedule jobs on the database, which can be very useful for certain DBA tasks like indexes rebuilding, backups checking or data consistency.
To view the information about currently running jobs, you can execute this query:
SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;
With this query you can check all the scheduled jobs into the database:
SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;
It is a very strong feature and very recommended currently. If you want to find more information about the scheduler, check the Oracle documentation in this link:
2. Oracle Jobs (DBMS_JOB):
DBMS_JOB is a deprecated feature but you can still use it even in Oracle 12c, even if it started in Oracle 7!
The DBMS JOB feature is a lot less intuitive to use, although some DBAs state that it is more flexible. In any case, either if you want to take advantage of its flexibility or if you need to understand how it works because your database already uses this feature, it is another option.
You can find the Oracle documentation here:
And a very good and practical post explaining how to use DBMS_JOB:
3. Crontab (Unix/Linux) / Windows Task Scheduler
The third option is to externalize the task execution to the Operating System, so that the tasks are executed through a .sh in Unix, or a .bat in Windows, executing later a .sql to the database.
This method is easier to manage if you are already using crontab or Task Scheduler for other tasks not related with the database, but the disadvantage is that it is node dependent so it can be a problem in the case of Oracle RAC.
Do you know any other method to schedule jobs in Oracle? What is your prefered one?
To create new temporary tablespace and set it as default, follow these steps:
1. Check the path of the current tempfiles to reuse the path:
SELECT NAME FROM V$TEMPFILE;
2. Create the new temporary tablespace:
CREATE TEMPORARY TABLESPACE TEMP_MARIO
or if you have OMF just:
CREATE TEMPORARY TABLESPACE TEMP_MARIO;
3. Establish this temporary tablespace as default:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_MARIO;
4. Check that the change has been made:
WHERE property_name like ‘%TEMP%TABLESPACE’;
If we have Dataguard, either with physical or logical standby, our databases will always be synchronized and we will be able to do switchover whenever we want.
Or not… maybe when we want to do a switchover, we notice that the standby database hasn’t been synchronized in months, and we need to restore the synchronization as soon as possible.
If the gap resolution is not an option, we will have to use RMAN in order to perform this operation. The steps will be the following ones:
1. On standby site, identify the minimum SCN used by all the datafiles:
SQL> select min(CHECKPOINT_CHANGE#) from v$datafile_header;
2. On primary site, take an incremental backup of the database using a number a bit smaller than the smallest SCN obtained.
backup as compressed backupset incremental from scn 262044600 database format ‘/opt/oracle/install/restore_syncr/full_backup_mario_%U’;
3. Take also a backup of the controlfile:
backup as copy current controlfile format ‘/opt/oracle/install/restore_syncr/backup_control_file_mario’;
4. Copy the files to standby site (scp, ftp, diskettes)
5. Execute the following commands on RMAN in order to apply the incremental backup:
startup force nomount;
restore standby controlfile from ‘/opt/oracle/install/restore_syncr/backup_control_file_mario’ ;
alter database mount;
catalog start with ‘+DATA/mario/datafile’ ;
catalog start with ‘/opt/oracle/install/restore_syncr/’ ;
switch database to copy ;
allocate channel a0 device type disk ;
allocate channel a1 device type disk ;
allocate channel a2 device type disk ;
allocate channel a3 device type disk ;
allocate channel a4 device type disk ;
allocate channel a5 device type disk ;
allocate channel a6 device type disk ;
allocate channel a7 device type disk ;
allocate channel a8 device type disk ;
allocate channel a9 device type disk ;
recover database ;
6. Start the MRP process:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT ;
7. Enjoy your synchronized database and make sure that you don’t have to restore it again
In the following post we will explain how to access to the information of Oracle datafiles without the original database, only with the database files.
This is particularly useful in case that you had some kind of disaster and the information has to be recovered at any cost. Of course, making a restore with RMAN, Data Pump or other methods would be desirable… but we all know that some people just work with databases in production without a backup system right?
If you ask Oracle support how to make a recovery ONLY with some datafiles, they will tell you that this is impossible. However, by definition these files contain information, although it is encrypted. Let’s see how can we access to this information:
1. Trying to restore into another database.
There is a slight that you are able to restore a datafile from one database into another, just having the .dbf. The concept is that you need to modify the header of this datafile, to fool the database and make it belive that the datafile belongs to it.
You will still need a foresnic tool to modify the datafile and addapt it to a new database. But the advantage is that you will be able to make a clean export and restore it in a new database if you are successful.
Personally, I have never succeded with this method, although it would be technically possible.
2. DBF Viewer and Editor
There is an application for Windows that you can use to read directly the datafiles using ODBC or BDE. It’s called DBF Viewer and Editor, and you can find more information here:
The license costs 71€, but you can try it for free, although you won’t be able to read the whole datafile.
For many years, BBED has been considered a secret tool, only known by a few, because it allows not only to read directly from datafiles, but also to write on them.
Nowdays, it is not included in Oracle installations anymore, but you can still use it on older versions.
The advantage of BBED is that it’s a free tool. It is really complex, and even if you are able to access the data, you can’t read this information easily, and much less perform selects. But if the information is really critical, and you don’t have any kind of backups, this could be a really valuable option.
I have personally used this tool, and I can assure that it works.
For more information, you can find a full explanation in this link:
Please keep in mind that this is the last resource, in case that restoring with RMAN or other backup methods is completely impossible.
Never try this tools in production, because overwritting a datafile will probably cause a database corruption, and you won’t be able to work with the database anymore. You should always work with a copy of the datafile.
Of course, there are more datafile foresnic methods. If you have worked with other programs and you have a better solution for this problem, please don’t hesitate to make a comment and share your knowledge with us.
If you ever have worked with a RAC database, and you have changed a normal user password, you will notice that it has no difference between a single instance database. You just have to perform “ALTER USER xxx IDENTIFIED BY yyyy” and your password will be changed.
However, today I had a problem with a RAC database. I had to change the SYS password, and I did it the same way I would do in a single-instance database. But when I tried to connect with sys user, the error “ORA-01017: invalid username/password; logon denied” appeared. What I was doing wrong?
After a few thoughts, I found the solution. SYS password is instance specific in RAC databases, so you have to change it in every single instance. That’s all.
This post will be simple but really efective. If you want to delete an instance in silent mode, without using any GUI, you just have to write the following as oracle user:
dbca -silent -deleteDatabase -sourceDB YOUR_DATABASE
I hope you will find this command as useful as it is for me 🙂
In response for a reader of this blog who asked me for some interview questions for a 3+Year experienced DBA, I have written this series of questions that you can use in your interviews. Either if you are the interviewer, or the interviewer. Enjoy!
1. Basic (Every DBA should answer correctly ALL these questions. This knowledge is just basic for a 3+ year experienced DBA)
1.1 Q- Which are the default passwords of SYSTEM/SYS?
A- MANAGER / CHANGE_ON_INSTALL
1.2 Q- How can you execute a script file in SQLPLUS?
A- To execute a script file in SQLPlus, type @ and then the file name.
1.3 Q- Where can you find official Oracle documentation?
1.4 Q- What is the address of the Official Oracle Support?
A- metalink.oracle.com or support.oracle.com
1.5 Q- What file will you use to establish Oracle connections from a remote client?
1.6 Q- How can you check if the database is accepting connections?
A- lsnrctl status or lsnrctl services
1.7 Q- Which log would you check if a database has a problem?
A- Alert log
1.8 Q- Name three clients to connect with Oracle, for example, SQL Developer:
A- SQL Developer, SQL-Plus, TOAD, dbvisualizer, PL/SQL Developer… There are several, but an experienced dba should know at least three clients.
1.9 Q- How can you check the structure of a table from sqlplus?
A- DESCRIBE or DESC
1.10 Q- What command will you start to run the installation of Oracle software on Linux?
2. Moderate (Standard knoledge for a daily-work of every DBA. He could fail one or two questions, but not more)
2.1 Q- What should you do if you encounter an ORA-600?
A- Contact Oracle Support
2.2 Q- Explain the differences between PFILE and SPFILE
A- A PFILE is a Static, text file that initialices the database parameter in the moment that it’s started. If you want to modify parameters in PFILE, you have to restart the database.
A SPFILE is a dynamic, binary file that allows you to overwrite parameters while the database is already started (with some exceptions)
2.3 Q- In which Oracle version was Data Pump introduced?
A- Oracle 10g
2.4 Q- Say two examples of DML, two of DCL and two of DDL
A- DML: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
DDL: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
DCL: GRANT, REVOKE
2.5 Q- You want to save the output of an Oracle script from sqlplus. How would you do it?
A- spool script_name.txt
select * from your_oracle_operations;
2.6 Q- What is the most important requirement in order to use RMAN to make consistent hot backups?
A- Your database has to be in ARCHIVELOG mode.
2.7 Q- Can you connect to a local database without a listener?
A- Yes, you can.
2.8 Q- In which view can you find information about every view and table of oracle dictionary?
A- DICT or DICTIONARY
2.9 Q- How can you view all the users account in the database?
A- SELECT USERNAME FROM DBA_USERS;
2.10 Q- In linux, how can we change which databases are started during a reboot?
A- Edit /etc/oratab
3. Advanced (A 3+ year experienced DBA should have enough knowledge to answer these questions. However, depending on the work he has done, he could still fail up to 4 questions)
3.1 Q- When a user process fails, what Oracle background process will clean after it?
3.2 Q- How can you reduce the space of TEMP datafile?
A- Prior to Oracle 11g, you had to recreate the datafile. In Oracle 11g a new feature was introduced, and you can shrink the TEMP tablespace.
3.3 Q- How can you view all the current users connected in your database in this moment?
A- SELECT COUNT(*),USERNAME FROM V$SESSION GROUP BY USERNAME;
3.4 Q- Explain the differences between SHUTDOWN, SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE AND SHUTDOWN ABORT
A- SHUTOWN NORMAL = SHUTDOWN : It waits for all sessions to end, without allowing new connections.
SHUTDOWN IMMEDIATE : Rollback current transactions and terminates every session.
SHUTDOWN ABORT : Aborts all the sessions, leaving the database in an inconsistent state. It’s the fastest method, but can lead to database corruption.
3.5 Q- Is it possible to backup your database without the use of an RMAN database to store the catalog?
A- Yes, but the catalog would be stored in the controlfile.
3.6 Q- Which are the main components of Oracle Grid Control?
A- OMR (Oracle Management Repository), OMS (Oracle Management Server) and OMA (Oracle Management Agent).
3.7 Q- What command will you use to navigate through ASM files?
3.8 Q- What is the difference between a view and a materialized view?
A- A view is a select that is executed each time an user accesses to it. A materialized view stores the result of this query in memory for faster access purposes.
3.9 Q- Which one is faster: DELETE or TRUNCATE?
3.10 Q- Are passwords in oracle case sensitive?
A- Only since Oracle 11g.
4. RAC (Only intended for RAC-specific DBAs, with varied difficultied questions)
4.1 Q- What is the recommended method to make backups of a RAC environment?
A- RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file.
4.2 Q- What command would you use to check the availability of the RAC system?
A- crs_stat -t -v (-t -v are optional)
4.3 Q- What is the minimum number of instances you need to have in order to create a RAC?
A- 1. You can create a RAC with just one server.
4.4 Q- Name two specific RAC background processes
A- RAC processes are: LMON, LMDx, LMSn, LKCx and DIAG.
4.5 Q- Can you have many database versions in the same RAC?
A- Yes, but Clusterware version must be greater than the greater database version.
4.6 Q- What was RAC previous name before it was called RAC?
A- OPS: Oracle Parallel Server
4.7 Q- What RAC component is used for communication between instances?
A- Private Interconnect.
4.8 Q- What is the difference between normal views and RAC views?
A- RAC views has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION
4.9 Q- Which command will we use to manage (stop, start…) RAC services in command-line mode?
4.10 Q- How many alert logs exist in a RAC environment?
A- One for each instance.
5. Master (A 3+ year experienced DBA would probably fail these questions, they are very specifid and specially difficult. Be glad if he’s able to answer some of them)
5.1 Q- How can you difference a usual parameter and an undocumented parameter?
A- Undocumented parameters have the prefix ‘_’. For example, _allow_resetlogs_corruption
5.2 Q- What is BBED?
A- An undocumented Oracle tool used for foresnic purposes. Stans for Block Browser and EDitor.
5.3 Q- The result of the logical comparison (NULL = NULL) will be… And in the case of (NULL != NULL)
A- False in both cases.
5.4 Q- Explain Oracle memory structure
The Oracle RDBMS creates and uses storage on the computer hard disk and in random access memory (RAM). The portion in the computer s RAM is called memory structure. Oracle has two memory structures in the computer s RAM. The two structures are the Program Global Area (PGA) and the System Global Area (SGA).
The PGA contains data and control information for a single user process. The SGA is the memory segment that stores data that the user has retrieved from the database or data that the user wants to place into the database.
5.5 Q- Will RMAN take backups of read-only tablespaces?
5.6 Q- Will a user be able to modify a table with SELECT only privilege?
A- He won’t be able to UPDATE/INSERT into that table, but for some reason, he will still be able to lock a certain table.
5.7 Q- What Oracle tool will you use to transform datafiles into text files?
A- Trick question: you can’t do that, at least with any Oracle tool. A very experienced DBA should perfectly know this.
5.8 Q- SQL> SELECT * FROM MY_SCHEMA.MY_TABLE;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
Why I’m getting this error?
A- The table has a BLOB column.
5.9 Q- What parameter will you use to force the starting of your database with a corrupted resetlog?
5.10 Q- Name the seven types of Oracle tables
A- Heap Organized Tables, Index Organized Tables, Index Clustered Tables, Hash Clustered Tables, Nested Tables, Global Temporary Tables, Object Tables.