Anuj Singh Oracle DBA: 10/01/2021

Friday, 15 October 2021

Oracle Undo Tablespace recovery

Oracle Undo TTablespace recovery

oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> !sql

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 3 14:46:15 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

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

Sql > update scott.emp set sal=99999;

14 rows updated.

Sql > !mv undotbs01.dbf undotbs01.orig

Sql > select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 99999 20

7499 ALLEN SALESMAN 7698 20-FEB-81 99999 300 30

7521 WARD SALESMAN 7698 22-FEB-81 99999 500 30

7566 JONES MANAGER 7839 02-APR-81 99999 20

7654 MARTIN SALESMAN 7698 28-SEP-81 99999 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 99999 30

7782 CLARK MANAGER 7839 09-JUN-81 99999 10

7788 SCOTT ANALYST 7566 19-APR-87 99999 20

7839 KING PRESIDENT 17-NOV-81 99999 10

7844 TURNER SALESMAN 7698 08-SEP-81 99999 0 30

7876 ADAMS CLERK 7788 23-MAY-87 99999 20

7900 JAMES CLERK 7698 03-DEC-81 99999 30

7902 FORD ANALYST 7566 03-DEC-81 99999 20

7934 MILLER CLERK 7782 23-JAN-82 99999 10

14 rows selected.

Sql > alter system flush sharepool;

alter system flush sharepool

ERROR at line 1:

ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword

Sql > alter system flush SHARED_POOL ;

System altered.

Sql > alter system flush BUFFER_CACHE ;

14 rows selected.

Sql > rollback;

^Crollback

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

ORA-01116: error in opening database file 3

ORA-01110: data file 3: '/opt/app/oracle/oradata/orcl/undotbs01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-01116: error in opening database file 3

ORA-01110: data file 3: '/opt/app/oracle/oradata/orcl/undotbs01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Process ID: 20893

Session ID: 47 Serial number: 347

Sql > shu immediate;

ORA-01116: error in opening database file 3

ORA-01110: data file 3: '/opt/app/oracle/oradata/orcl/undotbs01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

/opt/app/oracle/oradata/orcl

create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbsR.dbf' size 500m autoextend on;

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

alter database datafile '/opt/app/oracle/oradata/orcl/undotbs01.dbf' offline drop;

Sql > alter database datafile '/opt/app/oracle/oradata/orcl/undotbs01.dbf' offline drop;

Database altered.

create undo tablespace UNDOTBS2

datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS2' size 200M;

Sql > create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbsR.dbf' size 500m autoextend on;

create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbsR.dbf' size 500m autoextend on

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

ORA-06512: at line 957

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

alter system set UNDO_TABLESPACE='UNDOTBR' scope=spfile;

Sql > alter system set UNDO_TABLESPACE='UNDOTBR' scope=spfile;

System altered.

Sql > create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbsR.dbf' size 500m autoextend on ;

create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbR.dbf' size 500m autoextend on

ERROR at line 1:

ORA-01109: database not open

Sql > select * from dba_rollback_segs;

SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS

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

INSTANCE_NUM RELATIVE_FNO

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

SYSTEM SYS SYSTEM 0 1 128 114688 57344 1 32765 ONLINE

_SYSSMU1_3780397527$ PUBLIC UNDOTBS1 1 3 128 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU2_2232571081$ PUBLIC UNDOTBS1 2 3 144 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU3_2097677531$ PUBLIC UNDOTBS1 3 3 160 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU4_1152005954$ PUBLIC UNDOTBS1 4 3 176 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU5_1527469038$ PUBLIC UNDOTBS1 5 3 192 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU6_2443381498$ PUBLIC UNDOTBS1 6 3 208 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU7_3286610060$ PUBLIC UNDOTBS1 7 3 224 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU8_2012382730$ PUBLIC UNDOTBS1 8 3 240 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU9_1424341975$ PUBLIC UNDOTBS1 9 3 256 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU10_3550978943$ PUBLIC UNDOTBS1 10 3 272 131072 65536 2 32765 NEEDS RECOVERY

Sql > select tablespace_name,status from dba_tablespaces ;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

ANUJTEST ONLINE

TSAPEXF ONLINE

TSAPEXU ONLINE

TEST ONLINE

RMAN ONLINE

DROP1 ONLINE

PERFSTAT ONLINE

13 rows selected.

Sql > select file#,status from v$datafile;

FILE# STATUS

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

1 SYSTEM

2 ONLINE

3 RECOVER

4 ONLINE

5 ONLINE

6 ONLINE

7 ONLINE

8 ONLINE

9 ONLINE

10 ONLINE

11 ONLINE

12 ONLINE

12 rows selected.

Sql > select segment_name, tablespace_name, initial_extent,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME INITIAL_EXTENT STATUS

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

SYSTEM SYSTEM 114688 ONLINE

_SYSSMU10_3550978943$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU9_1424341975$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU8_2012382730$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU7_3286610060$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU6_2443381498$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU5_1527469038$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU4_1152005954$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU3_2097677531$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU2_2232571081$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU1_3780397527$ UNDOTBS1 131072 NEEDS RECOVERY

11 rows selected.

select segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE') ;

Select status ,name,file# from v$datafile where ts# in (Select ts# from v$tablespace where name='UNDOTBS1');

col NAME format a70

Select status ,name,file# from v$datafile where ts# in (Select ts# from v$tablespace where name='UNDOTBS1')

STATUS NAME FILE#

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

RECOVER /opt/app/oracle/oradata/orcl/undotbs01.dbf 3

Select checkpoint_change# from v$datafile_header where file_id=3

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS;

Sql > DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS;

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

ORA-06512: at line 957

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

Sql > show parameters rollback

NAME TYPE VALUE

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

fast_start_parallel_rollback string LOW

rollback_segments string

transactions_per_rollback_segment integer 5

Sql > show parameters undo

NAME TYPE VALUE

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

undo_management string MANUAL

undo_retention integer 900

undo_tablespace string UNDOTBR

Sql > alter system set undo_management=auto scope=spfile;

System altered.

Sql > alter system set undo_management=manual scope=spfile;

System altered.

Sql > startup force;

_corrupted_rollback_segments=('_SYSSMU10_3550978943$','_SYSSMU9_1424341975$','_SYSSMU8_2012382730$','_SYSSMU7_3286610060$','_SYSSMU6_2443381498$','_SYSSMU5_1527469038$','_SYSSMU4_1152005954$','_SYSSMU3_2097677531$','_SYSSMU2_2232571081$','_SYSSMU1_3780397527$')

Sql > create pfile='/tmp/anuj.txt' from spfile;

File created.

Sql > ed /tmp/anuj.txt

drop rollback segment "_SYSSMU10_3550978943$";

create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbR.dbf' size 500m autoextend on ;

drop tablespace UNDOTBS1

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

ORA-06512: at line 957

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

Sql > select segment_name, status, tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME STATUS TABLESPACE_NAME

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

_SYSSMU1_3780397527$ NEEDS RECOVERY UNDOTBS1

_SYSSMU2_2232571081$ NEEDS RECOVERY UNDOTBS1

_SYSSMU3_2097677531$ NEEDS RECOVERY UNDOTBS1

_SYSSMU4_1152005954$ NEEDS RECOVERY UNDOTBS1

_SYSSMU5_1527469038$ NEEDS RECOVERY UNDOTBS1

_SYSSMU6_2443381498$ NEEDS RECOVERY UNDOTBS1

_SYSSMU7_3286610060$ NEEDS RECOVERY UNDOTBS1

_SYSSMU8_2012382730$ NEEDS RECOVERY UNDOTBS1

_SYSSMU9_1424341975$ NEEDS RECOVERY UNDOTBS1

_SYSSMU10_3550978943$ NEEDS RECOVERY UNDOTBS1

10 rows selected.

drop undo segment "_SYSSMU1_3780397527$";

drop rollback segment "_SYSSMU1_3780397527$";

drop tablespace UNDOTBS1 including contents and datafiles;

ALTER TABLESPACE UNDOTBS1 offline ;

ALTER SYSTEM SET undo_tablespace='UNDOTBR' ;

startup pfile='/tmp/anuj.txt' ;

CREATE ROLLBACK SEGMENT rbs_dummy

TABLESPACE system

STORAGE (INITIAL 10k NEXT 10k MINEXTENTS 2);

