Oracle 11.2.0.4 download link for Windows x86_64




Oracle 11.2.0.4 download link for Windows x86_64:-

https://mega.nz/#F!KcQXCIAY!ZT4lFv6YfNoX8f4WzO7cCw


Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

How to restrict some user getting drop accidentally

Step 1. Lets create an user first.


SQL> create user soumya IDENTIFIED BY soumya;

User created.

SQL> grant connect,resource to soumya;



Grant succeeded.

Step 2. Run the procedure to restrict the user to be dropped.

SQL> Create OR Replace Trigger UserDropRestrict
  2
  3  Before Drop On Database
  4
  5  Declare
  6
  7  Begin
  8
  9       If Ora_Dict_Obj_Name In ('HR','SCOTT','SOUMYA')    Then
 10
 11                       Raise_Application_Error(-20001,'Cannot Drop User'||ora_dict_obj_name||' Your are not allowed to drop this User ! Please contact DBA !');
 12
 13       End If;
 14
 15  End;
 16
 17  /

Trigger created.



Step 3. Now lets try to drop the users mentioned in above trigger.

SQL> drop user soumya cascade;
drop user soumya cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Cannot Drop UserSOUMYA Your are not allowed to drop this User !
Please contact DBA !
ORA-06512: at line 7


So this way we can prevent some important schemas to be dropped mistakenly.

Oracle 10.2.0.4 download link for Windows 64 Bit

Oracle 10.2.0.4 download link for Windows 64Bit


Link :- https://mega.nz/#F!nE4lACaa!M2CDMHSPdKZUFpvouPSRdQ



Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

How to increase size of ASM Disks in Exadata


Overview:- In this scenario, we have free space available in celldisk . we would like to increase our DATAC2 disk group size .

Environment
Oracle SuperCluster  T5-8
2 SPARC T5-8 Compute nodes, 4 EXADATA X5-2 Storage cells


step 1. Find out freespace available in celldisk

Login to any storage cell :-
cellcli -e "list celldisk where name like 'CD.*' attributes name, size, freespace"

         CD_00_itscceladm01      /dev/sda3       2.81195068359375T
         CD_01_itscceladm01      /dev/sdb3       2.81195068359375T
         CD_02_itscceladm01      /dev/sdc        2.8449554443359375T
         CD_03_itscceladm01      /dev/sdd        2.8449554443359375T
         CD_04_itscceladm01      /dev/sde        2.8449554443359375T
         CD_05_itscceladm01      /dev/sdf        2.8449554443359375T
         CD_06_itscceladm01      /dev/sdg        2.8449554443359375T
         CD_07_itscceladm01      /dev/sdh        2.8449554443359375T
         CD_08_itscceladm01      /dev/sdi        2.8449554443359375T
         CD_09_itscceladm01      /dev/sdj        2.8449554443359375T
         CD_10_itscceladm01      /dev/sdk        2.8449554443359375T
         CD_11_itscceladm01      /dev/sdl        2.8449554443359375T



step 2.Increase size of DATA disks in storage cells

To check current griddisk size login to any storage cell:-
cellcli -e "list griddisk where name like 'DATAC2.*' attributes name, size"


         DATAC2_CD_00_itscceladm01       257G
         DATAC2_CD_01_itscceladm01       257G
         DATAC2_CD_02_itscceladm01       257G
         DATAC2_CD_03_itscceladm01       257G
         DATAC2_CD_04_itscceladm01       257G
         DATAC2_CD_05_itscceladm01       257G
         DATAC2_CD_06_itscceladm01       257G
         DATAC2_CD_07_itscceladm01       257G
         DATAC2_CD_08_itscceladm01       257G
         DATAC2_CD_09_itscceladm01       257G
         DATAC2_CD_10_itscceladm01       257G
         DATAC2_CD_11_itscceladm01       257G

Do same from all other 3 storage cell and save the output.


Step 3. Increase the grid disk size.

So in our case we are increasing the griddisk size by 200G.
Login to storage cell 1:-

cellcli -e alter griddisk DATAC2_CD_00_itscceladm01, DATAC2_CD_01_itscceladm01, DATAC2_CD_02_itscceladm01, DATAC2_CD_03_itscceladm01, DATAC2_CD_04_itscceladm01, DATAC2_CD_05_itscceladm01,
DATAC2_CD_06_itscceladm01, DATAC2_CD_07_itscceladm01, DATAC2_CD_08_itscceladm01, DATAC2_CD_09_itscceladm01, DATAC2_CD_10_itscceladm01, DATAC2_CD_11_itscceladm01 size=457G;

