When you don't know the date and time to which you want to recover the database, SCN-based incomplete recovery
could be taken into consideration especially if you are able to retrieve a particular SCN.
In this scenario I will perform
a SCN-based incomplete recovery after I have used the Oracle
(more...)
Every incomplete recovery is followed by a resetlogs command before opening the database: each time you use a resetlogs command, a new incarnation
of the database is created.
While performing incomplete recovery it should be known the target point at which the recovery process needs to terminate: there are several
(more...)
Sometimes it could happen to retain a backup for a very long time, even forever that is it should be maintained until you manually remove it.
This post is related on how to keep a backup forever.
In particular the syntax to have a backup retained forever is using the
(more...)
To simulate other RMAN scenarios I need to create a recovery catalog.
This post so will be related on the basic steps to create the user account managing the recovery catalog and the creation of the recovery catalog itself.
The first step is to create the recovery catalog user account.
(more...)
Today I want to resynchronize a duplicated database: the source database is PROD @vsi08 server and the target database will have the same SID, but located @vsi10 server.
All the steps to set up a duplicated database are already explained in this
previous post.
Let's duplicate for the first time
(more...)
In the following scenario I simply want to have a copy of my production database to a different server preserving the same database directory structures and even the same database name.
My production database is running on vsi08 server and its system id (ORACLE_SID) is PROD; the remote server is
(more...)
In
this previous post we were able to recover contiguous corrupt blocks using the '
recover datafile' RMAN syntax.
But what about if we need to recover several sparse corrupt blocks ? Should we need to issue several different recover commands ?
How can I recover a list of sparse corrupt
(more...)
In this post I want to simulate a corrupt block recovery: honestly it happened to me only two or three times several years ago and it's generally caused by the I/O subsystem or because of software bugs.
Naturally you should consider this kind of recovery when you have few corrupt
(more...)
Today I had to relocate a service named MYPRD02 from one node to another, but it didn't work as usual and something new happened.
This production installation is an Oracle RAC environment formed by three nodes: it is based on Oracle Database Server 10gR2 patched to 10.2.0.4 for x86_64 Linux architecture.
All my clustered resources were online like the service I wanted to relocate.
[oracle@myrac01 ~]$ crsstat
HA Resource Target State
----------- ------ -----
...
ora.MYRAC.MYPRD02.MYRAC1.srv ONLINE ONLINE
ora.MYRAC.MYPRD02.cs ONLINE ONLINE
...
I executed the following
relocate service command using
srvctl cli
(more...)
Few days ago my datawarehouse division asked to check and eventually speed up their nightly loading jobs:
they said already in the past
different solutions were implemented by other collegues and would like to know if it was possible to implement or adopt new feature to solve their problems.
I asked which were the most important jobs to speed up and which Oracle schemas they used.
It was clear and evident those hundred of jobs were created years ago and no one wanted to manage them.
Several shell scripts scheduled by Sun Grid Engine (now Oracle Grid Engine) using different
(more...)
In the following scenario I'm going to lose two datafiles of two different non-system critical tablespaces and restore them, while the database is CLOSED, to a location other than the original one because I'm experiencing a serious and permanent media failure.
To simulate this scenario I want to remove a datafile from EXAMPLE and APEX tablespaces.
Let's see first where are located those datafiles.
SQL> select file_name from dba_data_files
2 where TABLESPACE_NAME IN ('EXAMPLE', 'APEX');
FILE_NAME
----------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/example01.dbf
/home/oracle/app/oracle/oradata/orcl/APEX.dbf
/home/oracle/app/oracle/oradata/orcl/example02.dbf
/home/oracle/app/oracle/oradata/orcl/APEX02.dbf
I'm going to remove
example02.dbf and
APEX02.dbf datafiles because I have a valid
(more...)
In the following scenario I'm going to lose datafiles of a non-system critical tablespace and restore them, while the database is open, to a location other than the original one because I'm experiencing a serious and permanent media failure.
Before proceeding I add another datafile to EXAMPLE tablespace so it is now formed by 2 different datafiles.
SQL> select file_name from dba_data_files
2 where TABLESPACE_NAME = 'EXAMPLE';
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/example01.dbf
SQL> alter tablespace example add datafile '/home/oracle/app/oracle/oradata/orcl/example02.dbf' size 1M autoextend on next 5M maxsize 50M;
Tablespace altered.
SQL> select file_name from dba_data_files
2 where TABLESPACE_NAME = 'EXAMPLE';
FILE_NAME
(more...)
You have to export some schemas used by your latest application:
this new application restricts access by a customer to its own user's data based on a set of name-value pairs that Oracle Database stores in memory, the so called "Application Context".
You have already completed the export and import operations, but you do not find on production database any application context.
This is the export command you executed...
[oracle@vsi03dev log]$ expdp system dumpfile=3schema.dmp schemas=schema_q,schema_t,schema_v exclude=statistics
and the following is the import command:
[oracle@qdb01frm dump]$ impdp system dumpfile=3schema.dmp remap_tablespace=tsd_schema:tsd_schema_new,tsi_schema:tsi_schema_new
... but looking at the export log you discover the
(more...)
In a previous
post we saw how to proceed when you lose a non-system tablespace while the database is open.
Today I'm going to describe another way to restore it while the database is not open: for some reasons your database crashed and while trying to start it up you are getting "ORA-01157: cannot identify/lock data file %s - see DBWR trace file".
Let's simulate a loss of the EXAMPLE tablespace, in my case formed by only one datafile:
[oracle@localhost ~]$ ll /home/oracle/app/oracle/oradata/orcl/example01*
-rw-rw---- 1 oracle oracle 85991424 Nov 30 02:08 /home/oracle/app/oracle/oradata/orcl/example01.dbf
[oracle@localhost orcl]$ rm example01.dbf
The database
(more...)
This post is related to a different behaviour on revoking RESOURCE role between Oracle version 11gR2 and 10gR2.
It could happen that revoking the RESOURCE role from a user on 11gR2 generates several errors from user's prospective, especially when he tries to perform his usual DML operations: this situation doesn't happen instead on 10gR2.
Let's go through an example and connect to an Oracle 11gR2 instance.
[oracle@localhost orcl]$ sqlplus / as sysdba
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - (more...)
Few days ago I received an email from a user who had two empty table, one referenced by the other.
He was not able to execute a truncate command on the parent table and asked me to solve his error:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys.
Truncate command is very useful when you want to completely remove all data from a table: for very large tables it is usually the best way to remove your data.
When you use the TRUNCATE command Oracle sets back to zero the HWM (high-water mark) of the table and, compared
(more...)
This post will take into consideration a Recovery Manager setting using the CONTROLFILE AUTOBACKUP feature, a tablespace created after the only available full backup (so this backup doesn't have information on this tablespace), some rows committed on the new tablespace, a crash happened after a backup of the current controlfile was completed.
This crash envolves the lost of the "never backed up" tablespace and of the current controlfile.
Let's start with an example. Our instance is up and running.
[oracle@localhost orcl]$ ps -ef | grep smon
oracle 13600 1 0 06:11 ? 00:00:00 ora_smon_orcl
oracle 13794 13766 0 06:24 pts/5 00:00:00 (more...)
RSS