_corrupted_rollback_segments =('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$',

'_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

_OFFLINE_ROLLBACK_SEGMENTS=('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_S

if you get this error the set

*._system_trig_enabled=false

Sql > DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

ORA-06512: at line 957

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

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

orcl.__db_cache_size=205520896

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=293601280

orcl.__sga_target=549453824

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=322961408

orcl.__streams_pool_size=4194304

*.audit_file_dest='/opt/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/opt/app/oracle/oradata/orcl/control01.ctl','/opt/app/oracle/flash_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain='XXXXX.co.uk'

*.db_name='orcl'

*.db_recovery_file_dest_size=4070572032

*.db_recovery_file_dest=''

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

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.event='10468 trace name context forever, level 4'

*.fast_start_mttr_target=30

*.log_archive_dest='/opt/app/oracle/admin/arch'

*.memory_target=843055104

*.open_cursors=300

*.processes=150

*.recyclebin='OFF'

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace=UNDOTBS2

*.undo_management='MANUAL'

*._offline_rollback_segments=('_SYSSMU10_3550978943$','_SYSSMU9_1424341975$','_SYSSMU8_2012382730$','_SYSSMU7_3286610060$','_SYSSMU6_2443381498$','_SYSSMU5_1527469038$','_SYSSMU4_1152005954$','_SYSSMU3_2097677531$','_SYSSMU2_2232571081$','_SYSSMU1_3780397527$')

*._corrupted_rollback_segments=('_SYSSMU10_3550978943$','_SYSSMU9_1424341975$','_SYSSMU8_2012382730$','_SYSSMU7_3286610060$','_SYSSMU6_2443381498$','_SYSSMU5_1527469038$','_SYSSMU4_1152005954$','_SYSSMU3_2097677531$','_SYSSMU2_2232571081$','_SYSSMU1_3780397527$')

*._system_trig_enabled=false

Sql > startup pfile='/tmp/anuj.txt' ;

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes

Variable Size 629147640 bytes

Database Buffers 205520896 bytes

Redo Buffers 2396160 bytes

Database mounted.

Database opened.

Sql > drop rollback segment "_SYSSMU1_3780397527$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU2_2232571081$";

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU3_2097677531$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU4_1152005954$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU5_1527469038$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU6_2443381498$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU7_3286610060$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU8_2012382730$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU9_1424341975$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU10_3550978943$" ;

Rollback segment dropped.

Sql > select segment_name, status, tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

View Description

V$UNDOSTAT Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo

space required for the current workload. Oracle uses this view information to tune undo usage in the system.

V$ROLLSTAT For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace.

V$TRANSACTION Contains undo segment information.

DBA_UNDO_EXTENTS Shows the status and size of each extent in the undo tablespace.

WRH$_UNDOSTAT Contains statistical snapshots of V$UNDOSTAT information.

WRH$_ROLLSTAT Contains statistical snapshots of V$ROLLSTAT information.

Undo Space = UNDO_RETENTION in seconds * undo blocks for each second + overhead

My alias

alias alias alias +='pushd .' alias -='popd' alias ..='cd ..' alias ...='cd ../..' alias apt-win1='rdesktop -T "apt-winsvr-01 Production" -g 1024x768 apt-winsvr-01' alias aptwin='rdesktop -T "apt-win-01 Production" -g 1024x768 apt-win-01' alias beep='echo -en "\007"' alias cd..='cd ..' alias dir='ls -l | grep '\''^d'\''' alias l='ls -alF' alias la='ls -la' alias ll='ls -l' alias ls='ls $LS_OPTIONS' alias ls-l='ls -l' alias lsd='ls -l | grep '\''^d'\''' alias md='mkdir -p' alias mv='mv -i' alias o='less' alias rd='rmdir' alias rdpapt1='rdesktop -T "Aptus01 Production" -g 1024x768 aptus01' alias rdpapt2='rdesktop -T "Aptus02 Production" -g 1024x768 aptus02' alias rdpapt3='rdesktop -T "Aptus02 Production" -g 1270x950 aptus02' alias rdpcc='rdesktop -T "Windows Mangment - Prod" -g 1024x768 lon-cccwinmgt-01' alias rdpccdr='rdesktop -T "Windows Mangment - Prod" -g 1024x768 lon-cccwinmgt-01dr' alias rdpelmerk='rdesktop -T "Elmerk System" -g 1270x950 66.162.210.164' alias rdpelmerk-int='rdesktop -T "Elmerk Internal System" -g 1270x950 aptvm-elmerk1' alias rdplam='rdesktop -T "Lamentations" -g 1270x950 lamentations' alias rdppublic='rdesktop -T " Public Workstation" -g 1270x950 APT-HUB6430CYD' alias rehash='hash -r' alias rm='rm -i' alias sshcc='ssh -X anujs@lon-cccsolmgt-01' alias sshccdr='ssh -X anujs@lon-cccsolmgt-01dr' alias sshdan='ssh -X root@daniel' alias sshhose='ssh -X root@hosea' alias sshjerm='ssh -X root@jeremiah' alias sshjonah='ssh -X root@jonah' alias sshjosh='ssh -X root@joshua' alias sshjudge='ssh -X root@judges' alias sshlev='ssh -X root@leviticus' alias sshnahum='ssh -X root@nahum' alias sshnovag='ssh -X root@novagenesis' alias sshnumbers='ssh -X root@numbers' alias sshprov='ssh -X root@proverbs' alias sshsol='ssh -X root@solomon' alias sshtest='ssh -X root@joshua-test' alias sshwoogie='ssh -X marko@mail.woogie.nu' alias unmount='echo "Error: Try the command: umount" 1>&2; false' alias you='if test "$EUID" = 0 ; then /sbin/yast2 online_update ; else su - -c "/sbin/yast2 online_update" ; fi'

DBMS_REPAIR SCRIPT (Doc ID 556733.1)

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => '&tablespace_name');
END;
/

set serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'EMP',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

set serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'EMP',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/
number corrupt: 0

PL/SQL procedure successfully completed.

REM Optionally display any corrupted block identified by check_object:

select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION
from REPAIR_TABLE;

REM Mark the identified blocks as corrupted

DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME=> '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));
END;
/

REM Allow future DML statements to skip the corrupted blocks:

BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME => '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.SKIP_FLAG);
END;
/

session_wait_history.sql

SET TIME ON VERIFY OFF

COLUMN event FORMAT A30 HEADING 'Event' TRUNCATE

COLUMN wait_state FORMAT A7 HEADING 'State'

COLUMN seconds_in_wait FORMAT 999999 HEADING 'Time|Waiting'

COLUMN wait_time FORMAT 999999 HEADING 'Time|Waited'

COLUMN p1 FORMAT A20 HEADING 'P1' TRUNCATE

COLUMN p2 FORMAT A20 HEADING 'P2' TRUNCATE

COLUMN p3 FORMAT A20 HEADING 'P3' TRUNCATE

SELECT vswh.event,

CASE WHEN vswh.state = 'WAITING' THEN vswh.state

ELSE 'WAITED'

END wait_state,

vswh.seconds_in_wait,

vswh.wait_time,

DECODE(vswh.p1text, NULL, NULL, vswh.p1text||'='||vswh.p1) p1,

DECODE(vswh.p2text, NULL, NULL, vswh.p2text||'='||vswh.p2) p2,

DECODE(vswh.p3text, NULL, NULL, vswh.p3text||'='||vswh.p3) p3

FROM gv$session_wait_history vswh

WHERE vswh.sid = &&user_sid

ORDER BY vswh.seq# DESC

UNDEFINE user_sid

Saturday, 2 October 2021

RMAN: restore database


Oracle RMAN: restore database   ..


https://anuj-singh.blogspot.com/2010/01/oracle-datafile-need-recovery.html

export TWO_TASK=
export NLS_DATE_FORMAT='dd-mm-yyyy hh:mi:ss'

rman target / | tee rman.log 
run {
  allocate channel ch1 device type DISK;
  allocate channel ch2 device type DISK;
  backup incremental level=0 tag "RMANFULL" format '/u03/RAC/%d_T%T_db_s%s_p%p_t%t' database filesperset 4 plus archivelog tag "RMANFULL";
 sql "alter system archive log current";
  backup format '/u03/RAC/%d_T%T_arch_s%s_p%p_t%t' archivelog all filesperset 4 tag "RMANFULL";
  backup format '/u03/RAC/%d_T%T_cf%s_p%p_t%t' current controlfile tag "RMANFULL";
sql "alter system archive log current";
restore database preview;
}

====

export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/Rman/%F';


rman target / | tee /u01/app/Rman/rman_`/bin/date +\%d\%m\%y:%H:%M:%S`.log

run {
  allocate channel ch1 device type DISK;
    backup incremental level=0 tag "RMANFULL" format '/u01/app/Rman/%d_T%T_db_s%s_p%p_t%t' database filesperset 4 plus archivelog tag "RMANFULL";
 sql "alter system archive log current";
 sql "alter system archive log current";
  backup format '/u01/app/Rman/%d_T%T_arch_s%s_p%p_t%t' archivelog all filesperset 4 tag "RMANFULL";
  backup format '/u01/app/Rman/%d_T%T_cf%s_p%p_t%t' current controlfile tag "RMANFULLCONTRL";
sql "alter system archive log current";
restore database preview;
}

=======

http://anuj-singh.blogspot.com/2016/09/rman-create-set-newname-script.html

http://anuj-singh.blogspot.com/2016/09/rman-create-set-newname-script.html  << for  diffrent location 


 cat initorcl12c.ora  <<< pfile 
orcl12c.__data_transfer_cache_size=0
orcl12c.__db_cache_size=222298112
orcl12c.__java_pool_size=4194304
orcl12c.__large_pool_size=125829120
orcl12c.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl12c.__pga_aggregate_target=293601280
orcl12c.__sga_target=545259520
orcl12c.__shared_io_pool_size=0
orcl12c.__shared_pool_size=184549376
orcl12c.__streams_pool_size=0
*._ash_size=25165824
*._catalog_foreign_restore=FALSE
*.audit_file_dest='/u01/app/oracle/admin/orcl12c/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/orcl12c/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl12c'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl12cXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_ORCL12C'
*.log_archive_dest_1='location=/u01/app/oracle/Archive'
*.memory_target=800m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_servers=25
*.threaded_execution=FALSE
*.undo_tablespace='UNDOTBS2'




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

Total System Global Area  838860800 bytes
Fixed Size                  8798312 bytes
Variable Size             620760984 bytes
Database Buffers          205520896 bytes
Redo Buffers                3780608 bytes
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


[oracle@vbgeneric dbs]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Sat Oct 2 08:14:34 2021

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

connected to target database: ORCL12C (not mounted)



RMAN> RESTORE CONTROLFILE from '/u02/app/oracle/RmanBackup/ORCL12C_20210925_106_1_CONTROL';

Starting restore at 02-10-2021 08:15:20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/orcl12c/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl
Finished restore at 02-10-2021 08:15:23

RMAN> alter database mount ;

Statement processed
released channel: ORA_DISK_1

from web 

set linesize 300 pagesize 100
col inst_id                      for 9999999                 heading 'Instance #'
col file_nr                      for 9999999                 heading 'File #'
col file_name                    for A70                     heading 'File name'
col checkpoint_change_nr         for 99999999999999            heading 'Checkpoint #'
col checkpoint_change_time       for A20                     heading 'Checkpoint time'
col last_change_nr               for 99999999999999          heading 'Last change #'
SELECT
 fe.inst_id,
fe.CON_ID,
 fe.fenum file_nr,
 fn.fnnam file_name,
 TO_NUMBER (fe.fecps) checkpoint_change_nr,
 fe.fecpt checkpoint_change_time,
fe.fests last_change_nr,
 DECODE (
 fe.fetsn,
 0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
 DECODE (BITAND (fe.festa, 18),
         0, 'OFFLINE',
         2, 'ONLINE',
            'RECOVER')
 ) status
FROM x$kccfe fe, x$kccfn fn
WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 )  OR (fe.fepax = 65535 OR fe.fepax = 0) )
 AND fn.fnfno = fe.fenum
 AND fe.fefnh = fn.fnnum
 AND fe.fedup != 0
 AND fn.fntyp = 4
 AND fn.fnnam IS NOT NULL
 AND BITAND (fn.fnflg, 4) != 4
ORDER BY fe.fenum
;


Instance #     CON_ID   File # File name                                                                 Checkpoint # Checkpoint time      Last change #        STATUS
---------- ---------- -------- ---------------------------------------------------------------------- --------------- -------------------- -------------------- -------
         1          1        1 /u01/app/oracle/oradata/orcl12c/system01.dbf                                  16329399 09/25/2021 10:45:45                       SYSTEM
         1          1        3 /u01/app/oracle/oradata/orcl12c/sysaux01.dbf                                  16329399 09/25/2021 10:45:45                       ONLINE
         1          2        5 /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf                          14804862 12/26/2020 06:28:00                       SYSOFF
         1          1        6 /u01/app/oracle/oradata/orcl12c/users01.dbf                                   16329399 09/25/2021 10:45:45                       ONLINE
         1          2        7 /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf                          14804862 12/26/2020 06:28:00                       OFFLINE
         1          3        8 /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf                             16329339 09/25/2021 10:43:20                       SYSTEM
         1          3        9 /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf                             16329339 09/25/2021 10:43:20                       ONLINE
         1          3       10 /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf                16329339 09/25/2021 10:43:20                       ONLINE
         1          3       11 /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf                            16329339 09/25/2021 10:43:20                       ONLINE
         1          3       12 /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf                16329339 09/25/2021 10:43:20                       ONLINE
         1          3       13 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf                16329339 09/25/2021 10:43:20                       ONLINE
         1          1       17 /u01/app/oracle/oradata/orcl12c/undotbs2.dbf                                  16329399 09/25/2021 10:45:45                       ONLINE
         1          1       18 /u01/app/oracle/oradata/orcl12c/apex01.dbf                                    16329399 09/25/2021 10:45:45                       ONLINE
         1          3       19 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf                16329339 09/25/2021 10:43:20                       ONLINE
         1          5       21 /u01/app/oracle/oradata/orcl12c/ANUJ/system01.dbf                             16313846 09/25/2021 09:50:40                       SYSOFF
         1          5       22 /u01/app/oracle/oradata/orcl12c/ANUJ/sysaux01.dbf                             16313846 09/25/2021 09:50:40                       OFFLINE
         1          5       23 /u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf                              16313846 09/25/2021 09:50:40                       OFFLINE
         1          3       34 /u01/app/oracle/oradata/orcl12c/orcl/tts01_.dbf                                7375942 03/26/2018 16:08:23                       ONLINE
         1          5       36 /u01/app/oracle/oradata/orcl12c/ANUJ/tts01_.dbf                                7383435 03/26/2018 16:48:17                       ONLINE

19 rows selected.


set linesize 300 pagesize 100
col inst_id                      for 9999999                 heading 'Instance #'
col file_nr                      for 9999999                 heading 'File #'
col file_name                    for A70                     heading 'File name'
col checkpoint_change_nr         for 99999999999999          heading 'Checkpoint #'
col checkpoint_change_time       for A20                     heading 'Checkpoint time'
col last_change_nr               for 99999999999999          heading 'Last change #'
col SCNStatus for a15
SELECT
 fe.inst_id,
fe.CON_ID,  ---- for >12c
 fe.fenum file_nr,
 fn.fnnam file_name,
 TO_NUMBER (fe.fecps) checkpoint_change_nr,
 fe.fecpt checkpoint_change_time,
fe.fests last_change_nr,
NVL2(fe.fecps, '<-Good', 'Recover to this scn') SCNStatus,  ---- Recover to max 'scn'
 DECODE (
 fe.fetsn,
 0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
 DECODE (BITAND (fe.festa, 18),
         0, 'OFFLINE',
         2, 'ONLINE',
            'RECOVER')
 ) status
FROM x$kccfe fe, x$kccfn fn
WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 )  OR (fe.fepax = 65535 OR fe.fepax = 0) )
 AND fn.fnfno = fe.fenum
 AND fe.fefnh = fn.fnnum
 AND fe.fedup != 0
 AND fn.fntyp = 4
 AND fn.fnnam IS NOT NULL
 AND BITAND (fn.fnflg, 4) != 4