Login to storage cell 2:-

cellcli -e alter griddisk DATAC2_CD_00_itscceladm02, DATAC2_CD_01_itscceladm02, DATAC2_CD_02_itscceladm02, DATAC2_CD_03_itscceladm02, DATAC2_CD_04_itscceladm02, DATAC2_CD_05_itscceladm02,
DATAC2_CD_06_itscceladm02, DATAC2_CD_07_itscceladm02, DATAC2_CD_08_itscceladm02, DATAC2_CD_09_itscceladm02, DATAC2_CD_10_itscceladm02, DATAC2_CD_11_itscceladm02 size=457G;

Login to storage cell 3:-

cellcli -e alter griddisk DATAC2_CD_00_itscceladm03, DATAC2_CD_01_itscceladm03, DATAC2_CD_02_itscceladm03, DATAC2_CD_03_itscceladm03, DATAC2_CD_04_itscceladm03, DATAC2_CD_05_itscceladm03,
DATAC2_CD_06_itscceladm03, DATAC2_CD_07_itscceladm03, DATAC2_CD_08_itscceladm03, DATAC2_CD_09_itscceladm03, DATAC2_CD_10_itscceladm03, DATAC2_CD_11_itscceladm03 size=457G;


Login to storage cell 4:-

cellcli -e alter griddisk DATAC2_CD_00_itscceladm04, DATAC2_CD_01_itscceladm04, DATAC2_CD_02_itscceladm04, DATAC2_CD_03_itscceladm04, DATAC2_CD_04_itscceladm04, DATAC2_CD_05_itscceladm04,
DATAC2_CD_06_itscceladm04, DATAC2_CD_07_itscceladm04, DATAC2_CD_08_itscceladm04, DATAC2_CD_09_itscceladm04, DATAC2_CD_10_itscceladm04, DATAC2_CD_11_itscceladm04 size=457G;


Step 4. Verify the new size
cellcli -e "list grid disk where name like 'DATAC2.*' attributes name, size"


Step 5. Increase size of DATA disks in ASM
Increase the size of all disks in disk group DATAC2, with the following command:

$ sqlplus / as sysasm

SQL> alter diskgroup DATAC2 resize all rebalance power 32;

Diskgroup altered.

Note that there was no need to specify the new disks size, as ASM will get that from the griddisks. The rebalance clause is optional.

The command will trigger the rebalance operation for disk group DATA.

Monitor the rebalance with the following command:

SQL> select * from gv$asm_operation;

Once the command returns "no rows selected", the rebalance would have completed and all disks in disk group DATAC2 should show new size:


Verify the new sizes for the ASM disks and diskgroup is at the desired sizes

CellCLI> list grid disk where name like 'DATAC2.*' attributes name, size

DATAC2_CD_00_itscceladm01       457G
         DATAC2_CD_01_itscceladm01       457G
         DATAC2_CD_02_itscceladm01       457G
         DATAC2_CD_03_itscceladm01       457G
         DATAC2_CD_04_itscceladm01       457G
         DATAC2_CD_05_itscceladm01       457G
         DATAC2_CD_06_itscceladm01       457G
         DATAC2_CD_07_itscceladm01       457G
         DATAC2_CD_08_itscceladm01       457G
         DATAC2_CD_09_itscceladm01       457G
         DATAC2_CD_10_itscceladm01       457G
         DATAC2_CD_11_itscceladm01       457G


Error in invoking target 'agent nmhs' of makefile while installing oracle 11g

When installing Oracle 11g  you may get the following error :-

 Error in invoking target 'agent nmhs'  of makefile '/u01/app/oracle/product/11.1.0/db_1/sysman/lib/ins_emagent.mk

Solution:-

Open the file it reports in error message.

vi $ORACLE_HOME/sysman/lib/ins_emagent.mk


Search for the line 
$(MK_EMAGENT_NMECTL)
Change it to:
$(MK_EMAGENT_NMECTL) -lnnz11




Note : edit this file while you still have the installer open, once you have changed it just click retry on the installer window.

Migrating databases from NON-ASM to ASM in oracle 12c

Migrating databases from NON-ASM to ASM in oracle 12c


Step 1.
Login to the database and write down the path of all the datafiles, tempfiles and redo log files.


SQL> select status,version,instance_name from v$instance;

