Oracle User's Table Name and with column Name

list of user's table
list of schema table


spool table.sql
select 'prompt '||'Table Name =>'||table_name ||'
desc '||owner||'.'||table_name from dba_tables where owner='XYZ' order by table_name
spool off

@table.sql

Oracle set dispaly

DISPLAY not set. Please set the DISPLAY and try again.


[root@anuj]# yum install xorg*

[root@anuj]# yum groupinstall "X Window System"



[root@apt-bobj-01 lib]# yum install xorg-x11*
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.netrino.co.uk
* extras: mirror.netrino.co.uk
* updates: mirror.netrino.co.uk
Setting up Install Process
Package 1:xorg-x11-twm-1.0.3-5.1.el6.i686 already installed and latest version
Package xorg-x11-drv-intel-devel-2.11.0-7.el6.i686 already installed and latest version
Package xorg-x11-fonts-ISO8859-9-100dpi-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-drv-s3virge-1.10.4-1.1.el6.i686 already installed and latest version
Package xorg-x11-drv-voodoo-1.2.3-1.1.el6.i686 already installed and latest version
Package xorg-x11-drv-ati-firmware-6.13.0-6.el6.noarch already installed and latest version
Package xorg-x11-drv-openchrome-0.2.904-1.el6.i686 already installed and latest version
Package 1:xorg-x11-xdm-1.1.6-14.1.el6.i686 already installed and latest version
Package xorg-x11-drv-ati-6.13.0-6.el6.i686 already installed and latest version
Package 1:xorg-x11-font-utils-7.2-10.el6.i686 already installed and latest version
Package xorg-x11-drv-vesa-2.3.0-1.el6.i686 already installed and latest version
Package xorg-x11-server-Xephyr-1.7.7-26.el6_0.3.i686 already installed and latest version
Package xorg-x11-fonts-misc-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-drv-i740-1.3.2-1.1.el6.i686 already installed and latest version
Package xorg-x11-util-macros-1.4.1-1.el6.noarch already installed and latest version
Package xorg-x11-drv-openchrome-devel-0.2.904-1.el6.i686 already installed and latest version
Package xorg-x11-server-Xdmx-1.7.7-26.el6_0.3.i686 already installed and latest version
Package xorg-x11-drv-mach64-6.8.2-1.1.el6.i686 already installed and latest version
Package 1:xorg-x11-xauth-1.0.2-7.1.el6.i686 already installed and latest version
Package xorg-x11-drv-dummy-0.3.3-1.el6.i686 already installed and latest version
Package xorg-x11-fonts-ISO8859-2-100dpi-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-xbitmaps-1.0.1-9.1.el6.i686 already installed and latest version
Package xorg-x11-drv-tdfx-1.4.3-1.1.el6.i686 already installed and latest version
Package xorg-x11-drivers-7.3-13.2.el6.i686 already installed and latest version
Package xorg-x11-docs-1.3-6.1.el6.noarch already installed and latest version
Package xorg-x11-drv-vmware-10.16.7-2.1.el6.i686 already installed and latest version
Package xorg-x11-fonts-ISO8859-14-75dpi-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-server-Xnest-1.7.7-26.el6_0.3.i686 already installed and latest version
Package xorg-x11-drv-i128-1.3.3-1.1.el6.i686 already installed and latest version
Package xorg-x11-drv-glint-1.2.4-1.1.el6.i686 already installed and latest version
Package xorg-x11-drv-synaptics-devel-1.2.1-5.el6.i686 already installed and latest version
Package xorg-x11-fonts-75dpi-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-drv-evdev-2.3.2-8.el6.i686 already installed and latest version
Package xorg-x11-fonts-Type1-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-drv-savage-2.3.1-1.1.el6.i686 already installed and latest version
Package xorg-x11-xtrans-devel-1.2.2-4.1.el6.noarch already installed and latest version
Package xorg-x11-drv-trident-1.3.3-1.1.el6.i686 already installed and latest version
Package xorg-x11-fonts-ISO8859-1-75dpi-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-drv-aiptek-1.3.0-2.el6.i686 already installed and latest version
Package xorg-x11-drv-geode-2.11.4.1-1.el6.i686 already installed and latest version
Package xorg-x11-drv-apm-1.2.2-1.1.el6.i686 already installed and latest version
Package xorg-x11-drv-vmmouse-12.6.5-3.el6.i686 already installed and latest version
Package xorg-x11-drv-fbdev-0.4.2-1.el6.i686 already installed and latest version
Package xorg-x11-server-Xvfb-1.7.7-26.el6_0.3.i686 already installed and latest version
Package xorg-x11-drv-elographics-1.2.3-5.el6.i686 already installed and latest version
Package xorg-x11-drv-synaptics-1.2.1-5.el6.i686 already installed and latest version
Package xorg-x11-apps-7.4-10.el6.i686 already installed and latest version
Package xorg-x11-drv-fpit-1.3.0-5.el6.i686 already installed and latest version
Package xorg-x11-drv-mutouch-1.2.1-5.el6.i686 already installed and latest version
Package xorg-x11-fonts-ethiopic-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-server-utils-7.4-15.el6_0.2.i686 already installed and latest version
Package xorg-x11-drv-keyboard-1.4.0-3.el6.i686 already installed and latest version
Package xorg-x11-drv-siliconmotion-1.7.3-1.1.el6.i686 already installed and latest version
Package xorg-x11-drv-neomagic-1.2.4-2.el6.i686 already installed and latest version
Package xorg-x11-drv-wacom-devel-0.10.5-6.el6_0.2.i686 already installed and latest version
Package xorg-x11-server-Xorg-1.7.7-26.el6_0.3.i686 already installed and latest version
Package xorg-x11-server-source-1.7.7-26.el6_0.3.noarch already installed and latest version
Package xorg-x11-fonts-ISO8859-9-75dpi-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-drv-cirrus-1.3.2-1.1.el6.i686 already installed and latest version
Package xorg-x11-drv-sis-0.10.2-1.1.el6.i686 already installed and latest version
Package xorg-x11-fonts-ISO8859-2-75dpi-7.2-9.1.el6.noarch already installed and latest version
Package 1:xorg-x11-drv-nouveau-0.0.16-8.20100423git13c1043.el6.i686 already installed and latest version
Package xorg-x11-drv-r128-6.8.1-2.el6.i686 already installed and latest version
Package xorg-x11-proto-devel-7.4-35.el6.noarch already installed and latest version
Package xorg-x11-drv-evdev-devel-2.3.2-8.el6.i686 already installed and latest version
Package xorg-x11-drv-ast-0.89.9-1.1.el6.i686 already installed and latest version
Package xorg-x11-drv-mouse-1.5.0-4.el6.i686 already installed and latest version
Package xorg-x11-fonts-cyrillic-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-fonts-ISO8859-15-100dpi-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-drv-wacom-0.10.5-6.el6_0.2.i686 already installed and latest version
Package xorg-x11-xkb-extras-7.4-6.el6.i686 already installed and latest version
Package xorg-x11-drv-v4l-0.2.0-3.el6.3.i686 already installed and latest version
Package xorg-x11-xinit-session-1.0.9-13.el6.i686 already installed and latest version
Package xorg-x11-server-devel-1.7.7-26.el6_0.3.i686 already installed and latest version
Package xorg-x11-drv-qxl-0.0.12-2.1.el6_0.1.i686 already installed and latest version
Package xorg-x11-drv-mga-1.4.12-2.el6.i686 already installed and latest version
Package xorg-x11-fonts-ISO8859-14-100dpi-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-fonts-ISO8859-15-75dpi-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-fonts-100dpi-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-drv-void-1.3.0-4.el6.i686 already installed and latest version
Package xorg-x11-xinit-1.0.9-13.el6.i686 already installed and latest version
Package xorg-x11-drv-penmount-1.4.0-5.el6.i686 already installed and latest version
Package xorg-x11-drv-rendition-4.2.2-4.1.el6.i686 already installed and latest version
Package xorg-x11-drv-hyperpen-1.3.0-4.el6.i686 already installed and latest version
Package xorg-x11-drv-acecad-1.4.0-2.el6.i686 already installed and latest version
Package xorg-x11-utils-7.4-8.el6.i686 already installed and latest version
Package xorg-x11-drv-nv-2.1.15-4.el6.i686 already installed and latest version
Package xorg-x11-xkb-utils-7.4-6.el6.i686 already installed and latest version
Package xorg-x11-drv-intel-2.11.0-7.el6.i686 already installed and latest version
Package xorg-x11-fonts-ISO8859-1-100dpi-7.2-9.1.el6.noarch already installed and latest version
Package xorg-x11-drv-sisusb-0.9.3-1.1.el6.i686 already installed and latest version
Package xorg-x11-server-common-1.7.7-26.el6_0.3.i686 already installed and latest version

libXp.so.6: cannot open shared object file at time of ./runInstaller -ignoreSysPrereqs

/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory occurred..
/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory



rpm --query --whatprovides 'libXp.so.6'


rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep libXp


Install this rpm

yum install libXp

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


Cause

RHEL4 uses Xorg package
Although RHEL4 no longer uses the XFree86 package, OUI 10g still requires the libXp.so.6 library.

Remark:
This note is not an addition to the following note:
Note 339510.1 - Requirements for Installing Oracle 10gR2 RDBMS on RHEL 4 on AMD/EM64T
It is merely a reminder that OUI depends on default RedHat RPM's that are required to be installed as part of a default RedHat AS/ES 4 installation!

Solution

1) Check Note:169706.1 to make sure that all of the required packages have been installed, including XFree86-libs-4.3.0-78.EL.i386.rpm

2) If the problem still occurs, install package xorg-x11-deprecated-libs-6.8.2-1.EL.13.20.i386.rpm which also contains the libXp.so.6 library

3) Run the runInstaller

RMAN restore database from SNAPSHOT CONTROLFILE

CONFIGURE SNAPSHOT CONTROLFILE NAME

================================================
in my case this is the location ...
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/app/oracle/product/11.2/dbs/snapcf_orcl.f'; # default

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

RMAN> connect target /

connected to target database (not started)

RMAN> startup force nomount pfile='/tmp/spfileorcl.ora';

Oracle instance started

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes
Variable Size 608176120 bytes
Database Buffers 213909504 bytes
Redo Buffers 14979072 bytes

RMAN> restore controlfile from '/opt/app/oracle/product/11.2/dbs/snapcf_orcl.f' ;

Starting restore at 24-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/opt/app/oracle/oradata/orcl/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 24-JUL-11




RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1



RMAN> exit


Recovery Manager complete.
oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 24 05:27:12 2011

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


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> select group#, first_change#, status, archived from v$log;

GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
4 12374116 CURRENT NO
6 0 UNUSED YES
5 0 UNUSED YES

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jul 24 05:28:15 2011

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

RMAN> connect target /

connected to target database: ORCL (DBID=1267852645, not open)

RMAN> restore database;

Starting restore at 24-JUL-11
Starting implicit crosscheck backup at 24-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 24-JUL-11

Starting implicit crosscheck copy at 24-JUL-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 24-JUL-11

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

List of Cataloged Files
=======================
File Name: /opt/app/oracle/flash_recovery_area/ORCL/autobackup/2011_07_23/o1_mf_s_757265596_72otfxny_.bkp
File Name: /opt/app/oracle/flash_recovery_area/ORCL/autobackup/2011_07_23/o1_mf_n_757269308_72oy1z1v_.bkp
File Name: /opt/app/oracle/flash_recovery_area/ORCL/autobackup/2011_07_23/o1_mf_n_757280782_72p98jvx_.bkp
File Name: /opt/app/oracle/flash_recovery_area/ORCL/autobackup/2011_07_24/o1_mf_n_757315149_72qbthrt_.bkp

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 /opt/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/orcl/anujtest.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/app/oracle/oradata/orcl/tsapexf01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /opt/app/oracle/oradata/orcl/tsapexu01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /opt/app/oracle/oradata/orcl/test.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/app/oracle/oradata/orcl/rman.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/databasefiles_ORCL_0lmi5sv0_21_20110723
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/databasefiles_ORCL_0lmi5sv0_21_20110723 tag=TAG20110723T152936
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:55
Finished restore at 24-JUL-11

RMAN> recover database;

Starting recover at 24-JUL-11
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/24/2011 05:32:02
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 12374116

RMAN> alter database open resetlogs;

database opened

RMAN> exit


Recovery Manager complete.
oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 24 05:35:09 2011

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


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> select group#, first_change#, status, archived from v$log;

GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
4 12374117 CURRENT NO
5 0 UNUSED YES
6 0 UNUSED YES

SQL>

RMAN restore spfile

RMAN> connect target /

connected to target database (not started)

RMAN> >STARTUP FORCE NOMOUNT


RMAN> STARTUP FORCE NOMOUNT ;

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


RMAN> restore spfile to pfile '/tmp/spfileorcl.ora' from '/home/oracle/RmanBackup/spfile_ORCL_20_20110723.bak' ;

Starting restore at 23-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/RmanBackup/spfile_ORCL_20_20110723.bak
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 23-JUL-11

RMAN restore database

RMAN full restore/recover
RMAN recovery
RMAN Restore Database


RMAN> connect target /

connected to target database (not started)



start up the database with pfile

RMAN> startup force nomount pfile='/opt/app/oracle/product/11.2/dbs/initorcl.ora';

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

Backup file ....


-rw-r----- 1 oracle oinstall 98304 2011-07-23 15:29 spfile_ORCL_20_20110723.bak
-rw-r----- 1 oracle oinstall 469909504 2011-07-23 15:33 databasefiles_ORCL_0lmi5sv0_21_20110723
-rw-r----- 1 oracle oinstall 1130496 2011-07-23 15:33 controlfile_ORCL_0mmi5t5q_22_20110723



restore controlfile from '/home/oracle/RmanBackup/controlfile_ORCL_0mmi5t5q_22_20110723';

----


RMAN> restore controlfile from '/home/oracle/RmanBackup/controlfile_ORCL_0mmi5t5q_22_20110723';

Starting restore at 23-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/oracle/oradata/orcl/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 23-JUL-11



RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


RMAN> list backup;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13 Full 80.00K DISK 00:00:00 23-JUL-11
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: SPFILE
Piece Name: /home/oracle/RmanBackup/spfile_ORCL_20_20110723.bak
SPFILE Included: Modification time: 23-JUL-11
SPFILE db_unique_name: ORCL

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Full 448.13M DISK 00:03:30 23-JUL-11
BP Key: 16 Status: AVAILABLE Compressed: YES Tag: TAG20110723T152936
Piece Name: /home/oracle/RmanBackup/databasefiles_ORCL_0lmi5sv0_21_20110723
List of Datafiles in backup set 14
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/system01.dbf
2 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/sysaux01.dbf
3 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/undotbs01.dbf
4 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/users01.dbf
5 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/example01.dbf
6 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/anujtest.dbf
7 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/tsapexf01.dbf
8 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/tsapexu01.dbf
9 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/test.dbf
10 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/rman.dbf

RMAN> restore database;

Starting restore at 23-JUL-11
Starting implicit crosscheck backup at 23-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 23-JUL-11

Starting implicit crosscheck copy at 23-JUL-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 23-JUL-11

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

List of Cataloged Files
=======================
File Name: /opt/app/oracle/flash_recovery_area/ORCL/autobackup/2011_07_23/o1_mf_s_757265596_72otfxny_.bkp

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 /opt/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/orcl/anujtest.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/app/oracle/oradata/orcl/tsapexf01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /opt/app/oracle/oradata/orcl/tsapexu01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /opt/app/oracle/oradata/orcl/test.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/app/oracle/oradata/orcl/rman.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/databasefiles_ORCL_0lmi5sv0_21_20110723
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/databasefiles_ORCL_0lmi5sv0_21_20110723 tag=TAG20110723T152936
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:55
Finished restore at 23-JUL-11



RMAN> recover database;

Starting recover at 23-JUL-11
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=509
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/23/2011 16:28:17
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 509 and starting SCN of 12374114


so we have to restore the database upto 12374114 -1 = 12374113




RMAN>

run {
set until scn 12374113;
recover database;
}



RMAN> run {
set until scn 12374113;
recover database;
}
2> 3> 4>
executing command: SET until clause

Starting recover at 23-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/23/2011 16:30:09
RMAN-06556: datafile 1 must be restored from backup older than SCN 12374113


then try again

12374113 -1 =12374112



run {
set until scn 12374112;
recover database;
}