ORDER BY fe.fenum
;

find out file# no 1 and scn no i.e. 16329399 +1 =16329400 




set linesize 300
alter session set nls_date_format='dd-mm-yyyy hh24:mi';

set numf 99999999999999999
select * from v$database_incarnation ;

scn no for restore 16329400 

set linesize 300
alter session set nls_date_format='dd-mm-yyyy hh24:mi';

set numf 99999999999999999
select * from v$database_incarnation where RESETLOGS_CHANGE# in (select max(RESETLOGS_CHANGE#) from v$database_incarnation where RESETLOGS_CHANGE#< 16329400 ) ;


INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME   PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_ STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED     CON_ID
------------ ----------------- ---------------- ----------------------- ---------------- ------- ------------ ------------------ -------------------------- ----------
           4          16210201 25-09-2021 09:05                 8232571 23-08-2020 11:43 PARENT    1084179948                  3 NO                                  0

set to this incarnation
RMAN>
reset database to incarnation 4;
RMAN>

database reset to incarnation 4


run{
set until scn 16329400;
restore database;
RESTORE DATABASE CHECK READONLY;
recover database;
}


RMAN>
run{
set until scn 16329400;
restore database;
RESTORE DATABASE CHECK READONLY;
recover database;
}



RMAN> 

executing command: SET until clause

Starting restore at 02-10-2021 08:35:25
Starting implicit crosscheck backup at 02-10-2021 08:35:25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
Crosschecked 13 objects
Finished implicit crosscheck backup at 02-10-2021 08:35:30

Starting implicit crosscheck copy at 02-10-2021 08:35:30
using channel ORA_DISK_1
Finished implicit crosscheck copy at 02-10-2021 08:35:30

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORCL12C/autobackup/2021_10_02/o1_mf_n_1084857936_joj9dkql_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORCL12C/autobackup/2021_09_26/o1_mf_s_1084268218_jo0xvv3j_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORCL12C/autobackup/2021_09_25/o1_mf_s_1084186180_jnyfr4r1_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORCL12C/autobackup/2021_09_25/o1_mf_s_1084194159_jnyokhxr_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORCL12C/autobackup/2021_09_25/o1_mf_s_1084190659_jnyl445c_.bkp

using channel ORA_DISK_1

skipping datafile 34; already restored to file /u01/app/oracle/oradata/orcl12c/orcl/tts01_.dbf
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 00008 to /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf
channel ORA_DISK_1: restoring datafile 00019 to /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf
channel ORA_DISK_1: reading from backup piece /u02/app/oracle/RmanBackup/ORCL12C_20210925_99_1_FULL
channel ORA_DISK_1: piece handle=/u02/app/oracle/RmanBackup/ORCL12C_20210925_99_1_FULL tag=ORCLEE_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:06:15
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/oracle/oradata/orcl12c/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl12c/users01.dbf
channel ORA_DISK_1: restoring datafile 00017 to /u01/app/oracle/oradata/orcl12c/undotbs2.dbf
channel ORA_DISK_1: restoring datafile 00018 to /u01/app/oracle/oradata/orcl12c/apex01.dbf
channel ORA_DISK_1: reading from backup piece /u02/app/oracle/RmanBackup/ORCL12C_20210925_100_1_FULL
channel ORA_DISK_1: piece handle=/u02/app/oracle/RmanBackup/ORCL12C_20210925_100_1_FULL tag=ORCLEE_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:15
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 00021 to /u01/app/oracle/oradata/orcl12c/ANUJ/system01.dbf
channel ORA_DISK_1: restoring datafile 00022 to /u01/app/oracle/oradata/orcl12c/ANUJ/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00023 to /u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf
channel ORA_DISK_1: restoring datafile 00036 to /u01/app/oracle/oradata/orcl12c/ANUJ/tts01_.dbf
channel ORA_DISK_1: reading from backup piece /u02/app/oracle/RmanBackup/ORCL12C_20210925_101_1_FULL
channel ORA_DISK_1: piece handle=/u02/app/oracle/RmanBackup/ORCL12C_20210925_101_1_FULL tag=ORCLEE_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:45
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 00005 to /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u02/app/oracle/RmanBackup/ORCL12C_20210925_102_1_FULL
channel ORA_DISK_1: piece handle=/u02/app/oracle/RmanBackup/ORCL12C_20210925_102_1_FULL tag=ORCLEE_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 02-10-2021 08:51:18

Starting restore at 02-10-2021 08:51:19
using channel ORA_DISK_1

skipping datafile 34; already restored to file /u01/app/oracle/oradata/orcl12c/orcl/tts01_.dbf
skipping datafile 36; already restored to file /u01/app/oracle/oradata/orcl12c/ANUJ/tts01_.dbf
skipping datafile 1; already restored to file /u01/app/oracle/oradata/orcl12c/system01.dbf
skipping datafile 3; already restored to file /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/orcl12c/users01.dbf
skipping datafile 17; already restored to file /u01/app/oracle/oradata/orcl12c/undotbs2.dbf
skipping datafile 18; already restored to file /u01/app/oracle/oradata/orcl12c/apex01.dbf
skipping datafile 5; already restored to file /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
skipping datafile 7; already restored to file /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
skipping datafile 9; already restored to file /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
skipping datafile 10; already restored to file /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf
skipping datafile 11; already restored to file /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf
skipping datafile 12; already restored to file /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf
skipping datafile 13; already restored to file /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf
skipping datafile 19; already restored to file /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf
skipping datafile 21; already restored to file /u01/app/oracle/oradata/orcl12c/ANUJ/system01.dbf
skipping datafile 22; already restored to file /u01/app/oracle/oradata/orcl12c/ANUJ/sysaux01.dbf
skipping datafile 23; already restored to file /u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 02-10-2021 08:51:20

Starting recover at 02-10-2021 08:51:20
using channel ORA_DISK_1
datafile 34 not processed because file is read-only
datafile 36 not processed because file is read-only

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=11
channel ORA_DISK_1: reading from backup piece /u02/app/oracle/RmanBackup/ORCL12C_20210925_104_1_ARCHIVE
channel ORA_DISK_1: piece handle=/u02/app/oracle/RmanBackup/ORCL12C_20210925_104_1_ARCHIVE tag=ORCLEE_ARCHIVE
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/oracle/Archive/1_11_1084179948.dbf thread=1 sequence=11
media recovery complete, elapsed time: 00:00:00
Finished recover at 02-10-2021 08:51:24

RMAN>

always check path before resetlogs ..  !!!!!!!!!!!!!!!!!imp

col member for a70
select member from v$logfile;

MEMBER
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl12c/redo03.log
/u01/app/oracle/oradata/orcl12c/redo02.log
/u01/app/oracle/oradata/orcl12c/redo01.log


if above path correct ..

alter database open resetlogs;


 select fhdbn,fhdbi,hxfil,fhsta,fhscn,fhafs,fhrba_seq,fhtnm tbs_name from x$kcvfh;

FHDBN          FHDBI      HXFIL      FHSTA FHSCN                FHAFS                 FHRBA_SEQ TBS_NAME
--------- ---------- ---------- ---------- -------------------- -------------------- ---------- ------------------------------
ORCL12C    743937264          1       8196 16329404             0                             1 SYSTEM
ORCL12C    743937264          3          4 16329404             0                             1 SYSAUX
ORCL12C    743937264          5       8192 14804862             0                           459 SYSTEM
ORCL12C    743937264          6          4 16329404             0                             1 USERS
ORCL12C    743937264          7          0 14804862             0                           459 SYSAUX
ORCL12C    743937264          8       8196 16329573             0                             1 SYSTEM
ORCL12C    743937264          9          4 16329573             0                             1 SYSAUX
ORCL12C    743937264         10          4 16329573             0                             1 USERS
ORCL12C    743937264         11          4 16329573             0                             1 EXAMPLE
ORCL12C    743937264         12          4 16329573             0                             1 APEX_1851336378250219
ORCL12C    743937264         13          4 16329573             0                             1 APEX_5457999048253711
ORCL12C    743937264         17          4 16329404             0                             1 UNDOTBS2
ORCL12C    743937264         18          4 16329404             0                             1 APEX
ORCL12C    743937264         19          4 16329573             0                             1 APEX_5500333564645084
ORCL12C    743937264         21       8192 16329560             0                             1 SYSTEM
ORCL12C    743937264         22          0 16329560             0                             1 SYSAUX
ORCL12C    743937264         23          0 16329560             0                             1 USERS
ORCL12C    743937264         34          0 7375942              0                           170 TTS
ORCL12C    743937264         36          0 7383435              0                           170 TTS

19 rows selected.

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

Another restore ..

export TWO_TASK=
export NLS_DATE_FORMAT='dd-mm-yyyy hh:mi:ss'

rman target / | tee rman.log 
run {
  allocate channel ch1 device type DISK;
  allocate channel ch2 device type DISK;
  backup incremental level=0 tag "RMANFULL" format '/u01/app/oracle/RmanBackup/%d_T%T_db_s%s_p%p_t%t' database filesperset 4 plus archivelog tag "RMANFULL";
 sql "alter system archive log current";
  backup format '/u01/app/oracle/RmanBackup/%d_T%T_arch_s%s_p%p_t%t' archivelog all filesperset 4 tag "RMANFULL";
  backup format '/u01/app/oracle/RmanBackup/%d_T%T_cf%s_p%p_t%t' current controlfile tag "RMANFULL";
sql "alter system archive log current";
restore database preview;
}






using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=269 device type=DISK

allocated channel: ch2
channel ch2: SID=33 device type=DISK


Starting backup at 27-12-2022 10:15:24
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=159 RECID=1 STAMP=1124532569
channel ch1: starting piece 1 at 27-12-2022 10:15:26
channel ch2: starting archived log backup set
channel ch2: specifying archived log(s) in backup set
input archived log thread=1 sequence=160 RECID=2 STAMP=1124532925
channel ch2: starting piece 1 at 27-12-2022 10:15:26
channel ch1: finished piece 1 at 27-12-2022 10:15:27
piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_db_s2_p1_t1124532926 tag=RMANFULL comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch2: finished piece 1 at 27-12-2022 10:15:27
piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_db_s3_p1_t1124532926 tag=RMANFULL comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:01
Finished backup at 27-12-2022 10:15:27

Starting backup at 27-12-2022 10:15:27
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00003 name=/oracle/db/oradata/orcl/sysaux01.dbf
input datafile file number=00014 name=/oracle/db/oradata/orcl/dbfs01.dbf
channel ch1: starting piece 1 at 27-12-2022 10:15:29
channel ch2: starting incremental level 0 datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/db/oradata/orcl/system01.dbf
input datafile file number=00005 name=/oracle/db/oradata/orcl/WCPVM_webcenter_portlet.dbf
input datafile file number=00009 name=/oracle/db/oradata/orcl/WCPVM_svctbl.dbf
input datafile file number=00006 name=/oracle/db/oradata/orcl/users01.dbf
channel ch2: starting piece 1 at 27-12-2022 10:15:29
channel ch1: finished piece 1 at 27-12-2022 10:16:04
piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_db_s4_p1_t1124532929 tag=RMANFULL comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:35
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00013 name=/oracle/db/oradata/orcl/WCPVM_iasactivities.dbf
input datafile file number=00007 name=/oracle/db/oradata/orcl/WCPVM_iaswebcenter.dbf
input datafile file number=00011 name=/oracle/db/oradata/orcl/WCPVM_mds.dbf
input datafile file number=00010 name=/oracle/db/oradata/orcl/WCPVM_iau.dbf
channel ch1: starting piece 1 at 27-12-2022 10:16:04
channel ch2: finished piece 1 at 27-12-2022 10:16:04
piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_db_s5_p1_t1124532929 tag=RMANFULL comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:35
channel ch2: starting incremental level 0 datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00012 name=/oracle/db/oradata/orcl/WCPVM_ocs.dbf
input datafile file number=00004 name=/oracle/db/oradata/orcl/undotbs01.dbf
input datafile file number=00008 name=/oracle/db/oradata/orcl/WCPVM_iasjive.dbf
input datafile file number=00002 name=/oracle/db/oradata/orcl/WCPVM_ias_opss.dbf
channel ch2: starting piece 1 at 27-12-2022 10:16:05
channel ch1: finished piece 1 at 27-12-2022 10:16:05
piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_db_s6_p1_t1124532964 tag=RMANFULL comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch2: finished piece 1 at 27-12-2022 10:16:12
piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_db_s7_p1_t1124532965 tag=RMANFULL comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:07
Finished backup at 27-12-2022 10:16:12

