Search This Blog

Total Pageviews

Sunday 17 April 2016

DUPLICATE DATABASE WITH RMAN BACKUP

Oracle DUPLICATE DATABASE WITH RMAN BACKUP ...

DUPLICATE DATABASE WITHOUT CONNECTING TO TARGET DATABASE ON SAME ASM disk group ..


If duplicating a database on the same host as the source database, then ensure that NOFILENAMECHECK is not set. Otherwise, RMAN can potentially overwrite and corrupt the target database data files, temp files, or online logs. 



cat /etc/oratab 

ora11g:/u01/app/oracle/product/11.2.0/db_1:N            # line added by Agent  ------ This is a orignal database 
ora11ga:/u01/app/oracle/product/11.2.0/db_1:N          # line added by Agent  ------ aim to create duplicate database on same +ASM disk group i.e. +DATA
+ASM:/u01/app/grid/product/12.2.0/grid:N                 # line added by Agent


ora11g database init file i.e. orignal database ...

[oracle@ora12c dbs]$ cat initora11g.ora
ora11g.__db_cache_size=503316480
ora11g.__java_pool_size=16777216
ora11g.__large_pool_size=33554432
ora11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ora11g.__pga_aggregate_target=503316480
ora11g.__sga_target=754974720
ora11g.__shared_io_pool_size=0
ora11g.__shared_pool_size=184549376
ora11g.__streams_pool_size=0
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/ora11g/controlfile/current.281.905721923','+DATA/ora11g/controlfile/current.282.905721925'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='ora11g'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.memory_target=1258291200
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.audit_file_dest='/u01/app/oracle/admin/ora11g/adump'
*.log_archive_dest_1='location=+DATA'




creating  duplicate database name ora11ga

ora11ga database init file i.e. duplicate database ...


cat initora11ga.ora

ora11ga.__db_cache_size=503316480
ora11ga.__java_pool_size=16777216
ora11ga.__large_pool_size=33554432
ora11ga.__oracle_base='/u01/app/oracle'
ora11ga.__pga_aggregate_target=503316480
ora11ga.__sga_target=754974720
ora11ga.__shared_io_pool_size=0
ora11ga.__shared_pool_size=184549376
ora11ga.__streams_pool_size=0
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA','+DATA'
*.db_block_size=8192
*.db_domain=''
*.db_name='ora11ga'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gaXDB)'
*.memory_target=1258291200
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_create_file_dest='+DATA/ORA11GA'
*.log_archive_dest_1='location=+DATA/ORA11GA'
*.db_unique_name='ora11ga'
*.db_file_name_convert  = ("+DATA/ORA11G","+DATA/ORA11GA")
*.log_file_name_convert = ("+DATA/ORA11G","+DATA/ORA11GA")
*.audit_file_dest='/u01/app/oracle/admin/ora11ga/adump'


On ASM create a new dir ..

ASMCMD [+DATA] > ls -l
Type      Redund  Striped  Time             Sys  Name
                                            Y    ASM/
                                            Y    ORA11G/
PASSWORD  UNPROT  COARSE   JAN 09 18:00:00  N    orapwasm => +DATA/ASM/PASSWORD/pwdasm.256.900698615
ASMCMD [+DATA] > mkdir ORA11GA ------ create dir in ASM


set the environment for duplicate database  

[oracle@ora12c dbs]$ . oraenv
ORACLE_SID = [ora11g] ? ora11ga
The Oracle base remains unchanged with value /u01/app/oracle

start the database in nomount 

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

Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             788533224 bytes
Database Buffers          452984832 bytes
Redo Buffers                8892416 bytes


Rman backup location ...

[oracle@ora12c dbs]$ cd /u01/app/RmanBackup/
[oracle@ora12c RmanBackup]$ ls -ltr
total 4
drwxr-xr-x 2 oracle oinstall 4096 Mar  6 07:01 ORA11G
[oracle@ora12c RmanBackup]$ cd ORA11G/

[oracle@ora12c ORA11G]$ pwd
/u01/app/RmanBackup/ORA11G  <<<<<<--------