RMAN> run {
set until scn 12374112;
recover database;
}2> 3> 4>

executing command: SET until clause

Starting recover at 23-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/23/2011 16:31:20
RMAN-06556: datafile 1 must be restored from backup older than SCN 12374112



no luck ... then I did resetlogs


RMAN> alter database open resetlogs;

database opened


RMAN> exit





sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 23 16:35:57 2011

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


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> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN



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

GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
4 12374115 CURRENT NO
5 0 UNUSED YES
6 0 UNUSED YES


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

umount2: Device or resource busy

mount point busy
device is busy

not able to unmount unix

apt-amd-02:~ # umount -f /opt
umount2: Device or resource busy
umount: /opt: device is busy.
(In some cases useful info about processes that use
the device is found by lsof(8) or fuser(1))
umount2: Device or resource busy

find the process through lsof

apt-amd-02:~ # lsof /opt <<<<<<<<<<<<-------
lsof: WARNING: can't stat() fuse.gvfs-fuse-daemon file system /home/anujs/.gvfs
Output information may be incomplete.
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
kdm 1828 root txt REG 253,1 156904 83982271 /opt/kde3/bin/kdm
kdm 2022 root txt REG 253,1 156904 83982271 /opt/kde3/bin/kdm
acroread 5322 root mem REG 253,1 11664616 117462946 /opt/kde3/share/icons/hicolor/icon-theme.cache
bash 6061 root cwd DIR 253,1 4096 94473498 /opt/app/oracle/product/10.2/db10g/agent10g
bash 24434 root cwd DIR 253,1 4096 33666700 /opt/app/oracle/oraInventory/logs
firefox 31691 anujs mem REG 253,1 11664616 117462946 /opt/kde3/share/icons/hicolor/icon-theme.cache


kill all the process


apt-amd-02:~ # kill -9 1828 2022 5322 6061 24434 31691

apt-amd-02:~ # umount -f /opt
apt-amd-02:~ # mount /opt
apt-amd-02:~ # df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 10G 8.7G 1.4G 87% /
devtmpfs 1003M 452K 1003M 1% /dev
tmpfs 1005M 16K 1005M 1% /dev/shm
/dev/sda1 69M 59M 5.9M 91% /boot
/dev/mapper/system-home
42G 24G 17G 59% /home
/dev/mapper/system-opt
20G 14G 7.0G 66% /opt

Oracle delete ADDM advisory

delete ADDM report advice
ADDM report delete