STATUS       VERSION           INSTANCE_NAME
------------ ----------------- ----------------
OPEN         12.1.0.2.0        orclnew


SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME                                                                        TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------------------
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_users_dcldb9g3_.dbf               USERS
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_undotbs1_dcldbbm9_.dbf            UNDOTBS1
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_system_dcld7ty2_.dbf              SYSTEM
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_sysaux_dcld611r_.dbf              SYSAUX


SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/ORCLNE
                                                 W/controlfile/o1_mf_dcldcd09_.
                                                 ctl


Step 2. Lets find out what are the disks available in ask disk group along with the free space.

SQL> select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;
NAME                           STATE      TOTAL_MB PATH
------------------------------ -------- ---------- -------------------------
VOL1                           NORMAL         9209 ORCL:VOL1
VOL2                           NORMAL         9209 ORCL:VOL2
VOL3                           NORMAL         9209 ORCL:VOL3

SQL>  select NAME,GROUP_NUMBER,FREE_MB,TOTAL_MB from v$asm_diskgroup;

NAME                           GROUP_NUMBER    FREE_MB   TOTAL_MB
------------------------------ ------------ ---------- ----------
DATA                                      1      23719      27627


Now set the control file to point the ASM disk group DATA.

SQL> alter system set control_files='+DATA' scope=spfile;

System altered.


Step 3. Since our database in running on archivelog mode, lets change the db_recovery_file_dest location to ASM diskgroup DATA.

SQL> alter system set db_recovery_file_dest='+DATA' scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest_SIZE=200M scope=spfile;

System altered.


Step 4. Shutdown and start the database in nomount stage.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  440401920 bytes
Fixed Size                  2925360 bytes
Variable Size             360713424 bytes
Database Buffers           71303168 bytes
Redo Buffers                5459968 bytes
SQL>
Step 5.
Connect to RMAN and restore the controlfile from the file system to the disk group DATA.

[oracle@server3 ~]$ rman target sys/redhat

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Mar 3 15:12:56 2017

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