[oracle@ora12c ORA11G]$ ls -ltr
total 339416
-rw-r----- 1 oracle asmadmin 346447872 Mar  5 18:08 20160305_ora11g_6_1_905710001
-rw-r----- 1 oracle asmadmin   1097728 Mar  5 18:08 20160305_ora11g_7_1_905710117
-rw-r----- 1 oracle asmadmin      3584 Mar  5 18:08 20160305_ora11g_8_1_905710124


rman auxiliary /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 16 21:16:27 2016

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

connected to auxiliary database: ORA11GA (not mounted)



RMAN> duplicate target database to ora11ga backup location '/u01/app/RmanBackup/ORA11G/'  nofilenamecheck ;

Starting Duplicate Db at 16-APR-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    1252663296 bytes

Fixed Size                     2252824 bytes
Variable Size                805310440 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8892416 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/ora11ga/controlfile/current.312.909350245'', ''+DATA/ora11ga/controlfile/current.311.909350245'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''ORA11G'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''ORA11GA'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/app/RmanBackup/ORA11G/20160305_
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/ora11ga/controlfile/c                                                                                        et by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''ORA11G'' comment= ''Modified by RM

sql statement: alter system set  db_unique_name =  ''ORA11GA'' comment= ''Modifi

Oracle instance shut down

Oracle instance started

Total System Global Area    1252663296 bytes

Fixed Size                     2252824 bytes
Variable Size                805310440 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8892416 bytes

Starting restore at 16-APR-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:10
output file name=+DATA/ora11ga/controlfile/current.312.909350245
output file name=+DATA/ora11ga/controlfile/current.311.909350245
Finished restore at 16-APR-16

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set until scn  760753;
   set newname for datafile  1 to
 "+data";
   set newname for datafile  2 to
 "+data";
   set newname for datafile  3 to
 "+data";
   set newname for datafile  4 to
 "+data";
   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 16-APR-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 +data
channel ORA_AUX_DISK_1: restoring datafile 00002 to +data
channel ORA_AUX_DISK_1: restoring datafile 00003 to +data
channel ORA_AUX_DISK_1: restoring datafile 00004 to +data
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/RmanBackup/ORA11G/20160305_ora11g_6_1_905710001
channel ORA_AUX_DISK_1: piece handle=/u01/app/RmanBackup/ORA11G/20160305_ora11g_6_1_905710001 tag=TAG20160305T180640
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:44
Finished restore at 16-APR-16

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

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=909350516 file name=+DATA/ora11ga/datafile/system.310.909350291
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=909350517 file name=+DATA/ora11ga/datafile/sysaux.295.909350291
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=909350517 file name=+DATA/ora11ga/datafile/undotbs1.294.909350291
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=909350518 file name=+DATA/ora11ga/datafile/users.293.909350291

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

executing command: SET until clause

Starting recover at 16-APR-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=68
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/RmanBackup/ORA11G/20160305_ora11g_8_1_905710124
channel ORA_AUX_DISK_1: piece handle=/u01/app/RmanBackup/ORA11G/20160305_ora11g_8_1_905710124 tag=TAG20160305T180843
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=+DATA/ora11ga/1_68_900708574.dbf thread=1 sequence=68
channel clone_default: deleting archived log(s)
archived log file name=+DATA/ora11ga/1_68_900708574.dbf RECID=1 STAMP=909350535
media recovery complete, elapsed time: 00:00:03
Finished recover at 16-APR-16
Oracle instance started

Total System Global Area    1252663296 bytes

Fixed Size                     2252824 bytes
Variable Size                805310440 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8892416 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORA11GA'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''ORA11GA'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1252663296 bytes

Fixed Size                     2252824 bytes
Variable Size                805310440 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8892416 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORA11GA" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '+DATA/ora11ga/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '+DATA/ora11ga/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '+DATA/ora11ga/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '+DATA/ora11ga/datafile/system.310.909350291'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "+data";
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/ora11ga/datafile/sysaux.295.909350291",
 "+DATA/ora11ga/datafile/undotbs1.294.909350291",
 "+DATA/ora11ga/datafile/users.293.909350291";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