spool delete.sql
select 'exec dbms_advisor.delete_task(''' || TASK_NAME || ''' )' ||';' from dba_advisor_tasks;

spool off

will delete all the advisory

sql>@delete


apt-rdbms-01.:APTDB\sys> l
1 select 'exec dbms_advisor.delete_task(''' || TASK_NAME || ''' )' ||';' from dba_advisor_tasks
2* rows selected.

Oracle script maintain with comment

sql script comment
sql script maintenance
good example from http://blog.tanelpoder.com/?s=snapper to maintain the . sql script


put a line like this in the sql script

-- Purpose: Shows table, column, index, stats for a table

and you will get result like this

oracle@apt-lnxtst-01:~> grep -i Purpose: *.sql | awk -F: '{ printf("%20s %-50s\n", $1, $3) }'
roll1.sql to get the average writez of a rollback segment extent
table_stat2.sql Shows table, column, index, stats for a table

Oracle Table statistics report

Oracle Table statistics report

Table stat report



from web ...




set echo off feed off
set serveroutput on size 1000000

accept ownname prompt 'Owner : '
accept tabname prompt 'Table : '

set lines 80

define v_desc = '&ownname..&tabname.'
desc &v_desc
set lines 200

declare
v_query varchar2(4000) ;
v_owner varchar2(30) := upper('&&ownname');
v_table varchar2(30) := upper('&&tabname');
v_max_colname number ;
v_max_ndv number ;
v_max_nulls number ;
v_max_bkts number ;
v_max_smpl number ;
v_max_endnum number ;
v_max_endval number ;
v_ct number ;
prev_col varchar2(30) ;


cursor col_stats is
select a.column_name, nvl(a.last_analyzed,to_date('01/01/1900','mm/dd/yyyy')) last_analyzed,
decode(a.nullable,'N','NOT NULL',' ') nullable,
a.num_distinct, a.density, a.num_nulls,
a.num_buckets, a.avg_col_len, a.sample_size
from all_tab_columns a
where a.owner = v_owner
and a.table_name = v_table ;

cursor hist_stats is
select b.column_name, b.endpoint_number, b.endpoint_value, b.endpoint_actual_value
from all_tab_histograms b
where b.owner = v_owner
and b.table_name = v_table
and (exists (select 1 from all_tab_columns
where num_buckets > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
or
exists (select 1 from all_tab_histograms
where endpoint_number > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
)
order by b.column_name, b.endpoint_number;

procedure print_table ( p_query in varchar2, p_date_fmt in varchar2 default 'dd-MON-yyyy hh24:mi:ss' ) is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_cs varchar2(255);
l_date_fmt varchar2(255);

-- Small inline procedure to restore the session's state.
-- We may have modified the cursor sharing and nls date format
-- session variables. This just restores them.
procedure restore
is
begin
if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
then
execute immediate
'alter session set cursor_sharing=exact';
end if;
if ( p_date_fmt is not null )
then
execute immediate
'alter session set nls_date_format=''' || l_date_fmt || '''';
end if;
dbms_sql.close_cursor(l_theCursor);
end restore;
begin
-- I like to see the dates print out with times, by default. The
-- format mask I use includes that. In order to be "friendly"
-- we save the current session's date format and then use
-- the one with the date and time. Passing in NULL will cause
-- this routine just to use the current date format.
if ( p_date_fmt is not null )
then
select sys_context( 'userenv', 'nls_date_format' )
into l_date_fmt
from dual;
execute immediate
'alter session set nls_date_format=''' || p_date_fmt || '''';
end if;

-- To be bind variable friendly on ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar. If not, set it to force so when we parse literals
-- are replaced with binds.
if ( dbms_utility.get_parameter_value
( 'cursor_sharing', l_status, l_cs ) = 1 )
then
if ( upper(l_cs) not in ('FORCE','SIMILAR'))
then
execute immediate
'alter session set cursor_sharing=force';
end if;
end if;


-- Parse and describe the query sent to us. We need
-- to know the number of columns and their names.
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

-- Define all columns to be cast to varchar2s. We
-- are just printing them out.
for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;

-- Execute the query, so we can fetch.
l_status := dbms_sql.execute(l_theCursor);

-- Loop and print out each column on a separate line.
-- Bear in mind that dbms_output prints only 255 characters/line
-- so we'll see only the first 200 characters by my design...
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
substr( l_columnValue, 1, 200 ) );
end loop;
dbms_output.put_line( '-----------------' );
end loop;

-- Now, restore the session state, no matter what.
restore;
exception
when others then
restore;
raise;
end;


begin
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Table Statistics');
dbms_output.put_line('==========================================================================================');


v_query := 'select table_name, last_analyzed, trim(degree) degree, partitioned,
num_rows, chain_cnt, blocks, empty_blocks, avg_space,
avg_row_len, monitoring, sample_size
from all_tables
where owner = ''' || UPPER(v_owner) || ''' and table_name = ''' || UPPER(v_table) || '''';
print_table (v_query);

v_ct := 0 ;

select count(1)
into v_ct
from all_tab_partitions
where table_owner = v_owner
and table_name = v_table;

if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Partition Information');
dbms_output.put_line('==========================================================================================');


v_query := 'select partition_name, last_analyzed, high_value,
num_rows, chain_cnt, blocks, empty_blocks,
avg_space, avg_row_len, compress_for
from all_tab_partitions
where table_owner = ''' || UPPER(v_owner) || '''
and table_name = ''' || UPPER(v_table) || '''';

print_table (v_query);
end if ;

select max(length(column_name)) + 1, max(length(num_distinct)) + 3,
max(length(num_nulls)) + 1, max(length(num_buckets)) + 1,
max(length(sample_size)) + 1
into v_max_colname, v_max_ndv, v_max_nulls, v_max_bkts, v_max_smpl
from all_tab_columns
where owner = v_owner
and table_name = v_table ;

if v_max_nulls < 8 then
v_max_nulls := 8 ;
end if ;

if v_max_bkts < 10 then
v_max_bkts := 10 ;
end if ;

if v_max_smpl < 7 then
v_max_smpl := 7;
end if;

dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Column Statistics');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' ' || rpad('Name',v_max_colname) || ' Analyzed Null? ' ||
rpad(' NDV',v_max_ndv) || ' ' || rpad(' Density',10) ||
rpad('# Nulls',v_max_nulls) || ' ' || rpad('# Buckets',v_max_bkts) || ' ' ||
rpad('Sample',v_max_smpl) || ' Avg Col Len');
dbms_output.put_line('==========================================================================================');


for v_rec in col_stats loop
dbms_output.put_line(rpad(v_rec.column_name,v_max_colname) || ' ' ||
to_char(v_rec.last_analyzed,'mm/dd/yyyy') || ' ' ||
v_rec.nullable || ' ' ||
rpad(v_rec.num_distinct,v_max_ndv) ||
to_char(v_rec.density,'9.999999') || ' ' ||
rpad(v_rec.num_nulls,v_max_nulls) || ' ' ||
rpad(v_rec.num_buckets,v_max_bkts) || ' ' ||
rpad(v_rec.sample_size,v_max_smpl) || ' ' ||
v_rec.avg_col_len );

end loop ;

select max(length(column_name)) + 1, max(length(endpoint_number)) + 1,
max(length(endpoint_value)) + 1
into v_max_colname, v_max_endnum, v_max_endval
from all_tab_histograms
where owner = v_owner
and table_name = v_table ;

if v_max_endnum < 12 then
v_max_endnum := 12 ;
end if ;

if v_max_endval < 16 then
v_max_endval := 16 ;
end if ;

select count(1)
into v_ct
from all_tab_histograms b
where b.owner = v_owner
and b.table_name = v_table
and (exists (select 1 from all_tab_columns
where num_buckets > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
or
exists (select 1 from all_tab_histograms
where endpoint_number > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
);

/* Histogram data commented out

if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Histogram Statistics');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' ' || rpad('Name',v_max_colname) || ' ' ||
rpad('Endpoint #',v_max_endnum) || ' ' ||
rpad('Endpoint Value',v_max_endval) || ' Endpoint Actual Value');

v_ct := 0 ;
for v_rec in hist_stats loop
if v_ct = 0 then
v_ct := 1 ;
prev_col := v_rec.column_name ;
elsif prev_col <> v_rec.column_name then
dbms_output.put_line('------------------------------------------------------------------------------------------');
prev_col := v_rec.column_name ;
end if ;
dbms_output.put_line(rpad(v_rec.column_name, v_max_colname) || ' ' ||
rpad(v_rec.endpoint_number,v_max_endnum) || ' ' ||
rpad(v_rec.endpoint_value,v_max_endval) || ' ' ||
substr(v_rec.endpoint_actual_value,1,20) ) ;
end loop ;
end if ;
*/

v_ct := 0;

select count(1)
into v_ct
from all_indexes a
where a.table_owner = v_owner
and a.table_name = v_table;

if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Index Information');
dbms_output.put_line('==========================================================================================');

v_query := 'select a.index_name, substr(a.index_type, 1, 4) index_type,
a.last_analyzed, a.degree, a.partitioned, a.blevel,
a.leaf_blocks, a.distinct_keys,
a.avg_leaf_blocks_per_key, a.avg_data_blocks_per_key,
a.clustering_factor, b.blocks blocks_in_table, b.num_rows rows_in_table
from all_indexes a, all_tables b
where (a.table_name = b.table_name and a.table_owner = b.owner)
and a.table_owner = ''' || UPPER(v_owner) || '''
and a.table_name = ''' || UPPER(v_table) || '''' ;

print_table (v_query);

dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Index Columns Information');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line('Index Name Pos# Order Column Name Expression');
dbms_output.put_line('==========================================================================================');
end if;

end ;
/

set verify off feed off numwidth 15 lines 500 heading off
column column_name format a30 heading 'Column Name'
column index_name heading 'Index Name' format a30
column column_position format 999999999 heading 'Position'
column descend format a5 heading 'Order'
column column_expression format a40 heading 'Expression'

break on index_name skip 1

select b.index_name, b.column_position, b.descend, b.column_name, e.column_expression
from all_ind_columns b, all_ind_expressions e
where b.table_owner = upper('&ownname')
and b.table_name = UPPER('&tabname')
and b.index_name = e.index_name(+)
order by b.index_name, b.column_position, b.column_name
/

undefine ownname
undefine tabname

set feed on numwidth 12 lines 120

clear columns
clear breaks
set lines 155
set head on

Oracle histogram delete

rid of histogram
get rid of a histogram
histogram delete


exec dbms_stats.gather_table_stats(user, 'anuj', method_opt => 'for columns n size 1', cascade => true,NO_INVALIDATE => FALSE );


table name is anuj


METHOD_OPT => 'FOR COLUMNS X SIZE 1'

and NO_INVALIDATE => FALSE. dependent cursors will be invalid immediately after stats have
been gathered.

RMAN Check Syntax

RMAN COMMAND check

-bash-3.2$ rman checksyntax

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 19 15:17:25 2011

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

RMAN> backup spfile format '/aptus/oracle/backup/RmanBackup/spfile_%d_%s_%T.bak' tag 'SPFILE';

The command has no syntax errors


or

rman checksyntax @file_name

Oracle error message

DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/




ORA-10000: controlfile debug event, name 'control_file'
ORA-10001: controlfile crash event1
ORA-10002: controlfile crash event2
ORA-10003: controlfile crash event3
ORA-10004: controlfile crash event4
ORA-10005: trace latch operations for debugging
ORA-10006: testing - block recovery forced
ORA-10007: log switch debug crash after new log select, thread
ORA-10008: log switch debug crash after new log header write, thread
ORA-10009: log switch debug crash after old log header write, thread
ORA-10010: Begin Transaction
ORA-10011: End Transaction
ORA-10012: Abort Transaction
ORA-10013: Instance Recovery
ORA-10014: Roll Back to Save Point
ORA-10015: Undo Segment Recovery
ORA-10016: Undo Segment extend
ORA-10017: Undo Segment Wrap
ORA-10018: Data Segment Create
ORA-10019: Data Segment Recovery
ORA-10020: partial link restored to linked list (KSG)
ORA-10021: latch cleanup for state objects (KSS)
ORA-10022: trace ktsgsp
ORA-10023: Create Save Undo Segment
ORA-10024: Write to Save Undo
ORA-10025: Extend Save Undo Segment
ORA-10026: Apply Save Undo
ORA-10027: Specify Deadlock Trace Information to be Dumped
ORA-10028: Dump trace information during lock / resource latch cleanup
ORA-10029: session logon (KSU)
ORA-10030: session logoff (KSU)
ORA-10031: sort debug event (S*)
ORA-10032: sort statistics (SOR*)
ORA-10033: sort run information (SRD*/SRS*)
ORA-10035: parse SQL statement (OPIPRS)
ORA-10036: create remote row source (QKANET)
ORA-10037: allocate remote row source (QKARWS)
ORA-10038: dump row source tree (QBADRV)
ORA-10039: type checking (OPITCA)
ORA-10040: dirty cache list
ORA-10041: dump undo records skipped
ORA-10042: trap error during undo application
ORA-10043: check consistency of owner/waiter/converter lists in KSQ
ORA-10044: free list undo operations
ORA-10045: free list update operations - ktsrsp, ktsunl
ORA-10046: enable SQL statement timing
ORA-10047: trace switching of sessions
ORA-10048: Undo segment shrink
ORA-10049: protect library cache memory heaps
ORA-10050: sniper trace
ORA-10051: trace OPI calls
ORA-10052: don't clean up obj$
ORA-10053: CBO Enable optimizer trace
ORA-10054: trace UNDO handling in MLS
ORA-10055: trace UNDO handing
ORA-10056: dump analyze stats (kdg)
ORA-10057: suppress file names in error messages
ORA-10058: use table scan cost in tab$.spare1
ORA-10059: simulate error in logfile create/clear
ORA-10060: CBO Enable predicate dump
ORA-10061: disable SMON from cleaning temp segment
ORA-10062: disable usage of OS Roles in osds
ORA-10063: disable usage of DBA and OPER privileges in osds
ORA-10064: thread enable debug crash level , thread
ORA-10065: limit library cache dump information for state object dump
ORA-10066: simulate failure to verify file
ORA-10067: force redo log checksum errors - block number
ORA-10068: force redo log checksum errors - file number
ORA-10069: Trusted Oracle test event
ORA-10070: force datafile checksum errors - block number
ORA-10071: force datafile checksum errors - file number
ORA-10072: protect latch recovery memory
ORA-10073: have PMON dump info before latch cleanup
ORA-10074: default trace function mask for kst
ORA-10075: CBO Disable outer-join to regular join conversion
ORA-10076: CBO Enable cartesian product join costing
ORA-10077: CBO Disable view-merging optimization for outer-joins
ORA-10078: CBO Disable constant predicate elimination optimization
ORA-10079: trace data sent/received via SQL*Net
ORA-10080: dump a block on a segment list which cannot be exchanged
ORA-10081: segment High Water Mark has been advanced
ORA-10082: free list head block is the same as the last block
ORA-10083: a brand new block has been requested from space management
ORA-10084: free list becomes empty
ORA-10085: free lists have been merged
ORA-10086: CBO Enable error if kko and qka disagree on oby sort
ORA-10087: disable repair of media corrupt data blocks
ORA-10088: CBO Disable new NOT IN optimization
ORA-10089: CBO Disable index sorting
ORA-10090: invoke other events before crash recovery
ORA-10091: CBO Disable constant predicate merging
ORA-10092: CBO Disable hash join
ORA-10093: CBO Enable force hash joins
ORA-10094: before resizing a data file
ORA-10095: dump debugger commands to trace file
ORA-10096: after the cross instance call when resizing a data file
ORA-10097: after generating redo when resizing a data file
ORA-10098: after the OS has increased the size of a data file
ORA-10099: after updating the file header with the new file size
ORA-10100: after the OS has decreased the size of a data file
ORA-10101: atomic redo write recovery
ORA-10102: switch off anti-joins
ORA-10103: CBO Disable hash join swapping
ORA-10104: dump hash join statistics to trace file
ORA-10105: CBO Enable constant pred trans and MPs w WHERE-clause
ORA-10106: CBO Disable evaluating correlation pred last for NOT IN
ORA-10107: CBO Always use bitmap index
ORA-10108: CBO Don't use bitmap index
ORA-10109: CBO Disable move of negated predicates
ORA-10110: CBO Try index rowid range scans
ORA-10111: Bitmap index creation switch
ORA-10112: Bitmap index creation switch
ORA-10113: Bitmap index creation switch
ORA-10114: Bitmap index creation switch
ORA-10115: CBO Bitmap optimization use maximal expression
ORA-10116: CBO Bitmap optimization switch
ORA-10117: CBO Disable new parallel cost model
ORA-10118: CBO Enable hash join costing
ORA-10119: QKA Disable GBY sort elimination
ORA-10120: generate relative file # different from absolute
ORA-10121: CBO Don't sort bitmap chains
ORA-10122: Disable transformation of count(col) to count(*)
ORA-10123: QKA Disable Bitmap And-EQuals
ORA-10124: Force creation of segmented arrays by kscsAllocate
ORA-10125: Disable remote sort elimination
ORA-10126: Debug oracle java xa
ORA-10127: Disable remote query block operation
ORA-10128: Dump Partition Pruning Information
ORA-10129: Alter histogram lookup for remote queries
ORA-10130: sort disable readaheads
ORA-10131: use v$sql_plan code path for explain plan
ORA-10132: dump plan after compilation
ORA-10133: testing for SQL Memory Management
ORA-10134: tracing for SQL Memory Management for session
ORA-10135: CBO do not count 0 rows partitions
ORA-10136: CBO turn off fix for bug 1089848
ORA-10137: CBO turn off fix for bug 1344111
ORA-10138: CBO turn off fix for bug 1577003
ORA-10139: CBO turn off fix for bug 1386119
ORA-10140: CBO turn off fix for bug 1332980
ORA-10141: CBO disable additional keys for inlist in bitmap optimization
ORA-10142: CBO turn off advanced OR-expansion checks
ORA-10143: CBO turn off hints
ORA-10144: CBO turn off cost based selection of bji over bsj subquery
ORA-10145: test auditing network errors
ORA-10146: enable Oracle TRACE collection
ORA-10147: enable join push through UNION view
ORA-10148: Use pre-7.3.3 random generator
ORA-10149: allow the creation of constraints with illegal date constants
ORA-10150: import exceptions
ORA-10151: Force duplicate dependency removal
ORA-10152: CBO don't consider function costs in plans
ORA-10153: Switch to use public synonym if private one does not translate
ORA-10154: Switch to disallow synonyms in DDL statements
ORA-10155: CBO disable generation of transitive OR-chains
ORA-10156: CBO disable index fast full scan
ORA-10157: CBO disable index access path for in-list
ORA-10158: CBO preserve predicate order in post-filters
ORA-10159: CBO disable order-by sort pushdown into domain indexes
ORA-10160: CBO disable use of join index
ORA-10161: CBO recursive semi-join on/off-switch
ORA-10162: CBO join-back elimination on/off-switch
ORA-10163: CBO join-back elimination on/off-switch
ORA-10164: CBO disable subquery-adjusted cardinality fix
ORA-10165: mark session to be aborted during shutdown normal
ORA-10166: trace long operation statistics updates
ORA-10167: CBO use old index MIN/MAX optimization
ORA-10168: CBO disable single-table predicate predicate generation
ORA-10169: CBO disable histograms for multi partitions
ORA-10170: CBO use old bitmap costing
ORA-10171: CBO disable transitive join predicates
ORA-10172: CBO force hash join back
ORA-10173: CBO no constraint-based join-back elimination
ORA-10174: view join-back elimination switch
ORA-10175: CBO star transformation switch
ORA-10176: CBO colocated join switch
ORA-10177: CBO colocated join switch
ORA-10178: CBO turn off hash cluster filtering through memcmp
ORA-10179: CBO turn off transitive predicate replacement
ORA-10180: temp table transformation print error messages
ORA-10181: CBO disable multi-column in-list processing
ORA-10182: CBO disable generation of implied predicates
ORA-10183: CBO disable cost rounding
ORA-10184: CBO disable OR-exp if long inlist on bitmap column
ORA-10185: CBO force index joins
ORA-10186: CBO disable index join
ORA-10187: CBO additional index join switch
ORA-10188: CBO additional index join switch
ORA-10189: CBO turn off FFS null fix
ORA-10190: Analyze use old frequency histogram collection and density
ORA-10191: Avoid conversion of in-lists back to OR-expanded form
ORA-10192: nopushdown when number of groups exceed number of rows
ORA-10193: Force repeatable sampling with specified seed
ORA-10194: CBO disable new LIKE selectivity heuristic
ORA-10195: CBO don't use check constraints for transitive predicates
ORA-10196: CBO disable index skip scan
ORA-10197: CBO force index skip scan
ORA-10198: check undo record
ORA-10199: set parameter in session
ORA-10200: consistent read buffer status
ORA-10201: consistent read undo application
ORA-10202: consistent read block header
ORA-10203: block cleanout
ORA-10204: signal recursive extend
ORA-10205: row cache debugging
ORA-10206: transaction table consistent read
ORA-10207: consistent read transactions' status report
ORA-10208: consistent read loop check
ORA-10209: enable simulated error on controlfile
ORA-10210: check data block integrity
ORA-10211: check index block integrity
ORA-10212: check cluster integrity
ORA-10213: crash after controlfile write
ORA-10214: simulate write errors on controlfile
ORA-10215: simulate read errors on controlfile
ORA-10216: dump controlfile header
ORA-10217: debug sequence numbers
ORA-10218: dump uba of applied undo
ORA-10219: monitor multi-pass row locking
ORA-10220: show updates to the transaction table
ORA-10221: show changes done with undo
ORA-10222: row cache
ORA-10223: transaction layer - turn on verification codes
ORA-10224: index block split/delete trace
ORA-10225: free/used extent row cache
ORA-10226: trace CR applications of undo for data operations
ORA-10227: verify (multi-piece) row structure
ORA-10228: trace application of redo by kcocbk
ORA-10229: simulate I/O error against datafiles
ORA-10230: check redo generation by copying before applying
ORA-10231: skip corrupted blocks on _table_scans_
ORA-10232: dump corrupted blocks symbolically when kcbgotten
ORA-10233: skip corrupted blocks on index operations
ORA-10234: trigger event after calling kcrapc to do redo N times
ORA-10235: check memory manager internal structures
ORA-10236: library cache manager
ORA-10237: simulate ^C (for testing purposes)
ORA-10238: instantiation manager
ORA-10239: multi-instance library cache manager
ORA-10240: dump dba's of blocks that we wait for
ORA-10241: remote SQL execution tracing/validation
ORA-10242: suppress OER 2063 (for testing distrib w/o different error log)
ORA-10243: simulated error for test of K2GTAB latch cleanup
ORA-10244: make tranids in error msgs print as 0.0.0 (for testing)
ORA-10245: simulate lock conflict error for testing PMON
ORA-10246: print trace of PMON actions to trace file
ORA-10247: Turn on scgcmn tracing. (VMS ONLY)
ORA-10248: turn on tracing for dispatchers
ORA-10249: turn on tracing for multi-stated servers
ORA-10250: Trace all allocate and free calls to the topmost SGA heap
ORA-10251: check consistency of transaction table and undo block
ORA-10252: simulate write error to data file header
ORA-10253: simulate write error to redo log
ORA-10254: trace cross-instance calls
ORA-10255: pl/sql parse checking
ORA-10256: turn off shared server load balancing
ORA-10257: trace shared server load balancing
ORA-10258: force shared servers to be chosen round-robin
ORA-10259: get error message text from remote using explicit call
ORA-10260: Trace calls to SMPRSET (VMS ONLY)
ORA-10261: Limit the size of the PGA heap
ORA-10262: Don't check for memory leaks
ORA-10263: Don't free empty PGA heap extents
ORA-10264: Collect statistics on context area usage (x$ksmcx)
ORA-10265: Keep random system generated output out of error messages
ORA-10266: Trace OSD stack usage
ORA-10267: Inhibit KSEDMP for testing
ORA-10268: Don't do forward coalesce when deleting extents
ORA-10269: Don't do coalesces of free space in SMON
ORA-10270: Debug shared cursors
ORA-10271: distributed transaction after COLLECT
ORA-10272: distributed transaction before PREPARE
ORA-10273: distributed transaction after PREPARE
ORA-10274: distributed transaction before COMMIT
ORA-10275: distributed transaction after COMMIT
ORA-10276: distributed transaction before FORGET
ORA-10277: Cursor sharing (or not) related event (used for testing)
ORA-10278: Internal testing
ORA-10279: Simulate block corruption in kdb4chk
ORA-10280: Internal testing - segmentation fault during crash recovery
ORA-10281: maximum time to wait for process creation
ORA-10282: Inhibit signalling of other backgrounds when one dies
ORA-10283: simulate asynch I/O never completing
ORA-10284: simulate zero/infinite asynch I/O buffering
ORA-10285: Simulate controlfile header corruption
ORA-10286: Simulate controlfile open error
ORA-10287: Simulate archiver error
ORA-10288: Do not check block type in ktrget
ORA-10289: Do block dumps to trace file in hex rather than fromatted
ORA-10290: kdnchk - checkvalid event - not for general purpose use.
ORA-10291: die in tbsdrv to test controlfile undo
ORA-10292: dump uet entries on a 1561 from dtsdrv
ORA-10293: dump debugging information when doing block recovery
ORA-10294: enable PERSISTENT DLM operations on non-compliant systems
ORA-10295: die after file header update durning cf xact
ORA-10296: disable ORA-379
ORA-10297: customize dictionary object number cache
ORA-10298: ksfd i/o tracing
ORA-10299: Trace prefetch tracking decisions made by CKPT
ORA-10300: disable undo compatibility check at database open
ORA-10301: Enable LCK timeout table consistency check
ORA-10302: trace create or drop internal trigger
ORA-10303: trace loading of library cache for internal triggers
ORA-10304: trace replication trigger
ORA-10305: trace updatable materialized view trigger
ORA-10306: trace materialized view log trigger
ORA-10307: trace RepCat execution
ORA-10308: replication testing event
ORA-10309: Trigger Debug event
ORA-10310: trace synchronous change table trigger
ORA-10311: Disable Flashback Table Timestamp checking
ORA-10312: Allow disable to log rows into the mapping table
ORA-10313: Allow Row CR operations for single instance
ORA-10314: Enable extra stats gathering for CR
ORA-10316: Events for extensible txn header, non zero ext header size
ORA-10317: Events for extensible txn header, zero ext header size
ORA-10318: Trace extensible txn header movements
ORA-10319: Trace PGA statistics maintenance
ORA-10320: Enable data layer (kdtgrs) tracing of space management calls
ORA-10321: Datafile header verification debug failure.
ORA-10322: CBO don't simplify inlist predicates
ORA-10323: before committing an add datafile command
ORA-10324: Enable better checking of redo logs errors
ORA-10325: Trace control file record section expand and shrink operations
ORA-10326: clear logfile debug crash at , log
ORA-10327: simulate ORA-00235 error for testing
ORA-10328: disable first-to-mount split-brain error, for testing
ORA-10329: simulate lost write, test detection by two-pass recovery
ORA-10330: clear MTTR statistics in checkpoint progress record
ORA-10331: simulate resilvering during recovery
ORA-10332: force ALTER SYSTEM QUIESCE RESTRICTED command to fail
ORA-10333: dump MTTR statistics each time it is updated
ORA-10334: force FG to wait to be killed during MTTR advisory simulation
ORA-10336: Do remote object transfer using remote SQL
ORA-10337: enable padding owner name in slave sql
ORA-10338: CBO don't use inlist iterator with function-based indexes
ORA-10339: CBO disable DECODE simplification
ORA-10340: Buffer queues sanity check for corrupted buffers
ORA-10341: Simulate out of PGA memory in DBWR during object reuse
ORA-10342: Raise unknown exception in ACQ_ADD when checkpointing
ORA-10343: Raise an out of memory exception-OER 4031 in ACQ_ADD
ORA-10344: Simulate kghxal returning 0 in ACQ_ADD but no exception
ORA-10345: validate queue when linking or unlinking a buffer
ORA-10346: check that all buffers for checkpoint have been written
ORA-10347: dump active checkpoint entries and checkpoint buffers
ORA-10348: test abnormal termination of process initiating file checkpoint
ORA-10349: do not allow ckpt to complete
ORA-10350: Simulate more than one object & tsn id in object reuse
ORA-10351: size of slots
ORA-10352: report direct path statistics
ORA-10353: number of slots
ORA-10354: turn on direct read path for parallel query
ORA-10355: turn on direct read path for scans
ORA-10356: turn on hint usage for direct read
ORA-10357: turn on debug information for direct path
ORA-10358: Simulate out of PGA memory in cache advisory reset
ORA-10359: turn off updates to control file for direct writes
ORA-10360: enable dbwr consistency checking
ORA-10365: turn on debug information for adaptive direct reads
ORA-10370: parallel query server kill event
ORA-10371: disable TQ hint
ORA-10372: parallel query server kill event proc
ORA-10373: parallel query server kill event
ORA-10374: parallel query server interrupt (validate lock value)
ORA-10375: turn on checks for statistics rollups
ORA-10376: turn on table queue statistics
ORA-10377: turn off load balancing
ORA-10378: force hard process/range affinity
ORA-10379: direct read for rowid range scans (unimplemented)
ORA-10380: kxfp latch cleanup testing event
ORA-10381: kxfp latch cleanup testing event
ORA-10382: parallel query server interrupt (reset)
ORA-10383: auto parallelization testing event
ORA-10384: parallel dataflow scheduler tracing
ORA-10385: parallel table scan range sampling method
ORA-10386: parallel SQL hash and range statistics
ORA-10387: parallel query server interrupt (normal)
ORA-10388: parallel query server interrupt (failure)
ORA-10389: parallel query server interrupt (cleanup)
ORA-10390: Trace parallel query slave execution
ORA-10391: trace PX granule allocation/assignment
ORA-10392: parallel query debugging bits
ORA-10393: print parallel query statistics
ORA-10394: generate a fake load to test adaptive and load balancing
ORA-10395: adjust sample size for range table queues
ORA-10396: circumvent range table queues for queries
ORA-10397: suppress verbose parallel coordinator error reporting
ORA-10398: enable timeouts in parallel query threads
ORA-10399: trace buffer allocation
ORA-10400: turn on system state dumps for shutdown debugging
ORA-10401: turn on IPC (ksxp) debugging
ORA-10402: turn on IPC (skgxp) debugging
ORA-10403: fake CPU number for default degree of parallelism
ORA-10404: crash dbwr after write
ORA-10405: emulate broken mirrors
ORA-10406: enable datetime TIMESTAMP, INTERVAL datatype creation
ORA-10407: enable datetime TIME datatype creation
ORA-10408: disable OLAP builtin window function usage
ORA-10409: enable granule memset and block invalidation at startup
ORA-10410: trigger simulated communications errors in KSXP
ORA-10411: simulate errors in IMR
ORA-10412: trigger simulated errors in CGS/CM interface
ORA-10413: force simulated error for testing purposes
ORA-10414: simulated error from event level
ORA-10425: enable global enqueue service open event trace
ORA-10426: enable global enqueue service convert event trace
ORA-10427: enable global enqueue service traffic controller event trace
ORA-10428: enable tracing of global enqueue service distributed resource
ORA-10429: enable tracing of global enqueue service IPC calls
ORA-10430: enable tracing of global enqueue service AST calls
ORA-10431: enable verification messages on pi consistency
ORA-10432: enable tracing of global cache service fusion calls
ORA-10433: global enqueue service testing event
ORA-10434: enable tracing of global enqueue service multiple LMS
ORA-10435: enable tracing of global enqueue service deadlock detetction
ORA-10436: enable global cache service duplicate ping checking
ORA-10437: enable trace of global enqueue service S optimized resources
ORA-10442: enable trace of kst for ORA-01555 diagnostics
ORA-10450: signal ctrl-c in kdddca (drop column) after n rows
ORA-10496: Turn off fix for bug 2554178
ORA-10498: Trim blank characters including contol characters
ORA-10499: revert to old scale behaviour
ORA-10500: turn on traces for SMON
ORA-10501: periodically check selected heap
ORA-10502: CBO disable the fix for bug 2098120
ORA-10503: enable user-specified graduated bind lengths
ORA-10504: CBO disable the fix for bug 2607029
ORA-10510: turn off SMON check to offline pending offline rollback segment
ORA-10511: turn off SMON check to cleanup undo dictionary
ORA-10512: turn off SMON check to shrink rollback segments
ORA-10513: turn off wrap source compression
ORA-10515: turn on event to use physical cleanout
ORA-10520: recreate package/procedure/view only if definition has changed
ORA-10550: signal error during create as select/create index after n rows
ORA-10560: block type ''
ORA-10561: block type '', data object#
ORA-10562: Error occurred while applying redo to data block (file# , block# )
ORA-10563: Test recovery had to corrupt data block (file# , block# ) in order to proceed
ORA-10564: tablespace
ORA-10565: Another test recovery session is active
ORA-10566: Test recovery has used all the memory it can use
ORA-10567: Redo is inconsistent with data block (file# , block# )
ORA-10568: Failed to allocate recovery state object: out of SGA memory
ORA-10570: Test recovery complete
ORA-10571: Test recovery canceled
ORA-10572: Test recovery canceled due to errors
ORA-10573: Test recovery tested redo from change to
ORA-10574: Test recovery did not corrupt any data block
ORA-10575: Give up restoring recovered datafiles to consistent state: out of memory
ORA-10576: Give up restoring recovered datafiles to consistent state: some error occurred
ORA-10577: Can not invoke test recovery for managed standby database recovery
ORA-10578: Can not allow corruption for managed standby database recovery
ORA-10579: Can not modify control file during test recovery
ORA-10580: Can not modify datafile header during test recovery
ORA-10581: Can not modify redo log header during test recovery
ORA-10582: The control file is not a backup control file
ORA-10583: Can not recovery file renamed as missing during test recovery
ORA-10584: Can not invoke parallel recovery for test recovery
ORA-10585: Test recovery can not apply redo that may modify control file
ORA-10586: Test recovery had to corrupt 1 data block in order to proceed
ORA-10587: Invalid count for ALLOW n CORRUPTION option
ORA-10588: Can only allow 1 corruption for normal media/standby recovery
ORA-10589: Test recovery had to corrupt data blocks in order to proceed
ORA-10590: kga (argus debugger) test flags
ORA-10591: kga (argus debugger) test flags
ORA-10592: kga (argus debugger) test flags
ORA-10593: kga (argus debugger) test flags
ORA-10594: kga (argus debugger) test flags
ORA-10595: kga (argus debugger) test flags
ORA-10596: kga (argus debugger) test flags
ORA-10597: kga (argus debugger) test flags
ORA-10598: kga (argus debugger) test flags
ORA-10599: kga (argus debugger) test flags
ORA-10600: check cursor frame allocation
ORA-10601: turn on debugging for cursor_sharing (literal replacement)
ORA-10602: cause an access violation (for testing purposes)
ORA-10603: cause an error to occur during truncate (for testing purposes)
ORA-10604: trace parallel create index
ORA-10605: enable parallel create index by default
ORA-10606: trace parallel create index
ORA-10607: trace index rowid partition scan
ORA-10608: trace create bitmap index
ORA-10609: trace for array index insertion
ORA-10610: trace create index pseudo optimizer
ORA-10611: causes migration to fail - testing only
ORA-10612: prints debug information for auto-space managed segments
ORA-10613: prints debug information for auto-space managed segments
ORA-10614: Operation not allowed on this segment
ORA-10615: Invalid tablespace type for temporary tablespace
ORA-10616: Operation not allowed on this tablespace
ORA-10617: Cannot create rollback segment in dictionary managed tablespace
ORA-10618: Operation not allowed on this segment
ORA-10619: Avoid assertions when possible
ORA-10620: Operation not allowed on this segment
ORA-10621: data block does not belong to the segment
ORA-10622: test/trace online index (re)build
ORA-10623: Enable Index range scan Prefetch - testing only
ORA-10624: Disable UJV invalidation on drop index
ORA-10625: Turn off redo log dump for the index when OERI 12700
ORA-10627: Dump the content of the index leaf block
ORA-10628: Turn on sanity check for kdiss index skip scan state
ORA-10640: Operation not permitted during SYSTEM tablespace migration
ORA-10641: Cannot find a rollback segment to bind to
ORA-10642: Found rollback segments in dictionary managed tablespaces
ORA-10643: Database should be mounted in restricted mode and Exclusive mode
ORA-10644: SYSTEM tablespace cannot be default temporary tablespace
ORA-10645: Recursive Extension in SYSTEM tablespace during migration
ORA-10646: Too many recursive extensions during SYSTEM tablespace migration
ORA-10647: Tablespace other than SYSTEM,, not found in read only mode
ORA-10650: disable cache-callback optimisation
ORA-10651: incorrect file number block number specified
ORA-10666: Do not get database enqueue name
ORA-10667: Cause sppst to check for valid process ids
ORA-10690: Set shadow process core file dump type (Unix only)
ORA-10691: Set background process core file type (Unix only)
ORA-10700: Alter access violation exception handler
ORA-10701: Dump direct loader index keys
ORA-10702: Enable histogram data generation
ORA-10703: Simulate process death during enqueue get
ORA-10704: Print out information about what enqueues are being obtained
ORA-10705: Print Out Tracing information for every I/O done by ODSs
ORA-10706: Print out information about global enqueue manipulation
ORA-10707: Simulate process death for instance registration
ORA-10708: print out trace information from the RAC buffer cache
ORA-10709: enable parallel instances in create index by default
ORA-10710: trace bitmap index access
ORA-10711: trace bitmap index merge
ORA-10712: trace bitmap index or
ORA-10713: trace bitmap index and
ORA-10714: trace bitmap index minus
ORA-10715: trace bitmap index conversion to rowids
ORA-10716: trace bitmap index compress/decompress
ORA-10717: trace bitmap index compaction trace for index creation
ORA-10718: event to disable automatic compaction after index creation
ORA-10719: trace bitmap index dml
ORA-10720: trace db scheduling
ORA-10721: Internal testing - temp table transformation
ORA-10722: set parameters for CPU frequency calculation (debug)
ORA-10723: Internal testing - release buffer for buffer cache shrink
ORA-10724: trace cross-instance broadcast
ORA-10730: trace row level security policy predicates
ORA-10731: dump SQL for CURSOR expressions
ORA-10740: disables fix for bug 598861
ORA-10750: test rollback segment blksize guessing for index array insert
ORA-10800: disable Smart Disk scan
ORA-10801: enable Smart Disk trace
ORA-10802: reserved for Smart Disk
ORA-10803: write timing statistics on cluster database recovery scan
ORA-10804: reserved for ksxb
ORA-10806: Switch to 7.3 mode when detaching sessions
ORA-10807: Disable user id check when switching to a global transaction
ORA-10810: Trace snapshot too old
ORA-10811: Trace block cleanouts
ORA-10812: Trace Consistent Reads
ORA-10826: enable upgrade/downgrade error message trace
ORA-10827: database must be opened with MIGRATE option
ORA-10830: Trace group by sort row source
ORA-10831: Trace group by rollup row source
ORA-10841: Default un-inintialized charact set form to SQLCS_IMPLICIT
ORA-10842: Event for OCI Tracing and Statistics Info
ORA-10850: Enable time manager tracing
ORA-10851: Allow Drop command to drop queue tables
ORA-10852: Enable tracing for Enqueue Dequeue Operations
ORA-10853: event for AQ statistics latch cleanup testing
ORA-10856: Disable AQ propagator from using streaming
ORA-10857: Force AQ propagator to use two-phase commit
ORA-10858: Crash the AQ propagator at different stages of commit
ORA-10859: Disable updates of message retry count
ORA-10860: event for AQ admin disable new name parser
ORA-10861: disable storing extended message properties
ORA-10862: resolve default queue owner to current user in enqueue/dequeue
ORA-10871: dump file open/close timestamp during media recovery
ORA-10900: extent manager fault insertion event #
ORA-10902: disable seghdr conversion for ro operation
ORA-10903: Force tablespaces to become locally managed
ORA-10904: Allow locally managed tablespaces to have user allocation
ORA-10905: Do cache verification (kcbcxx) on extent allocation
ORA-10906: Unable to extend segment after insert direct load
ORA-10907: Trace extent management events
ORA-10908: Trace temp tablespace events
ORA-10909: Trace free list events
ORA-10910: inject corner case events into the RAC buffer cache
ORA-10911: Locally managed SYSTEM tablespace bitmaps can be modified only under the supervision of Oracle Support
ORA-10912: Used to perform admin operations on locally managed SYSTEM tablespace
ORA-10913: Create locally managed database if compatible > 920 by default
ORA-10924: import storage parse error ignore event
ORA-10925: trace name context forever
ORA-10926: trace name context forever
ORA-10927: trace name context forever
ORA-10928: trace name context forever
ORA-10929: trace name context forever
ORA-10930: trace name context forever
ORA-10931: trace name context forever
ORA-10932: trace name context forever
ORA-10933: trace name context forever
ORA-10934: Reserved. Used only in version 7.x.
ORA-10935: Reserved. Used only in version 7.x.
ORA-10936: trace name context forever
ORA-10937: trace name context forever
ORA-10938: trace name context forever
ORA-10939: trace name context forever
ORA-10940: trace name context forever
ORA-10941: trace name context forever
ORA-10943: trace name context forever
ORA-10944: trace name context forever
ORA-10945: trace name context forever
ORA-10970: backout event for bug 2133357
ORA-10975: trace execution of parallel propagation
ORA-10976: internal package related tracing
ORA-10977: trace event for RepAPI
ORA-10979: trace flags for join index implementation
ORA-10980: prevent sharing of parsed query during Materialized View query generation
ORA-10981: dscn computation-related event in replication
ORA-10982: event to turn off CDC-format MV Logs
ORA-10983: event to enable Create_Change_Table debugging
ORA-10984: subquery materialized view-related event
ORA-10985: event for NULL refresh of materialized views
ORA-10986: donot use HASH_AJ in refresh
ORA-10987: event for the support of caching table with object feature
ORA-10988: event to get exclusive lock during materialized view refresh in IAS
ORA-10989: event to internally create statistics MV
ORA-10996: event to make a process hold a latch in ksu
ORA-10999: do not get database enqueue name

Oracle Default user's info

some Oracle Default user



('SYS','SYSTEM','OUTLN','SCOTT','ADAMS','JONES','CLARK',
'BLAKE','WOOD','STEEL','CLOTH','PAPER','HR','OE','SH','OE','SH',
'DEMO','ANONYMOUS','AURORA$ORB$UNAUTHENTICATED','AWR_STAGE',
'CSMIG','CTXSYS','DBSNMP','DIP','DMSYS','DSSYS','EXFSYS','LBACSYS',
'MDSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS','PERFSTAT',
'TRACESVR','TSMSYS','XDB')




'SYS',
'SYSTEM' ,
'OUTLN' --- OUTLN user schema to support Plan Stability. The OUTLN user acts
'SCOTT', ---?/rdbms/admin/utlsampl.sql
'SCOTT',
'ADAMS',
'JONES',
'CLARK',
'BLAKE',
'HR', --(Human Resources) ---/demo/schema/mksample.sql
'OE', --(Order Entry)
'SH', --(Sales History)
'DEMO', -- ?/rdbms/admin/demo.sql
'ANONYMOUS', -- Used by the PL/SQL gateway that enables a Web browser to invoke a PL/SQL stored procedure through an HTTP listener.
'AURORA$ORB$UNAUTHENTICATED',---Used for users who do not authenticate in Aurora/ORB
'AWR_STAGE', ---?/rdbms/admin/awrload.sql
'CSMIG', --- Character Set Scanner user named CSMIG
'CTXSYS', -- ?/ctx/admin/dr0csys.sql
'DBSNMP', -- ?/rdbms/admin/catsnmp.sql
'DIP', -- ?/rdbms/admin/catdip.sql,
'DMSYS', -- Data Mining user ?/rdbms/admin/odmcrt.sql called from dminst.sql
'DSSYS', -- Oracle Spatial
'EXFSYS', --?/rdbms/admin/exfsys.sql,
'LBACSYS', --?/rdbms/admin/catlbacs.sql, called from catols.sql
'MDSYS', --?/ord/admin/ordinst.sql
'ORACLE_OCM', -- ?/rdbms/admin/catocm.sql,
'ORDPLUGINS', -- ?/ord/admin/ordinst.sql
'ORDSYS', --?/ord/admin/ordinst.sql
'PERFSTAT', --?/rdbms/admin/statscre.sql
'TRACESVR', --?/rdbms/admin/otrcsvr.sql
'TSMSYS', --?/rdbms/admin/cattsm.sql,
'XDB') -- ?/rdbms/admin/catqm.sql Owner of objects for XDB system



select dba_segments.owner,segment_name as table_name,sum(dba_tables.num_rows) as total_rows,sum ((bytes/1024/1024)) as total_mb from dba_segments,dba_tables
where dba_segments.owner not in
('SYSTEM','SYS','OUTLN','WMSYS','ORDSYS','MDSYS','CTXSYS','SQLTXPLAIN','XDB','DBSNMP','ADMN','SYSMAN','EXFSYS','TRCANLZR','APPQOSSYS','SH','CSMIG','PERFSTAT','TRCANLZR')
and segment_type IN ('TABLE', 'TABLE PARTITION')
and dba_tables.table_name=dba_segments.segment_name
and dba_tables.owner=dba_segments.owner
group by dba_segments.owner,segment_name
order by total_mb desc ;

RMAN handy commands

RMAN handy command

$ rman checksyntax 

RMAN> host 'export NLS_DATE_FORMAT="DD.MON.YYYY HH24:MI:SS"; $ORACLE_HOME/bin/rman target /';

RMAN> list backup summary completed after "sysdate-1";

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
8054    B  F  A SBT_TAPE    09.FEB.2012 16:05:48 1       1       NO         TAG20120209T160539
RMAN> list backup summary completed after "sysdate-1";




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

alias rmanme='rman target / | tee rman_`/bin/date +\%d\%m\%y:%H:%M:%S`.log'

with logfile 

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

log_date=`date "+%Y%m%d_%H:%M"`

rman target / log /home/oracle/log_${log_date}.log 

oracle@apt-amd-02:~> rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jul 16 06:23:02 2011

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

select timestamp_to_scn(to_timestamp('02/06/2024 14:24:54','DD/MM/YYYY HH24:MI:SS')) as scn from dual;

RMAN> connect target /

connected to target database: ORCL (DBID=1267852645)

RMAN> list backupset;

using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> list backup of database;

specification does not match any backup in the repository

RMAN> list backup of archivelog all;

specification does not match any backup in the repository

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
no obsolete backups found

RMAN> report obsolete redundancy = 2;

no obsolete backups found

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=54 device type=DISK
no obsolete backups found

RMAN> restore database validate;

Starting restore at 16-JUL-11
using channel ORA_DISK_1

datafile 6 will be created automatically during restore operation
datafile 7 will be created automatically during restore operation
datafile 8 will be created automatically during restore operation
datafile 9 will be created automatically during restore operation
datafile 10 will be created automatically during restore operation
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/16/2011 06:24:37
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
5 full /opt/app/oracle/oradata/orcl/example01.dbf

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 810 SYSTEM *** /opt/app/oracle/oradata/orcl/system01.dbf
2 830 SYSAUX *** /opt/app/oracle/oradata/orcl/sysaux01.dbf
3 325 UNDOTBS1 *** /opt/app/oracle/oradata/orcl/undotbs01.dbf
4 352 USERS *** /opt/app/oracle/oradata/orcl/users01.dbf
5 100 EXAMPLE *** /opt/app/oracle/oradata/orcl/example01.dbf
6 10 ANUJTEST *** /opt/app/oracle/oradata/orcl/anujtest.dbf
7 50 TSAPEXF *** /opt/app/oracle/oradata/orcl/tsapexf01.dbf
8 110 TSAPEXU *** /opt/app/oracle/oradata/orcl/tsapexu01.dbf
9 20 TEST *** /opt/app/oracle/oradata/orcl/test.dbf
10 50 RMAN *** /opt/app/oracle/oradata/orcl/rman.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 276 TEMP 32767 /opt/app/oracle/oradata/orcl/temp01.dbf

RMAN> crosscheck backup;

using channel ORA_DISK_1
specification does not match any backup in the repository

RMAN> delete expired backup;

using channel ORA_DISK_1
specification does not match any backup in the repository

RMAN> LIST BACKUPSET OF DATABASE;

specification does not match any backup in the repository

RMAN>



RMAN> RESTORE DATABASE VALIDATE;

Starting restore at 16-JUL-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /aptus/oracle/data/1/Rmanbackup/databasefiles_APTDB_3jmhii8t_17523_20110716
channel ORA_DISK_1: restored backup piece 1
piece handle=/aptus/oracle/data/1/Rmanbackup/databasefiles_APTDB_3jmhii8t_17523_20110716 tag=TAG20110716T073037
channel ORA_DISK_1: validation complete, elapsed time: 00:01:06
Finished restore at 16-JUL-11


===
delete archive

RMAN> backup archive log all format '/u1/oradata/db/arch-bak';
RMAN> delete archive until time 'trunc(sysdate)';




In order to skip two tablespaces issue command in RMAN twice

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE abc;
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE xyz;


RMAN> SHOW EXCLUDE;

You can override this exclusion feature by explicitly issuing keyword NOEXCLUDE in order to take whole database backup.


RMAN>BACKUP DATABASE NOEXCLUDE;

disable the exclusion feature for xyz tablespace
RMAN>CONFIGURE EXCLUDE FOR TABLESPACE xyz CLEAR;


===

RMAN> restore database validate preview; - <<<<<<<<<<<<<<<<<-----------------------

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

RESTORE DATABASE PREVIEW;

RESTORE TABLESPACE system PREVIEW;

RESTORE DATAFILE 1 PREVIEW;

RESTORE ARCHIVELOG FROM LOGSEQ 4 PREVIEW;

RESTORE ARCHIVELOG FROM TIME 'SYSDATE-1' PREVIEW;

RESTORE ARCHIVELOG FROM SCN 4470909 PREVIEW;

RESTORE DATABASE UNTIL TIME "TO_DATE('18.11.2024 18:00:00','DD.MM.YYYY HH24:MI:SS')" PREVIEW;

 LIST BACKUP OF CONTROLFILE;

RESTORE DATABASE PREVIEW SUMMARY;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

38      Full    17.95M     DISK        00:00:00     13-OCT-2024 18:41:15

        BP Key: 38   Status: AVAILABLE  Compressed: NO  Tag: TAG20241013T184115

        Piece Name: /u01/app/oracle/fra/ORADB/autobackup/2024_10_13/o1_mf_s_1182278475_mjqq0cwx_.bkp

  Control File Included: Ckp SCN: 4471035      Ckp time: 13-OCT-2024 18:41:15

delete archivelog until time "to_date('oct 13 2024 20:00:00','Mon DD YYYY HH24:MI:SS')";

37      B  F  A DISK        13-OCT-2024 18:41:14 1       1       NO         RMANFULL

38      B  F  A DISK        13-OCT-2024 18:41:15 1       1       NO         TAG20241013T184115

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

37      Full    17.92M     DISK        00:00:01     13-OCT-2024 18:41:14

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

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

  Control File Included: Ckp SCN: 4471024      Ckp time: 13-OCT-2024 18:41:13

RESTORE DATABASE UNTIL TIME "TO_DATE('13-10-2024 18:38:25', 'DD-MM-YYYY HH24:MI:SS')" PREVIEW summary;

RMAN>

Starting restore at 13-OCT-2024 19:23:25

using channel ORA_DISK_1

List of Backups

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

Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag

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

16      B  0  A DISK        13-OCT-2024 12:17:17 1       1       YES        TAG20241013T121519

30      B  0  A DISK        13-OCT-2024 18:40:45 1       1       NO         RMANFULL

32      B  0  A DISK        13-OCT-2024 18:41:02 1       1       NO         RMANFULL

17      B  0  A DISK        13-OCT-2024 12:18:45 1       1       YES        TAG20241013T121519

List of Backups

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

Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag

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

24      B  A  A DISK        13-OCT-2024 18:38:27 1       1       NO         RMANFULL

22      B  A  A DISK        13-OCT-2024 18:38:25 1       1       NO         RMANFULL

recovery will be done up to SCN 4470778

Media recovery start SCN is 4456406

Recovery must be done beyond SCN 4456467 to clear datafile fuzziness

Finished restore at 13-OCT-2024 19:23:26

RESTORE DATABASE UNTIL TIME 'sysdate-1' PREVIEW SUMMARY;

RESTORE DATABASE UNTIL TIME 'sysdate-1/24' preview summary; --> one hour ago

RESTORE TABLESPACE system PREVIEW SUMMARY;

RESTORE DATAFILE 1 PREVIEW SUMMARY;

RESTORE ARCHIVELOG FROM SCN 4456467 PREVIEW SUMMARY;

RESTORE DATABASE PREVIEW ;

RESTORE DATABASE VALIDATE;

RESTORE ARCHIVELOG FROM sequence 1 UNTIL SEQUENCE 2 THREAD 1 VALIDATE;

RESTORE CONTROLFILE VALIDATE;

RESTORE SPFILE VALIDATE;

4470908

RMAN> 

RUN

{

 set until time "to_date('13-10-2024 18:38:25', 'DD-MM-YYYY HH24:MI:SS')";

 RESTORE DATABASE PREVIEW ;

 }   

RUN

{

set until time "to_date('13-10-2024 18:38:25', 'DD-MM-YYYY HH24:MI:SS')";

restore database validate;

}

RMAN> RUN

 {

restore archivelog from sequence 10 until sequence 20 thread 1 validate;

restore archivelog from sequence 8 until sequence 15 thread 2 validate;

}

RMAN> 

RUN

 {

set until time "to_date('13-10-2024 18:38:25', 'DD-MM-YYYY HH24:MI:SS')";

restore controlfile validate;

restore spfile validate;

}

executing command: SET until clause

Starting restore at 13-OCT-2024 19:44:36

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fra/ORADB/autobackup/2024_10_13/o1_mf_s_1182258134_mjq34qqj_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fra/ORADB/autobackup/2024_10_13/o1_mf_s_1182258134_mjq34qqj_.bkp tag=TAG20241013T130214

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: validation complete, elapsed time: 00:00:00

Finished restore at 13-OCT-2024 19:44:37

Starting restore at 13-OCT-2024 19:44:37

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fra/ORADB/autobackup/2024_10_13/o1_mf_s_1182278475_mjqq0cwx_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fra/ORADB/autobackup/2024_10_13/o1_mf_s_1182278475_mjqq0cwx_.bkp tag=TAG20241013T184115

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: validation complete, elapsed time: 00:00:00

Finished restore at 13-OCT-2024 19:44:37

RMAN> restore spfile to pfile '/tmp/pfile.txt'  from autobackup;

Starting restore at 13-OCT-2024 19:40:25

using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/fra

database name (or database unique name) used for search: ORADB

channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fra/ORADB/autobackup/2024_10_13/o1_mf_s_1182278475_mjqq0cwx_.bkp found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20241013

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fra/ORADB/autobackup/2024_10_13/o1_mf_s_1182278475_mjqq0cwx_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 13-OCT-2024 19:40:27

restore spfile to pfile '/tmp/pfile1.txt' from autobackup db_recovery_file_dest='/u01/app/oracle/fra' db_name='oradb';

 

RMAN> restore spfile to pfile '/tmp/pfile1.txt' from autobackup db_recovery_file_dest='/u01/app/oracle/fra' db_name='oradb';

Starting restore at 13-OCT-2024 19:43:52

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=41 device type=DISK

recovery area destination: /u01/app/oracle/fra

database name (or database unique name) used for search: ORADB

channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fra/ORADB/autobackup/2024_10_13/o1_mf_s_1182278475_mjqq0cwx_.bkp found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20241013

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fra/ORADB/autobackup/2024_10_13/o1_mf_s_1182278475_mjqq0cwx_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 13-OCT-2024 19:43:54

RMAN> list backup of controlfile;


rman> list backup;
rman> list backup of database;
rman> list backup summary;
rman> list incarnation;
rman> list backup by file;
rman> list copy of database archivelog all;
rman> list copy of datafile 1, 2, 3;
rman> list backup of datafile 11 summary;
rman> list backup of archivelog from sequence 1234;
rman> list controlfilecopy "/u01/app/oracle/ctrl1.cpy";
rman> list backupset of datafile 1;




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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15667 Full 1.88M DISK 00:00:01 06-JUL-11
BP Key: 15667 Status: AVAILABLE Compressed: YES Tag: TAG20110706T010535
Piece Name: /aptus/oracle/data/1/Rmanbackup/databasefiles_APTDB_3hmgngbu_17521_20110706
Control File Included: Ckp SCN: 127799062 Ckp time: 06-JUL-11

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15670 Full 1.88M DISK 00:00:02 16-JUL-11
BP Key: 15670 Status: AVAILABLE Compressed: YES Tag: TAG20110716T073037
Piece Name: /aptus/oracle/data/1/Rmanbackup/databasefiles_APTDB_3kmhiibs_17524_20110716
Control File Included: Ckp SCN: 128194984 Ckp time: 16-JUL-11

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15671 Full 1.86M DISK 00:00:01 16-JUL-11
BP Key: 15671 Status: AVAILABLE Compressed: YES Tag: TAG20110716T073219
Piece Name: /aptus/oracle/data/1/Rmanbackup/controlfile_APTDB_3lmhiic3_17525_20110716
Control File Included: Ckp SCN: 128194995 Ckp time: 16-JUL-11



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

 RUN

{

 set until time "to_date('02-FEB-2018 11:20:00','dd-mon-yyyyhh24:mi:ss')";

 RESTORE DATABASE PREVIEW ;

 }   

RMAN> RUN

{

set until time "to_date('02-FEB-2018 11:20:00','dd-mon-yyyyhh24:mi:ss')";

restore database validate;

RMAN> RUN

 {

set until time "to_date('02-FEB-2018 11:20:00','dd-mom-yyyyhh24:mi:ss')";

restore controlfile validate;

restore spfile validate;

}

RESTORE DATABASE UNTIL TIME "TO_DATE('18.03.2018 01:04:00','DD.MM.YYYY HH24:MI:SS')" PREVIEW;

BS Key  Size       Device Type Elapsed Time Completion Time

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

730     5.54M      DISK        00:00:01     18-03-2018 01:04:50

        BP Key: 730   Status: AVAILABLE  Compressed: YES  Tag: TAG20180318T010449

        Piece Name: /dumps/IBRAC/20180318_ibrac1_742_1_971053489

  List of Archived Logs in backup set 730

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

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

  1    163     239713659  18-03-2018 01:00:03 239721594  18-03-2018 01:04:49

  2    149     239713662  18-03-2018 01:00:04 239721591  18-03-2018 01:04:49

recovery will be done up to SCN 239715207

Media recovery start SCN is 228048219

Recovery must be done beyond SCN 239715207 to clear datafile fuzziness

Finished restore at 07-07-2022 11:18:32

RUN

{

  set until time "to_date('18.03.2018 01:04:00','DD.MM.YYYY HH24:MI:SS')";

  RESTORE DATABASE;

  RECOVER DATABASE;

}

 alter database open resetlogs;

RMAN> run {
set until time to_date('04-Aug-2004 00:00:00', 'DD-MON-YYYY HH24:MI:SS');
restore database;
recover database;
}

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




RMAN> run {
2> set until time '27-oct-2011 12:11:00';
3> }

executing command: SET until clause



RMAN> sql "alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''";

using target database control file instead of recovery catalog
sql statement: alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''








recover database UNTIL TIME '2011-11-15:14:07:00'
The time format must be in the format YYYY-MM-DD:HH24:MM:SS irrespective of NLS_DATE_FORMAT





run {
shutdown immediate;
startup mount;
sql "alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''";
set until time '15-aug-2006 14:11:00';
restore database;
recover database;
alter database open resetlogs;}




col group# for 9999
col status for a8
col member for a40
select f.group#,
sequence#,
l.status,
member
from v$logfile f, v$log l
where f.group#=l.group#
order by group#



GROUP# SEQUENCE# STATUS MEMBER
------ ---------- -------- ----------------------------------------
4 58 INACTIVE /opt/app/oracle/oradata/orcl/redo04.log
5 59 INACTIVE /opt/app/oracle/oradata/orcl/redo05.log
6 60 CURRENT /opt/app/oracle/oradata/orcl/redo06.log





run {
shutdown immediate;
startup mount;
set until sequence 59 thread 1;
restore database;
recover database;
alter database open resetlogs;
}

UNTIL CHANGE / UNTIL SCN Recovery

* SQL> shutdown immediate;
* SQL> startup mount;
* —No restore b/c OS or other means are used to restore necessary files—
* SQL> recover database UNTIL CHANGE 309121;
* SQL> alter database open resetlogs;
* —–ALTERNATIVE in RMAN—–


run {
shutdown immediate;
startup mount;
set until scn 309121;
restore database;
recover database;
alter database open resetlogs;}








oracle@novagenesis$ rman target=/ log=anujrman.log <<<<< --- for RMAN log file




to duplicate database .. from asm file system non ASM filesystem

[oracle@rac1 dupdb]$ rman target=sys/oracle@demodb auxiliary=/
RMAN> duplicate target database to dupdb nofilenamecheck;


to restore RMAN


RMAN> restore controlfile from 'e:\backup\CTL_SP_BAK_xxxxxxxxxx-20050228-00';


RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/aptus/oracle/data/1/Rmanbackup/cotrolfile%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/aptus/oracle/data/1/Rmanbackup/%d_DB_%u_%s_%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/aptus/oracle/product/10g/dbs/snapcf_aptdb.f'; # default





====


Listing Incarnations

A new incarnation of the database is created when you perform the open resetlogs operation. To view the list of database incarnations,

RMAN> List incarnation;

List Summary of Backups

The summary of backups include backupset key, the status, device type, completion time etc,

RMAN> List Backup Summary;
RMAN> List expired Backup of archivelog all summary;
RMAN> List Backup of tablespace Test summary;



List Backups of various files

It provides the summary of the backups available for each datafile, controlfile, archivelog file and spfile.

RMAN> List Backup By File;


f you want the detailed report on the backups, then issue the following command.

RMAN> List Backup;

It lists the all available information about the backups.

Backups used for Recovery

To list the backups used for restore and recovery,

RMAN> list recoverable backup;

Expired Backups

The list backup shows both available and expired backups. To view only the expired backups,

RMAN> List expired Backup;
RMAN> List expired Backup summary;
RMAN> List expired Backup of Archivelog all;
RMAN> List expired Backup of datafile 10;

Listing Tablespace and Datafile Backups

RMAN> List Backup of Tablespace Test;
RMAN> List Backup of Datafile 4;

Listing Archivelog Backups

RMAN> List Archivelog all;
RMAN> List Archivelog all backedup 2 times to device type sbt;

Listing Controlfile and Spfile Backups

RMAN> List Backup of Controlfile;
RMAN> List Backup of Spfile;

The above list commands displayed information about the backusets.
If you have performed Image copy backups then you must use the list copy command as shown below,

RMAN> List Copy;
RMAN> List Copy of database;
RMAN> List Copy of tablespace test;
RMAN> List Copy of archivelog all;
RMAN> List Copy of archivelog from sequence 12345;
RMAN> List Copy of archivelog from sequence 1000 until sequence 1010;
RMAN> List Copy of Controlfile;
RMAN> List Copy of Spfile;



recover database from tag="TAG20051024T1XXXXX" validate;
restore database from tag="XXXXXXXX";




RMAN> SET DBID=228033884;

RMAN> CONNECT TARGET

4. Restore the controlfile from autobackup

% rman trace recocf.log

RMAN> SET DBID=228033884;

RMAN> CONNECT TARGET

RMAN> RUN
{ SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/rman/V920/%F';
ALLOCATE CHANNEL d1 DEVICE TYPE disk;
RESTORE CONTROLFILE FROM AUTOBACKUP
MAXSEQ 5 # start at sequence 5 and count down (optional)
MAXDAYS 5; # start at UNTIL TIME and search back 5 days (optional)
MOUNT DATABASE;
}




5. Verify what is available for incomplete recovery.

We will recover to the highest SCN log sequence and thread. We will use the log sequence in this case.

The options are "until time", "until scn", or "until sequence".

* First we need to fine the highest sequence of each thread:

SQL> select max(sequence#) from v$archived_log where thread#=1;

MAX(SEQUENCE#)
--------------
25

SQL> select max(sequence#) from v$archived_log where thread#=2;

MAX(SEQUENCE#)
--------------
13

* Next is to find the thread with highest SCN ( NEXT_CHANGE# )

In this case the SCN is greater in thread 2 sequence 13 than in sequence 25 thread 1. So we will use thread 2. We will set sequence 14 thread 2 for RMAN 'until sequence' recovery, because RMAN stops the recovery before applying the indicated sequence. Log sequence for recovery needs always be sequence+1 to end at +1 after applying the prior sequence.

SQL> select sequence#, thread#, first_change#, next_change#
from v$archived_log
where sequence# in (13,25);

SEQUENCE# THREAD# FIRST_CHANGE# NEXT_CHANGE#
-------------------- -------------- ------------------------- -------------------------
25 1 1744432 1744802
13 2 1744429 1744805

SQL> select sequence#, thread#, first_change#, next_change#
from v$backup_redolog
where sequence# in (13,25);

SEQUENCE# THREAD# FIRST_CHANGE# NEXT_CHANGE#
-------------------- -------------- ------------------------- -------------------------
25 1 1744432 1744802
13 2 1744429 1744805


--- SET UNTIL SEQUENCE 14 THREAD 2 ----

6. Get the sentences to add TEMPFILES after opening DB.

Locally Managed Temporary Tablespaces are not restored by RESTORE command, we need to create them manually after recovery is complete.

If using LMT Temporary tablespace the controlfile will have the syntax to add the tempfile after recovery is complete. The following command will give us the create controlfile sencence:

SQL> alter database backup controlfile to trace;

Example:

# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/dev/db/rV92B_temp_01.dbf' SIZE 41943040 REUSE AUTOEXTEND OFF;
# End of tempfile additions.
#

7. Run the rman script

Since log sequence 13 thread 2 next_change# is 3 changes ahead of thread 1 sequence 25 we are using sequence 14 to stop recovery. This will restore the data files and recover them completely using the online logs.

run {
SET UNTIL SEQUENCE 14 THREAD 2;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

Finally add the tempfiles with sentences from step 6.




# Preview
RESTORE DATABASE PREVIEW;
RESTORE TABLESPACE users PREVIEW;

# Preview Summary
RESTORE DATABASE PREVIEW SUMMARY;
RESTORE TABLESPACE users PREVIEW SUMMARY;


RMAN> list incarnation of database aptdb ;


RMAN> LIST INCARNATION ;


List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 APTDB 204677159 CURRENT 1 01-JUN-11





Debug the restore session using:

RMAN trace

C:\>rman target / log=RmanLog.txt trace=rmanTrace.txt
RMAN> debug on;
RMAN> restore datafile 1;
RMAN> debug off;
RMAN> exit;



Resetting RMAN to a Previous Incarnation in NOCATALOG Mode


CONNECT TARGET / NOCATALOG

# step 1: start and mount a control file that knows about the incarnation to which
# you want to return. Refer to the RESTORE command for appropriate options.
STARTUP NOMOUNT;
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;

# step 2: obtain the primary key of old incarnation
LIST INCARNATION OF DATABASE trgt;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ------------- ------- ---------- ----------
1 2 TRGT 1334358386 PARENT 154381 OCT 30 2007 16:02:12
1 116 TRGT 1334358386 CURRENT 154877 OCT 30 2007 16:37:39

# step 3: in this example, reset database to incarnation key 2
RESET DATABASE TO INCARNATION 2;

# step 4: restore and recover the database to a point before the RESETLOGS
RESTORE DATABASE UNTIL SCN 154876;
RECOVER DATABASE UNTIL SCN 154876;

# step 5: make this incarnation the current incarnation and list incarnations:
ALTER DATABASE OPEN RESETLOGS;
LIST INCARNATION OF DATABASE trgt;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- ------- ---------- ----------
1 2 TRGT 1334358386 PARENT 154381 OCT 30 2007 16:02:12
1 116 TRGT 1334358386 PARENT 154877 OCT 30 2007 16:37:39
1 311 TRGT 1334358386 CURRENT 156234 AUG 13 2007 17:17:03



Backupset Maintenance using the configured retention policy

RMAN> list backup summary;
list backup by datafile;
list backup of database;
list backup of archivelog all;
list backup of controlfile;



Start RMAN in debug mode:
rman target debug trace=rman.trc log=rman.log

Activate sql traces
rman> set echo on;
rman> sql "alter system set max_dump_file_size=UNLIMITED";
rman> sql "alter session set events ''10046 trace name context forever, level 12''" ;








###########################
# RMAN Format Description
###########################

%a Current database activation id

%A Zero-filled activation ID

%c The copy number of the backup piece within a set of duplexed backup pieces.bMaximum value is 256

%d Database name

%D Current day of the month from the Gregorian calendar in format DD

%e Archived log sequence number

%f Absolute file number

%F Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name

%h Archived redo log thread number

%I DBID

%M Month in the Gregorian calendar in the format MM

%n Database name, padded on the right with x characters to a total length of eight characters

%N Tablespace name. Only valid when backing up datafiles as image copies.

%p Piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1
for each backup piece created. If a PROXY is specified, the %p variable must be included in the FORMAT
string either explicitly or implicitly within %U.

%r Resetlogs ID

%s Backup set number. This number is a counter in the control file that is incremented for each backup set.
The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup
control file, then duplicate values can result. CREATE CONTROLFILE initializes the counter at 1.

%S Zero-filled sequence number

%t Backup set time stamp, a 4-byte value derived as the number of seconds elapsed since a fixed reference time.
The combination of %s and %t can be used to form a unique name for the backup set.

%T Year, month, and day in the Gregorian calendar in the format: YYYYMMDD

%u An 8-character name constituted by compressed representations of the backup set or image copy number and the
time the backup set or image copy was created

%U A system-generated unique filename (default). %U is different for image copies and backup pieces.
For a backup piece, %U is a shorthend for %u_%p_%c and guarantees uniqueness in generated backup filenames.
For an image copy of a datafile, %U means the following: data-D-%d_id-%I_TS-%N_FNO-%f_%u

%Y Year in this format: YYYY

%% Percent (%) character. For example, %%Y translates to the string %Y



RMAN> LIST FAILURE;

no failures found that match specification

RMAN> ADVISE FAILURE;

no failures found that match specification

RMAN> ADVISE FAILURE 101;


show COMPRESSION ALGORITHM;


CONFIGURE COMPRESSION ALGORITHM TO 'alg_name';


SQL> col ALGORITHM_NAME format a10
select * from v$rman_encryption_algorithms

ALGORITHM_ID ALGORITHM_ ALGORITHM_DESCRIPTION IS_ RES
------------ ---------- ---------------------------------------------------------------- --- ---
1 AES128 AES 128-bit key YES NO
2 AES192 AES 192-bit key NO NO
3 AES256 AES 256-bit key NO NO



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

restore database preview and validate


RMAN> restore database preview;

RMAN> restore database preview;

RMAN> restore database from tag FULL_BKP preview;

RMAN> restore datafile 1, 2 preview;

RMAN> restore archivelog all preview summary;

RMAN> restore archivelog from time 'sysdate - 1/24' preview summary;

RMAN> restore archivelog from scn 25 preview summary;

RMAN> restore database validate check logical;

RMAN> restore database validate;

RMAN> restore database from tag FULL_BKP validate;

RMAN> restore datafile 1 validate;

RMAN> restore archivelog all validate;

RMAN> restore controlfile validate;

RMAN> restore tablespace users validate;

—————————————————————

RMAN> validate backupset 999 check logical;

Use the RMAN> list backup; command to obtain the backupset key (99 above)

RMAN> validate database check logical;

RMAN> validate database;



SET DBID 669001291;

restore until time 'sysdate-3' CONTROLFILE to 'c:\temp\cfile' from autobackup;

restore until time 'sysdate-3' CONTROLFILE to 'c:\temp\cfile' from autobackup maxdays 100;

startup force nomount;

RMAN> RUN
{ SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F';
RESTORE CONTROLFILE FROM AUTOBACKUP; }


select DBID, NAME ,STATUS from RC_DATABASE_INCARNATION;

LIST BACKUP; # lists backup sets, image copies, and proxy copies
LIST BACKUPSET; # lists only backup sets and proxy copies
LIST COPY; # lists only disk copies
LIST BACKUP BY FILE; # shows backup sets, proxy copies, and image copies
LIST COPY BY FILE; # shows only disk copies
LIST EXPIRED BACKUP BY FILE;
LIST BACKUP SUMMARY; # lists backup sets, proxy copies, and disk copies

LIST EXPIRED BACKUP SUMMARY;

# lists backups of all files in database
LIST BACKUP OF DATABASE;
# lists copy of specified datafile
LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf';
# lists specified backup set
LIST BACKUPSET 213;
# lists datafile copy
LIST DATAFILECOPY '/tmp/tools01.dbf';
# specify a backup set by tag
LIST BACKUPSET TAG 'weekly_full_db_backup';
# specify a backup or copy by device type
LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf' DEVICE TYPE sbt;
# specify a backup by directory or path
LIST BACKUP LIKE '/tmp/%';
# specify a backup or copy by a range of completion dates
LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '10-DEC-2002' AND '17-DEC-2002';
# specify logs backed up at least twice to tape
LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;


RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 VIHAAN 1611193673 PARENT 1 18-SEP-11
2 2 VIHAAN 1611193673 CURRENT 787897 13-DEC-11





LIST EXPIRED BACKUP




RMAN> show all;

RMAN configuration parameters for database with db_unique_name VIHAAN are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_vihaan.f'; # default



contralfile autobackup location

oracle_home/dba

Piece Name: /u01/app/oracle/product/11.2.0/db_1/dbs/c-1611193673-20120120-00

rman target=sys/{password}@ldg auxiliary=/ @create_standby.rman

Useful RMAN COMMANDS

Duplicate from earlier disk backup
RUN
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL c4 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL c5 DEVICE TYPE DISK;
DUPLICATE TARGET DATABASE to ERPSAND;
}
---------------------------------------------------
restore a particular sequence of a thread
restore archivelog from sequence 73 thread 3;
----------------------------------------------------------------------
Which datafile is in which backup piece

SQL> select handle from v$backup_piece where set_count in(select set_count from v$backup_datafile where file#=5);
--------------------------------------------------------------
Delete old archive logs

1. delete noprompt archivelog all backed up 1 times to device type disk
2. delete noprompt archivelog all backed up 1 times to device type disk completed before '(sysdate-0.2)';
------------------------------------------------------
catalogging a backup piece in rman
catalog backuppiece '/erpuat/orashr/pju_bkp4cloning/ccm4aoeu_1_1' ;
or
CATALOG START WITH '/erpuat/orashr/pju_bkp4cloning/c';
---------------------------------------------------
backing up log sequneces

run{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/erp/ohr/bkp4cloning/ARCH_2_%U.rman';
backup archivelog from logseq=75 until logseq=88 thread 2 ;
}
----------------------------------------------------------
To clean rman tape config

rman> configure CHANNEL DEVICE TYPE 'SBT_TAPE' clear;
---------------------------------------------------------------
 Restore the archive logs to production diskgroup using the following commands if not present in the ASM diskgroup or if rman backup deleted them

     Rman target /   
    restore archivelog from sequence 17958 until sequence 17970 thread 1;
    restore archivelog from sequence 16967 until sequence 16980 thread 2;
----------------------------------------------------------
To check  database complete backup.

list backup  of database summary completed  between '15-JAN-2009' and '16-JAN-2009';
-------------------------------------------------
to check archive log backup

list backup of archivelog time between "to_date('16-JAN-2009 00:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('16-JAN-2009 00:00:00','DD-MON-YYYY HH24:MI:SS')";
-----------------------------------------------
To monitor the Rman progress

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0
  AND SOFAR <> TOTALWORK ;
----------------------------------------------------
 Few Rman commands

copy archivelog '+ARCHLOGS/SPPWP_EWDC/ARCHIVELOG/2010_04_29/thread_1_seq_3544.1229.717603011' to '/orabkup/thread_1_seq_3544.1229.717603011';

registering logfile in dr database thru sqlplus

SQL> alter database register logfile '/spdb/data/arch/sppw/thread_2_seq_3521.1228.717601065';

Database altered.
------------------------------------------------------------
Few Rman commands

Restore controlfile:
restore controlfile from '/u02/oraback/iiss/ARCH.iiss1.20100528.1212.CTRL.cf_D-IISS_id-3057886536_4qleqchk.compr.rman';
-----------------------------------------------------------------------------
To restore individual archived logs from rman backups to a specified directory

RMAN> run
 {
#optional line to override default location for a restore
set archivelog destination to "/spdb/data/backup/psdrp1";
restore archivelog from logseq=101837 until logseq=101839;
}
----------------------------------------------------
Clean archivelogs

delete force archivelog all completed before '(sysdate-100)';
--------------------------------------------------
List database backups

list backup of database completed after '(SYSDATE-.5)';
list backup summary  completed after '(SYSDATE-.5)';
list backupset 20459  ;

    list archivelog all;
    delete noprompt expired archivelog all ;
    crosscheck backup ;
    delete noprompt obsolete;
    backup archivelog all delete input ;
    list archivelog all;
    list copy of archivelog until time 'SYSDATE-10' ;
    list copy of archivelog from time 'SYSDATE-10'
    list copy of archivelog from time 'SYSDATE-10′ until time 'SYSDATE-2';
    list copy of archivelog from sequence 1000  ;
    list copy of archivelog until sequence 1500 ;
    list copy of archivelog from sequence 1000 until sequence 1500 ; 

-- Archivelog Delete Commands

    delete archivelog all;
    delete archivelog until time 'SYSDATE-10' ;
    delete archivelog from time 'SYSDATE-10';
    delete archivelog from time 'SYSDATE-10' until time 'SYSDATE-2';
    delete archivelog from sequence 1000 ;
    delete archivelog until sequence 1500 ;
    delete archivelog from sequence 1000 until sequence 1500 ;
    DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE = 300;
    DELETE NOPROMPT ARCHIVELOG ALL;
    delete noprompt expired archivelog all;

-- Deleting all backups:

   DELETE BACKUP;

-- noprompt statement for do not yes-no question.
 
   delete noprompt archivelog until time 'SYSDATE-10' ; 

-- Deleting Image copy backup:

   DELETE COPY;

-- Deleting obsolete backups older than 3 days:

      delete obsolete recovery window of 3 days;

-- Deleting expired backup

      delete expired backup;


-- Checking Archive log files with crosscheck:

      crosscheck archivelog all;

-- Checking whole backup with crosscheck:

      crosscheck backup;

-- Checking image copy backup with crosscheck:

      crosscheck copy;

-- Checking tagged backup with crosscheck:

      crosscheck backuppiece tag = 'nightly_backup';


-- Showing Backup Preview (In which objects are listed in):

      restore database preview;

      restore tablespace users preview;



-- Verification whole backup:

      backup validate;

-- Verification archive log files backup:

      backup validate database archivelog all;

-- Verification whole database restore:

      restore database validate;

-- Verification control file restore:

      restore controlfile validate;




-- To debug and trace RMAN backup

     $rman target=/ debug=all trace=rman.trc

-- Create the backup log file

     $export NLS_DATE_FORMAT=’dd.mm.yyyy hh24:mi:ss’;
     $rman target=/ log=rman.log



-- then confirm this

    report need backup;

convert tablespace tbs_2 format '/tmp/tbs_2_%U.df';

RUN {

change archivelog all crosscheck;

report obsolete orphan;

report obsolete;

crosscheck backup;

crosscheck copy;

crosscheck backup of controlfile;

delete noprompt expired backup;

delete noprompt expired archivelog all;

delete noprompt expired backup of controlfile;

delete force noprompt expired copy;

delete force noprompt obsolete orphan;

delete force noprompt obsolete;

}

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

http://anuj-singh.blogspot.com/2021/10/rman-restore-database.html

rman script 

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

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

run {

allocate channel ch1 device type DISK;

allocate channel ch2 device type DISK;

sql "alter system archive log current";

backup incremental level=0 tag "RMANFULL" format '/dumps/UGARY/%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 '/dumps/UGARY/%d_T%T_arch_s%s_p%p_t%t' archivelog all filesperset 4 tag "RMANFULL";

backup format '/dumps/UGARY/%d_T%T_cf%s_p%p_t%t' current controlfile tag "RMANFULL";

restore database preview;

}

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

 run {

restore controlfile from '/dumps/UGARY/UGARY_T20220716_cf34_p1_t1110180865';

 alter database mount;

 }

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

#!/bin/bash

export ORACLE_SID=vihcdbd8

export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1

export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/sbin:/bin:/usr/local/bin

export LOG=/home/oracle/delete.log

echo "Archive Delete :`date`" >$LOG

rman target / <<EOF>>$LOG

RUN {

delete noprompt ARCHIVELOG ALL ;

}

exit;

EOF

echo "Backup finished at `date`" >> $LOG

exit ;

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 DELETE INPUT 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;

}

00 1 * * 0 /home/oracle/ORCLX/rman0.sh &> /dev/null

00 1 * * 1-6 /home/oracle/ORCLX/archive1.sh &> /dev/null

 cat /home/oracle/ORCLX/archive1.sh

#!/bin/bash

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

export ORACLE_SID=ORCLX1

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_3

export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/sbin:/bin:/usr/local/bin

export LOG=/home/oracle/ORCLX/rman_`/bin/date +\%d\%m\%y:%H:%M:%S`.log

echo "Archive backup :`date`" >$LOG

rman target / <<EOF>>$LOG

run {

    backup as compressed backupset  format '/home/oracle/%d/%T_@_%s_%p_%t'

    (archivelog all delete input);

}

report schema;

restore database preview;

run {

        delete noprompt obsolete;

}

EOF

echo "Archive backup at `date`" >> $LOG

exit ;

[oracle@ ORCLX]$ cat rman0.sh

#!/bin/bash

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

export ORACLE_SID=ORCLX1

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_3

export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/sbin:/bin:/usr/local/bin

export LOG=/home/oracle/ORCLX/rman_`/bin/date +\%d\%m\%y:%H:%M:%S`.log

echo "level 0 backup :`date`" >$LOG

rman target / <<EOF>>$LOG

run {

        backup as compressed backupset

        incremental level 0

        format '/home/oracle/%d/%T_@_%s_%p_%t'  database include current controlfile  plus archivelog;

}

report schema;

restore database preview;

run {

        delete obsolete;

}

EOF

echo "End level 0 backup at `date`" >> $LOG

exit ;

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

cat rman0.sh

#!/bin/bash

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

export ORACLE_SID=oradb

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1

export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/sbin:/bin:/usr/local/bin

export LOG=/u01/app/oracle/RmanBackup/rman_`/bin/date +\%d\%m\%y:%H:%M:%S`.log

echo "level 0 backup :`date`" >$LOG

rman target / <<EOF>>$LOG

run {

        backup as compressed backupset

        incremental level 0

        format '/u01/app/oracle/RmanBackup/%T_@_%s_%p_%t'  database include current controlfile  plus archivelog;

}

report schema;

restore database preview;

run {

delete noprompt expired backup of database;

}

EOF

echo "End level 0 backup at `date`" >> $LOG

exit ;

RMAN> list backup ;

 List of Archived Logs in backup set 33

  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time

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

  1    7       6688434    16-JUL-2022 07:34:18 6688443    16-JUL-2022 07:34:21

  1    8       6688443    16-JUL-2022 07:34:21 6688451    16-JUL-2022 07:34:21  <<<<<< check this date for restore 

RUN

{

  set until time "to_date('16.07.2022 07:34:21','DD.MM.YYYY HH24:MI:SS')";

  restore database;

recover database;

alter database open resetlogs;

}

select 'restore validate archivelog from scn '||min_first_change#||' until scn '||max_next_change#  "Restore" from v$backup_archivelog_summary;

set numf 999999999999999999

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

select 

 NUM_FILES_BACKED,          

 NUM_DISTINCT_FILES_BACKED, 

 MIN_FIRST_CHANGE#  ,       

 MAX_NEXT_CHANGE# ,         

 MIN_FIRST_TIME ,           

 MAX_NEXT_TIME ,            

 INPUT_BYTES ,              

 OUTPUT_BYTES  ,            

 COMPRESSION_RATIO ,        

 --INPUT_BYTES_DISPLAY       

 --OUTPUT_BYTES_DISPLAY      

 CON_ID 

from  v$backup_archivelog_summary;

 NUM_FILES_BACKED NUM_DISTINCT_FILES_BACKED   MIN_FIRST_CHANGE#    MAX_NEXT_CHANGE# MIN_FIRST_TIME      MAX_NEXT_TIME               INPUT_BYTES        OUTPUT_BYTES   COMPRESSION_RATIO              CON_ID

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

                340                       340           203747990          6696255128 18-02-2018 01:00:04 25-07-2022 01:00:04         48832481792         14235590656                   3                   1

====

SQL> RECOVER automatic DATABASE UNTIL TIME '2019-10-21:06:00:00' USING BACKUP CONTROLFILE;

alter database recover automatic  database  until time '2023-06-30:23:59:59' using backup controlfile

alter database recover automatic  database  until time 

select to_char(controlfile_time,'dd/mm/yyyy hh24:mi:ss') from v$database;

select r.file#, to_char(r.time, 'dd/mm/yyyy hh24:mi:ss'), f.name

from v$recover_file r, v$datafile f where r.file# = f.file#;

alter database recover managed standby cancel;

recover automatic standby database until time '2020-08-15:08:00:00';

====

restore validate preview summary archivelog from time "TO_DATE('24-JUL-2022 22:03:32','DD-MON-YYYY HH24:MI:SS')";

.

.

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 25-JUL-22

For before 10 min RMAN recovery
set until time "SYSDATE -10/24/60" ;

 restore validate archivelog UNTIL TIME "to_date('23/07/2022 23:59:59','dd/mm/yyyy hh24:mi:ss')";

   rman target / log="/home/oracle/rman_log.log"

rman target sys log="/home/oracle/rman_log.log" append

RMAN> spool log to '/home/oracle/rman_log2.log' append

====

cat $HOME/rman_scripts/dgbdr_level0.rcv

connect target /

run {

        backup as compressed backupset

        incremental level 0

        format '/dumps/DGBDR/%T_@_%s_%p_%t'

        database

        include current controlfile

        plus archivelog;

}

run {

        delete obsolete;

}

exit;

=====

run {   backup as compressed backupset

        incremental level 0

        format '/dumps/VIHAAN8/rman/%T_@_%s_%p_%t'

        database

        include current controlfile

        plus archivelog;

}

---

 cat $HOME/rman_scripts/dgbdr_arclogs.rcv

connect target /

run {

    backup as compressed backupset

    format '/dumps/DGBDR/%T_@_%s_%p_%t'

    (archivelog all delete input);

}

run {

        delete obsolete;

}

run {

        backup tag vihaan8_CONTROL current controlfile format '/dumps/DGBDR/%d_%T_%s_%p_CONTROL';

}

w

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

good link

RMAN block recovery & ORA-600

http://oraclehandson.wordpress.com/2011/04/01/rman-block-recovery-ora-600/

https://www.youtube.com/watch?v=Qt8eV5WA7yw

Recover a database with JUST the datafiles?

rman backup script

run {
        allocate channel dev0 type disk format '/u02/DISK_BACKUP/%d_%t_%s_%p.bkp';
        allocate channel dev1 type disk format '/u02/DISK_BACKUP/%d_%t_%s_%p.bkp';
        allocate channel dev2 type disk format '/u02/DISK_BACKUP/%d_%t_%s_%p.bkp';
        allocate channel dev3 type disk format '/u02/DISK_BACKUP/%d_%t_%s_%p.bkp';
        backup
             incremental level 0
             skip inaccessible
             tag DiskFull_Oracle_Backup
             filesperset 1
             database
             ;
        sql 'alter system archive log current';
        backup
             filesperset 1
             format '/u02/DISK_BACKUP/A_%d_%u_%s_%T'
             archivelog all
             ;
        backup
             filesperset 1
             format '/u02/DISK_BACKUP/%d_%t_%s_%p.cf'
             current controlfile
             ;
        backup spfile;
        }

run { 
crosscheck backupset of database;
crosscheck backupset of controlfile;
crosscheck backupset of archivelog all;
crosscheck backup;
delete noprompt expired backup;
delete force  noprompt obsolete;
delete expired archivelog all;
delete noprompt obsolete;
delete noprompt obsolete orphan;
crosscheck archivelog all;
delete expired archivelog all;
CROSSCHECK COPY OF CONTROLFILE;
}

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';

run {
        allocate channel dev0 type disk format '/u01/app/Rman/%d_%t_%s_%p.bkp';
         backup
             incremental level 0
             skip inaccessible
             tag DiskFull_Oracle_Backup
             filesperset 1
             database
             ;
        sql 'alter system archive log current';
        backup
             filesperset 1
             format '/u01/app/Rman/A_%d_%u_%s_%T'
             archivelog all delete input
             ;
        backup
             filesperset 1
             format '/u01/app/Rman/%d_%t_%s_%p.cf'
             current controlfile
             ;
        sql 'alter system archive log current';
        backup
             filesperset 1
             format '/u01/app/Rman/A_%d_%u_%s_%T'
             archivelog all delete input
             ;
        backup spfile format '/u01/app/Rman/spfile_%d_%s_%T.bak' tag 'spfile backup';
        }

http://anuj-singh.blogspot.com/2016/09/  

http://anuj-singh.blogspot.com/2021/10/rman-restore-database.html 

run   
    {   
      Shutdown Immediate   
      Startup Mount   
      Set Until Sequence 15 Thread 1;   
     #set until scn  42773496;
     #set until time "to_date('21-OCT-2020 14:45:00','DD-MON-YYYY HH24:MI:SS')";
      Restore Database;   
      Recover Database;   
      Alter Database Open Resetlogs;   

    } 

====

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

RMAN: RAC Backup and Recovery using RMAN [ID 243760.1]

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     981393
         2     744280





set numf 99999999999999
select sequence#, thread#, first_change#, next_change#  ,COMPLETION_TIME  from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# 
and  sequence# in (981393,744280)
;




set numf 99999999999999
select sequence#, thread#, first_change#, next_change#    from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# 
and  sequence# in (981393,744280)
;



      SEQUENCE#         THREAD#   FIRST_CHANGE#    NEXT_CHANGE#
--------------- --------------- --------------- ---------------
         981393               1     10540998662     10541024661
         744280               2     10541014332     10541024657  * In a RAC environment, use the lower to ensure we have the redo required from threads



select sequence#, thread#, first_change#, next_change#  from v$backup_redolog
           where 1=1 
and sequence# in (981393,744280);

      SEQUENCE#         THREAD#   FIRST_CHANGE#    NEXT_CHANGE#
--------------- --------------- --------------- ---------------
         981393               1     10540998662     10541024661
         744280               2     10541014332     10541024657 *  In a RAC environment, use the lower to ensure we have the redo required from threads

for sequnce no 744280+1

  The options are "until time", "until scn", or "until sequence".

to create restore script

for RAC

define oldname='ibrac'
define newname='xbrac'


set pages 0 head off feed off veri off lines 500 pages 2000 echo off termout off trimspool on

column cmd format a140
spool restore_recover_&&newname\.rman

select 'RUN '||CHR(10)||'{' cmd from dual
union all
--Rename the datafiles
select 'SET NEWNAME FOR DATAFILE '||file#||' TO '''||NNAME||''';' cmd
from (SELECT FILE# ,NAME,replace(replace(name,upper('&&oldname'),upper('&&newname')),lower('&&oldname'),lower('&&newname')) NNAME FROM V$DATAFILE) df
union all
--Rename the online redo logs
select 'SQL "ALTER DATABASE RENAME FILE '''''||MEMBER||''''' TO '||chr(10)||' '''''||replace(replace(member,upper('&&oldname'),upper('&&newname')),lower('&&oldname'),lower('&&newname'))||''''' ";' cmd
from (SELECT GROUP#,MEMBER FROM V$LOGFILE)
union all
--Do a SET UNTIL to prevent recovery of the online logs
select 'SET UNTIL SCN '||MINSCN||';' cmd
from (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#
                             )
)
      )
union all
--restore the database and switch the datafile names
select  'RESTORE DATABASE CHECK READONLY;' cmd from dual
union all
select 'SWITCH DATAFILE ALL;' cmd from dual
union all
--recover the database
select 'RECOVER DATABASE DELETE ARCHIVELOG;' from dual
union all
select '}'||CHR(10)||'EXIT' from dual
/

select sequence#, thread#, first_change#, next_change#  from v$backup_redolog
           where 1=1 
and sequence# in (981393,744280);

--with scn no !!!

set linesize 200 heading off feedback off 
col file_name format a100
col "Query For RMAN Restoration" for a70

select 'run {' "Query For RMAN Restoration" from dual 
union all
select 'set newname for datafile ' ||FILE#|| ' to '||'''/u01/datafile/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||'.dbf'' ;' file_name from v$datafile
union all
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#
                             )
)
union all
select 'restore database;' from dual 
union all
select 'switch datafile all;' from dual 
union all
select 'recover database;' from dual 
union all
select '} ' from dual ;







