RMAN create duplicate database using Oracle 11g on same host

Some times there is a need to create the duplicate database from the running database on same host. This will require following steps for the creation of the duplicate database.

1. Create spfile for the duplicate database and corresponding directories.
2. Create the password file.
3. Setup listener and tnsnames.ora for duplicate database.
4. Check the latest archive log sequence number from main database.
5. connect using RMAN and run script to duplicate database.

In this example I am duplicating from TDB (Test database) to DUPLI (Duplicate database).

1. First of all an initDUPLI.ora was created under ORACLE_HOME/dbs with below parameters:

*.audit_file_dest='/u01/app/oracle/admin/DUPLI/adump'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/DUPLI/control01.ctl','/arch/FRA2/DUPLI/control02.ctl'

*.db_file_name_convert='/u01/app/oracle/oradata/TDB/','/u01/app/oracle/oradata/DUPLI/'

*.log_file_name_convert='/u01/app/oracle/oradata/TDB/','/u01/app/oracle/oradata/DUPLI/'

*.db_block_size=8192

*.db_name='DUPLI'

*.db_recovery_file_dest='/arch/FRA2'

*.db_recovery_file_dest_size=10485760000

*.diagnostic_dest='/u01/app/oracle'

*.log_archive_format='%t_%s_%r.arc'

*.memory_max_target=500M

I have highlighted the important parameters.

mkdir -p /u01/app/oracle/admin/DUPLI/adump
mkdir -p /arch/FRA2/DUPLI/

Now to create spfile and start up database in nomount.

startup nomount
create spfile from pfile='?/dbs/initDUPLI.ora'
shutdown immediate
startup nomount

2. Create password file so that rman can connect 

orapwd file=orapwDUPLI password=********

3. Listener setup was done and a TNS entry was created.

DUPLI_TNS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hname.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DUPLI)
    )

  )

Check the status of the listener and make sure that you are getting 

Service "DUPLI" has 2 instance(s).
  Instance "DUPLI", status UNKNOWN, has 1 handler(s) for this service...

  Instance "DUPLI", status BLOCKED, has 1 handler(s) for this service...


4. Check the archivelog sequence number from main database which is TDB in our case.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Next log sequence to archive   8

Current log sequence           8


5. Log into RMAN and run following to create duplicate database.

$ rman 
RMAN> 
RMAN> connect target sys/********@tdb

RMAN> connect auxiliary sys/********@dupli_tns
RMAN> run {
2> set until sequence 8 thread 1;
3> duplicate target database to dupli;
4> }
RMAN> 


and let this finish and you are done.

Grant readonly access to PL/SQL (Function, procedures, packages etc) user logged in through SQL Developer

Sometimes developer need read only access to the code of you PL/SQL in oracle, this is not difficult to achieve. You can create a user

create user readonlyU identified by strongpassword default tablespace users temporary tablespace temp;

grant connect to readonlyU;
grant select on sys.dba_source to readonlyU;

This will work great but if you log in through SQL Developer you will not able to see the PL/SQL objects.

There are two solutions to this problem. After implementation of these solutions readonlyU should be able to view the source code in SQL Developer like the image below:

1. Grant select access to all tables under database and then set O7_DICTIONARY_ACCESSIBILITY=TRUE

This would allow user to have select access on all the tables including data dictionary tables. This could not be the best option.

grant select any table to readonlyU;
alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;
shutdown immediate
startup

2. SQL Developer us two dba views to show the PL/SQL objects under left navigation and those are dba_objects and dba_plsql_object_settings. Hence granting select on these two views will do the trick.

grant select on sys.dba_objects to readonlyU;
grant select on sys.dba_plsql_object_settings to readonlyU;