cataloged datafile copy
datafile copy file name=+DATA/ora11ga/datafile/sysaux.295.909350291 RECID=1 STAMP=909350576
cataloged datafile copy
datafile copy file name=+DATA/ora11ga/datafile/undotbs1.294.909350291 RECID=2 STAMP=909350578
cataloged datafile copy
datafile copy file name=+DATA/ora11ga/datafile/users.293.909350291 RECID=3 STAMP=909350578

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=909350576 file name=+DATA/ora11ga/datafile/sysaux.295.909350291
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=909350578 file name=+DATA/ora11ga/datafile/undotbs1.294.909350291
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=909350578 file name=+DATA/ora11ga/datafile/users.293.909350291

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

database opened
Finished Duplicate Db at 16-APR-16

=======================================


Check the new database i.e. ora11ga


SQL> select * from dual;

D
-
X


show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfileora11ga.ora

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA/ora11ga
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1



alter session set nls_date_format='dd-mm-yyyy HH24:mi';

SQL> select name,CREATED from v$database;

NAME      CREATED
--------- ----------------
ORA11GA   16-04-2016 21:22



SQL> select FILE_NAME from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/ora11ga/datafile/system.310.909350291
+DATA/ora11ga/datafile/sysaux.295.909350291
+DATA/ora11ga/datafile/undotbs1.294.909350291
+DATA/ora11ga/datafile/users.293.909350291


set numf 999.99 linesize 200 pagesize 200
col FILE_NAME            for a50
col AUTOEXTENSIBLE for a15
SELECT tablespace_name, file_name, bytes/1024/1024/1024 Gb ,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 Max_Gb FROM dba_temp_files
WHERE tablespace_name in (select TABLESPACE_NAME from dba_tablespaces where CONTENTS='TEMPORARY') ;

TABLESPACE_NAME                FILE_NAME                                               GB AUTOEXTENSIBLE   MAX_GB
------------------------------ -------------------------------------------------- ------- --------------- -------
TEMP                           +DATA/ora11ga/tempfile/temp.304.909350621              .06 YES               32.00



select 
   group#, 
   members, 
   thread#, 
   sequence#, 
   bytes/1024/1024 mb, 
   archived, 
   status, 
   first_time
from  v$log
order by group#;

    GROUP#    MEMBERS    THREAD#  SEQUENCE#         MB ARC STATUS           FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ----------------
         1          1          1          1         50 NO  CURRENT          16-04-2016 21:23
         2          1          1          0         50 YES UNUSED
         3          1          1          0         50 YES UNUSED


col MEMBER                   for a50
col IS_RECOVERY_DEST_FILE for a25
SELECT * FROM V$LOGFILE;

  GROUP# STATUS  TYPE    MEMBER                                             IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- -------------------------
         3         ONLINE  +DATA/ora11ga/redo03.log                           NO
         2         ONLINE  +DATA/ora11ga/redo02.log                           NO
         1         ONLINE  +DATA/ora11ga/redo01.log                           NO



Displays information about redo log files.

SET LINESIZE 200 pagesize 200
COLUMN member          FORMAT A30
COLUMN first_change#   FORMAT 99999999999999999999
COLUMN next_change#    FORMAT 99999999999999999999
SELECT l.thread#,
       lf.group#,
       lf.member,
       TRUNC(l.bytes/1024/1024) AS size_mb,
       l.status,
       l.archived,
       lf.type,
       lf.is_recovery_dest_file AS rdf,
       l.sequence#,
       l.first_change#,
       l.next_change#   
FROM   v$logfile lf, v$log l
where  l.group# = lf.group#
ORDER BY l.thread#,lf.group#, lf.member;