run {
set newname for datafile 1 to '/u01/datafile/system01.dbf' ;
set newname for datafile 2 to '/u01/datafile/apex_data01.dbf' ;
set newname for datafile 3 to '/u01/datafile/sysaux01.dbf' ;
set newname for datafile 4 to '/u01/datafile/undotbs01.dbf' ;
set newname for datafile 5 to '/u01/datafile/undotbs02.dbf' ;
set newname for datafile 7 to '/u01/datafile/users01.dbf' ;
set newname for datafile 8 to '/u01/datafile/test_data01.dbf' ;
set newname for datafile 9 to '/u01/datafile/test_data_ind_01.dbf' ;
set newname for datafile 10 to '/u01/datafile/lobtest.dbf' ;
set newname for datafile 11 to '/u01/datafile/test_uniform.dbf' ;
set newname for datafile 12 to '/u01/datafile/test.dbf' ;
set newname for datafile 13 to '/u01/datafile/test1.dbf' ;
set newname for datafile 14 to '/u01/datafile/test1.dbf' ;
set newname for datafile 15 to '/u01/datafile/bigtabs.dbf' ;
set newname for datafile 16 to '/u01/datafile/test_data02.dbf' ;
set until scn 2773770359;
restore database;
switch datafile all;
recover database;
}