Starting backup at 27-12-2022 10:16:13
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=161 RECID=3 STAMP=1124532973
channel ch1: starting piece 1 at 27-12-2022 10:16:13
channel ch1: finished piece 1 at 27-12-2022 10:16:14
piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_db_s8_p1_t1124532973 tag=RMANFULL comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-12-2022 10:16:14

Starting Control File Autobackup at 27-12-2022 10:16:14
piece handle=/u01/app/oracle/Archive/ORCL/autobackup/2022_12_27/o1_mf_n_1124532974_ktpfmh1g_.bkp comment=NONE
Finished Control File Autobackup at 27-12-2022 10:16:15

sql statement: alter system archive log current

Starting backup at 27-12-2022 10:16:16
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=159 RECID=1 STAMP=1124532569
channel ch1: starting piece 1 at 27-12-2022 10:16:19
channel ch2: starting archived log backup set
channel ch2: specifying archived log(s) in backup set
input archived log thread=1 sequence=160 RECID=2 STAMP=1124532925
input archived log thread=1 sequence=161 RECID=3 STAMP=1124532973
input archived log thread=1 sequence=162 RECID=4 STAMP=1124532975
channel ch2: starting piece 1 at 27-12-2022 10:16:19
channel ch1: finished piece 1 at 27-12-2022 10:16:20
piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_arch_s10_p1_t1124532979 tag=RMANFULL comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=163 RECID=5 STAMP=1124532978
channel ch1: starting piece 1 at 27-12-2022 10:16:20
channel ch2: finished piece 1 at 27-12-2022 10:16:20
piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_arch_s11_p1_t1124532979 tag=RMANFULL comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:01
channel ch1: finished piece 1 at 27-12-2022 10:16:21
piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_arch_s12_p1_t1124532980 tag=RMANFULL comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-12-2022 10:16:21

Starting backup at 27-12-2022 10:16:23
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 27-12-2022 10:16:24
channel ch1: finished piece 1 at 27-12-2022 10:16:25
piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_cf13_p1_t1124532983 tag=RMANFULL comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-12-2022 10:16:25

Starting Control File Autobackup at 27-12-2022 10:16:25
piece handle=/u01/app/oracle/Archive/ORCL/autobackup/2022_12_27/o1_mf_n_1124532986_ktpfmt6s_.bkp comment=NONE
Finished Control File Autobackup at 27-12-2022 10:16:27

sql statement: alter system archive log current

Starting restore at 27-12-2022 10:16:27


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
4       Incr 0  910.52M    DISK        00:00:29     27-12-2022 10:15:58
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: RMANFULL
        Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s5_p1_t1124532929
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  1    0  Incr 3351472    27-12-2022 10:15:29              NO    /oracle/db/oradata/orcl/system01.dbf
  5    0  Incr 3351472    27-12-2022 10:15:29              NO    /oracle/db/oradata/orcl/WCPVM_webcenter_portlet.dbf
  6    0  Incr 3351472    27-12-2022 10:15:29              NO    /oracle/db/oradata/orcl/users01.dbf
  9    0  Incr 3351472    27-12-2022 10:15:29              NO    /oracle/db/oradata/orcl/WCPVM_svctbl.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7       Incr 0  79.70M     DISK        00:00:06     27-12-2022 10:16:07
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: RMANFULL
        Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s7_p1_t1124532965
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  2    0  Incr 3351496    27-12-2022 10:16:05              NO    /oracle/db/oradata/orcl/WCPVM_ias_opss.dbf
  4    0  Incr 3351496    27-12-2022 10:16:05              NO    /oracle/db/oradata/orcl/undotbs01.dbf
  8    0  Incr 3351496    27-12-2022 10:16:05              NO    /oracle/db/oradata/orcl/WCPVM_iasjive.dbf
  12   0  Incr 3351496    27-12-2022 10:16:05              NO    /oracle/db/oradata/orcl/WCPVM_ocs.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
5       Incr 0  1000.22M   DISK        00:00:31     27-12-2022 10:16:00
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: RMANFULL
        Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s4_p1_t1124532929
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  3    0  Incr 3351471    27-12-2022 10:15:29              NO    /oracle/db/oradata/orcl/sysaux01.dbf
  14   0  Incr 3351471    27-12-2022 10:15:29              NO    /oracle/db/oradata/orcl/dbfs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
6       Incr 0  33.38M     DISK        00:00:01     27-12-2022 10:16:05
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: RMANFULL
        Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s6_p1_t1124532964
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  7    0  Incr 3351495    27-12-2022 10:16:04              NO    /oracle/db/oradata/orcl/WCPVM_iaswebcenter.dbf
  10   0  Incr 3351495    27-12-2022 10:16:04              NO    /oracle/db/oradata/orcl/WCPVM_iau.dbf
  11   0  Incr 3351495    27-12-2022 10:16:04              NO    /oracle/db/oradata/orcl/WCPVM_mds.dbf
  13   0  Incr 3351495    27-12-2022 10:16:04              NO    /oracle/db/oradata/orcl/WCPVM_iasactivities.dbf

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
3       1    161     A 27-12-2022 10:15:24
        Name: /u01/app/oracle/Archive/ORCL/archivelog/2022_12_27/o1_mf_1_161_ktpfmf3c_.arc

4       1    162     A 27-12-2022 10:16:13
        Name: /u01/app/oracle/Archive/ORCL/archivelog/2022_12_27/o1_mf_1_162_ktpfmhxb_.arc

5       1    163     A 27-12-2022 10:16:15
        Name: /u01/app/oracle/Archive/ORCL/archivelog/2022_12_27/o1_mf_1_163_ktpfml6n_.arc

6       1    164     A 27-12-2022 10:16:18
        Name: /u01/app/oracle/Archive/ORCL/archivelog/2022_12_27/o1_mf_1_164_ktpfmv70_.arc

recovery will be done up to SCN 3351471
Media recovery start SCN is 3351471
Recovery must be done beyond SCN 3351496 to clear datafile fuzziness
Finished restore at 27-12-2022 10:16:28
released channel: ch1
released channel: ch2






[oracle@wcp12cr2 dbs]$ rman target / | tee rman_`/bin/date +\%d\%m\%y:%H:%M:%S`.log

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Dec 27 13:19:02 2022

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

connected to target database (not started)

RMAN> startup nomount ;

Oracle instance started

Total System Global Area    1895825408 bytes

Fixed Size                     8622048 bytes
Variable Size                570425376 bytes
Database Buffers            1308622848 bytes
Redo Buffers                   8155136 bytes

RMAN> restore controlfile from '/u01/app/oracle/RmanBackup/ORCL_T20221227_cf13_p1_t1124532983';

Starting restore at 27-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/Datafile/control01.ctl
Finished restore at 27-DEC-22

RMAN> startup mount ;

database is already started
database mounted
released channel: ORA_DISK_1





set linesize 300 pagesize 100
col inst_id                      for 9999999                 heading 'Instance #'
col file_nr                      for 9999999                 heading 'File #'
col file_name                    for A70                     heading 'File name'
col checkpoint_change_nr         for 99999999999999          heading 'Checkpoint #'
col checkpoint_change_time       for A20                     heading 'Checkpoint time'
col last_change_nr               for 99999999999999          heading 'Last change #'
col SCNStatus for a15
SELECT
 fe.inst_id,
fe.CON_ID,  ---- for >12c
 fe.fenum file_nr,
 fn.fnnam file_name,
 TO_NUMBER (fe.fecps) checkpoint_change_nr,
 fe.fecpt checkpoint_change_time,
fe.fests last_change_nr,
NVL2(fe.fecps, '<-Good', 'Recover to this scn') SCNStatus,  ---- Recover to max 'scn'
 DECODE (
 fe.fetsn,
 0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
 DECODE (BITAND (fe.festa, 18),
         0, 'OFFLINE',
         2, 'ONLINE',
            'RECOVER')
 ) status
FROM x$kccfe fe, x$kccfn fn
WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 )  OR (fe.fepax = 65535 OR fe.fepax = 0) )
 AND fn.fnfno = fe.fenum
 AND fe.fefnh = fn.fnnum
 AND fe.fedup != 0
 AND fn.fntyp = 4
 AND fn.fnnam IS NOT NULL
 AND BITAND (fn.fnflg, 4) != 4
ORDER BY fe.fenum
;






Instance #     CON_ID   File # File name                                                                 Checkpoint # Checkpoint time      Last change # SCNSTATUS       STATUS
---------- ---------- -------- ---------------------------------------------------------------------- --------------- -------------------- -------------------- --------------- -------
         1          0        1 /oracle/db/oradata/orcl/system01.dbf                                           3351530 12/27/2022 10:16:18<-Good          SYSTEM
         1          0        2 /oracle/db/oradata/orcl/WCPVM_ias_opss.dbf                                     3351530 12/27/2022 10:16:18<-Good          ONLINE
         1          0        3 /oracle/db/oradata/orcl/sysaux01.dbf                                           3351530 12/27/2022 10:16:18<-Good          ONLINE
         1          0        4 /oracle/db/oradata/orcl/undotbs01.dbf                                          3351530 12/27/2022 10:16:18<-Good          ONLINE
         1          0        5 /oracle/db/oradata/orcl/WCPVM_webcenter_portlet.dbf                            3351530 12/27/2022 10:16:18<-Good          ONLINE
         1          0        6 /oracle/db/oradata/orcl/users01.dbf                                            3351530 12/27/2022 10:16:18<-Good          ONLINE
         1          0        7 /oracle/db/oradata/orcl/WCPVM_iaswebcenter.dbf                                 3351530 12/27/2022 10:16:18<-Good          ONLINE
         1          0        8 /oracle/db/oradata/orcl/WCPVM_iasjive.dbf                                      3351530 12/27/2022 10:16:18<-Good          ONLINE
         1          0        9 /oracle/db/oradata/orcl/WCPVM_svctbl.dbf                                       3351530 12/27/2022 10:16:18<-Good          ONLINE
         1          0       10 /oracle/db/oradata/orcl/WCPVM_iau.dbf                                          3351530 12/27/2022 10:16:18<-Good          ONLINE
         1          0       11 /oracle/db/oradata/orcl/WCPVM_mds.dbf                                          3351530 12/27/2022 10:16:18<-Good          ONLINE
         1          0       12 /oracle/db/oradata/orcl/WCPVM_ocs.dbf                                          3351530 12/27/2022 10:16:18<-Good          ONLINE
         1          0       13 /oracle/db/oradata/orcl/WCPVM_iasactivities.dbf                                3351530 12/27/2022 10:16:18<-Good          ONLINE
         1          0       14 /oracle/db/oradata/orcl/dbfs01.dbf                                             3351530 12/27/2022 10:16:18<-Good          ONLINE

14 rows selected.

SQL> SQL>







RMAN>

run {
SET NEWNAME FOR DATABASE to '/u01/app/oracle/Datafile/%b';
set newname for tempfile 1 to '/u01/app/oracle/Datafile/temp.dbf';
set until scn 3351530;
restore database;
switch datafile all;
switch tempfile all;
recover database ;
}
RMAN>
RMAN> 

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET until clause

Starting restore at 27-DEC-22
Starting implicit crosscheck backup at 27-DEC-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=258 device type=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 27-DEC-22

Starting implicit crosscheck copy at 27-DEC-22
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-DEC-22

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/Archive/ORCL/archivelog/2022_12_27/o1_mf_1_164_ktpq66td_.arc
File Name: /u01/app/oracle/Archive/ORCL/archivelog/2022_12_27/o1_mf_1_164_ktpfmv70_.arc