connected to target database: ORCLNEW (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/oradata/ORCLNEW/controlfile/o1_mf_dcldcd09_.ctl';

Starting restore at 03-MAR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/ORCLNEW/CONTROLFILE/current.271.937667637
Finished restore at 03-MAR-17


Step 6. After restoring the controlfile mount the database from rman.

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1


Step 7. Now take the backup of the database in the ASM disk group DATA.

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 03-MAR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_system_dcld7ty2_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/system.272.937667983 tag=TAG20170303T151941 RECID=1 STAMP=937668020
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_sysaux_dcld611r_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/sysaux.273.937668029 tag=TAG20170303T151941 RECID=2 STAMP=937668059
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_undotbs1_dcldbbm9_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/undotbs1.274.937668065 tag=TAG20170303T151941 RECID=3 STAMP=937668068
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/ORCLNEW/CONTROLFILE/backup.275.937668073 tag=TAG20170303T151941 RECID=4 STAMP=937668074
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_users_dcldb9g3_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/users.276.937668075 tag=TAG20170303T151941 RECID=5 STAMP=937668075
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-MAR-17
channel ORA_DISK_1: finished piece 1 at 03-MAR-17
piece handle=+DATA/ORCLNEW/BACKUPSET/2017_03_03/nnsnf0_tag20170303t151941_0.277.937668077 tag=TAG20170303T151941 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-MAR-17


Step 8. Now change the NON ASM database to ASM by running the below command.
RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/system.272.937667983"
datafile 3 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/sysaux.273.937668029"
datafile 4 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/undotbs1.274.937668065"
datafile 6 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/users.276.937668075"
RMAN>


Step 9. Now once the datafiles have been moved into ASM diskgroup we can open the database.
RMAN> alter database open;

Statement processed


Step 10. Once database has been opened determine the datafiles location

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                                                        TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATA/ORCLNEW/DATAFILE/users.276.937668075                                       USERS
+DATA/ORCLNEW/DATAFILE/undotbs1.274.937668065                                    UNDOTBS1
+DATA/ORCLNEW/DATAFILE/system.272.937667983                                      SYSTEM
+DATA/ORCLNEW/DATAFILE/sysaux.273.937668029                                      SYSAUX

SQL>


Step 11.After moving the datafiles into ASM diskgroup , we still need to move the temp tablespace and redolog files into ASM diskgroup.
SQL> select file_name,tablespace_name from dba_Temp_Files;

FILE_NAME                                                                        TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_temp_dcldd5h8_.tmp                TEMP


SQL> select group#,member from v$logfile;
    GROUP# MEMBER
---------- ----------------------------------------------------------------------
         3 /u01/app/oracle/oradata/ORCLNEW/onlinelog/o1_mf_3_dcldchys_.log
         2 /u01/app/oracle/oradata/ORCLNEW/onlinelog/o1_mf_2_dcldcgk9_.log
         1 /u01/app/oracle/oradata/ORCLNEW/onlinelog/o1_mf_1_dcldcfg7_.log



Step 11. To move the temp tablespace we need to drop the existing temp files  first then recreate it into ASM diskgroup

SQL> alter database tempfile '/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_temp_dcldd5h8_.tmp' drop including datafiles;

Database altered.


SQL> alter tablespace TEMP add tempfile '+DATA' size 150M autoextend on;

Tablespace altered.

SQL> select file_name,tablespace_name from dba_Temp_Files;

FILE_NAME                                                                        TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATA/ORCLNEW/TEMPFILE/temp.278.937669453                                        TEMP

SQL>



Step 12. Now move the redolog group into ASM diskgroup

For this first we will drop the inactive redo log group and then recreate it.
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

Here log group 2 and log group 3 are INACTIVE. Hence, we can drop them and recreate the log groups 2 and 3.

Change  default location of online redo log files as +DATA disk group.


SQL> alter system set db_create_online_log_dest_1='+DATA' SCOPE=BOTH;

System altered.


SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database  add logfile group 2;

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database  add logfile group 3;


SQL>alter system switch logfile;

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database  add logfile group 1;

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                 IS_     CON_ID
---------- ------- ------- ---------------------------------------------------------------------- --- ----------
         3         ONLINE  +DATA/ORCLNEW/ONLINELOG/group_3.280.937672657                          NO           0
         2         ONLINE  +DATA/ORCLNEW/ONLINELOG/group_2.279.937672551                          NO           0
         1         ONLINE  +DATA/ORCLNEW/ONLINELOG/group_1.285.937672783                          NO           0


Now finally all physical datafiles/logfiles have been moved from NON ASM TO ASM.

Clone a database using cold backup and restore it with different SID



Objective :-We are cloning a database running on target server  and restoring it into clone server with a different sid.

Note- Make sure oracle binary is already installed on target db server.In this case the directory structure of target server is different than source server.

Database Version :- Oracle Enterprise Edition  11.2.0.1 on RHEL 6.4 on both server

Source  Server: 192.168.0.102
Database sid:- prim
Hostname:-server1.soumya.com


Destination Server: 192.168.0.104
database sid: newprim
Hostname:-server2.soumya.com


1.Take backup of controlfile as trace:[SOURCE DB]

SQL> alter database backup controlfile to trace as '/u01/app/oracle/control01.sql';
Database altered.

2. check the location of datafiles[SOURCE DB]

SQL>  select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/example01.dbf


SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/temp01.dbf


3. Shutdown the database:[SOURCE DB]
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


4. Copy the datafiles to the target db server
[oracle@server1 ]$cd /u01/app/oracle/oradata/prim

[oracle@server1 backup]$ scp *.dbf oracle@server2:/u01/
The authenticity of host 'server2 (192.168.0.104)' can't be established.
RSA key fingerprint is 0b:59:e4:8b:b1:e6:12:3a:38:4f:ba:74:ef:8a:ad:46.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'server2,192.168.0.104' (RSA) to the list of known hosts.
oracle@server2's password:
example01.dbf                           100%  100MB  25.0MB/s   00:04   
sysaux01.dbf                            100%  500MB  16.7MB/s   00:30   
system01.dbf                            100%  670MB  19.7MB/s   00:34   
temp01.dbf                              100%   20MB   6.7MB/s   00:03   
undotbs01.dbf                           100%   55MB  27.5MB/s   00:02   
users01.dbf                             100% 5128KB   5.0MB/s   00:00   

Also copy the controlfile which we took backup at step 1 and transfer it into target server.

5.Make changes in the init file for target db:[TARGET DB]
We can copy the pfile from source db and the change the required parameters like DB_NAME and control_file localtion,audit_file_dest location,diag location etc.


[oracle@server2 dbs]$ cat initnewprim.ora

newprim.__db_cache_size=138412032
newprim.__java_pool_size=4194304
newprim.__large_pool_size=4194304
newprim.__oracle_base='/u01/newapp/oracle'#ORACLE_BASE set from environment
newprim.__pga_aggregate_target=167772160
newprim.__sga_target=247463936
newprim.__shared_io_pool_size=0
newprim.__shared_pool_size=92274688
newprim.__streams_pool_size=0
*.audit_file_dest='/u01/newapp/oracle/admin/newprim/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/newapp/oracle/oradata/newprim/control01.ctl','/u01/newapp/oracle/flash_recovery_area/newprim/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='newprim'
*.db_recovery_file_dest='/u01/newapp/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/newapp/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=newprimXDB)'
*.memory_target=414187520
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

Create necessary directories as per new sid

[oracle@server2 dbs]$ mkdir -p /u01/newapp/oracle/admin/newprim/adump
[oracle@server2 dbs]$ mkdir -p /u01/newapp/oracle/oradata/newprim/
[oracle@server2 dbs]$ mkdir -p /u01/newapp/oracle/flash_recovery_area/newprim/



6. Now Start the database in nomount stage:[TARGET DB]
[oracle@server2 dbs]$ export ORACLE_SID=newprim

[oracle@server2 dbs]$sqlplus / as sysdba
SQL> startup nomount pfile='/u01/newapp/oracle/product/11.2.0/db_1/dbs/initnewprim.ora';
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             268437496 bytes
Database Buffers          138412032 bytes
Redo Buffers                4308992 bytes



7. Re-recreate the controlfile [ TARGET DB ]

Open the controlfile and remove word REUSE and make it SET
remove word NORESETLOGS and make it RESETLOGS
remove or keep the word ARCHIVELOG depending upon our requirement
change database name from "prim" to "newprim"
change directory name everywhere from 'prim' to 'newprim'
:wq

The content should look like this

[oracle@server2 u01]$ vi control01.sql

CREATE CONTROLFILE SET DATABASE "NEWPRIM" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/newprim/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/newprim/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/newprim/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/newprim/system01.dbf',
  '/u01/app/oracle/oradata/newprim/sysaux01.dbf',
  '/u01/app/oracle/oradata/newprim/undotbs01.dbf',
  '/u01/app/oracle/oradata/newprim/users01.dbf',
  '/u01/app/oracle/oradata/newprim/example01.dbf'
CHARACTER SET WE8MSWIN1252
;


8.Create the controlfile at target db.

SQL>@/u01/control01.sql
Control file created.

9.Open the database in resetlog mode:

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

10.Create the temp files.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/newapp/oracle/oradata/newprim/temp_01.dbf' SIZE 50m autoextend on next 10m maxsize unlimited;
Tablespace altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
newprim

How to clone a database using hot backup in oracle database 11g

How to clone a database using user managed hot backup in oracle database 11g

Objective :-We are cloning a database running on target server  and restoring it into clone server.


Database Version :- Oracle Enterprise Edition  11.2.0.1 on RHEL 6.4

Target Server: 192.168.0.101
Database sid:- prim

Clone Server: 192.168.0.104
database sid: newprim


****At target Database

Take online backup of database

Before taking the online backup we need to put the database into archivelog mode.


$ export ORACLE_SID=prim

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

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             310380536 bytes
Database Buffers           96468992 bytes
Redo Buffers                4308992 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL>alter database begin backup;
Database altered.

SQL> create table test (id number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> /

1 row created.

SQL> /
SQL> commit;
SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                1067890 01-MAY-18
         2 ACTIVE                1067890 01-MAY-18
         3 ACTIVE                1067890 01-MAY-18
         4 ACTIVE                1067890 01-MAY-18
         5 ACTIVE                1067890 01-MAY-18

SQL> exit


Now switch into the datafile directory and copy all the .dbf and .log files into the backup directory

[oracle@server1]$ cd /u01/app/oracle/oradata/prim
[oracle@server1 prim]$ cp -rpf *.log /home/oracle/bkup/
[oracle@server1 prim]$ cp -rpf *.dbf /home/oracle/bkup/


SQL> alter database end backup;

Database altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE            1067890 01-MAY-18
         2 NOT ACTIVE            1067890 01-MAY-18
         3 NOT ACTIVE            1067890 01-MAY-18
         4 NOT ACTIVE            1067890 01-MAY-18
         5 NOT ACTIVE            1067890 01-MAY-18

Now take backup of controlfile
SQL> alter database backup controlfile to trace as '/home/oracle/control.sql';

Database altered.

Now edit the content of control file as per below.

[oracle@server1 ~]$vi /home/oracle/control.sql
Remove word REUSE and make it SET
remove word NORESETLOGS and make it RESETLOGS
remove or keep the word ARCHIVELOG depending upon our requirement
change database name from "prim" to "newprim"
change folder name everywhere from 'prim' to 'newprim'
:wq

The content should look like this


CREATE CONTROLFILE SET DATABASE "NEWPRIM" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/newprim/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/newprim/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/newprim/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/oradata/newprim/system01.dbf',
  '/u01/app/oracle/oradata/newprim/sysaux01.dbf',
  '/u01/app/oracle/oradata/newprim/undotbs01.dbf',
  '/u01/app/oracle/oradata/newprim/users01.dbf',
  '/u01/app/oracle/oradata/newprim/example01.dbf'
CHARACTER SET WE8MSWIN1252
;



Now transfer all the datafiles, controlfile, redolog files into clone server using scp.

[oracle@server1 ~]$ scp control.sql oracle@192.168.0.104:/u01/app/oracle/oradata/newprim/
The authenticity of host '192.168.0.104 (192.168.0.104)' can't be established.
RSA key fingerprint is 0b:59:e4:8b:b1:e6:12:3a:38:4f:ba:74:ef:8a:ad:46.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.104' (RSA) to the list of known hosts.
oracle@192.168.0.104's password:
control.sql                                 100%  698     0.7KB/s   00:00   
[oracle@server1 ~]$ cd bkup/
[oracle@server1 bkup]$ ll
total 1534024
-rw-r-----. 1 oracle oinstall 104865792 May  1 20:23 example01.dbf
-rw-r-----. 1 oracle oinstall  52429312 May  1 20:33 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 May  1 20:23 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 May  1 20:23 redo03.log
-rw-r-----. 1 oracle oinstall 524296192 May  1 20:29 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 702554112 May  1 20:33 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 May  1 19:58 temp01.dbf
-rw-r-----. 1 oracle oinstall  57679872 May  1 20:31 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 May  1 20:33 users01.dbf
[oracle@server1 bkup]$ scp * oracle@192.168.0.104:/u01/app/oracle/oradata/newprim/
oracle@192.168.0.104's password:
example01.dbf                                100%  100MB  50.0MB/s   00:02   
redo01.log                                   100%   50MB  50.0MB/s   00:01   
redo02.log                                   100%   50MB  25.0MB/s   00:02   
redo03.log                                   100%   50MB  25.0MB/s   00:02   
sysaux01.dbf                                 100%  500MB  19.2MB/s   00:26   
system01.dbf                                 100%  670MB  12.6MB/s   00:53   
temp01.dbf                                   100%   20MB  20.0MB/s   00:01   
undotbs01.dbf                                100%   55MB  55.0MB/s   00:00   
users01.dbf                                  100% 5128KB   5.0MB/s   00:00   
[oracle@server1 bkup]$ cd
[oracle@server1 ~]$ ll



****At clone server

Create necessary directory structure for clone database.
mkdir -p /u01/app/oracle/oradata/newprim/
mkdir -p /u01/app/oracle/admin/newprim/adump


export ORACLE_SID=newprim
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initnewprim.ora';
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             268437496 bytes
Database Buffers          138412032 bytes
Redo Buffers                4308992 bytes

Now recreate control file.
SQL> @/u01/app/oracle/oradata/newprim/control.sql;

Control file created.


Now recover the database.

sql> recover database using backup controlfile until cancel;
here one by one apply all archivelog files
apply all 3 redo log files with full path until we get the msg that recovery is done.

Here its better to notedown the current redo log file and the pending archivelogs from target db at the time of backup. We need to apply only those
archives and only one current redo log file.


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1067890 generated at 05/01/2018 20:23:49 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/NEWPRIM/archivelog/2014_12_28/o1_mf_1_7_%u_.
arc
ORA-00280: change 1067890 for thread 1 is in sequence #7


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/NEWPRIM/archivelog/2014_12_28/o1_mf_1_7_%u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/NEWPRIM/archivelog/2014_12_28/o1_mf_1_7_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/newprim/redo01.log
Log applied.
Media recovery complete.


Open the database with resetlogs

SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
newprim

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Now the process of cloning a database is complete into a new server using user managed backup.
Thanks