---with Sequence 

c
set linesize 200 heading off feedback off 
col file_name format a100
col "Query For RMAN Restoration" for a70

select 'run {' "Query For RMAN Restoration" from dual 
union all
select 'set newname for datafile ' ||FILE#|| ' to '||'''/u01/datafile/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||'.dbf'' ;' file_name from v$datafile
union all
select 'Set Until Sequence '||(sequence# +1) || ' ' ||'Thread '||thread# ||';' 
--, next_change# 
   from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# 
and  next_change#  in (select min(next_change# ) 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#
                             )
                     )
					 )
union all
select 'restore database;' from dual 
union all
select 'switch datafile all;' from dual 
union all
select 'recover database;' from dual 
union all
select '} ' from dual ;


run {
set newname for datafile 1 to '/u01/datafile/system01.dbf' ;
set newname for datafile 2 to '/u01/datafile/apex_data01.dbf' ;
set newname for datafile 3 to '/u01/datafile/sysaux01.dbf' ;
set newname for datafile 4 to '/u01/datafile/undotbs01.dbf' ;
set newname for datafile 5 to '/u01/datafile/undotbs02.dbf' ;
set newname for datafile 7 to '/u01/datafile/users01.dbf' ;
set newname for datafile 8 to '/u01/datafile/test_data01.dbf' ;
set newname for datafile 9 to '/u01/datafile/test_data_ind_01.dbf' ;
set newname for datafile 10 to '/u01/datafile/lobtest.dbf' ;
set newname for datafile 11 to '/u01/datafile/test_uniform.dbf' ;
set newname for datafile 12 to '/u01/datafile/test.dbf' ;
set newname for datafile 13 to '/u01/datafile/test1.dbf' ;
set newname for datafile 14 to '/u01/datafile/test1.dbf' ;
set newname for datafile 15 to '/u01/datafile/bigtabs.dbf' ;
set newname for datafile 16 to '/u01/datafile/test_data02.dbf' ;
Set Until Sequence 10 Thread 1;
restore database;
switch datafile all;
recover database;
}