using channel ORA_DISK_1

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/oracle/Datafile/system01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/Datafile/WCPVM_webcenter_portlet.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/Datafile/users01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/Datafile/WCPVM_svctbl.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s5_p1_t1124532929
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_db_s5_p1_t1124532929 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 00003 to /u01/app/oracle/Datafile/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/Datafile/dbfs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s4_p1_t1124532929
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_db_s4_p1_t1124532929 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 00007 to /u01/app/oracle/Datafile/WCPVM_iaswebcenter.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/Datafile/WCPVM_iau.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/Datafile/WCPVM_mds.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/Datafile/WCPVM_iasactivities.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s6_p1_t1124532964
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_db_s6_p1_t1124532964 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00002 to /u01/app/oracle/Datafile/WCPVM_ias_opss.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/Datafile/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/Datafile/WCPVM_iasjive.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/Datafile/WCPVM_ocs.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s7_p1_t1124532965
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_db_s7_p1_t1124532965 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 27-DEC-22

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=1124544114 file name=/u01/app/oracle/Datafile/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=1124544114 file name=/u01/app/oracle/Datafile/WCPVM_ias_opss.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=1124544114 file name=/u01/app/oracle/Datafile/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=1124544114 file name=/u01/app/oracle/Datafile/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=1124544114 file name=/u01/app/oracle/Datafile/WCPVM_webcenter_portlet.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=20 STAMP=1124544114 file name=/u01/app/oracle/Datafile/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=21 STAMP=1124544115 file name=/u01/app/oracle/Datafile/WCPVM_iaswebcenter.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=22 STAMP=1124544115 file name=/u01/app/oracle/Datafile/WCPVM_iasjive.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=23 STAMP=1124544115 file name=/u01/app/oracle/Datafile/WCPVM_svctbl.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=24 STAMP=1124544115 file name=/u01/app/oracle/Datafile/WCPVM_iau.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=25 STAMP=1124544115 file name=/u01/app/oracle/Datafile/WCPVM_mds.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=26 STAMP=1124544115 file name=/u01/app/oracle/Datafile/WCPVM_ocs.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=27 STAMP=1124544115 file name=/u01/app/oracle/Datafile/WCPVM_iasactivities.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=28 STAMP=1124544115 file name=/u01/app/oracle/Datafile/dbfs01.dbf

renamed tempfile 1 to /u01/app/oracle/Datafile/temp.dbf in control file
renamed tempfile 2 to /u01/app/oracle/Datafile/WCPVM_iastemp.dbf in control file
renamed tempfile 3 to /u01/app/oracle/Datafile/WCPVM_ocstemp.dbf in control file

Starting recover at 27-DEC-22
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 161 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_27/o1_mf_1_161_ktpfmf3c_.arc
archived log for thread 1 with sequence 162 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_27/o1_mf_1_162_ktpfmhxb_.arc
archived log for thread 1 with sequence 163 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_27/o1_mf_1_163_ktpfml6n_.arc
archived log file name=/u01/app/oracle/Archive/ORCL/archivelog/2022_12_27/o1_mf_1_161_ktpfmf3c_.arc thread=1 sequence=161
archived log file name=/u01/app/oracle/Archive/ORCL/archivelog/2022_12_27/o1_mf_1_162_ktpfmhxb_.arc thread=1 sequence=162
archived log file name=/u01/app/oracle/Archive/ORCL/archivelog/2022_12_27/o1_mf_1_163_ktpfml6n_.arc thread=1 sequence=163
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-DEC-22





SQL> 

set linesize 200
col member for a70
select GROUP#,STATUS,member from v$logfile;SQL> SQL>

    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------------------------------------
         3         /oracle/db/oradata/orcl/redo03.log
         2         /oracle/db/oradata/orcl/redo02.log
         1         /oracle/db/oradata/orcl/redo01.log

SQL>
alter database rename file '/oracle/db/oradata/orcl/redo01.log' to '/u01/app/oracle/Datafile/log01.log';SQL>

Database altered.

SQL>
alter database rename file '/oracle/db/oradata/orcl/redo02.log' to '/u01/app/oracle/Datafile/log02.log';SQL>

Database altered.

SQL>
alter database rename file '/oracle/db/oradata/orcl/redo03.log' to '/u01/app/oracle/Datafile/log03.log';
SQL>
Database altered.




RMAN> alter database open resetlogs;

Statement processed

RMAN> exit



 select GROUP#,STATUS,member from v$logfile;

    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------------------------------------
         3         /u01/app/oracle/Datafile/log03.log
         2         /u01/app/oracle/Datafile/log02.log
         1         /u01/app/oracle/Datafile/log01.log

set linesize 200 pagesize 200
column member            format a50
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#
union 
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$standby_log l
where  l.group# = lf.group#
order by 7;

--With tablespace name

set linesize 300 pagesize 100

col inst_id for 9999999 heading 'Instance #'

col file_nr for 9999999 heading 'File #'

col file_name for A70 heading 'File name'

col checkpoint_change_nr for 99999999999999 heading 'Checkpoint #'

col checkpoint_change_time for A20 heading 'Checkpoint time'

col last_change_nr for 99999999999999 heading 'Last change #'

col SCNStatus for a15

col FNNAM for a70

SELECT

fe.inst_id,

fe.CON_ID, ---- for >12c

tablespace_name,

fe.fenum file_nr,

fn.fnnam file_name,

TO_NUMBER (fe.fecps) checkpoint_change_nr,

fe.fecpt checkpoint_change_time,

fe.fests last_change_nr,

NVL2(fe.fecps, '<-Good', 'Recover to this scn') SCNStatus, ---- Recover to max 'scn'

DECODE (

fe.fetsn,

0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),

DECODE (BITAND (fe.festa, 18),

0, 'OFFLINE',

2, 'ONLINE',

'RECOVER')

) status

-- ,FNNAM

FROM x$kccfe fe, x$kccfn fn,v$datafile_header vh

WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 ) OR (fe.fepax = 65535 OR fe.fepax = 0) )

AND fn.fnfno = fe.fenum

AND fe.fefnh = fn.fnnum

AND fe.fedup != 0

AND fn.fntyp = 4

AND fn.fnnam IS NOT NULL

AND BITAND (fn.fnflg, 4) != 4

and vh.file#=fn.fnfno

ORDER BY fe.fenum

;

Instance # CON_ID TABLESPACE_NAME File # File name Checkpoint # Checkpoint time Last change # SCNSTATUS STATUS

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

1 1 SYSTEM 1 /u01/app/oracle/oradata/ORCLCDB/system01.dbf 4196453 12/31/2022 05:31:37 <-Good SYSTEM

1 1 SYSAUX 3 /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf 4196453 12/31/2022 05:31:37 <-Good ONLINE

1 2 SYSTEM 5 /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf 2163739 05/31/2019 15:32:31 2163739 <-Good SYSOFF

1 2 SYSAUX 6 /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf 2163739 05/31/2019 15:32:31 2163739 <-Good OFFLINE

1 1 USERS 7 /u01/app/oracle/oradata/ORCLCDB/users01.dbf 4196453 12/31/2022 05:31:37 <-Good ONLINE

1 2 UNDOTBS1 8 /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf 2163739 05/31/2019 15:32:31 2163739 <-Good OFFLINE

1 3 SYSTEM 9 /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf 4196453 12/31/2022 05:31:37 <-Good SYSTEM

1 3 SYSAUX 10 /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf 4196453 12/31/2022 05:31:37 <-Good ONLINE

1 3 USERS 12 /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf 4196453 12/31/2022 05:31:37 <-Good ONLINE

1 3 APEX_1291597703607401 13 /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf 4196453 12/31/2022 05:31:37 <-Good ONLINE

1 1 UNDOTBS2 14 /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf 4196453 12/31/2022 05:31:37 <-Good ONLINE

1 3 UNDOTBS2 15 /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf 4196453 12/31/2022 05:31:37 <-Good ONLINE

1 4 16 /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf 4078427 12/30/2022 17:42:42 <-Good SYSOFF

1 4 17 /u01/app/oracle/oradata/ORCLCDB/orcld/sysaux01.dbf 4078427 12/30/2022 17:42:42 4079972 <-Good OFFLINE

1 4 18 /u01/app/oracle/oradata/ORCLCDB/orcld/undotbs01.dbf 4078427 12/30/2022 17:42:42 4079972 <-Good OFFLINE

1 4 19 /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf 4078427 12/30/2022 17:42:42 4079972 <-Good OFFLINE

16 rows selected.

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

export NLS_DATE_FORMAT='dd-mm-yyyy hh:mi:ss'

rman target / | tee /home/oracle/rman_`/bin/date +\%d\%m\%y:%H:%M:%S`.log

RMAN> restore controlfile from '/u01/app/oracle/RmanBackup/ORCL_T20221227_cf13_p1_t1124532983';

Starting restore at 28-12-2022 02:17:03

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=237 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/Datafile/control01.ctl

Finished restore at 28-12-2022 02:17:07

RMAN> startup mount ;

database is already started

database mounted

released channel: ORA_DISK_1

to find SCN No

set linesize 300 pagesize 100

col inst_id for 9999999 heading 'Instance #'

col file_nr for 9999999 heading 'File #'

col file_name for A70 heading 'File name'

col checkpoint_change_nr for 99999999999999 heading 'Checkpoint #'

col checkpoint_change_time for A20 heading 'Checkpoint time'

col last_change_nr for 99999999999999 heading 'Last change #'

col SCNStatus for a15

SELECT

fe.inst_id,

fe.CON_ID, ---- for >12c

fe.fenum file_nr,

fn.fnnam file_name,

TO_NUMBER (fe.fecps) checkpoint_change_nr,

fe.fecpt checkpoint_change_time,

fe.fests last_change_nr,

NVL2(fe.fecps, '<-Good', 'Recover to this scn') SCNStatus, ---- Recover to max 'scn'

DECODE (

fe.fetsn,

0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),

DECODE (BITAND (fe.festa, 18),

0, 'OFFLINE',

2, 'ONLINE',

'RECOVER')

) status

FROM x$kccfe fe, x$kccfn fn

WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 ) OR (fe.fepax = 65535 OR fe.fepax = 0) )

AND fn.fnfno = fe.fenum

AND fe.fefnh = fn.fnnum

AND fe.fedup != 0

AND fn.fntyp = 4

AND fn.fnnam IS NOT NULL

AND BITAND (fn.fnflg, 4) != 4

ORDER BY fe.fenum

;

RMAN>

run {

SET NEWNAME FOR DATABASE to '/u01/app/oracle/Datafile/%b';

set newname for tempfile 1 to '/u01/app/oracle/Datafile/temp.dbf';

set until scn 3351530;

restore database;

switch datafile all;

switch tempfile all;

recover database ;

}

define 1='/u01/app/oracle/Datafile/'

set linesize 200

col file_name for a150

select 'ALTER DATABASE RENAME FILE ''' ||member|| ''' to '||'''&1'|| substr(member,instr(member,'/',-1)+1, instr(substr(member,instr(member,'/',-1)+1),'.')-1 )||'.log' ||''' ;' file_name

from V$logfile;

FILE_NAME

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

ALTER DATABASE RENAME FILE '/oracle/db/oradata/orcl/redo03.log' to '/u01/app/oracle/Datafile/redo03.log' ;

ALTER DATABASE RENAME FILE '/oracle/db/oradata/orcl/redo02.log' to '/u01/app/oracle/Datafile/redo02.log' ;

ALTER DATABASE RENAME FILE '/oracle/db/oradata/orcl/redo01.log' to '/u01/app/oracle/Datafile/redo01.log' ;

RMAN> alter database open resetlogs;

Statement processed

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

from

https://smarttechways.com/2020/06/23/incomplete-recovery-script-with-sequence-and-time-which-generate-set-until-statement-for-rman/

Incomplete recovery script with sequence and time which generate set until statement for RMAN

col inputgb for 9999.99

col outputgb for 9999.99

col time for 9999.99

col start_Time for a21

col end_time for a21

col status for a10

select session_key,session_recid,session_stamp, to_char(start_time, 'DD-MON-YYYY HH24:MI:SS') Start_time,

to_char(END_Time, 'DD-MON-YYYY HH24:MI:SS') END_Time, input_bytes/(1024*1024*1024) as "InputGB", output_bytes/(1024*1024*1024) as "outputGB",

status,elapsed_seconds/60/60 as "time" from v$rman_backup_job_details order by 1;

SESSION_KEY SESSION_RECID SESSION_STAMP START_TIME END_TIME InputGB outputGB STATUS time

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

