Recover from a DROP or TRUNCATE table by using RMAN

To recover from a dropped or truncated table, a dummy database (copy of primary) will be restored and recovered to point in time so the table can be exported.  Once the table export is complete, the table can be imported into the primary  database. This dummy database can be a subset of the primary database. However, the 'dummy' database must include the SYSTEM, UNDO (or ROLLBACK), and the  tablespace(s) where the dropped/truncated table resides.

Read more

Impdp does not created user/schema

Until  now my understanding for EXPDP/IMPDP was that if the user performing import has 'IMPORT FULL DATABASE' privilege, it'll create the users/schemas in the target database. I needed to export (expdp) schema from one machine and import (impdp) it to another machine. I just wanted  impdp to create schema on target database.

While doing  import  i encountered error

$ impdp system/oracle123 directory=TEST_DIR dumpfile=test.dmp logfile=impdptest.log

 .......

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=TEST_DIR dumpfile=USR_TEST.dmp logfile=impdpUSR_TEST.log

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

ORA-39083: Object type PROCACT_SCHEMA failed to create with error:

ORA-31625: Schema USR_TEST is needed to import this object, but is unaccessible

ORA-01435: user does not exist

Failing sql is:

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'XXX.YYY.COM', inst_scn=>'7788478540892');COMMIT; END;

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"USR_TEST"."TABLE1" failed to create with error:

ORA-01918: user 'USR_TEST' does not exist

Failing sql is:

CREATE TABLE "USR_TEST"."TABLE1" ("COLUMN1" VARCHAR2(20 BYTE) NOT NULL ENABLE, "COLUMN2" VARCHAR2(20 BYTE), "COLUMN3" VARCHAR2(20 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAU

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s).

$

After reading some documents,blogs I discovered  that If  the schema you are importing to does not already exist, the import operation creates it, provided the dump file set contains the necessary CREATE USER metadata and you are importing with enough privileges.

Meaning the oracle user that exported the schema, should have had CREATE USER privilege.Export was done with user (USR_TEST) was a basic user and not DBA/create-user-privileged. 

Below query given result as.

SQL>  select  *  from  ROLE_SYS_PRIVS  where  PRIVILEGE = ’CREATE USER’ ;

ROLE                                              PRIVILEGE                     ADM
----------------------------           --------------------              ---

DBA                                               CREATE USER                 YES

IMP_FULL_DATABASE           CREATE USER                  NO

2 rows selected.

It means EXP_FULL_DATABAS role does not have create user privilege.

I did 'GRANT CREATE USER TO USR_TEST' here and  ran the export again and this time some additional "object types" were processed.

 when USR_TEST user didn't have 'CREATE USER' privilege:

 $ expdp USR_TEST/USR_TEST schemas=USR_TEST content=METADATA_ONLY directory=TEST_DIR dumpfile=USR_TEST.dmp logfile=expdpUSR_TEST.log

.......

Starting "USR_TEST"."SYS_EXPORT_SCHEMA_01":  USR_TEST/******** schemas=USR_TEST content=METADATA_ONLY directory=TEST_DIR dumpfile=USR_TEST.dmp logfile=expdpUSR_TEST.log

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Master table "USR_TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

.......

when USR_TEST user  have 'CREATE USER' privilege:

 $ expdp USR_TEST/USR_TEST schemas=USR_TEST content=METADATA_ONLY directory=TEST_DIR dumpfile=USR_TEST.dmp logfile=expdpUSR_TEST.log

.......

Starting "USR_TEST"."SYS_EXPORT_SCHEMA_01":  USR_TEST/******** schemas=USR_TEST content=METADATA_ONLY directory=TEST_DIR dumpfile=USR_TEST.dmp logfile=expdpUSR_TEST.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Master table "USR_TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

.......

Note the Processing object type SCHEMA_EXPORT/USER, SCHEMA_EXPORT/SYSTEM_GRANT, SCHEMA_EXPORT/ROLE_GRANT, SCHEMA_EXPORT/DEFAULT_ROLE.

To avoid such situations always try to take the export using SYSTEM user.

Read more

Identify the Tablespace names from EXPDP dump file

 I got  request from development team to import some old data in UAT region.The expdp dump file received from backup team was without  log file and i have no access to the export file's source DB. I wanted to import that dumpfile into UAT DB and i do not know the tablespace names required to do so.

Solution:

Use the sqlfile option present in impdp utility. This option will write all the SQL DDL to a specified file instead of executing them.

$ impdp scott/tiger directory=exp_dir dumpfile=scott.dmp sqlfile=script.sql

Import: Release 11.1.0.7.0 - 64bit Production on Tuesday, 20 February, 2014 21:34:36

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SCOTT"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_SQL_FILE_FULL_01":  scott/******** directory=exp_dir dumpfile=scott.dmp sqlfile=script.sql

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTT"."SYS_SQL_FILE_FULL_01" successfully completed at 21:34:53

The content of the scripts.sql file would be like this.

-- CONNECT SCOTT

ALTER SESSION SET EDITION = "ORA$BASE";

-- new object type path: SCHEMA_EXPORT/USER

-- CONNECT SYSTEM

ALTER SESSION SET EDITION = "ORA$BASE";

 CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:D846EA3EB87287A3AED08AF38EB0B4F640F49A9A4A972108BF3917B769;DB1B37F84BDF15E6'

      DEFAULT TABLESPACE "USERS"

      TEMPORARY TABLESPACE "TEMP";

-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT

GRANT UNLIMITED TABLESPACE TO "SCOTT";

-- new object type path: SCHEMA_EXPORT/ROLE_GRANT

 GRANT "DBA" TO "SCOTT";

-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE

 ALTER USER "SCOTT" DEFAULT ROLE ALL;

-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

-- CONNECT SCOTT

ALTER SESSION SET EDITION = "ORA$BASE";

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'PROD9.DIAMOND.COM', inst_scn=>

'11626845804212');

COMMIT;

END;

/

 .

.

.

.

.

.

After finding all the required tablespaces i used REMAP_TABLESPACE  and then import the dump file .

One can manually create same  tablespaces and import the dump file. 

Read more

Restoring an RMAN Backup to Another Node /Clone Database

In certain circumstances, it may be desirable to restore a database from an RMAN backup onto a machine other than the original host. 
For example,Restore and recover data at a given point in time for UAT/Testing, or to duplicate a production instance.
The example assumes:
the target database is on host A
the database is to be restored onto host B
the directory structure of host B is different to host A
the ORACLE_SID will not change for the restored database
a recovery catalog is being used
the backups were carried out to disk (for illustrative purposes, and to disassociate from any media manager specific issues)

The following steps are required:
1. backup the target on host A
2. list the datafile locations on host A
3. make the backup available to host B
4. make a copy of the init.ora available to host B
5. edit the init.ora to reflect directory structure changes
6. configure SQL*Net connectivity from host to the recovery catalog and duplicated database
7. set up a password file for the duplicated database
8. startup nomount the duplicated database
9. RMAN restore the controlfile(s)
10. mount the database
11. restore and rename the datafiles
12. recover and open the database
These steps are expanded further below.

1.Backup the Target on Host A
The target database needs to be backed up using RMAN. The following  commands of RMAN doing an online database backup. In this example, the backup sets are written to disk.

RMAN>
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
#backup the whole db
backup tag whole_database_open format '/oracle/backups/df_%u' database;
# switch out of the current logfile
sql 'alter system archive log current';
#backup the archived logs
backup archivelog all format '/oracle/backups/al_%u';
# backup a copy of the controlfile that contains records for the other backups just made
backup current controlfile tag = cf1 format '/oracle/backups/cf_%u';
}

2.List Datafile Locations on Host A
The datafile numbers and location on host A are required. These datafile locations will change on host B 
select file#, name from v$datafile;
file#   name
-----   -----------------------------------------------------------
1       /oracle/OFA_base/u01/oradata/TEST/system01.dbf
2       /oracle/OFA_base/u01/oradata/TEST/rbs01.dbf
3       /oracle/OFA_base/u01/oradata/TEST/temp01.dbf
4       /oracle/OFA_base/u01/oradata/TEST/tools01.dbf
5       /oracle/OFA_base/u01/oradata/TEST/users01.dbf
6       /oracle/OFA_base/u01/oradata/TEST/users02.dbf
7       /oracle/OFA_base/u01/oradata/TEST/rbs02.dbf
8       /oracle/OFA_base/u01/oradata/TEST/rcvcat.dbf

The log file names should also be recorded
select group#, member from v$logfile;
group#    member
---------   -----------------------------------------------------------
1            /oracle/OFA_base/u01/oradata/TEST/redo01.log
2            /oracle/OFA_base/u01/oradata/TEST/redo02.log
3            /oracle/OFA_base/u01/oradata/TEST/redo03.log

3 Make the Backups Available to Host B
3.1 Disk Backups
During restore, RMAN will expect the backup sets to be located in the same directory as written to during the backup. For disk backups, the DBA can accomplish this in many ways: set up an NFS directory, mounted on both host A and host B create the same directory structure on host A and host B, use of symbolic links on host B,FTP backup to host B.
If backup directory is not same at HOST B then catalog backup files using RMAN.
3.2 Tape Backups
The media management software must be configured such that host B is a media manager client, and can read the backup sets. The media management vendor should be consulted for support on this issue.

4. and 5. init.ora on host B
The "init.ora" needs to be made available on host B. Any location specific parameters must be ammended. For example,
*_dump_dest
log_archive_dest*
control_files

6. SQL*Net configuration
If running rman from host A:
a. connectivity to the catalog remains unchanged
b. configure tnsnames.ora on host A to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database
If running rman from host B:
a. configure tnsnames.ora on host B to connect to catalog listener.ora on catalog host remains unchanged
b. configure tnsnames.ora on host B to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database
If running rman from host C (ie, neither host A or host B):
a. connectivity to the catalog remains unchanged
b. configure tnsnames.ora on host C to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database

7. Setup Password File
In order to allow RMAN remote connections, a password file must be setup for the duplicated database. 
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password1

8. Startup nomount the database
SVRMGR> startup nomount pfile=<location of init.ora>

9. RMAN restore the controlfile(s)
Restore the controlfile(s). 
RMAN>
run{
allocate channel c1 type disk;
restore controlfile;
}

10. Mount the database
SVRMGR> alter database mount;

11. Rename and restore the datafiles
Rename and Restore the Files, and perform database recovery. RMAN can be used to change the location of the datafiles from the location on host A (see Section 2) to the new location on host B.
RMAN>
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
set newname for datafile 1 to '/oracle/datafiles/system01.dbf';
set newname for datafile 2 to '/oracle/datafiles/rbs01.dbf';
set newname for datafile 3 to '/oracle/datafiles/temp01.dbf';
set newname for datafile 4 to '/oracle/datafiles/tools01.dbf';
set newname for datafile 5 to '/oracle/datafiles/users01.dbf';
set newname for datafile 6 to '/oracle/datafiles/users02.dbf';
set newname for datafile 7 to '/oracle/datafiles/rbs02.dbf';
set newname for datafile 8 to '/oracle/datafiles/rcvcat.dbf';
restore database;
switch datafile all;
}


12. Recover and open the database
Perform incomplete recovery:
SVRMGR> recover database using backup controlfile until cancel;
Forward the database applying archived redo log files until you decide to stop recovery by typing cancel at the prompt (assuming that you have required archived redo log files in the log_archive_dest directory). You may archive the source database redo log files and apply them at the target database if required.
SVRMGR> alter database open resetlogs;
Note: this will create the online redo logs in the same location as that on host A. If this directory location does not exist, then this will fail with:
ora344 : unable to recreate online log <name>
The workaround is to rename the logfiles prior to opening the database:
SVRMGR> alter database rename file '<host A location>' to '<host B location>';
Alternatively, the logfile groups can be dropped and recreated. 

Read more