allocate channel from different nodes

$rman target /

run {
allocate channel d0_1 device type disk maxopenfiles 32 connect 'sys/sys@//ibrac01.anuj.net:1521/ibrac' ;
allocate channel d0_2 device type disk maxopenfiles 32 connect 'sys/sys@//ibrac02.anuj.net:1521/ibrac' ;

DELETE EXPIRED ARCHIVELOG ALL;

release channel d0_1;
release channel d0_2;
}

using target database control file instead of recovery catalog
allocated channel: d0_1
channel d0_1: SID=1836 instance=ibrac1 device type=DISK

allocated channel: d0_2
channel d0_2: SID=1346 instance=ibrac2 device type=DISK

specification does not match any archived log in the repository

released channel: d0_1

released channel: d0_2

validate

export NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss' ;

RUN
 {
set until time "to_date('28-AUG-2023 01:30:00','DD-MON-YYYY HH24:MI:SS')";
restore spfile validate;
restore controlfile validate;
restore archivelog from time 'sysdate-23/24' validate;
}

 

cat Rman.sh

cat: Rman.sh: No such file or directory

[oracle@wcp12cr2 ~]$ cd RmanBackup/

[oracle@wcp12cr2 RmanBackup]$ cat Rman.sh

#!/bin/bash

export ORACLE_SID=orcl