2 2 1124532910 27-DEC-2022 10:15:24 27-DEC-2022 10:16:24 2.81 2.08 COMPLETED .02

select 'set until sequence ' || seq# || ' thread ' || thread# || '; ' || chr(13)|| chr(10) || 'recover database ;' "Recover Command"

from (

select * from (

select thread#, sequence# + 1 seq#, next_change# from (

select * from v$backup_archivelog_details

where thread# || '_' || sequence# in

(select thread# || '_' || max(sequence#) from v$backup_archivelog_details group by thread#)

) order by next_change#

) where rownum = 1 ) ;

Recover Command

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

set until sequence 164 thread 1;

recover database ;

col "Restore Command" for a100

select ' restore archivelog from logseq ' || Min_arch_backup.startNo || ' until logseq ' || Max_arch_backup.endNo || ' thread=' || Max_arch_backup.thread# || ';' "Restore Command"

from

(select thread#,session_key,session_recid,session_stamp,min(sequence#) startNo from v$backup_archivelog_details group by thread#,session_key,session_recid,session_stamp) Min_arch_backup,

(select thread#,session_key,session_recid,session_stamp, max(sequence#) endNo from v$backup_archivelog_details group by thread#,session_key,session_recid,session_stamp) Max_arch_backup

where Min_arch_backup.thread# = Max_arch_backup.thread# and Min_arch_backup.session_key = Max_arch_backup.session_key

and Min_arch_backup.session_recid = Max_arch_backup.session_recid

and Min_arch_backup.session_stamp = Max_arch_backup.session_stamp

;

Restore Command

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

restore archivelog from logseq 159 until logseq 163 thread=1;

col NEXT_CHANGE# heading "*NEXT_CHANGE#*"

select session_key,session_recid,session_stamp,thread#,FIRST_CHANGE#,NEXT_CHANGE# ,Sequence# "LastArchiveLogBackupSequence#",to_char(next_time,'DD-MON-YYYY HH24:MI:SS') "Time"

from v$backup_archivelog_details

where thread# || '_' || sequence# in (select thread# || '_' || max(sequence#)

from v$backup_archivelog_details

group by thread#

);

SESSION_KEY SESSION_RECID SESSION_STAMP THREAD# FIRST_CHANGE# *NEXT_CHANGE#* LastArchiveLogBackupSequence# Time

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

2 2 1124532910 1 3351522 3351530 163 27-DEC-2022 10:16:18

--For last 16 Sequence time detail

select * from (

select session_key,session_recid,session_stamp,thread#,FIRST_CHANGE#,NEXT_CHANGE#, Sequence# "LastArchiveLogBackupSequence#",

to_char(next_time,'DD-MON-YYYY HH24:MI:SS') "Time" from v$backup_archivelog_details order by sequence# desc)

where rownum <= 16;

SESSION_KEY SESSION_RECID SESSION_STAMP THREAD# FIRST_CHANGE# NEXT_CHANGE# LastArchiveLogBackupSequence# Time

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

2 2 1124532910 1 3351522 3351530 163 27-DEC-2022 10:16:18

2 2 1124532910 1 3351504 3351522 162 27-DEC-2022 10:16:15

2 2 1124532910 1 3351464 3351504 161 27-DEC-2022 10:16:13

2 2 1124532910 1 3350945 3351464 160 27-DEC-2022 10:15:24

2 2 1124532910 1 3331357 3350945 159 27-DEC-2022 10:09:28

8 rows selected.

==========

RMAN> startup nomount ;

Oracle instance started

Total System Global Area 1895825408 bytes

Fixed Size 8622048 bytes

Variable Size 570425376 bytes

Database Buffers 1308622848 bytes

Redo Buffers 8155136 bytes

RMAN> restore controlfile from '/u01/app/oracle/RmanBackup/ORCL_T20221227_cf13_p1_t1124532983';

Starting restore at 29-12-2022 04:08:28

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=237 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/Datafile/control01.ctl

Finished restore at 29-12-2022 04:08:31

RMAN> alter database mount;

Statement processed

released channel: ORA_DISK_1

RMAN> list backup ;

List of Backup Sets

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

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

1 Full 9.80M DISK 00:00:00 27-12-2022 10:13:32

BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20221227T101332

Piece Name: /u01/app/oracle/Archive/ORCL/autobackup/2022_12_27/o1_mf_n_1124532812_ktpfgdmn_.bkp

Control File Included: Ckp SCN: 3351072 Ckp time: 27-12-2022 10:13:32

BS Key Size Device Type Elapsed Time Completion Time

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

2 6.31M DISK 00:00:00 27-12-2022 10:15:26

BP Key: 2 Status: AVAILABLE Compressed: NO Tag: RMANFULL

Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s3_p1_t1124532926

List of Archived Logs in backup set 2

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 160 3350945 27-12-2022 10:09:28 3351464 27-12-2022 10:15:24

BS Key Size Device Type Elapsed Time Completion Time

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

3 46.44M DISK 00:00:01 27-12-2022 10:15:27

BP Key: 3 Status: AVAILABLE Compressed: NO Tag: RMANFULL

Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s2_p1_t1124532926

List of Archived Logs in backup set 3

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 159 3331357 26-12-2022 08:29:03 3350945 27-12-2022 10:09:28

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

4 Incr 0 910.52M DISK 00:00:29 27-12-2022 10:15:58

BP Key: 4 Status: AVAILABLE Compressed: NO Tag: RMANFULL

Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s5_p1_t1124532929

List of Datafiles in backup set 4

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

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

1 0 Incr 3351472 27-12-2022 10:15:29 NO /oracle/db/oradata/orcl/system01.dbf

5 0 Incr 3351472 27-12-2022 10:15:29 NO /oracle/db/oradata/orcl/WCPVM_webcenter_portlet.dbf

6 0 Incr 3351472 27-12-2022 10:15:29 NO /oracle/db/oradata/orcl/users01.dbf

9 0 Incr 3351472 27-12-2022 10:15:29 NO /oracle/db/oradata/orcl/WCPVM_svctbl.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

5 Incr 0 1000.22M DISK 00:00:31 27-12-2022 10:16:00

BP Key: 5 Status: AVAILABLE Compressed: NO Tag: RMANFULL

Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s4_p1_t1124532929

List of Datafiles in backup set 5

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

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

3 0 Incr 3351471 27-12-2022 10:15:29 NO /oracle/db/oradata/orcl/sysaux01.dbf

14 0 Incr 3351471 27-12-2022 10:15:29 NO /oracle/db/oradata/orcl/dbfs01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

6 Incr 0 33.38M DISK 00:00:01 27-12-2022 10:16:05

BP Key: 6 Status: AVAILABLE Compressed: NO Tag: RMANFULL

Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s6_p1_t1124532964

List of Datafiles in backup set 6

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

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

7 0 Incr 3351495 27-12-2022 10:16:04 NO /oracle/db/oradata/orcl/WCPVM_iaswebcenter.dbf

10 0 Incr 3351495 27-12-2022 10:16:04 NO /oracle/db/oradata/orcl/WCPVM_iau.dbf

11 0 Incr 3351495 27-12-2022 10:16:04 NO /oracle/db/oradata/orcl/WCPVM_mds.dbf

13 0 Incr 3351495 27-12-2022 10:16:04 NO /oracle/db/oradata/orcl/WCPVM_iasactivities.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

7 Incr 0 79.70M DISK 00:00:06 27-12-2022 10:16:11

BP Key: 7 Status: AVAILABLE Compressed: NO Tag: RMANFULL

Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s7_p1_t1124532965

List of Datafiles in backup set 7

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

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

2 0 Incr 3351496 27-12-2022 10:16:05 NO /oracle/db/oradata/orcl/WCPVM_ias_opss.dbf

4 0 Incr 3351496 27-12-2022 10:16:05 NO /oracle/db/oradata/orcl/undotbs01.dbf

8 0 Incr 3351496 27-12-2022 10:16:05 NO /oracle/db/oradata/orcl/WCPVM_iasjive.dbf

12 0 Incr 3351496 27-12-2022 10:16:05 NO /oracle/db/oradata/orcl/WCPVM_ocs.dbf

BS Key Size Device Type Elapsed Time Completion Time

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

8 40.00K DISK 00:00:00 27-12-2022 10:16:13

BP Key: 8 Status: AVAILABLE Compressed: NO Tag: RMANFULL

Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_db_s8_p1_t1124532973

List of Archived Logs in backup set 8

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 161 3351464 27-12-2022 10:15:24 3351504 27-12-2022 10:16:13

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

9 Full 9.80M DISK 00:00:01 27-12-2022 10:16:15

BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20221227T101614

Piece Name: /u01/app/oracle/Archive/ORCL/autobackup/2022_12_27/o1_mf_n_1124532974_ktpfmh1g_.bkp

Control File Included: Ckp SCN: 3351513 Ckp time: 27-12-2022 10:16:14

BS Key Size Device Type Elapsed Time Completion Time

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

10 6.35M DISK 00:00:00 27-12-2022 10:16:19

BP Key: 10 Status: AVAILABLE Compressed: NO Tag: RMANFULL

Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_arch_s11_p1_t1124532979

List of Archived Logs in backup set 10

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 160 3350945 27-12-2022 10:09:28 3351464 27-12-2022 10:15:24

1 161 3351464 27-12-2022 10:15:24 3351504 27-12-2022 10:16:13

1 162 3351504 27-12-2022 10:16:13 3351522 27-12-2022 10:16:15

BS Key Size Device Type Elapsed Time Completion Time

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

11 46.44M DISK 00:00:00 27-12-2022 10:16:19

BP Key: 11 Status: AVAILABLE Compressed: NO Tag: RMANFULL

Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_arch_s10_p1_t1124532979

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 159 3331357 26-12-2022 08:29:03 3350945 27-12-2022 10:09:28

BS Key Size Device Type Elapsed Time Completion Time

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

12 2.50K DISK 00:00:00 27-12-2022 10:16:20

BP Key: 12 Status: AVAILABLE Compressed: NO Tag: RMANFULL

Piece Name: /u01/app/oracle/RmanBackup/ORCL_T20221227_arch_s12_p1_t1124532980

List of Archived Logs in backup set 12

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 163 3351522 27-12-2022 10:16:15 3351530 27-12-2022 10:16:18 -----<<<<<<recover to this time -1

--- '27-12-2022 10:16:17','DD-MM-YYYY HH24:MI:SS'

set until time="to_date('2021/08/11 02:55:48', 'yyyy/mm/dd hh24:mi:ss')";

restore database;

recover database;

run {

SET NEWNAME FOR DATABASE to '/u01/app/oracle/Datafile/%U';

set newname for tempfile 1 to '/u01/app/oracle/Datafile/temp.dbf';

set until time "to_date('27-12-2022 10:16:17','DD-MM-YYYY HH24:MI:SS')";

restore database;

switch datafile all;

switch tempfile all;

recover database ;

}

RMAN>

run {

SET NEWNAME FOR DATABARMAN> 2> SE to '/u01/app/oracle/Datafile/%U';

set newname for tempfile 1 to '/u01/app/oracle/Datafile/temp.dbf';

set until time "to_date('27-12-2022 10:16:17','DD-MM-YYYY HH24:MI:SS')";

restore database;

switch datafile all;

switch tempfile all;

recover database ;

}3> 4> 5> 6> 7> 8> 9>

executing command: SET NEWNAME

executing command: SET until clause

Starting restore at 29-12-2022 04:09:31

Starting implicit crosscheck backup at 29-12-2022 04:09:31

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=258 device type=DISK

Crosschecked 12 objects

Finished implicit crosscheck backup at 29-12-2022 04:09:34

Starting implicit crosscheck copy at 29-12-2022 04:09:34

using channel ORA_DISK_1

Finished implicit crosscheck copy at 29-12-2022 04:09:34

searching for all files in the recovery area

cataloging files...

no files cataloged

using channel ORA_DISK_1

skipping datafile 1; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1

skipping datafile 5; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5

skipping datafile 6; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6

skipping datafile 9; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9

skipping datafile 2; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2

skipping datafile 4; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4

skipping datafile 8; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8

skipping datafile 12; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12

skipping datafile 3; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3

skipping datafile 14; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14

skipping datafile 7; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7

skipping datafile 10; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10

skipping datafile 11; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11

skipping datafile 13; already restored to file /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13

restore not done; all files read only, offline, excluded, or already restored

Finished restore at 29-12-2022 04:09:36

datafile 1 switched to datafile copy

input datafile copy RECID=15 STAMP=1124683776 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1

datafile 2 switched to datafile copy

input datafile copy RECID=16 STAMP=1124683776 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2

datafile 3 switched to datafile copy

input datafile copy RECID=17 STAMP=1124683776 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3

datafile 4 switched to datafile copy

input datafile copy RECID=18 STAMP=1124683776 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4

datafile 5 switched to datafile copy

input datafile copy RECID=19 STAMP=1124683776 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5

datafile 6 switched to datafile copy

input datafile copy RECID=20 STAMP=1124683776 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6

datafile 7 switched to datafile copy

input datafile copy RECID=21 STAMP=1124683776 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7

datafile 8 switched to datafile copy

input datafile copy RECID=22 STAMP=1124683776 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8

datafile 9 switched to datafile copy

input datafile copy RECID=23 STAMP=1124683777 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9

datafile 10 switched to datafile copy

input datafile copy RECID=24 STAMP=1124683777 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10

datafile 11 switched to datafile copy

input datafile copy RECID=25 STAMP=1124683777 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11

datafile 12 switched to datafile copy

input datafile copy RECID=26 STAMP=1124683777 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12

datafile 13 switched to datafile copy

input datafile copy RECID=27 STAMP=1124683777 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13

datafile 14 switched to datafile copy

input datafile copy RECID=28 STAMP=1124683777 file name=/u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14

renamed tempfile 1 to /u01/app/oracle/Datafile/temp.dbf in control file

renamed tempfile 2 to /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_TEMP_FNO-2 in control file

renamed tempfile 3 to /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_TEMP_FNO-3 in control file

Starting recover at 29-12-2022 04:09:38

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=161

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=162

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_T20221227_arch_s11_p1_t1124532979

channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_arch_s11_p1_t1124532979 tag=RMANFULL

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/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_161_ktv0w4wb_.arc thread=1 sequence=161

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_161_ktv0w4wb_.arc RECID=6 STAMP=1124683780

archived log file name=/u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_162_ktv0w4xq_.arc thread=1 sequence=162

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_162_ktv0w4xq_.arc RECID=7 STAMP=1124683780

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=163

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_T20221227_arch_s12_p1_t1124532980

channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221227_arch_s12_p1_t1124532980 tag=RMANFULL

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/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_163_ktv0w6sl_.arc thread=1 sequence=163

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_163_ktv0w6sl_.arc RECID=8 STAMP=1124683782

media recovery complete, elapsed time: 00:00:00

Finished recover at 29-12-2022 04:09:44

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

--- Rename redo logs

define 1='/u01/app/oracle/Datafile/' -----<<<<< chnage your path

set linesize 200

col file_name for a150

select 'ALTER DATABASE RENAME FILE ''' ||member|| ''' to '||'''&1'|| substr(member,instr(member,'/',-1)+1, instr(substr(member,instr(member,'/',-1)+1),'.')-1 )||'.log' ||''' ;' file_name

from V$logfile;

ALTER DATABASE RENAME FILE '/oracle/db/oradata/orcl/redo03.log' to '/u01/app/oracle/Datafile/redo03.log'

ALTER DATABASE RENAME FILE '/oracle/db/oradata/orcl/redo02.log' to '/u01/app/oracle/Datafile/redo02.log' ;

ALTER DATABASE RENAME FILE '/oracle/db/oradata/orcl/redo01.log' to '/u01/app/oracle/Datafile/redo01.log' ;

Database altered.

SQL>

Database altered.

SQL>

Database altered.

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

RMAN> alter database open resetlogs;

Statement processed

RMAN>

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

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

as per metalink RMAN: RAC Backup, Restore and Recovery using RMAN (Doc ID 243760.1)

===

Oracle Rac database restore !!!!!

RMAN> set dbid 1222414252;

executing command: SET DBID

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initibrac1.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area 1073741824 bytes

Fixed Size 8628936 bytes

Variable Size 369100088 bytes

Database Buffers 687865856 bytes

Redo Buffers 8146944 bytes

RMAN>

restore spfile !!!

RMAN>

run

{

allocate channel d1 type disk;

set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/dumps/IBRAC/%F';

Restore spfile from autobackup;

Release channel d1;

}

RMAN> 2> 3> 4> 5> 6> 7>

released channel: ORA_DISK_1

allocated channel: d1

channel d1: SID=993 device type=DISK

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 28-08-2023 07:31:38

channel d1: looking for AUTOBACKUP on day: 20230828

channel d1: AUTOBACKUP found: /dumps/IBRAC/c-1222414252-20230828-00

channel d1: restoring spfile from AUTOBACKUP /dumps/IBRAC/c-1222414252-20230828-00

channel d1: SPFILE restore from AUTOBACKUP complete

Finished restore at 28-08-2023 07:31:40

released channel: d1

RMAN>

====

run

{

allocate channel d1 type disk;

set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/dumps/IBRAC/%F';

Restore CONTROLFILE from autobackup;

Release channel d1;

}

RMAN>

run

{

allocate channel d1 type disk;

set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/dumps/IBRAC/%F';

Restore CONTROLFILE from autobackup;

Release channel d1;

}

RMAN> 2> 3> 4> 5> 6> 7>

allocated channel: d1

channel d1: SID=993 device type=DISK

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 28-08-2023 07:37:32

channel d1: looking for AUTOBACKUP on day: 20230828

channel d1: AUTOBACKUP found: /dumps/IBRAC/c-1222414252-20230828-00

channel d1: restoring control file from AUTOBACKUP /dumps/IBRAC/c-1222414252-20230828-00

channel d1: control file restore from AUTOBACKUP complete

output file name=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/cntrlibrac1.dbf

Finished restore at 28-08-2023 07:37:33

released channel: d1

-rw-r----- 1 oracle oinstall 5632 Aug 28 07:31 spfileibrac1.ora

-rw-r----- 1 oracle oinstall 20430848 Aug 28 07:37 cntrlibrac1.dbf

/u01/app/oracle/product/12.2.0/dbhome_1/dbs/cntrlibrac1.dbf

modify pfile with above control file !!!!!

startup mount pfile='initibrac1.ora';

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size 8628936 bytes

Variable Size 1677722936 bytes

Database Buffers 2600468480 bytes

Redo Buffers 8146944 bytes

Database mounted.

SQL> SQL>

SQL>

select thread#,max(sequence#) sequence# from v$archived_log L, v$database D

where L.resetlogs_change# = D.resetlogs_change#

group by thread#

;

THREAD# SEQUENCE#

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

1 13

2 24

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

set numf 99999999999999 linesize 300

select sequence#,NEXT_TIME, thread#, first_change#, next_change# from v$archived_log L, v$database D

where L.resetlogs_change# = D.resetlogs_change#

and sequence# in (13,24);

SEQUENCE# NEXT_TIME THREAD# FIRST_CHANGE# NEXT_CHANGE#

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

13 26-08-2023 22:30:04 2 2771303261 2771622222

13 28-08-2023 01:00:04 1 2791076362 2793397863 +1 *(243760.1) In a RAC environment, use the lower to ensure we have the redo required from threads

24 28-08-2023 01:00:05 2 2793157493 2793398098

all in one sql ...

set numf 99999999999999

select (min(next_change# )+1) MINSCN from (select thread#,sequence#, first_change#, next_change# from v$archived_log L, v$database D

where L.resetlogs_change# = D.resetlogs_change#

and (thread#,sequence#) in (select thread#,max(sequence#) sequence# from v$archived_log L, v$database D

where L.resetlogs_change# = D.resetlogs_change#

group by thread#

)

;

MINSCN

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

2793397864

set numf 99999999999999 pagesize 0

select 'set until scn ' ||(min(next_change# )+1) ||';'

from (select thread#,sequence#, first_change#, next_change# from v$archived_log L, v$database D

where L.resetlogs_change# = D.resetlogs_change#

and (thread#,sequence#) in (select thread#,max(sequence#) sequence# from v$archived_log L, v$database D

where L.resetlogs_change# = D.resetlogs_change#

group by thread#

)

;

set until scn 2793397864;

restore database !!!

run {

set until scn 2793397864;

Restore Database;

Recover Database;

Alter Database Open Resetlogs;

}

RMAN>

RMAN> 2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 28-08-2023 07:52:50

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=737 instance=ibrac1 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 +DATA/IBRAC/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to +DATA/IBRAC/apex_data01.dbf

channel ORA_DISK_1: restoring datafile 00003 to +DATA/IBRAC/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00004 to +DATA/IBRAC/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00005 to +DATA/IBRAC/undotbs02.dbf

channel ORA_DISK_1: restoring datafile 00007 to +DATA/IBRAC/users01.dbf

channel ORA_DISK_1: restoring datafile 00008 to +DATA/IBRAC/test_data01.dbf

channel ORA_DISK_1: restoring datafile 00009 to +DATA/IBRAC/test_data_ind_01.dbf

channel ORA_DISK_1: restoring datafile 00010 to +DATA/IBRAC/DATAFILE/lobtest.319.1145681515

channel ORA_DISK_1: restoring datafile 00011 to +DATA/IBRAC/DATAFILE/test_uniform.3791.1145681597

channel ORA_DISK_1: restoring datafile 00012 to +DATA/IBRAC/DATAFILE/test.3794.1145681591

channel ORA_DISK_1: restoring datafile 00013 to +DATA/IBRAC/DATAFILE/test1.3789.1145681597

channel ORA_DISK_1: restoring datafile 00014 to +DATA/IBRAC/DATAFILE/test1.3786.1145681597

channel ORA_DISK_1: restoring datafile 00015 to +DATA/IBRAC/DATAFILE/bigtabs.3754.1145681517

channel ORA_DISK_1: restoring datafile 00016 to +DATA/IBRAC/test_data02.dbf

channel ORA_DISK_1: reading from backup piece /dumps/IBRAC/20230827_ibrac1_1838_1_1145926871

channel ORA_DISK_1: piece handle=/dumps/IBRAC/20230827_ibrac1_1838_1_1145926871 tag=TAG20230827T010111

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:07:15

Finished restore at 28-08-2023 08:00:06

Starting recover at 28-08-2023 08:00:06

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 11 is already on disk as file +DATA/IBRAC/ONLINELOG/group_5.3741.1145682259

archived log for thread 1 with sequence 12 is already on disk as file +DATA/IBRAC/ONLINELOG/group_6.3736.1145682259

archived log for thread 1 with sequence 13 is already on disk as file +DATA/IBRAC/ONLINELOG/group_1.3735.1145682261

archived log for thread 1 with sequence 14 is already on disk as file +DATA/IBRAC/ARCHIVELOG/2023_08_28/thread_1_seq_14.3714.1146035535

archived log for thread 2 with sequence 22 is already on disk as file +DATA/IBRAC/ONLINELOG/group_4.3745.1145682259

archived log for thread 2 with sequence 23 is already on disk as file +DATA/IBRAC/ONLINELOG/group_7.3740.1145682259

archived log for thread 2 with sequence 24 is already on disk as file +DATA/IBRAC/ONLINELOG/group_8.3737.1145682259

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=10

channel ORA_DISK_1: restoring archived log

archived log thread=2 sequence=16

channel ORA_DISK_1: reading from backup piece /dumps/IBRAC/20230827_ibrac1_1840_1_1145927134

channel ORA_DISK_1: piece handle=/dumps/IBRAC/20230827_ibrac1_1840_1_1145927134 tag=TAG20230827T010534

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=+DATA/IBRAC/ARCHIVELOG/2023_08_28/thread_1_seq_10.281.1146038409 thread=1 sequence=10

archived log file name=+DATA/IBRAC/ARCHIVELOG/2023_08_28/thread_2_seq_16.3717.1146038409 thread=2 sequence=16

archived log file name=+DATA/IBRAC/ONLINELOG/group_5.3741.1145682259 thread=1 sequence=11

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=2 sequence=17

channel ORA_DISK_1: restoring archived log

archived log thread=2 sequence=18

channel ORA_DISK_1: restoring archived log

archived log thread=2 sequence=19

channel ORA_DISK_1: restoring archived log

archived log thread=2 sequence=20

channel ORA_DISK_1: restoring archived log

archived log thread=2 sequence=21

channel ORA_DISK_1: reading from backup piece /dumps/IBRAC/20230828_ibrac1_1842_1_1146013207

channel ORA_DISK_1: piece handle=/dumps/IBRAC/20230828_ibrac1_1842_1_1146013207 tag=TAG20230828T010007

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:05

archived log file name=+DATA/IBRAC/ARCHIVELOG/2023_08_28/thread_2_seq_17.4021.1146038411 thread=2 sequence=17

archived log file name=+DATA/IBRAC/ARCHIVELOG/2023_08_28/thread_2_seq_18.3725.1146038411 thread=2 sequence=18

archived log file name=+DATA/IBRAC/ARCHIVELOG/2023_08_28/thread_2_seq_19.3730.1146038411 thread=2 sequence=19

archived log file name=+DATA/IBRAC/ONLINELOG/group_6.3736.1145682259 thread=1 sequence=12

archived log file name=+DATA/IBRAC/ARCHIVELOG/2023_08_28/thread_2_seq_20.5052.1146038411 thread=2 sequence=20

archived log file name=+DATA/IBRAC/ARCHIVELOG/2023_08_28/thread_2_seq_21.3722.1146038411 thread=2 sequence=21

archived log file name=+DATA/IBRAC/ONLINELOG/group_4.3745.1145682259 thread=2 sequence=22

archived log file name=+DATA/IBRAC/ONLINELOG/group_1.3735.1145682261 thread=1 sequence=13

archived log file name=+DATA/IBRAC/ONLINELOG/group_7.3740.1145682259 thread=2 sequence=23

archived log file name=+DATA/IBRAC/ONLINELOG/group_8.3737.1145682259 thread=2 sequence=24

archived log file name=+DATA/IBRAC/ARCHIVELOG/2023_08_28/thread_1_seq_14.3714.1146035535 thread=1 sequence=14

media recovery complete, elapsed time: 00:00:20

Finished recover at 28-08-2023 08:01:36

Statement processed

SQL> startup nomount pfile='initibrac1.ora';

ORA-32006: UTL_FILE_DIR initialization parameter has been deprecated

ORACLE instance started.

restore controlfile to '+DATA' from '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/cntrlibrac1.dbf';

RMAN>

restore controlfile to '+DATA' from '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/cntrlibrac1.dbf';

RMAN>

Starting restore at 28-08-2023 08:05:04

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1831 instance=ibrac1 device type=DISK

channel ORA_DISK_1: copied control file copy

Finished restore at 28-08-2023 08:05:05

find -t CONTROLFILE +DATA/IBRAC *

ASMCMD [+DATA/IBRAC/CONTROLFILE] >

find -t CONTROLFILE +DATA/IBRAC *ASMCMD [+DATA/IBRAC/CONTROLFILE] >

WARNING:option 't' is deprecated for 'find'

please use 'type'

+DATA/IBRAC/CONTROLFILE/current.3989.1145681321

+DATA/IBRAC/CONTROLFILE/current.4022.1146038705

ASMCMD [+DATA/IBRAC/CONTROLFILE] > ls -l +DATA/IBRAC/CONTROLFILE/current.4022.1146038705

Type Redund Striped Time Sys Name

CONTROLFILE UNPROT FINE AUG 28 08:00:00 Y current.4022.1146038705

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

File created.

-rw-r----- 1 oracle oinstall 5632 Aug 28 08:31 spfileibrac1.ora

create spfile='+DATA' from pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initibrac1.ora';

create spfile='+DATA' from pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initibrac1.ora';SQL> SQL>

File created.

ASMCMD [+] > find + spfile*

+DATA/GARBAGE/PARAMETERFILE/spfile.269.966963741

+DATA/IBRAC/PARAMETERFILE/spfile.274.1146040391

ASMCMD [+] > ls -l +DATA/IBRAC/PARAMETERFILE/spfile.274.1146040391

Type Redund Striped Time Sys Name

PARAMETERFILE UNPROT COARSE AUG 28 08:00:00 Y spfile.274.1146040391

ASMCMD [+] >

[grid@ibrac01 ~]$ date

Mon Aug 28 08:36:39 EDT 2023

$srvctl config database -d ibrac

Database unique name: ibrac

Database name:

Oracle home: /u01/app/oracle/product/12.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/IBRAC/PARAMETERFILE/spfile.274.1146040391

Password file: +DATA/IBRAC/PASSWORD/pwdibrac.4019.1145693377

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: ibrac1,ibrac2

Configured nodes: ibrac01,ibrac02

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

asmcmd ls -l +DATA/IBRAC/PARAMETERFILE/spfile.274.1146040391

Type Redund Striped Time Sys Name

PARAMETERFILE UNPROT COARSE AUG 28 08:00:00 Y spfile.274.1146040391

if required ..

srvctl modify database -d ibrac -spfile +DATA/IBRAC/PARAMETERFILE/spfile.274.1146040391

$srvctl status database -d ibrac -v

Instance ibrac1 is running on node ibrac01. Instance status: Open.

Instance ibrac2 is running on node ibrac02. Instance status: Open.

======

RMAN>

List of Archived Logs in backup set 51

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 55 2263162 19-10-2023 23:55:02 2263171 19-10-2023 23:55:02

1 56 2263171 19-10-2023 23:55:02 2263179 19-10-2023 23:55:03 +1

set until time "to_date('19-10-2023 23:55:04','DD-MM-YYYY HH24:MI:SS')";

run

{

set until time "to_date('19-10-2023 23:55:04','DD-MM-YYYY HH24:MI:SS')";

set newname for database to '/oracle/db/oradata1/orcl/%b';

restore database;

SWITCH DATAFILE ALL;

SWITCH TEMPFILE ALL;

recover database;

alter database open resetlogs;

}

RMAN>

run

{

set until time "to_date('19-10-2023 23:55:04','DD-MM-YYYY HH24:MI:SS')";

set newname for database to '/oracle/db/oradata1/orcl/%b';

restore database;

SWITCH DATAFILE ALL;

SWITCH TEMPFILE ALL;

recover database;

alter database open resetlogs;

}

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10>

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 20-10-2023 04:33:14

Starting implicit crosscheck backup at 20-10-2023 04:33:14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=248 device type=DISK

Crosschecked 52 objects

Finished implicit crosscheck backup at 20-10-2023 04:33:14

Starting implicit crosscheck copy at 20-10-2023 04:33:14

using channel ORA_DISK_1

Crosschecked 2 objects

Finished implicit crosscheck copy at 20-10-2023 04:33:14

searching for all files in the recovery area

cataloging files...

no files cataloged

using channel ORA_DISK_1

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 /oracle/db/oradata1/orcl/system01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /oracle/db/oradata1/orcl/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /oracle/db/oradata1/orcl/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /oracle/db/oradata1/orcl/users01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/rman_bkp_inc_ORCL_1150610386_10_1_0a299qui_1_1

channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/rman_bkp_inc_ORCL_1150610386_10_1_0a299qui_1_1 tag=ORCL_FULL

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:05

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 00002 to /oracle/db/oradata1/orcl/WCPVM_ias_opss.dbf

channel ORA_DISK_1: restoring datafile 00005 to /oracle/db/oradata1/orcl/WCPVM_webcenter_portlet.dbf

channel ORA_DISK_1: restoring datafile 00007 to /oracle/db/oradata1/orcl/WCPVM_iaswebcenter.dbf

channel ORA_DISK_1: restoring datafile 00008 to /oracle/db/oradata1/orcl/WCPVM_iasjive.dbf

channel ORA_DISK_1: restoring datafile 00009 to /oracle/db/oradata1/orcl/WCPVM_svctbl.dbf

channel ORA_DISK_1: restoring datafile 00010 to /oracle/db/oradata1/orcl/WCPVM_iau.dbf

channel ORA_DISK_1: restoring datafile 00011 to /oracle/db/oradata1/orcl/WCPVM_mds.dbf

channel ORA_DISK_1: restoring datafile 00012 to /oracle/db/oradata1/orcl/WCPVM_ocs.dbf

channel ORA_DISK_1: restoring datafile 00013 to /oracle/db/oradata1/orcl/WCPVM_iasactivities.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/rman_bkp_inc_ORCL_1150610451_11_1_0b299r0j_1_1

channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/rman_bkp_inc_ORCL_1150610451_11_1_0b299r0j_1_1 tag=ORCL_FULL

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 20-10-2023 04:34:35

datafile 1 switched to datafile copy

input datafile copy RECID=15 STAMP=1150691675 file name=/oracle/db/oradata1/orcl/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=16 STAMP=1150691675 file name=/oracle/db/oradata1/orcl/WCPVM_ias_opss.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=17 STAMP=1150691675 file name=/oracle/db/oradata1/orcl/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=18 STAMP=1150691675 file name=/oracle/db/oradata1/orcl/undotbs01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=19 STAMP=1150691675 file name=/oracle/db/oradata1/orcl/WCPVM_webcenter_portlet.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=20 STAMP=1150691675 file name=/oracle/db/oradata1/orcl/users01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=21 STAMP=1150691675 file name=/oracle/db/oradata1/orcl/WCPVM_iaswebcenter.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=22 STAMP=1150691675 file name=/oracle/db/oradata1/orcl/WCPVM_iasjive.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=23 STAMP=1150691675 file name=/oracle/db/oradata1/orcl/WCPVM_svctbl.dbf

datafile 10 switched to datafile copy

input datafile copy RECID=24 STAMP=1150691675 file name=/oracle/db/oradata1/orcl/WCPVM_iau.dbf

datafile 11 switched to datafile copy

input datafile copy RECID=25 STAMP=1150691675 file name=/oracle/db/oradata1/orcl/WCPVM_mds.dbf

datafile 12 switched to datafile copy

input datafile copy RECID=26 STAMP=1150691675 file name=/oracle/db/oradata1/orcl/WCPVM_ocs.dbf

datafile 13 switched to datafile copy

input datafile copy RECID=27 STAMP=1150691675 file name=/oracle/db/oradata1/orcl/WCPVM_iasactivities.dbf

renamed tempfile 1 to /oracle/db/oradata1/orcl/temp01.dbf in control file

renamed tempfile 2 to /oracle/db/oradata1/orcl/WCPVM_iastemp.dbf in control file

renamed tempfile 3 to /oracle/db/oradata1/orcl/WCPVM_ocstemp.dbf in control file

Starting recover at 20-10-2023 04:34:36

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /oracle/db/oradata1/orcl/system01.dbf

destination for restore of datafile 00002: /oracle/db/oradata1/orcl/WCPVM_ias_opss.dbf

destination for restore of datafile 00004: /oracle/db/oradata1/orcl/undotbs01.dbf

destination for restore of datafile 00005: /oracle/db/oradata1/orcl/WCPVM_webcenter_portlet.dbf

destination for restore of datafile 00008: /oracle/db/oradata1/orcl/WCPVM_iasjive.dbf

destination for restore of datafile 00010: /oracle/db/oradata1/orcl/WCPVM_iau.dbf

destination for restore of datafile 00013: /oracle/db/oradata1/orcl/WCPVM_iasactivities.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/rman_bkp_inc_ORCL_1150610538_13_1_0d299r3a_1_1

channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/rman_bkp_inc_ORCL_1150610538_13_1_0d299r3a_1_1 tag=ORCL_FULL

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00003: /oracle/db/oradata1/orcl/sysaux01.dbf

destination for restore of datafile 00006: /oracle/db/oradata1/orcl/users01.dbf

destination for restore of datafile 00007: /oracle/db/oradata1/orcl/WCPVM_iaswebcenter.dbf

destination for restore of datafile 00009: /oracle/db/oradata1/orcl/WCPVM_svctbl.dbf

destination for restore of datafile 00011: /oracle/db/oradata1/orcl/WCPVM_mds.dbf

destination for restore of datafile 00012: /oracle/db/oradata1/orcl/WCPVM_ocs.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/rman_bkp_inc_ORCL_1150610538_14_1_0e299r3a_1_1

channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/rman_bkp_inc_ORCL_1150610538_14_1_0e299r3a_1_1 tag=ORCL_FULL