THREAD#  GROUP# MEMBER                         SIZE_MB STATUS           ARC TYPE    RDF SEQUENCE#         FIRST_CHANGE#          NEXT_CHANGE#
------- ------- ------------------------------ ------- ---------------- --- ------- --- --------- --------------------- ---------------------
   1.00    1.00 +DATA/ora11ga/redo01.log         50.00 INACTIVE         YES ONLINE  NO       1.00                760754                783792
   1.00    2.00 +DATA/ora11ga/redo02.log         50.00 CURRENT          NO  ONLINE  NO       2.00                783792       281474976710655
   1.00    3.00 +DATA/ora11ga/redo03.log         50.00 UNUSED           YES ONLINE  NO        .00                     0                     0


SET LINESIZE 80
SELECT GROUP#, BYTES, 'ONLINE'  AS TYPE FROM V$LOG
UNION
SELECT GROUP#, BYTES, 'STANDBY' AS TYPE FROM V$STANDBY_LOG
ORDER BY 1;

    GROUP#      BYTES TYPE
---------- ---------- -------
         1   52428800 ONLINE
         2   52428800 ONLINE
         3   52428800 ONLINE

=====================================================
For file system !!!!!!!!!!!!!!!!!!!!!

[oracle@ora tmp]$ cat oraeed.txt
oraeed.__db_cache_size=360710144
oraeed.__java_pool_size=4194304
oraeed.__large_pool_size=4194304
oraeed.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
oraeed.__pga_aggregate_target=184549376
oraeed.__sga_target=536870912
oraeed.__shared_io_pool_size=0
oraeed.__shared_pool_size=159383552
oraeed.__streams_pool_size=0
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='oraee'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oraeeXDB)'
*.open_cursors=300
*.pga_aggregate_target=183500800
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_tablespace='UNDOTBS1'
*.audit_file_dest='/u01/app/oracle/admin/oraeed/adump'
*.control_files='/u01/app/oracle/oradata/oraeed/control01.ctl'
*.log_archive_dest_1='location=/u01/app/oracle/ArchiveEED'
*.db_file_name_convert  = ("/u01/app/oracle/oradata/oraee/oraee","/u01/app/oracle/oradata/oraeed")
*.log_file_name_convert = ("/u01/app/oracle/oradata/oraee/oraee","/u01/app/oracle/oradata/oraeed")
*.db_create_file_dest='/u01/app/oracle/oradata/oraeed'



export ORACLE_SID=oraeed



SQL> def

DEFINE _DATE           = "15-JAN-17" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "oraeed" (CHAR)  <<<<<<<<<<---- Check Oracle SID
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Release 11.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)



SQL> create spfile from pfile='/tmp/oraeed.txt';


File created.


SQL> startup nomount ;

ORACLE instance started.

Total System Global Area  534462464 bytes

Fixed Size                  2215064 bytes
Variable Size             167773032 bytes
Database Buffers          360710144 bytes
Redo Buffers                3764224 bytes
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production



[oracle@ora tmp]$ rman auxiliary /


Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 15 09:07:16 2017


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


connected to auxiliary database: ORAEE (not mounted)





RMAN> duplicate target database to oraeed backup location '/u01/app/oracle/RmanBackup/'  nofilenamecheck ;


Starting Duplicate Db at 15-JAN-17


contents of Memory Script:

{
   sql clone "alter system set  db_name =
 ''ORAEE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/app/oracle/RmanBackup/c-2924229127-20170115-03.bck';
   alter clone database mount;
}
executing Memory Script

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


Oracle instance shut down


Oracle instance started


Total System Global Area     534462464 bytes


Fixed Size                     2215064 bytes

Variable Size                167773032 bytes
Database Buffers             360710144 bytes
Redo Buffers                   3764224 bytes

Starting restore at 15-JAN-17

allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/oraeed/control01.ctl
Finished restore at 15-JAN-17

database mounted

released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

contents of Memory Script:

{
   set until scn  975349;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/oraeed/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/oraeed/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/oraeed/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/oraeed/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 15-JAN-17

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/oracle/oradata/oraeed/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/oraeed/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/oraeed/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/oraeed/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORAEE_20170115_6_1_FULL
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORAEE_20170115_6_1_FULL tag=ORCLEE_FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 15-JAN-17

contents of Memory Script:

{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=933325748 file name=/u01/app/oracle/oradata/oraeed/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=933325748 file name=/u01/app/oracle/oradata/oraeed/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=933325748 file name=/u01/app/oracle/oradata/oraeed/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=933325748 file name=/u01/app/oracle/oradata/oraeed/users01.dbf

contents of Memory Script:

{
   set until scn  975349;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause


Starting recover at 15-JAN-17

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=5
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORAEE_20170115_8_1_ARCHIVE
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORAEE_20170115_8_1_ARCHIVE tag=ORCLEE_ARCHIVE
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/oracle/ArchiveEED/1_5_933321159.dbf thread=1 sequence=5
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/ArchiveEED/1_5_933321159.dbf RECID=1 STAMP=933325749
archived log file name=/u01/app/oracle/ArchiveEED/1_6_933321159.dbf thread=1 sequence=6
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/ArchiveEED/1_6_933321159.dbf RECID=2 STAMP=933325749
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-JAN-17

contents of Memory Script:

{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''ORAEED'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     534462464 bytes


Fixed Size                     2215064 bytes

Variable Size                167773032 bytes
Database Buffers             360710144 bytes
Redo Buffers                   3764224 bytes

sql statement: alter system set  db_name =  ''ORAEED'' comment= ''Reset to original value by RMAN'' scope=spfile


Oracle instance shut down


connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     534462464 bytes


Fixed Size                     2215064 bytes

Variable Size                167773032 bytes
Database Buffers             360710144 bytes
Redo Buffers                   3764224 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORAEED" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/oraeed/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/oraeed/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/oraeed/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/oraeed/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:

{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/oraeed/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/oraeed/sysaux01.dbf",
 "/u01/app/oracle/oradata/oraeed/undotbs01.dbf",
 "/u01/app/oracle/oradata/oraeed/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME


renamed tempfile 1 to /u01/app/oracle/oradata/oraeed/temp01.dbf in control file


cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/oraeed/sysaux01.dbf RECID=1 STAMP=933325759
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/oraeed/undotbs01.dbf RECID=2 STAMP=933325759
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/oraeed/users01.dbf RECID=3 STAMP=933325759

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=933325759 file name=/u01/app/oracle/oradata/oraeed/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=933325759 file name=/u01/app/oracle/oradata/oraeed/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=933325759 file name=/u01/app/oracle/oradata/oraeed/users01.dbf

contents of Memory Script:

{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened

Finished Duplicate Db at 15-JAN-17



====================


Check All datafile ....



COLUMN  today NOPRINT New_Value strToday

SELECT  TO_CHAR( SYSDATE, 'DD Mon YYYY HH24:MI:SS' ) today FROM DUAL;
COLUMN  DATABASE_NAME NOPRINT New_Value strDatabaseName
SELECT  'Data File Report (all physical files) '||HOST_NAME ||'-' || DB_UNIQUE_NAME AS DATABASE_NAME FROM V$Database,V$INSTANCE ;
TTITLE LEFT     '______________________________________________________________________________________________________________________' -
SKIP 2 CENTER   strToday -
SKIP CENTER     'File Report (all physical files)' -
SKIP CENTER     strDatabaseName -
SKIP LEFT       '______________________________________________________________________________________________________________________' -
SKIP LEFT       ''
SET ECHO OFF  FEEDBACK 6 HEADING  ON LINESIZE 200 PAGESIZE 500  TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL   ON  VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN tablespace      FORMAT a30                    HEADING 'Tablespace Name / File Class'
COLUMN filename        FORMAT a75                    HEADING 'Filename'
COLUMN filesize_mb     FORMAT 9,999,999,999,999      HEADING 'File Size MB'
COLUMN autoextensible  FORMAT a4                     HEADING 'Auto'
COLUMN increment_by    FORMAT 999,999,999,999        HEADING 'Next'
COLUMN max_mb          FORMAT 999,999,999,999        HEADING 'Max MB'
BREAK ON report
COMPUTE sum OF filesize  ON report
SELECT /*+ ordered */
    d.tablespace_name                     tablespace
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM sys.dba_data_files d , v$datafile v
  , (SELECT value  FROM v$parameter  WHERE name = 'db_block_size') e
WHERE  (d.file_name = v.name)
UNION
SELECT
    d.tablespace_name                     tablespace 
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM  sys.dba_temp_files d
  , (SELECT value  FROM v$parameter   WHERE name = 'db_block_size') e
UNION
SELECT
    '[ ONLINE REDO LOG ]'
  , a.member
  , b.bytes/1024/1024
  , null
  , TO_NUMBER(null)
  , b.bytes/1024/1024
FROM  v$logfile a, v$log b
WHERE   a.group# = b.group#
UNION
SELECT
    '[ CONTROL FILE    ]'
  , a.name
  ,(select ceil(2 * sum(RECORD_SIZE * records_total)/1024/1024) meg from v$controlfile_record_section)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  v$controlfile a
union
SELECT
    '[ BLOCK TRACKING]'||a.status
  , a.filename
  , TO_NUMBER(null)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  V$BLOCK_CHANGE_TRACKING a
union 
SELECT
    '[ FLASHBACK DATABASE]'
  , a.name
  , a.bytes/1024/1024
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  V$FLASHBACK_DATABASE_LOGFILE a
union
SELECT
    '[ Spfile or Pfile]'||DECODE(value, NULL, 'PFILE', 'SPFILE')
  , a.DISPLAY_VALUE
  , TO_NUMBER(null)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  sys.v_$parameter a WHERE name = 'spfile'
ORDER BY 1,2
/


______________________________________________________________________________________________________________________


                                                                                          15 Jan 2017 09:46:55

                                                                                    File Report (all physical files)
                                                                          Data File Report (all physical files) ora.singh.com-
______________________________________________________________________________________________________________________

Tablespace Name / File Class   Filename                                                                          File Size MB Auto             Next           Max MB

------------------------------ --------------------------------------------------------------------------- ------------------ ---- ---------------- ----------------
SYSAUX                         /u01/app/oracle/oradata/oraeed/sysaux01.dbf                                                440 YES                10           32,768
SYSTEM                         /u01/app/oracle/oradata/oraeed/system01.dbf                                                670 YES                10           32,768
TEMP                           /u01/app/oracle/oradata/oraeed/temp01.dbf                                                   20 YES                 1           32,768
UNDOTBS1                       /u01/app/oracle/oradata/oraeed/undotbs01.dbf                                                30 YES                 5           32,768
USERS                          /u01/app/oracle/oradata/oraeed/users01.dbf                                                   5 YES                 1           32,768
[ BLOCK TRACKING]DISABLED
[ CONTROL FILE    ]            /u01/app/oracle/oradata/oraeed/control01.ctl                                                10
[ ONLINE REDO LOG ]            /u01/app/oracle/oradata/oraeed/redo01.log                                                   50                                     50
[ ONLINE REDO LOG ]            /u01/app/oracle/oradata/oraeed/redo02.log                                                   50                                     50
[ ONLINE REDO LOG ]            /u01/app/oracle/oradata/oraeed/redo03.log                                                   50                                     50
[ Spfile or Pfile]SPFILE       /u01/app/oracle/product/11.2.0/dbhome_3/dbs/spfileoraeed.ora

11 rows selected.





SQL> archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/ArchiveEED
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1


SQL> alter system switch logfile;


System altered.


SQL> !ls -ltr /u01/app/oracle/ArchiveEED

total 1284
-rw-r----- 1 oracle oinstall 1312768 Jan 15 09:49 1_1_933325760.dbf


=====


backup 


backup as compressed backupset  incremental level 0 format '/dumps/VIHAAN8/rman/%T_@_%s_%p_%t'  database include current controlfile plus archivelog;


www
duplicate target database to VIHCDBD9 backup location '/dumps/VIHAAN8/rman/' pluggable database ANUJV,anujy  ;


show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 ANUJV                          READ WRITE NO
         5 ANUJY                          READ WRITE NO





Oracle DBA

anuj blog Archive