export ORACLE_HOME=/oracle/db/ohome

export PATH=$PATH:$ORACLE_HOME/bin

export LOG=/home/oracle/backup_orcl.log

echo " Backup started at :`date`" >$LOG

rman target / <<EOF>>$LOG

run {

allocate channel c1 type disk ;

allocate channel c2 type disk ;

backup AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 database tag ORCL_FULL format '/home/oracle/RmanBackup/rman_bkp_inc_%d_%t_%s_%p_%U';

sql 'alter system archive log current';

sql 'alter system archive log current';

backup archivelog all delete all input FORMAT '/home/oracle/RmanBackup/Arc_%t_set%s_piece%p.rman';

backup spfile format '/home/oracle/RmanBackup/spfile_%d_%s_%T_dbid%I.rman';

backup current controlfile format '/home/oracle/RmanBackup/ctl_%t_dbid%I.rman';

release channel c1;

release channel c2;

}

exit

EOF

echo " Backup completed at :`date`" >>$LOG

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

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

RUN

{

 set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/Rman/%F';

ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;

ALLOCATE CHANNEL ch12 TYPE DISK MAXPIECESIZE 10G;

ALLOCATE CHANNEL ch13 TYPE DISK MAXPIECESIZE 10G;

BACKUP   FORMAT '/opt/Rman/%d_D_%T_%u_s%s_p%p'   

INCREMENTAL LEVEL 0 DATABASE   CURRENT CONTROLFILE FORMAT '/opt/Rman/%d_C_%T_%u'

SPFILE   FORMAT '/opt/Rman/%d_S_%T_%u' PLUS ARCHIVELOG FORMAT '/opt/Rman/%d_T%T_arch_s%s_p%p_t%t';

sql "alter system archive log current";

backup format '/opt/Rman/%d_T%T_arch_s%s_p%p_t%t' archivelog all filesperset 4 tag "RMANFULL";

sql "alter system archive log current";

backup format '/opt/Rman/%d_T%T_arch_s%s_p%p_t%t' archivelog all filesperset 4 tag "RMANFULL";

SQL "alter database backup controlfile to trace as ''/opt/Rman/ctl_@_trace.sql'' reuse resetlogs";

sql "ALTER DATABASE CREATE STANDBY CONTROLFILE AS ''/opt/Rman/STANDBY_control01.ctl'' REUSE";

SQL "create pfile=''/opt/Rman/init@.ora'' from spfile";

report schema;

restore database preview;

RELEASE CHANNEL ch11;

RELEASE CHANNEL ch12;

RELEASE CHANNEL ch13;

}

