Oracle grant and revoke from user - GRANT and REVOKE

Below example show how grant and revoke work for a user in Oracle:


SQL> select owner from all_objects where object_name like '%CUSTOMER%';

OWNER
--------------------------------------------------------------------------------
MY_WORKSPACE

SQL> conn MY_WORKSPACE/*******
Connected.

SQL>  desc customer
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUST_ID                                            NUMBER
 CUST_NAME                                          VARCHAR2(40)
 CUST_SEX                                           VARCHAR2(10)
 CUST_ADD                                           VARCHAR2(100)
 CUST_MOBILE                                        NUMBER
 CUST_JOIN_DATE                                     DATE
 CUST_STATUS                                        VARCHAR2(10)

SQL> CREATE VIEW V_CUSTOMER
AS SELECT CUST_ID,CUST_NAME
FROM CUSTOMER;  2    3

View created.


SQL> select * from V_CUSTOMER;

   CUST_ID CUST_NAME
---------- ----------------------------------------
         2 ram
         3 shayam
         4 madhu



SQL> create user temp_user identified by welcome1;

User created.

SQL> grant create session to temp_user;

Grant succeeded.

SQL> grant select on MY_WORKSPACE.V_CUSTOMER to temp_user;

Grant succeeded.

SQL> conn temp_user/welcome1
Connected.

SQL> select * from MY_WORKSPACE.V_CUSTOMER;

   CUST_ID CUST_NAME
---------- ----------------------------------------
         2 ram
         3 shayam
         4 madhu

3 rows selected.

As sysdba revoke it :

SQL> revoke select on MY_WORKSPACE.V_CUSTOMER from temp_user;

Revoke succeeded.

SQL> conn temp_user/welcome1
Connected.

SQL> select * from MY_WORKSPACE.V_CUSTOMER;
select * from MY_WORKSPACE.V_CUSTOMER
                           *
ERROR at line 1:
ORA-00942: table or view does not exist

Regards

Using XMING to install software

If VNC Server not configured or any issue with Linux display/desktop then use below 
method to install any software from locally using putty.

1) Download Xming , there might be other product but I am using it.

2) Start Xming and it will show you in system tray, just let it run in background.

3) start putty and make connection to server . IMPORTANT Settings now

In putty SSH -->> X11 -->> Enable X11 Forwarding -->> X Display location put it as localhost:0

4) Execute installer and it launcher should come in local machine using XMING

Regards

Apex Upgrade - Upgrade Apex from 5.1 to 19.1

Apex Upgrade - Upgrade Apex from 5.1 to 19.1

Upgrade Apex 5.1 to 19.1

Take full database backup before doing apex upgrade

1) Download latest Apex 19.1 in our case we are upgrading apex

2) Unzip it to the location where you wanted to keep it.

3) mv old apex directory as /u01/app/apex to /u01/app/apex_old

4) start upgrade process

cd /u01/app/apex

SQL> @apexins APEX APEX TEMP /i/

output something like below

. Application Express (APEX) Installation.
..........................................
.
...set_appun.sql
... Checking prerequisites (MANUAL)
.
. SYSDBA Privilege
.   pass - Connection with SYSDBA privilege.
. Database rolling upgrade
.   pass - No rolling upgrade.
. DB components
.   pass - APEX: version=5.1.4.00.08
.   pass - CATPROC: version=12.1.0.2.0
.   pass - XDB: version=12.1.0.2.0
. XDB
.   pass - is valid
. PL/SQL Web Toolkit
.   pass - version 10.1.2.1.9
. Tablespaces
.   pass - found APEX
.   pass - found APEX
.   pass - found TEMP
. PHASES (1,2,3)...

PL/SQL procedure successfully completed.




# Creating APEX_INSTANCE_ADMIN_USER
#
APEX_INSTANCE_ADMIN_USER already exists - do nothing.
#
# Actions in Phase 1:
#
    ok 1 - BEGIN                                                        |   0.00
    ok 2 - Creating FLOWS_FILES                                         |   0.00
    ok 3 - Creating APEX_PUBLIC_USER                                    |   0.00
    ok 4 - Creating APEX_GRANTS_FOR_NEW_USERS_ROLE                      |   0.02
    ok 5 - Creating SYS Objects                                         |   0.03
    ok 6 - Creating APEX Tables                                         |   0.37
    ok 7 - Installing Package Specs (Runtime)                           |   1.52
    ok 8 - Installing Package Bodies (Runtime)                          |   0.38
    ok 9 - Dev Grants                                                   |   0.05
    ok 10 - Installing Package Specs (Dev)                              |   0.27
    ok 11 - Installing Views (Dev)                                      |   0.05
    ok 12 - Installing Package Bodies (Dev)                             |   0.33
    ok 13 - Recompiling APEX_190100                                     |   1.57
    ok 14 - Creating APEX$ objects in APEX_190100                       |   0.10
    ok 15 - Creating Spatial Support                                    |   0.03
    ok 16 - Creating Instance Parameter Defaults                        |   0.00
    ok 17 - Inherit Privileges                                          |   0.02
    ok 18 - Creating APEX_INSTANCE_ADMIN_USER                           |   0.00
ok 1 - 18 actions passed, 0 actions failed                              |   4.73
timing for: Creating APEX_INSTANCE_ADMIN_USER
Elapsed: 00:00:00.24
timing for: Phase 1 (Installation)
Elapsed: 00:04:52.05
Phase 2 (Upgrade)

timing for: Enabling Phase 2
Elapsed: 00:00:00.03
#
# Upgrade Metadata (1)
#
...reset_state_and_show_invalid.sql

timing for: Upgrade Metadata (1)
Elapsed: 00:01:20.06
#
# Upgrade Metadata (2)
#
   -- Upgrading new schema. -------

timing for: Upgrade Metadata (2)
Elapsed: 00:00:00.46


Thank you for installing Oracle Application Express 19.1.0.00.15

Oracle Application Express is installed in the APEX_190100 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex/apex_admin     (Oracle REST Data Services)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex     (Oracle REST Data Services)

timing for: Phase 3 (Switch)
Elapsed: 00:01:19.64
timing for: Complete Installation
Elapsed: 00:12:31.76

PL/SQL procedure successfully completed.




SQL> @apex_epg_config.sql /u01/app/

PL/SQL procedure successfully completed.

timing for: Load Images
Elapsed: 00:04:00.66

PL/SQL procedure successfully completed.


Commit complete.


Launch apex url if you see any issues before digging more into issue clear cache from browser.

RMAN Scripts

Take a RMAN Full backup :

rman target /

sql 'alter system archive log current';

sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";

RUN

{

configure controlfile autobackup on;

ALLOCATE CHANNEL c1 DEVICE TYPE disk;

ALLOCATE CHANNEL c2 DEVICE TYPE disk;

ALLOCATE CHANNEL c3 DEVICE TYPE disk;

ALLOCATE CHANNEL c4 DEVICE TYPE disk;

ALLOCATE CHANNEL c5 DEVICE TYPE disk;

ALLOCATE CHANNEL c6 DEVICE TYPE disk;

backup AS COMPRESSED BACKUPSET full database tag FULL_DB_BKP format '/u04/backup/%d_%T_%s_%p_FULL' ;

sql 'alter system archive log current';

backup tag DB_ARCH format '/u04/backup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;

backup tag DB_CTL current controlfile format '/u04/backup/%d_%T_%s_%p_CONTROL';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

release channel c5;

release channel c6;

}

Another Script we might use depending on requirement :

run {

allocate channel t1 TYPE DISK FORMAT '/backup/RMAN//%d_%t_%p_%s_%c_%u.bkp';

allocate channel t2 TYPE DISK FORMAT '/backup/RMAN/%d_%t_%p_%s_%c_%u.bkp';

allocate channel t3 TYPE DISK FORMAT '/backup/RMAN/%d_%t_%p_%s_%c_%u.bkp';

crosscheck archivelog all;

backup current controlfile FORMAT '/backup/RMAN/%d_%t_%p_%s_%c_%u.bkp';

delete NOPROMPT archivelog until time 'SYSDATE-1' ;

sql 'alter system archive log current';

DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘sysdate-1’;

backup archivelog all delete input

backup archivelog from time 'SYSDATE-2';

crosscheck backup;

crosscheck archivelog all;

delete NOPROMPT archivelog until time 'SYSDATE-2' ;

delete noprompt obsolete device type disk;

release channel t1;

release channel t2;

release channel t3;

}

Take a RMAN cold backup :

shutdown immediate ;

startup mount ;    - Only one instance if RAC

rman 

connect target /

Rman> Spool log to '/u04/backup/DB_COLD_BKP.log'

Rman > run { allocate channel c1 device type disk ;

             allocate channel c2 device type disk ;

             allocate channel c3 device type disk ;

             allocate channel c4 device type disk ;

             backup database tag='COLD_BKP' format '/u04/backup/DB_COLD_s%s_p%p-t%T.bck';

             backup tag='COLD_BKP' format '/u04/backup/%d_cfile_s%s_p%p_open.bck' current controlfile;

             backup spfile format '/u04/backup/%d_spfile_s%s_p%p_%T_dbid%I.rman';

             sql 'alter database backup controlfile to TRACE';

             release channel c1;  

             release channel c2;

             release channel c3;

             release channel c4;

             }

Take a RMAN Level 0 backup :

connect target /

connect catalog rcat/********@rcat

run {

allocate channel d1 type disk;

allocate channel d2 type disk;

allocate channel d3 type disk;

allocate channel d4 type disk;

backup filesperset 10 INCREMENTAL LEVEL 0 CUMULATIVE database tag

'LEVEL_0' format

'/u04/backup/rman/PROD_dbf-level-0_s%s_p%p-t%T.bck'

plus archivelog tag 'LEVEL_0' format

'/u04/backup/rman/PROD_arch-level-0_s%s_p%p-t%T.bck';

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

To see backup status :

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%'

  AND OPNAME NOT LIKE '%aggregate%'

  AND TOTALWORK != 0

  AND SOFAR  != TOTALWORK

;

Install Apex 5.1

Install Apex 5.1

1 Download and Unzip the APEX 5.1.4 software

unzip apex_5.1.4.zip -d /u01/app/

2 Create a new tablespace for APEX

CREATE TABLESPACE APEX DATAFILE '/u01/app/12c/database/oradata/prim/apex01.dbf' SIZE 500M;

3 Check if APEX already installed there:

SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

no rows selected

4 Instal  APEX 5.1.4

cd /u01/app/apex

SQL> @apexins APEX APEX TEMP /i/


SQL> select username,created from all_users where USERNAME like '%APEX%';

USERNAME
--------------------------------------------------------------------------------
CREATED
---------
APEX_PUBLIC_USER
29-OCT-15

APEX_050100
29-OCT-15

APEX_INSTANCE_ADMIN_USER
29-OCT-15


5 Check Installed product information

SQL> SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
Oracle Application Express
5.1.4.00.08                    VALID


SQL> select * from apex_release;

VERSION_NO
--------------------------------------------------------------------------------
API_COMPATIBILITY
--------------------------------------------------------------------------------
PATCH_APPLIED
--------------------------------------------------------------------------------
5.1.4.00.08
2016.08.24
APPLIED


Configure APEX:


SQL> @apex_epg_config.sql /u01/app

PL/SQL procedure successfully completed.


6 Unlock below APEX related user account

ALTER USER anonymous ACCOUNT UNLOCK;
ALTER USER xdb ACCOUNT UNLOCK;
ALTER USER apex_public_user ACCOUNT UNLOCK;
ALTER USER flows_files ACCOUNT UNLOCK;

7 Increase shared server and job_queue_processes

SQL> SHOW PARAMETER job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000
SQL> SHOW PARAMETER shared_servers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers                   integer
shared_servers                       integer     5

8 Execute apxconf.sql to complete final configuration

SQL> @apxconf.sql

      PORT
----------
      8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.


================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN]
User "ADMIN" does not yet exist and will be created.
Enter ADMIN's email [ADMIN]
Enter ADMIN's password []
Created instance administrator ADMIN.

Enter a port for the XDB HTTP listener [      8080]
...changing HTTP Port
SQL>  select dbms_xdb.gethttpport from dual;

GETHTTPPORT
-----------
       8080


9 Check if APEX services registered with listener or not

[oracle@stand apex]$  lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 29-OCT-2019 12:59:35

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_TEST
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                29-OCT-2019 10:53:33
Uptime                    0 days 2 hr. 6 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12c/database/dbhome_2/network/admin/listener.ora
Listener Log File         /u01/app/12c/database/diag/tnslsnr/stand/listener_test/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(QUEUESIZE=50))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stand.som.com)(PORT=1521))(QUEUESIZE=50))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stand.som.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=stand.som.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/12c/database/dbhome_2/admin/prim/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "prim.som.com" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
Service "primXDB.som.com" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully


Login to below  Apex url :

http://stand.som.com:8080/apex/apex_admin

Make sure you have hosts entry and firewall off to access above url


Thanks,

Create a disk if new space added in linux

Suppose new space has been added and mount to mount it in linux , I followed below method:


[root@localhost ~]# fdisk -l

Disk /dev/sda: 53.7 GB, 53687091200 bytes, 104857600 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x0002e0b7

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     2099199     1048576   83  Linux
/dev/sda2         2099200   104857599    51379200   8e  Linux LVM

Disk /dev/mapper/ol-root: 50.5 GB, 50457477120 bytes, 98549760 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/mapper/ol-swap: 2147 MB, 2147483648 bytes, 4194304 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdb: 42.9 GB, 42949672960 bytes, 83886080 sectors    >>> Added this and showing here
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes



[root@localhost ~]# fdisk /dev/sdb
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0xb5cc826b.

Command (m for help):
Command (m for help): m
Command action
   a   toggle a bootable flag
   b   edit bsd disklabel
   c   toggle the dos compatibility flag
   d   delete a partition
   g   create a new empty GPT partition table
   G   create an IRIX (SGI) partition table
   l   list known partition types
   m   print this menu
   n   add a new partition
   o   create a new empty DOS partition table
   p   print the partition table
   q   quit without saving changes
   s   create a new empty Sun disklabel
   t   change a partition's system id
   u   change display/entry units
   v   verify the partition table
   w   write table to disk and exit
   x   extra functionality (experts only)

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
Partition number (1-4, default 1):
First sector (2048-83886079, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-83886079, default 83886079):
Using default value 83886079
Partition 1 of type Linux and of size 40 GiB is set

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@localhost ~]# fdisk -l

Disk /dev/sda: 53.7 GB, 53687091200 bytes, 104857600 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x0002e0b7

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     2099199     1048576   83  Linux
/dev/sda2         2099200   104857599    51379200   8e  Linux LVM

Disk /dev/mapper/ol-root: 50.5 GB, 50457477120 bytes, 98549760 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/mapper/ol-swap: 2147 MB, 2147483648 bytes, 4194304 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdb: 42.9 GB, 42949672960 bytes, 83886080 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0xb5cc826b

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1            2048    83886079    41942016   83  Linux             >>> Now showing as device

[root@localhost ~]# /sbin/mkfs -t ext3 /dev/sdb1             >> Format this
mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
2621440 inodes, 10485504 blocks
524275 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
320 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000, 7962624

Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done



[root@localhost ~]# /sbin/e2label /dev/sdb1 /u01
[root@localhost ~]# /sbin/tune2fs -l /dev/sdb1 |grep volume
Filesystem volume name:   /u01
[root@localhost ~]# vi /etc/fstab
[root@localhost ~]# cat /etc/fstab

#
# /etc/fstab
# Created by anaconda on Fri Sep  6 20:35:06 2019
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/ol-root     /                       xfs     defaults        0 0
UUID=66fcdbe7-5bd5-45de-b216-3edc20a3c33a /boot                   xfs     defaults        0 0
/dev/mapper/ol-swap     swap                    swap    defaults        0 0
LABEL=/u01          /u01               ext3    defaults        1 2


[root@localhost ~]# mount /dev/sdb1 /u01
[root@localhost ~]# df -h
Filesystem           Size  Used Avail Use% Mounted on
devtmpfs             833M     0  833M   0% /dev
tmpfs                852M     0  852M   0% /dev/shm
tmpfs                852M  9.8M  842M   2% /run
tmpfs                852M     0  852M   0% /sys/fs/cgroup
/dev/mapper/ol-root   47G   11G   37G  23% /
/dev/sda1           1014M  333M  682M  33% /boot
tmpfs                171M  4.0K  171M   1% /run/user/42
tmpfs                171M   28K  171M   1% /run/user/0
/dev/sr0             4.3G  4.3G     0 100% /run/media/root/OL-7.6 Server.x86_64
/dev/sdb1             40G   49M   38G   1% /u01



Thanks,

Database Refresh - ( Clone ) - RMAN - Duplicate database to ...

Database Refresh - ( Clone ) - RMAN - Duplicate database to ...

[oracle@stand ~]$ cat test.env
export ORACLE_SID=test
export ORACLE_HOME=/u01/app/12c/database/dbhome_2
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$ORACLE_HOME/bin:/opt/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH


[oracle@stand dbs]$ cat initTEST_20OCT.ora
test.__data_transfer_cache_size=0
*.__db_cache_size=184M
test.__db_cache_size=289406976
test.__java_pool_size=4194304
test.__large_pool_size=8388608
*.__oracle_base='/u01/app/12c/database'# ORACLE_BASE set from environment
test.__pga_aggregate_target=293601280
test.__sga_target=486539264
test.__shared_io_pool_size=12582912
*.__shared_pool_size=128M
test.__shared_pool_size=159383552
test.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='/u01/app/12c/database/admin/prim/adump'
*.audit_trail='DB'
*.compatible='12.1.0.2.0'
*.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))','((TYPE=EMON)(BROKERS=1))'# connection_brokers default value
*.control_files='/u01/app/12c/database/oradata/prim/control01.ctl','/u01/app/12c/database/fast_recovery_area/prim/control02.ctl'#Restore Controlfile
*.core_dump_dest='/u01/app/12c/database/diag/rdbms/prim/test/cdump'
*.db_block_size=8192
*.db_domain='som.com'
*.db_file_name_convert='/u01/app/12c/database/oradata/stand','/u01/app/12c/database/oradata/prim'
*.db_name='TEST'#Reset to original value by RMAN
*.db_recovery_file_dest='/u01/app/12c/database/fast_recovery_area'
*.db_recovery_file_dest_size=4560M
*.db_unique_name='prim'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/12c/database'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.fal_client='PRIM'
*.fal_server='STAND'
*.log_archive_config='dg_config=(prim,stand)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=prim'
*.log_archive_dest_2='service="stand"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="stand" net_timeout=30','valid_for=(online_logfile,all_roles)'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
*.log_buffer=5068K# log buffer update
*.log_file_name_convert='/u01/app/12c/database/oradata/stand','/u01/app/12c/database/oradata/prim'
*.memory_target=744M
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=300
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=1920K
*.skip_unusable_indexes=TRUE
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'



Once backup copied from Source , NOMOUNT USING above pfile and then use below method

[oracle@stand 2016_10_18]$ rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Oct 20 18:18:27 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: TEST (not mounted)

RMAN> DUPLICATE DATABASE TO "TEST"  BACKUP LOCATION '/u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18' NOFILENAMECHECK;

Starting Duplicate Db at 20-OCT-16

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     780140544 bytes

Fixed Size                     2929256 bytes
Variable Size                587206040 bytes
Database Buffers             184549376 bytes
Redo Buffers                   5455872 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PRIM'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_ncsnf_TAG20161018T164053_gtm7kbb6_.bkp';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PRIM'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     780140544 bytes

Fixed Size                     2929256 bytes
Variable Size                583011736 bytes
Database Buffers             188743680 bytes
Redo Buffers                   5455872 bytes

Starting restore at 20-OCT-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/12c/database/oradata/prim/control01.ctl
output file name=/u01/app/12c/database/fast_recovery_area/prim/control02.ctl
Finished restore at 20-OCT-16

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 device type=DISK

contents of Memory Script:
{
   set until scn  2189123;
   set newname for datafile  1 to
 "/u01/app/12c/database/oradata/prim/system01.dbf";
   set newname for datafile  3 to
 "/u01/app/12c/database/oradata/prim/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/12c/database/oradata/prim/undotbs01.dbf";
   set newname for datafile  6 to
 "/u01/app/12c/database/oradata/prim/users01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 20-OCT-16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/12c/database/oradata/prim/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/12c/database/oradata/prim/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/12c/database/oradata/prim/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/12c/database/oradata/prim/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_nnndf_TAG20161018T164053_gtm7fy6w_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_nnndf_TAG20161018T164053_gtm7fy6w_.bkp tag=TAG20161018T164053
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 20-OCT-16

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1022178438 file name=/u01/app/12c/database/oradata/prim/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1022178438 file name=/u01/app/12c/database/oradata/prim/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1022178438 file name=/u01/app/12c/database/oradata/prim/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=1022178438 file name=/u01/app/12c/database/oradata/prim/users01.dbf

contents of Memory Script:
{
   set until scn  2189123;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 20-OCT-16
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_annnn_TAG20161018T164244_gtm7kd7d_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_annnn_TAG20161018T164244_gtm7kd7d_.bkp tag=TAG20161018T164244
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/12c/database/fast_recovery_area/PRIM/archivelog/2016_10_20/o1_mf_1_4_gtrpfk20_.arc thread=1 sequence=4
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/12c/database/fast_recovery_area/PRIM/archivelog/2016_10_20/o1_mf_1_4_gtrpfk20_.arc RECID=1 STAMP=1022178441
media recovery complete, elapsed time: 00:00:00
Finished recover at 20-OCT-16
Oracle instance started

Total System Global Area     780140544 bytes

Fixed Size                     2929256 bytes
Variable Size                583011736 bytes
Database Buffers             188743680 bytes
Redo Buffers                   5455872 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TEST'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
}
executing Memory Script

sql statement: alter system set  db_name =  ''TEST'' comment= ''Reset to original value by RMAN'' scope=spfile
Oracle instance started

Total System Global Area     780140544 bytes

Fixed Size                     2929256 bytes
Variable Size                583011736 bytes
Database Buffers             188743680 bytes
Redo Buffers                   5455872 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/u01/app/12c/database/oradata/prim/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/u01/app/12c/database/oradata/prim/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/u01/app/12c/database/oradata/prim/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/12c/database/oradata/prim/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/12c/database/oradata/prim/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/12c/database/oradata/prim/sysaux01.dbf",
 "/u01/app/12c/database/oradata/prim/undotbs01.dbf",
 "/u01/app/12c/database/oradata/prim/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/12c/database/oradata/prim/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/12c/database/oradata/prim/sysaux01.dbf RECID=1 STAMP=1022178465
cataloged datafile copy
datafile copy file name=/u01/app/12c/database/oradata/prim/undotbs01.dbf RECID=2 STAMP=1022178465
cataloged datafile copy
datafile copy file name=/u01/app/12c/database/oradata/prim/users01.dbf RECID=3 STAMP=1022178465

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=1022178465 file name=/u01/app/12c/database/oradata/prim/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1022178465 file name=/u01/app/12c/database/oradata/prim/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=3 STAMP=1022178465 file name=/u01/app/12c/database/oradata/prim/users01.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database force logging

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Cannot remove created server parameter file
Finished Duplicate Db at 20-OCT-16

RMAN>

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

TEST      READ WRITE

BR,

Drop a database

Please follow below steps to drop a database :

SQL>  shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive restrict;
ORACLE instance started.

Total System Global Area  780140544 bytes
Fixed Size                  2929256 bytes
Variable Size             578817432 bytes
Database Buffers          192937984 bytes
Redo Buffers                5455872 bytes
Database mounted.


SQL> drop database;

Database dropped.

Using RMAN you can also drop as below:


RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is "TEST" and DBID is 19077478

Do you really want to drop all backups and the database (enter YES or NO)? yes

There are other syntax with drop database , you can try those also.

Clone of TEST from Source using RMAN Backup

Once Oracle Home Copied from Source then clone it using below procedure:
=========================================================================

[oracle@stand dbhome_2]$ export PATH=/u01/app/12c/database/dbhome_2/perl/bin/perl:$PATH
[oracle@stand dbhome_2]$ export ORACLE_HOME=/u01/app/12c/database/dbhome_2
[oracle@stand dbhome_2]$ /usr/bin/perl $ORACLE_HOME/clone/bin/clone.pl '-O"LOCAL_NODE=stand"' ORACLE_BASE=/u01/app/12c/database ORACLE_HOME=/u01/app/12c/database/dbhome_2 ORACLE_HOME_NAME=dbhome_2 '-O-noConfig'
./runInstaller -clone -waitForCompletion  "LOCAL_NODE=stand" "ORACLE_BASE=/u01/app/12c/database" "ORACLE_HOME=/u01/app/12c/database/dbhome_2" "ORACLE_HOME_NAME=dbhome_2" -noConfig -silent -paramFile /u01/app/12c/database/dbhome_2/clone/clone_oraparam.ini
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 26774 MB    Passed
Checking swap space: must be greater than 500 MB.   Actual 3995 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-10-20_01-07-07PM. Please wait ...You can find the log of this install session at:
 /u01/app/oraInventory/logs/cloneActions2016-10-20_01-07-07PM.log
..................................................   5% Done.
..................................................   10% Done.
..................................................   15% Done.
..................................................   20% Done.
..................................................   25% Done.
..................................................   30% Done.
..................................................   35% Done.
..................................................   40% Done.
..................................................   45% Done.
..................................................   50% Done.
..................................................   55% Done.
..................................................   60% Done.
..................................................   65% Done.
..................................................   70% Done.
..................................................   75% Done.
..................................................   80% Done.
..................................................   85% Done.
..........
Copy files in progress.

Copy files successful.

Link binaries in progress.

Link binaries successful.

Setup files in progress.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup successful.
The cloning of dbhome_2 was successful.
Please check '/u01/app/oraInventory/logs/cloneActions2016-10-20_01-07-07PM.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
..................................................   95% Done.

As a root user, execute the following script(s):
        1. /u01/app/12c/database/dbhome_2/root.sh



..................................................   100% Done.


Change the Environment file:
=============================
[oracle@stand ~]$ vi test.env
[oracle@stand ~]$ cat test.env
export ORACLE_SID=prim    >>>> Kept as Source database
export ORACLE_HOME=/u01/app/12c/database/dbhome_2
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$ORACLE_HOME/bin:/opt/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

[oracle@stand ~]$ . ./test.env
[oracle@stand ~]$ cd $ORACLE_HOME/dbs
[oracle@stand dbs]$ ls -ltr
total 80
-rw-r----- 1 oracle oinstall  4608 Oct 18 16:57 spfilestand.ora
-rw-r----- 1 oracle oinstall  7680 Oct 18 16:57 orapwstand_bad
-rw-r----- 1 oracle oinstall  7680 Oct 18 16:57 orapwstand
-rw-r----- 1 oracle oinstall    24 Oct 18 16:57 lkSTAND
-rw-r----- 1 oracle oinstall    24 Oct 18 16:57 lkPRIM
-rw-r----- 1 oracle oinstall     0 Oct 18 16:57 lkinststand
-rw-r--r-- 1 oracle oinstall    16 Oct 18 16:57 initstand.ora
-rw-r--r-- 1 oracle oinstall  2992 Oct 18 16:57 init.ora
-rw-r----- 1 oracle oinstall  1544 Oct 18 16:57 hc_stand.dat
-rw-r----- 1 oracle oinstall 12288 Oct 18 16:57 dr2stand.dat
-rw-r----- 1 oracle oinstall 12288 Oct 18 16:57 dr1stand.dat
-rw-r--r-- 1 oracle oinstall  1852 Oct 20 13:16 initPRIM_20OCT  >>> Copied from Source
-rw-r----- 1 oracle oinstall  7680 Oct 20 13:16 orapwprim       >>> Copied from Source

Nomount the Target:
===================

[oracle@stand dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 20 13:17:27 2016

Copyright (c) 1682, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='initPRIM_20OCT';
ORACLE instance started.

Total System Global Area  780140544 bytes
Fixed Size                  2929256 bytes
Variable Size             578817432 bytes
Database Buffers          162937984 bytes
Redo Buffers                5455872 bytes
SQL> exit

RMAN backup pieces copied from Source to below target location:
===============================================================

[oracle@stand dbs]$ cd /u01/app/12c/database/fast_recovery_area/PRIM/backupset/
[oracle@stand backupset]$ cd 2016_10_18/
[oracle@stand 2016_10_18]$ ls -ltr
total 1492464
-rw-r----- 1 oracle oinstall     212480 Oct 20 12:45 o1_mf_annnn_TAG20161018T164244_gtm7kd7d_.bkp
-rw-r----- 1 oracle oinstall 1273954304 Oct 20 12:49 o1_mf_nnndf_TAG20161018T164053_gtm7fy6w_.bkp
-rw-r----- 1 oracle oinstall   16028992 Oct 20 12:49 o1_mf_annnn_TAG20161018T163837_gtm7fook_.bkp
-rw-r----- 1 oracle oinstall   25372672 Oct 20 12:49 o1_mf_annnn_TAG20161018T163837_gtm7flh5_.bkp
-rw-r----- 1 oracle oinstall   73538560 Oct 20 12:49 o1_mf_annnn_TAG20161018T163837_gtm7ds25_.bkp
-rw-r----- 1 oracle oinstall   11042816 Oct 20 12:49 o1_mf_ncsnf_TAG20161018T164053_gtm7kbb6_.bkp
-rw-r----- 1 oracle oinstall  123593728 Oct 20 12:49 o1_mf_annnn_TAG20161018T163837_gtm79s7s_.bkp


Restore control file from backup and mount the TARGET also catalog backup pieces:
=================================================================================

[oracle@stand oradata]$ mkdir -p /u01/app/12c/database/oradata/prim
[oracle@stand oradata]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Oct 20 13:21:22 2016

Copyright (c) 1682, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRIM (not mounted)

RMAN> restore controlfile from '/u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_ncsnf_TAG20161018T164053_gtm7kbb6_.bkp';

Starting restore at 20-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/12c/database/oradata/prim/control01.ctl
output file name=/u01/app/12c/database/fast_recovery_area/prim/control02.ctl
Finished restore at 20-OCT-16

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1
RMAN> catalog backuppiece '/u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_annnn_TAG20161018T164244_gtm7kd7d_.bkp';

using target database control file instead of recovery catalog
cataloged backup piece
backup piece handle=/u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_annnn_TAG20161018T164244_gtm7kd7d_.bkp RECID=8 STAMP=1022161606

RMAN> catalog backuppiece '/u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_nnndf_TAG20161018T164053_gtm7fy6w_.bkp';

cataloged backup piece
backup piece handle=/u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_nnndf_TAG20161018T164053_gtm7fy6w_.bkp RECID=9 STAMP=1022161633

RMAN> catalog backuppiece '/u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_annnn_TAG20161018T163837_gtm7fook_.bkp';

cataloged backup piece
backup piece handle=/u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_annnn_TAG20161018T163837_gtm7fook_.bkp RECID=10 STAMP=1022161645

....
.........
Catalog all the pieces.


[oracle@stand prim]$ mkdir -p /u01/app/12c/database/datafiles/prim
[oracle@stand prim]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Oct 20 13:53:36 2016

Copyright (c) 1682, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRIM (DBID=16077478, not open)

RMAN> run
{
set newname for datafile 1 to '/u01/app/12c/database/datafiles/prim/system01.dbf';
set newname for datafile 3 to '/u01/app/12c/database/datafiles/prim/sysaux01.dbf';
set newname for datafile 6 to '/u01/app/12c/database/datafiles/prim/users01.dbf';
set newname for datafile 4 to '/u01/app/12c/database/datafiles/prim/undotbs01.dbf';
restore database;
switch datafile all;
}2> 3> 4> 5> 6> 7> 8> 9>

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 20-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/12c/database/datafiles/prim/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/12c/database/datafiles/prim/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/12c/database/datafiles/prim/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/12c/database/datafiles/prim/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_nnndf_TAG20161018T164053_gtm7fy6w_.bkp
channel ORA_DISK_1: piece handle=/u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_nnndf_TAG20161018T164053_gtm7fy6w_.bkp tag=TAG20161018T164053
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:26
Finished restore at 20-OCT-16

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1022162108 file name=/u01/app/12c/database/datafiles/prim/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1022162108 file name=/u01/app/12c/database/datafiles/prim/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1022162108 file name=/u01/app/12c/database/datafiles/prim/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1022162108 file name=/u01/app/12c/database/datafiles/prim/users01.dbf

RMAN> recover database;

Starting recover at 20-OCT-16
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_annnn_TAG20161018T164244_gtm7kd7d_.bkp
channel ORA_DISK_1: piece handle=/u01/app/12c/database/fast_recovery_area/PRIM/backupset/2016_10_18/o1_mf_annnn_TAG20161018T164244_gtm7kd7d_.bkp tag=TAG20161018T164244
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/12c/database/fast_recovery_area/PRIM/archivelog/2016_10_20/o1_mf_1_4_gtr6ood5_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:26
Finished recover at 20-OCT-16

Open reset logs:
================
SQL> alter database open resetlogs;

Database altered.


Check redolog/temp files and recreate those:
==========================================

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/12c/database/oradata/prim/redo03.log
/u01/app/12c/database/oradata/prim/redo02.log
/u01/app/12c/database/oradata/prim/redo01.log
/u01/app/12c/database/oradata/prim/redo04.log
/u01/app/12c/database/oradata/prim/redo05.log
/u01/app/12c/database/oradata/prim/redo06.log

6 rows selected.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/12c/database/oradata/prim/temp01.dbf




SQL> create temporary tablespace temp1 tempfile '/u01/app/12c/database/oradata/prim/temp.dbf' size 50m;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL>  drop tablespace temp including contents and datafiles;

Tablespace dropped.

SQL> create temporary tablespace temp tempfile '/u01/app/12c/database/datafiles/prim/temp.dbf' size 50m;

Tablespace created.

SQL>  alter database default temporary tablespace temp;

Database altered.

SQL>  drop tablespace temp1 including contents and datafiles;

Tablespace dropped.

SQL>  select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/12c/database/datafiles/prim/temp.dbf



SQL> alter database drop logfile '/u01/app/12c/database/oradata/prim/redo01.log';

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 '/u01/app/12c/database/datafiles/prim/redo01.log' size 50m;

Database altered.



SQL> select GROUP#,TYPE,MEMBER from v$logfile;

    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------
         3 ONLINE  /u01/app/12c/database/datafiles/prim/redo03.log
         2 ONLINE  /u01/app/12c/database/datafiles/prim/redo02.log
         1 ONLINE  /u01/app/12c/database/datafiles/prim/redo01.log


Now change the name using NID:
==============================

SQL> select name from v$database;

NAME
---------
PRIM


SQL> create spfile from pfile='initTEST.ora';

File created.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount
ORACLE instance started.

Total System Global Area  780140544 bytes
Fixed Size                  2929256 bytes
Variable Size             578817432 bytes
Database Buffers          162937984 bytes
Redo Buffers                5455872 bytes
Database mounted.
SQL> exit


[oracle@stand admin]$ nid target=sys/oracle@PRIM DBNAME=TEST

DBNEWID: Release 12.1.0.2.0 - Production on Sun Oct 20 14:28:54 2016

Copyright (c) 1682, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to database PRIM (DBID=16077478)

Connected to server version 12.1.0

Control Files in database:
    /u01/app/12c/database/oradata/prim/control01.ctl
    /u01/app/12c/database/fast_recovery_area/prim/control02.ctl

Change database ID and database name PRIM to TEST? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 16077478 to 2320251431
Changing database name from PRIM to TEST
    Control File /u01/app/12c/database/oradata/prim/control01.ctl - modified
    Control File /u01/app/12c/database/fast_recovery_area/prim/control02.ctl - modified
    Datafile /u01/app/12c/database/datafiles/prim/system01.db - dbid changed, wrote new name
    Datafile /u01/app/12c/database/datafiles/prim/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/12c/database/datafiles/prim/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/12c/database/datafiles/prim/users01.db - dbid changed, wrote new name
    Datafile /u01/app/12c/database/datafiles/prim/temp.db - dbid changed, wrote new name
    Control File /u01/app/12c/database/oradata/prim/control01.ctl - dbid changed, wrote new name
    Control File /u01/app/12c/database/fast_recovery_area/prim/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 2320251431.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

[oracle@stand admin]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 20 14:29:56 2016

Copyright (c) 1682, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> exit
Disconnected
[oracle@stand admin]$ cd $ORACLE_HOME
[oracle@stand dbhome_2]$ cd dbs
[oracle@stand dbs]$ ls -ltr
total 96
-rw-r----- 1 oracle oinstall  4608 Oct 18 16:57 spfilestand.ora
-rw-r----- 1 oracle oinstall  7680 Oct 18 16:57 orapwstand_bad
-rw-r----- 1 oracle oinstall  7680 Oct 18 16:57 orapwstand
-rw-r----- 1 oracle oinstall    24 Oct 18 16:57 lkSTAND
-rw-r----- 1 oracle oinstall    24 Oct 18 16:57 lkPRIM
-rw-r----- 1 oracle oinstall     0 Oct 18 16:57 lkinststand
-rw-r--r-- 1 oracle oinstall    16 Oct 18 16:57 initstand.ora
-rw-r--r-- 1 oracle oinstall  2992 Oct 18 16:57 init.ora
-rw-r----- 1 oracle oinstall  1544 Oct 18 16:57 hc_stand.dat
-rw-r----- 1 oracle oinstall 12288 Oct 18 16:57 dr2stand.dat
-rw-r----- 1 oracle oinstall 12288 Oct 18 16:57 dr1stand.dat
-rw-r--r-- 1 oracle oinstall  1852 Oct 20 13:16 initPRIM_20OCT
-rw-r----- 1 oracle oinstall  7680 Oct 20 13:16 orapwprim
-rw-r--r-- 1 oracle oinstall  1852 Oct 20 14:16 initTEST.ora
-rw-r----- 1 oracle oinstall  4608 Oct 20 14:23 spfileprim.ora
-rw-rw---- 1 oracle oinstall  1544 Oct 20 14:29 hc_prim.dat
[oracle@stand dbs]$ vi initTEST.ora
[oracle@stand dbs]$ cd
[oracle@stand ~]$ vi test.env   >>> modify instance_name to test
[oracle@stand ~]$ . ./test.env


[oracle@stand ~]$ cd $ORACLE_HOME/dbs
[oracle@stand dbs]$ cp initTEST.ora inittest.ora  >>> Here modify db name to test

[oracle@stand dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 20 14:37:32 2016

Copyright (c) 1682, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  780140544 bytes
Fixed Size                  2929256 bytes
Variable Size             578817432 bytes
Database Buffers          162937984 bytes
Redo Buffers                5455872 bytes

SQL> alter database mount;

Database altered.

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      test
SQL> alter database open resetlogs;

Database altered.

SQL> create spfile from pfile='inittest.ora'
  2  ;

File created.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  780140544 bytes
Fixed Size                  2929256 bytes
Variable Size             578817432 bytes
Database Buffers          162937984 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      test
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      READ WRITE


Thanks

FAILOVER in dataguard

SQL> Select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PRIM      MOUNTED              PHYSICAL STANDBY

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database finish;

Database altered.

SQL> alter database activate standby database;

Database altered.

SQL> Select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PRIM      MOUNTED              PRIMARY

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  780140544 bytes
Fixed Size                  2929256 bytes
Variable Size             557845912 bytes
Database Buffers          213909504 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PRIM      READ WRITE           PRIMARY

You might need to recreate standby from current primary.

SWTICHOVER from PRIMARY to STANDBY

ON PRIMARY:


SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PRIM      READ WRITE           PRIMARY

SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

 SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED
---------- -------------------- -------------------- ---------
         8 15-OCT-2019 17:37:49 15-OCT-2019 19:33:06 NO
         9 15-OCT-2019 19:33:06 15-OCT-2019 19:33:15 NO
        10 15-OCT-2019 19:33:15 15-OCT-2019 22:01:23 NO
        11 15-OCT-2019 22:01:23 15-OCT-2019 22:02:08 NO
        12 15-OCT-2019 22:02:08 16-OCT-2019 11:19:43 NO
        13 16-OCT-2019 11:19:43 16-OCT-2019 13:17:32 NO
        14 16-OCT-2019 13:17:32 16-OCT-2019 18:44:53 NO
        15 16-OCT-2019 18:44:53 16-OCT-2019 19:20:48 NO
..
...
.....
       106 17-OCT-2019 11:25:41 17-OCT-2019 11:25:42 YES
       107 17-OCT-2019 11:25:42 17-OCT-2019 11:25:44 NO
       107 17-OCT-2019 11:25:42 17-OCT-2019 11:25:44 YES
       108 17-OCT-2019 11:25:44 17-OCT-2019 11:27:16 NO
       108 17-OCT-2019 11:25:44 17-OCT-2019 11:27:16 NO

195 rows selected.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> alter database commit to switchover to standby;

Database altered.

SQL> shutdown immediate;
ORA-01012: not logged on
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@prod ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 17 13:58:03 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  780140544 bytes
Fixed Size                  2929256 bytes
Variable Size             557845912 bytes
Database Buffers          213909504 bytes
Redo Buffers                5455872 bytes
SQL> alter database mount standby database;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PRIM      MOUNTED              PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;

Database altered.



On Standby:

SQL> alter database commit to switchover to primary;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PRIM      MOUNTED              PRIMARY

SQL> alter database open;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PRIM      READ WRITE           PRIMARY

DGMGRL - Broker configuration

Start DGMGRL configuration:

SQL> alter system set dg_broker_start=true sid='*';

System altered.


[oracle@prod ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL>  connect sysdg
Password:
Connected as SYSDG.
DGMGRL> CREATE CONFIGURATION 'prod_dg' AS PRIMARY DATABASE IS 'PRIM' CONNECT IDENTIFIER IS PRIM;
Configuration "prod_dg" created with primary database "PRIM"

[oracle@prod ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sysdg
Password:
Connected as SYSDG.
DGMGRL> show configuration

Configuration - prod_dg

  Protection Mode: MaxPerformance
  Members:
  PRIM - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> add database stand as connect identifier is stand;
Database "stand" added
DGMGRL> show configuration

Configuration - prod_dg

  Protection Mode: MaxPerformance
  Members:
  PRIM  - Primary database
    stand - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration - prod_dg

  Protection Mode: MaxPerformance
  Members:
  PRIM  - Primary database
    stand - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 12 seconds ago)

DGMGRL> exit

Active Datagaurd with Read Only with Apply

Active Datagaurd with Read Only with Apply

Detail	Primary	Standby
DB NAME	PRIM	STAND
IP	192.168.171.10	192.168.171.20
SERVER	prod.som.com	stand.som.com

If your primary not in archivelog please convert it to archivelog:

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 4
SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  369098752 bytes

Fixed Size                  2924544 bytes

Variable Size             289406976 bytes

Database Buffers           71303168 bytes

Redo Buffers                5464064 bytes

Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select FORCE_LOGGING,log_mode from v$database;
FORCE_LOGGING  LOG_MODE
————        ————
YES                               ARCHIVELOG

Adding Redolog for standby database

SQL> alter database add standby logfile group 4 '/u01/app/12c/database/oradata/prim/redo04.log' size 50M;
Database altered.

SQL> alter database add standby logfile group 5 '/u01/app/12c/database/oradata/prim/redo05.log' size 50M;
Database altered.

SQL> alter database add standby logfile group 6 '/u01/app/12c/database/oradata/prim/redo06.log' size 50M;
Database altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

All status will show as UNASSIGNED

TNS ENTRIES on BOTH the NODES:

STAND =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = stand.som.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = stand)

    )

  )

PRIM =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = prod.som.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prim.som.com)

    )

  )

LISTENER ENTRIES

[oracle@prod admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/12c/database/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

PRIM =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = prod.som.com)(PORT = 1521))

    )

  )

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = prim)

(ORACLE_HOME = /u01/app/12c/database/dbhome_1)

(SID_NAME = prim)

)

(SID_DESC =

(GLOBAL_DBNAME = stand)

(ORACLE_HOME = /u01/app/12c/database/dbhome_1)

(SID_NAME = stand)

)

)

[oracle@stand admin]$ cat listener.ora

LISTENER_STAND =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = stand.som.com)(PORT = 1521))

      (QUEUESIZE = 50)

     )

  )

SID_LIST_LISTENER_STAND =

        (SID_LIST =

        (SID_DESC =

         (GLOBAL_DBNAME=prim)

         (SID_NAME = prim)

         (ORACLE_HOME= /u01/app/12c/database/dbhome_1)

        )

        (SID_DESC =

        (SID_NAME = stand)

        (ORACLE_HOME = /u01/app/12c/database/dbhome_1)

        (GLOBAL_DBNAME=stand)

        )

        )

ONCE above done , please check tnsping from both the servers. ie.

tnsping prim

tnsping stand 

above should work on both the direction.

Change below parameters on primary database:

ALTER SYSTEM SET db_unique_name='prim' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_config='dg_config=(prim,stand)' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=prim' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_dest_2='service=stand async valid_for=(online_logfiles,primary_role) db_unique_name=stand' SCOPE=SPFILE;

ALTER SYSTEM SET fal_server='STAND' SCOPE=SPFILE;

ALTER SYSTEM SET fal_client='PRIM' SCOPE=SPFILE;

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE;

ALTER SYSTEM SET db_file_name_convert='/u01/app/12c/database/oradata/stand','/u01/app/12c/database/oradata/prim' SCOPE=SPFILE;

ALTER SYSTEM SET log_file_name_convert='/u01/app/12c/database/oradata/stand','/u01/app/12c/database/oradata/prim' SCOPE=SPFILE;

Password file creation on standby

copy the remote login password file (orapwprim) from the primary database server to the $ORACLE_HOME/dbs directory on the
standby database server, renaming it to orapwstand.

Changing parameters in standby database init file

In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initstand.ora
Containing a single parameter: DB_NAME=stand

[oracle@standby admin]$ cd $ORACLE_HOME/dbs
[oracle@standby dbs]$ cat initstand.ora
db_name=stand

Create directory Structure on standby

cd $ORACLE_BASE/admin/
mkdir stand
cd stand
mkdir adump pfile dpdump
mkdir -p /u01/app/12c/database/oradata/stand

Start the standby database using pfile

. ./stand.env
sqlplus ‘/as sysdba’startup nomount pfile=’initstand.ora’ 

RUN db duplicate:

rman target sys/oracle@prim

connect auxiliary sys/oracle@stand

run

{

allocate channel p1 type disk;

allocate channel p2 type disk;

allocate auxiliary channel s1 type disk;

allocate auxiliary channel s2 type disk;

duplicate target database for standby from active database

spfile

parameter_value_convert 'prime','stand'

set db_unique_name='stand'

set db_file_name_convert='/u01/app/12c/database/oradata/prim','/u01/app/12c/database/oradata/stand'

set log_file_name_convert='/u01/app/12c/database/oradata/prim','/u01/app/12c/database/oradata/stand'

set control_files='/u01/app/12c/database/oradata/stand/standby1.ctl'

set log_archive_max_processes='5'

set fal_client='stand'

set fal_server='prim'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(prim,stand)'

set compatible='12.1.0.2.0'

set memory_target='350m'

nofilenamecheck;

}

On standby database

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

Check log sync:

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PRIM      READ ONLY WITH APPLY