RMAN> run { set until sequence 120;
            restore database root ;  ------------------------->CDB$ROOT
            restore database "PDB$SEED";  -------------------------------->PDB$SEED is required
            restore database PDB1; }    ------------------------------->PDB needing to restore

OR

RMAN>run { Set until sequence 120 ;
          restore database root database "PDB$SEED" database PDB1; } 

If directory structure on test server is different than the target/source database, 'SET NEWNAME' will be needed:

RMAN> report schema ; 

This would show the file number associated with CDB$ROOT/PDB$SEED/PDB1.

Use 'set newname for datafile <fileno> to <new path>' to specify a new path for the datafiles.    

RMAN> run { set newname for datafile <fileno> to 'system.dbf' ;
            set newname for datafile <fileno> to 'system.dbf' ;
            ......

            restore database root; ------------------------->CDB$ROOT
            restore database "PDB$SEED"; -------------------------------->PDB$SEED is required 
            restore database PDB1;  ------------------------------->PDB we want to restore
            switch datafile all; }  

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

{

set controlfile autobackup format for device type disk to '/rmanbackup/oradata/uknppp/cf_%F';

restore spfile from autobackup;

}

restore until time 'sysdate-1' CONTROLFILE to '/u02/oradata/20130218_control01.ctl' from autobackup;

restore until time "to_date('2013-02-18:12:01','yyyy-mm-dd:hh24:mi')" CONTROLFILE to '/u02/oradata/20130218_control01.ctl' from autobackup;

RUN {

ALLOCATE CHANNEL channel0 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/opt/xx/xxamPluginforOracleRMAN/libOracleRMANPlugin.so';

RESTORE until time 'sysdate-1' CONTROLFILE FROM AUTOBACKUP maxdays 30;

}

run

{

set until time "to_date('2013-02-18:12:01','yyyy-mm-dd:hh24:mi')";

restore controlfile from autobackup preview;

}

 

rman> shutdown;

 

rman> startup force nomount;

 rman> run

{

set controlfile autobackup format for device type disk to '/rmanbackup/oradata/uknppp/cf_%F';

restore controlfile from autobackup;

 

}

RUN {

ALLOCATE CHANNEL channel0 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/opt/veeam/VeeamPluginforOracleRMAN/libOracleRMANPlugin.so';

SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F_RMAN_AUTOBACKUP.vab';

RESTORE controlfile FROM 'c-4097408439-20200410-00_RMAN_AUTOBACKUP.vab';

}

restore until time 'sysdate-1' CONTROLFILE to '/u02/oradata/20130218_control01.ctl' from autobackup maxdays 31;

ww

RMAN> restore until time 'sysdate-1/24' CONTROLFILE to '/u01/app/control01-1205.ctl' from '/u01/app/Rman/c-2798080093-20240511-06';

Starting restore at 12-MAY-2024 15:16:21

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

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

Finished restore at 12-MAY-2024 15:16:23

RMAN>

BACKUP ARCHIVELOG SEQUENCE 30 DELETE INPUT;

BACKUP ARCHIVELOG SEQUENCE BETWEEN 31 AND 40 THREAD 1 DELETE INPUT;

CROSSCHECK ARCHIVELOG FROM SEQUENCE 0 THREAD 1;

RESTORE ARCHIVELOG SCN BETWEEN 94097 AND 106245;

backup device type disk format '/u03/backup/%U' database plus archivelog;

backup device type disk format '/u03/backup/ctrlf_%U' current controlfile;

restore controlfile from '/u03/backup/ctrlf_'; — where is the unique string generated by %U.

restore until time 'sysdate-3' CONTROLFILE to 'c:tempcfile' from autobackup;

restore until time 'sysdate-3' SPFILE to 'c:tempSPFILE' from autobackup maxdays 100;

RMAN>Run {Set until sequence 120;
                   recover database skip forever tablespace PDB2:SYSTEM,PDB2:USER,PDB2:SYSAUX,..... ;  }

spfile info

===========

SELECT

       distinct p.handle

FROM v$backup_piece_details p

     , v$backup_spfile sp

     , v$backup_spfile_details s

WHERE p.bs_key = s.bs_key

AND p.status = 'A'

--AND p.device_type = 'DISK'

ORDER BY 1 DESC

/    

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT='sys/password@node1:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT='sys/password@node1:1521/clusterservice' FORMAT='/backup/FULL/%d_%U'

 export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
[oracle@<NODE>]$ rman target sys/<PASSWORD>@<TNS_ALIAS> auxiliary sys/<PASSWORD>@<TNS_ALIAS>| tee /tmp/rmanDUPLICATE.log

rman restore spfile

RMAN> restore spfile to '/tmp/spfile.ora' from '/u01/app/Rman/spfile_ORADB_19_20240511.bak' ; 

 Starting restore at 11-MAY-2024 20:51:28 using channel ORA_DISK_1 channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/Rman/spfile_ORADB_19_20240511.bak channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 11-MAY-2024 20:51:29

LIST BACKUP OF CONTROLFILE;

LIST BACKUP OF SPFILE;

select start_time,end_time,status,autobackup_done, AUTOBACKUP_COUNT from V$RMAN_BACKUP_JOB_DETAILS where autobackup_done = 'YES';

START_TIM END_TIME  STATUS                  AUT AUTOBACKUP_COUNT

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

11-MAY-24 11-MAY-24 COMPLETED               YES                3

11-MAY-24 11-MAY-24 COMPLETED               YES                3

set nocfau;

run { 

crosscheck backupset of database;

crosscheck backupset of controlfile;

crosscheck backupset of archivelog all;

crosscheck backup;

delete noprompt expired backup;

delete force  noprompt obsolete;

delete expired archivelog all;

delete noprompt obsolete;

delete noprompt obsolete orphan;

crosscheck archivelog all;

delete expired archivelog all;

CROSSCHECK COPY OF CONTROLFILE;

}

from web -----

run { SET DBID 208759155; allocate channel ch1 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,ENV=(CvInstanceName=Instance001)"; allocate channel ch2 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,ENV=(CvInstanceName=Instance001)"; allocate channel ch3 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,ENV=(CvInstanceName=Instance001)"; allocate channel ch4 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,ENV=(CvInstanceName=Instance001)"; restore spfile from autobackup ; startup force nomount; restore controlfile from autobackup ; sql 'alter database mount'; restore database ; recover database until time = "TO_DATE('JAN 07 2021 20:50:13', 'MON DD YYYY HH24:MI:SS')"; alter database open resetlogs; }

duplicate 

DUPLICATE TARGET DATABASE TO TEST FROM ACTIVE DATABASE DB_FILE_NAME_CONVERT '/PROD','/TEST' PFILE='/u01/app/oracle/product/11.2/db_1/dbs/initTEST.ora';

 Restore the spfile and controlfile

RMAN> set dbid=<dbid>;
  run {
    set controlfile autobackup format for device type disk to '\<path>\%F';
    restore spfile from autobackup;
    startup force nomount ;                # to read parameters from restored spfile
    restore controlfile from autobackup;
    alter database mount ;
  }

https://anuj-singh.blogspot.com/   How to restore spfile from Rman backup ?

-- level 0 

cat $HOME/rman_scripts/level0.rcv

connect target /

run {

        backup as compressed backupset

        incremental level 0

        format '/dumps/%d/%T_@_%s_%p_%t'

        database

        include current controlfile

        plus archivelog;

}

run {

        delete obsolete;

}

-- level 1

run {

        backup as compressed backupset

        incremental level 1

        format '/dumps/%d/%T_@_%s_%p_%t'

        database

        include current controlfile

        plus archivelog;

}

run {

        delete obsolete;

}

-- archive log 

connect target /

run {

    backup as compressed backupset

    format '/dumps/%d/%T_@_%s_%p_%t'

    (archivelog all delete input);

}

run {

        delete obsolete;

}

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

Level 0

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

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

RUN {

allocate channel dev0 type disk format '/u01/app/Rman/%d_%t_%s_%p.bkp';

DELETE NOPROMPT EXPIRED BACKUP;

DELETE NOPROMPT OBSOLETE;

    BACKUP tag 'INCR0_DB' AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG DELETE INPUT;

sql 'alter system archive log current';

sql 'alter system archive log current';  

BACKUP CURRENT CONTROLFILE TAG 'INCR0_CTL';

    BACKUP SPFILE TAG 'INCR0_SPFILE';

sql 'alter system archive log current';

backup Tag 'INCR0_ARC' filesperset 1  archivelog all delete input ;

    RELEASE CHANNEL dev0;

report schema;

    restore database preview;

}

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

Level 1

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

RUN {

    allocate channel dev0 type disk format '/u01/app/Rman/%d_%t_%s_%p.bkp';

    BACKUP tag 'INCR_L1_DB' AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG DELETE INPUT;

    BACKUP CURRENT CONTROLFILE TAG 'INCR_L1_CTL';

    BACKUP SPFILE TAG 'INCR_L1_SPFILE';

    RELEASE CHANNEL dev0;

}

========

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

run{

  DUPLICATE DATABASE TO doradb BACKUP LOCATION '/u01/app/Rman/' nofilenamecheck until time "TO_DATE('17-MAY-2024 18:40:00','DD-MON-YYYY HH24:MI:SS')";

}

RMAN> list backup of datafile 1;

alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';

col first_change# for 9999999999999999

col  next_change# for 9999999999999999

define SCN_bkupstart=994376036921

select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change#

from v$archived_log

where &SCN_bkupstart between first_change# and next_change#;

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

How To Use RMAN Dynamic Channel Allocation For RAC Environments (Doc ID 1100443.1)

nsnames.ora entry:
RACTEST=
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = yes)
(FAILOVER = on)
(ADDRESS = (PROTOCOL = TCP)(HOST = <node1>)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = <node2>)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = <node3>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACTEST)
)
)

RMAN connect:
rman target <username>/<password>@RACTEST

The username and password are for a user with SYSDBA privileges.  

RMAN channel configuration:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;

-- after SCN
SELECT NAME, THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#
FROM V$ARCHIVED_LOG
WHERE 556890767466 >= FIRST_CHANGE#

SELECT LF.MEMBER, LF.GROUP#, L.THREAD#, L.SEQUENCE#, L.STATUS, L.FIRST_CHANGE#, L.NEXT_CHANGE#
FROM V$LOG L, V$LOGFILE LF
WHERE LF.GROUP# = L.GROUP#
AND 556890767466 >= L.FIRST_CHANGE#

delete force noprompt archivelog until time 'sysdate-1' ;

delete  force noprompt archivelog until time "to_date('2024-10-22 10:30:00', 'yyyy-mm-dd hh24:mi:ss')";

 If we were to query using function SCN_TO_TIMESTAMP to get respective timestamp of a past SCN,
oracle will pick the information from SMON_SCN_TIME table.
SCN that is greater than the MAX SCN that is available in SMON_SCN_TIME table, Oracle will get the results from the SGA.
So, as long as your SCN is between the range of the below o/p, you will get the results successfully or else it will fail with ORA- errors.

SELECT * FROM (SELECT MIN(SCN) FROM SMON_SCN_TIME), (SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE);