Search This Blog

Total Pageviews

Wednesday, 27 September 2017

Oracle Database drop 12c 


Oracle Drop Database  ..




SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/vihcdb/system01.dbf
/u01/app/oracle/oradata/vihcdb/pdbseed/system01.dbf
/u01/app/oracle/oradata/vihcdb/sysaux01.dbf
/u01/app/oracle/oradata/vihcdb/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/vihcdb/undotbs01.dbf
/u01/app/oracle/oradata/vihcdb/users01.dbf
/u01/app/oracle/oradata/vihcdb/pdbseed/users01.dbf
/u01/app/oracle/oradata/vihcdb/pdbseed/usertbs01.dbf
/u01/app/oracle/oradata/ORA12CP/ao1_mf_system_dw9fo2v9_.dbf
/u01/app/oracle/oradata/ORA12CP/ao1_mf_sysaux_dw9fmon4_.dbf
/u01/app/oracle/oradata/ORA12CP/ao1_mf_users_dw9fpt4j_.dbf

11 rows selected.

SQL> !ls -ltr /u01/app/oracle/oradata/vihcdb/*
-rw-r----- 1 oracle oinstall 52429312 Sep 27 03:40 /u01/app/oracle/oradata/vihcdb/redo02a.log
-rw-r----- 1 oracle oinstall 52429312 Sep 27 03:40 /u01/app/oracle/oradata/vihcdb/redo03a.log
-rw-r----- 1 oracle oinstall 734011392 Sep 27 03:40 /u01/app/oracle/oradata/vihcdb/system01.dbf
-rw-r----- 1 oracle oinstall 634396672 Sep 27 03:40 /u01/app/oracle/oradata/vihcdb/undotbs01.dbf
-rw-r----- 1 oracle oinstall 524296192 Sep 27 03:40 /u01/app/oracle/oradata/vihcdb/users01.dbf
-rw-r----- 1 oracle oinstall 576724992 Sep 27 03:40 /u01/app/oracle/oradata/vihcdb/sysaux01.dbf
-rw-r----- 1 oracle oinstall 20979712 Sep 27 03:41 /u01/app/oracle/oradata/vihcdb/temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep 27 03:43 /u01/app/oracle/oradata/vihcdb/redo01a.log
-rw-r----- 1 oracle oinstall 17842176 Sep 27 03:43 /u01/app/oracle/oradata/vihcdb/ora_control1.ctl
-rw-r----- 1 oracle oinstall 17842176 Sep 27 03:43 /u01/app/oracle/oradata/vihcdb/ora_control2.ctl

/u01/app/oracle/oradata/vihcdb/pdbseed:
total 1092280
-rw-r----- 1 oracle oinstall 20979712 Sep 25 06:35 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Sep 26 10:07 usertbs01.dbf
-rw-r----- 1 oracle oinstall 524296192 Sep 26 10:07 users01.dbf
-rw-r----- 1 oracle oinstall 209723392 Sep 26 10:07 sysaux01.dbf
-rw-r----- 1 oracle oinstall 173023232 Sep 26 10:07 system01.dbf



Imp !!!!!
**************************************
SQL> startup force mount restrict ;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 713031784 bytes
Database Buffers 352321536 bytes
Redo Buffers 5455872 bytes
Database mounted.


Drop database command will delete Datafiles, Controlfiles, online redologfiles, Spfile
Drop database command will not delete init.ora file, password files, listener.ora file and it will also not remove entries from the oratab files.



SQL> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options




No file now !!!!


SQL> !ls -ltr /u01/app/oracle/oradata/vihcdb/*
total 0


SQL> !cat /etc/oratab
#

vihcdb:/u01/app/oracle/product/12.1.0/db_1:N












Posted by



at





No comments:
  

































          

        

          

        

Sunday, 24 September 2017


          

        









 Patch 26609817: DATABASE RELEASE UPDATE 12.2.0.1.170814














Patch 26609817: DATABASE RELEASE UPDATE 12.2.0.1.170814



Last Updated 14-Aug-2017 18:32 (1+ month ago)



Product Oracle Database - Enterprise Edition

(More...)

Release Oracle Database 12.2.0.1.0 Platform Linux x86-64





Size 39.4 MB

Download Access Software

Classification General

Patch Tag All Database





-rw-r--r-- 1 oracle oinstall   41275925 Sep 24 12:45 p26609817_122010_Linux-x86-64.zip 

 unzip p26609817_122010_Linux-x86-64.zip







$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.6

Copyright (c) 2017, Oracle Corporation.  All rights reserved.



PREREQ session



Oracle Home       : /u01/app/oracle/product/12.1.0/db_2

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/oracle/product/12.1.0/db_2/oraInst.loc

OPatch version    : 12.2.0.1.6

OUI version       : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.1.0/db_2/cfgtoollogs/opatch/opatch2017-09-24_12-54-11PM_1.log



Invoking prereq "checkconflictagainstohwithdetail"



Prereq "checkConflictAgainstOHWithDetail" passed.



OPatch succeeded.



[oracle@cloud-ora 26609817]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.6

Copyright (c) 2017, Oracle Corporation.  All rights reserved.



PREREQ session



Oracle Home       : /u01/app/oracle/product/12.1.0/db_2

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/oracle/product/12.1.0/db_2/oraInst.loc

OPatch version    : 12.2.0.1.6

OUI version       : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.1.0/db_2/cfgtoollogs/opatch/opatch2017-09-24_12-54-11PM_1.log



Invoking prereq "checkconflictagainstohwithdetail"



Prereq "checkConflictAgainstOHWithDetail" passed.



OPatch succeeded.

[oracle@cloud-ora 26609817]$ $ORACLE_HOME/OPatch/opatch apply

Oracle Interim Patch Installer version 12.2.0.1.6

Copyright (c) 2017, Oracle Corporation.  All rights reserved.





Oracle Home       : /u01/app/oracle/product/12.1.0/db_2

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/oracle/product/12.1.0/db_2/oraInst.loc

OPatch version    : 12.2.0.1.6

OUI version       : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.1.0/db_2/cfgtoollogs/opatch/opatch2017-09-24_12-56-06PM_1.log



Verifying environment and performing prerequisite checks...

OPatch continues with these patches:   26609817



Do you want to proceed? [y|n]

y

User Responded with: Y

All checks passed.



Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/oracle/product/12.1.0/db_2')





Is the local system ready for patching? [y|n]

y

User Responded with: Y

Backing up files...

Applying interim patch '26609817' to OH '/u01/app/oracle/product/12.1.0/db_2'



Patching component oracle.oracore.rsf, 12.2.0.1.0...



Patching component oracle.ctx, 12.2.0.1.0...



Patching component oracle.rdbms.deconfig, 12.2.0.1.0...



Patching component oracle.rdbms.rsf, 12.2.0.1.0...



Patching component oracle.rdbms.rman, 12.2.0.1.0...



Patching component oracle.rdbms, 12.2.0.1.0...



Patching component oracle.rdbms.util, 12.2.0.1.0...



Patching component oracle.rdbms.dbscripts, 12.2.0.1.0...



Patching component oracle.tfa, 12.2.0.1.0...



Patching component oracle.rdbms.rsf.ic, 12.2.0.1.0...

Patch 26609817 successfully applied.

Log file location: /u01/app/oracle/product/12.1.0/db_2/cfgtoollogs/opatch/opatch2017-09-24_12-56-06PM_1.log



OPatch succeeded.







[oracle@cloud-ora 26609817]$



[oracle@cloud-ora 26609817]$ sqlplus / as sysdba



SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 24 13:01:39 2017



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



Connected to an idle instance.



SQL> startup;

ORACLE instance started.



Total System Global Area 3321888768 bytes

Fixed Size                  8798216 bytes

Variable Size            1929383928 bytes

Database Buffers         1375731712 bytes

Redo Buffers                7974912 bytes

Database mounted.

Database opened.

SQL> def

DEFINE _DATE           = "24-SEP-17" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "vihcdb1" (CHAR)

DEFINE _USER           = "SYS" (CHAR)

DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)

DEFINE _EDITOR         = "vi" (CHAR)

DEFINE _O_VERSION      = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)

DEFINE _O_RELEASE      = "1202000100" (CHAR)

SQL> alter pluggable database all open;



Pluggable database altered.



SQL> quit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@cloud-ora 26609817]$ cd $ORACLE_HOME/OPatch



[oracle@cloud-ora OPatch]$ ./datapatch -verbose

SQL Patching tool version 12.2.0.1.0 Production on Sun Sep 24 13:02:54 2017

Copyright (c) 2012, 2017, Oracle.  All rights reserved.



Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_31769_2017_09_24_13_02_54/sqlpatch_invocation.log



Connecting to database...OK

Note:  Datapatch will only apply or rollback SQL fixes for PDBs

       that are in an open state, no patches will be applied to closed PDBs.

       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation

       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done

Determining current state...done



Current state of SQL patches:

Bundle series DBRU:

  ID 170814 in the binary registry and not installed in any PDB



Adding patches to installation queue and performing prereq checks...

Installation queue:

  For the following PDBs: CDB$ROOT PDB$SEED ANUJ

    Nothing to roll back

    The following patches will be applied:

      26609817 (DATABASE RELEASE UPDATE 12.2.0.1.170814)



Installing patches...

Patch installation complete.  Total patches installed: 3



Validating logfiles...

Patch 26609817 apply (pdb CDB$ROOT): SUCCESS

  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26609817/21483023/26609817_apply_VIHCDB1_CDBROOT_2017Sep24_13_03_17.log (no errors)

Patch 26609817 apply (pdb PDB$SEED): SUCCESS

  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26609817/21483023/26609817_apply_VIHCDB1_PDBSEED_2017Sep24_13_04_00.log (no errors)

Patch 26609817 apply (pdb ANUJ): SUCCESS

  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26609817/21483023/26609817_apply_VIHCDB1_ANUJ_2017Sep24_13_04_00.log (no errors)

SQL Patching tool complete on Sun Sep 24 13:04:25 2017





select owner, status, count(*) from all_objects where object_type like '%JAVA%' group by owner, status;



select owner, status, count(*) from all_objects where object_type like '%JAVA%' group by owner, status;SQL>



no rows selected





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



no rows selected



This patch now includes the OJVM Mitigation patch (Patch:19721304). If an OJVM RU is installed or planned to be installed, no further actions are necessary. Otherwise, the workaround of using the OJVM Mitigation patch can be activated. As SYSDBA do the following from the admin directory:



SQL > @dbmsjdev.sql

SQL > exec dbms_java_dev.disable









Check the following log files in $ORACLE_BASE/cfgtoollogs/sqlpatch/26609817/ for errors:





 set linesize 200

 select patch_id,action,status,description from dba_registry_sqlpatch



  PATCH_ID ACTION          STATUS          DESCRIPTION

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



  26609817 APPLY           SUCCESS         DATABASE RELEASE UPDATE 12.2.0.1.170814
















Posted by



at





No comments:
  

































          

        

          

        

Saturday, 23 September 2017


          

        









Can't locate Term/ReadKey.pm in @INC (@INC contains:












[oracle@cloud-ora admin]$ echo $ORACLE_HOME

/u01/app/oracle/product/12.1.0/db_1



[oracle@cloud-ora admin]$ perl catcon.pl -d /u01/app/oracle/product/12.1.0/db_1/rdbms/admin -b catalog catalog.sql



Can't locate Term/ReadKey.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 . ./) at catcon.pm line 189.

BEGIN failed--compilation aborted at catcon.pm line 189.

Compilation failed in require at catcon.pl line 94.

BEGIN failed--compilation aborted at catcon.pl line 94.









Install This rpm ...



 yum install perl-TermReadKey    !!!!!!!!!!!!!



Loaded plugins: ulninfo

Setting up Install Process

datapipe                                                 | 2.9 kB     00:00

datapipe-internal                                        | 2.9 kB     00:00

epel/metalink                                            |  13 kB     00:00

epel                                                     | 4.3 kB     00:00

public_ol6_UEKR3_latest                                  | 1.2 kB     00:00

public_ol6_latest                                        | 1.4 kB     00:00

Resolving Dependencies

There are unfinished transactions remaining. You might consider running yum-complete-transaction first to finish them.

The program yum-complete-transaction is found in the yum-utils package.

--> Running transaction check

---> Package perl-TermReadKey.x86_64 0:2.30-13.el6 will be installed

--> Finished Dependency Resolution



Dependencies Resolved



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

 Package              Arch       Version            Repository             Size

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

Installing:

 perl-TermReadKey     x86_64     2.30-13.el6        public_ol6_latest      30 k



Transaction Summary

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

Install       1 Package(s)



Total download size: 30 k

Installed size: 62 k

Is this ok [y/N]: y

Downloading Packages:

perl-TermReadKey-2.30-13.el6.x86_64.rpm                  |  30 kB     00:00

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

  Installing : perl-TermReadKey-2.30-13.el6.x86_64                          1/1

  Verifying  : perl-TermReadKey-2.30-13.el6.x86_64                          1/1



Installed:

  perl-TermReadKey.x86_64 0:2.30-13.el6



Complete!







Then try again !!!!!!!!!!









Posted by



at





No comments:
  

































          

        

          

        

Thursday, 14 September 2017


          

        









Object list via  File and Block ID.














Oracle Object list via  File and Block ID. ..




set pause on pagesize 100 linesize 300
column segment_name format a25
column segment_type format a25
select segment_name, segment_type, block_id, blocks from dba_extents where file_id = &file_no AND ( &block_value between block_id and ( block_id + blocks ) )

  












Posted by



at





No comments:
  









































RMAN LIST commands .














Oracle RMAN LIST commands ..



 


some usefull RMAN LIST commands .
 
RMAN> list backup summary;
RMAN> list backup of database;
RMAN> list backup;
RMAN> list backup of pluggable database anuj, anuj1;
RMAN> list copy of datafile  2, 3 ,6 ;
RMAN> list backup of datafile 4 summary;
RMAN> list archivelog all;
RMAN> list backup of archivelog all;
RMAN> list copy of database archivelog all;
RMAN> list incarnation;
RMAN> list backup of controlfile;
RMAN> list backup of spfile;
RMAN> list backup by file;
RMAN> list backup by file;
RMAN> list backup of datafile 1;
RMAN> list expired backup;
RMAN> list expired backup summary;
RMAN> list expired backup of archivelog all;
RMAN> list expired backup of datafile 10;
RMAN> list expired backup of archivelog all summary;
RMAN> list failure;
RMAN> list failure 641231 detail;
RMAN> list backup of tablespace anuj summary;
RMAN> list backup of tablespace test;
RMAN> list backup of archivelog from sequence 55;
RMAN> list backupset of datafile 1;
RMAN> list recoverable backup;
RMAN> LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";
RMAN> LIST BACKUPSET OF DATAFILE 1;
RMAN> LIST backup tag 'ORCLEE_FULL';
RMAN> LIST BACKUPPIECE ' ';
RMAN> LIST BACKUP RECOVERABLE;
RMAN> list backup of archivelog from scn 853145 until scn 854039;
RMAN> LIST DATAFILECOPY '/u01/app/oracle/copy/users01.dbf';
RMAN> LIST COPY OF CONTROLFILE;
RMAN> list archivelog all backed up 1 times to disk;
RMAN> list backup of archivelog from scn 901427;
RMAN> list incarnation of database anuj ;
 
 
 
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ANUJ     3057090325       PARENT  1          25-DEC-16 16:28:05
2       2       ANUJ     3057090325       CURRENT 825576     25-DEC-16 18:25:29
 
===========
 
export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
 
 
list backup of archivelog all
 
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 220;
 
RMAN>list backup of archivelog sequence 89961 thread 2;
RMAN> LIST BACKUP OF ARCHIVELOG ;
 
 
list backup of tablespace users ;
list backup of database device type disk;
list copy of database completed between '01-AUGUST-2017' and '08-AUGUST-2017';
list copy of database completed between '01-AUGUST-2017' and '08-AUGUST-2017';
list backup of database completed between '01-AUGUST-2017' and '08-AUGUST-2017';
 
 
xx
RMAN> list backup of datafile;
 
RMAN> list backupset tag 'T2017';
 
List of Backup Sets
===================
 
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
184     299.57M    DISK        00:00:42     17-AUG-17
        BP Key: 184   Status: AVAILABLE  Compressed: YES  Tag: TAG20170817T010006
        Piece Name: /dumps/ICRAC/20170817_icrac1_186_1_952218007
 
  List of Archived Logs in backup set 184
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    259     22824594   16-AUG-17 22847740   16-AUG-17
  1    260     22847740   16-AUG-17 22934969   17-AUG-17
  2    239     22723071   16-AUG-17 22847951   16-AUG-17
  2    240     22847951   16-AUG-17 22923793   17-AUG-17
  2    241     22923793   17-AUG-17 22935012   17-AUG-17
 
RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME "SYSDATE-1";
 
 
List of Backup Sets
===================
 
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
184     299.57M    DISK        00:00:42     17-AUG-17
        BP Key: 184   Status: AVAILABLE  Compressed: YES  Tag: TAG20170817T010006
        Piece Name: /dumps/ICRAC/20170817_icrac1_186_1_952218007
 
  List of Archived Logs in backup set 184
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    257     22723067   16-AUG-17 22798860   16-AUG-17
  1    258     22798860   16-AUG-17 22824594   16-AUG-17
  1    259     22824594   16-AUG-17 22847740   16-AUG-17
  1    260     22847740   16-AUG-17 22934969   17-AUG-17
  2    239     22723071   16-AUG-17 22847951   16-AUG-17
  2    240     22847951   16-AUG-17 22923793   17-AUG-17
  2    241     22923793   17-AUG-17 22935012   17-AUG-17
 
 
full backup info 
 
RMAN> list backup of database summary completed after 'sysdate - 7';
 
 
List of Backups
===============
Key     TY LV S Device Type Completion Time    #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------ ------- ------- ---------- ---
6       B  F  A DISK        02-SEP-17 13:10:39 1       1       YES        ORCLEE_FULL
 
 
RMAN>  list backup of controlfile ;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
7       Full    9.36M      DISK        00:00:01     02-SEP-17 13:10:50
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20170902T131049
        Piece Name: /u01/app/RmanBackup/c-3057090325-20170902-02
  Control File Included: Ckp SCN: 854026       Ckp time: 02-SEP-17 13:10:49
 
 
 
==========
 
Recovery Manager complete.
[oracle@oraasm11g ~]$ export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
[oracle@oraasm11g ~]$ rman target /
 
RMAN> list incarnation of database anuj ;
 
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ANUJ     3057090325       PARENT  1          25-DEC-16 16:28:05
2       2       ANUJ     3057090325       CURRENT 825576     25-DEC-16 18:25:29
 
 
 
RMAN> list backup summary;
 
using target database control file instead of recovery catalog
 
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
6       B  F  A DISK        02-SEP-17       1       1       YES        ORCLEE_FULL
7       B  F  A DISK        02-SEP-17       1       1       NO         TAG20170902T131049
8       B  A  A DISK        02-SEP-17       1       1       NO         ORCLEE_ARCHIVE
 
RMAN> list backup ;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 854000     02-SEP-17 +DATA/anuj/datafile/system.268.931544353
  2       Full 854000     02-SEP-17 +DATA/anuj/datafile/sysaux.270.931544485
  3       Full 854000     02-SEP-17 +DATA/anuj/datafile/undotbs1.269.931544399
  4       Full 854000     02-SEP-17 +DATA/anuj/datafile/users.272.931544539
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    9.36M      DISK        00:00:01     02-SEP-17
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20170902T131049
        Piece Name: /u01/app/RmanBackup/c-3057090325-20170902-02
  SPFILE Included: Modification time: 02-SEP-17
  SPFILE db_unique_name: ANUJ
  Control File Included: Ckp SCN: 854026       Ckp time: 02-SEP-17
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8       291.50K    DISK        00:00:00     02-SEP-17
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    5       853145     02-SEP-17 853302     02-SEP-17
  1    6       853302     02-SEP-17 853310     02-SEP-17
  1    7       853310     02-SEP-17 854039     02-SEP-17
  1    8       854039     02-SEP-17 854047     02-SEP-17
 
 
RMAN> list backup of database;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 854000     02-SEP-17 +DATA/anuj/datafile/system.268.931544353
  2       Full 854000     02-SEP-17 +DATA/anuj/datafile/sysaux.270.931544485
  3       Full 854000     02-SEP-17 +DATA/anuj/datafile/undotbs1.269.931544399
  4       Full 854000     02-SEP-17 +DATA/anuj/datafile/users.272.931544539
 
RMAN> list backup;
 
List of Backup Sets
===================
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 854000     02-SEP-17 +DATA/anuj/datafile/system.268.931544353
  2       Full 854000     02-SEP-17 +DATA/anuj/datafile/sysaux.270.931544485
  3       Full 854000     02-SEP-17 +DATA/anuj/datafile/undotbs1.269.931544399
  4       Full 854000     02-SEP-17 +DATA/anuj/datafile/users.272.931544539
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    9.36M      DISK        00:00:01     02-SEP-17
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20170902T131049
        Piece Name: /u01/app/RmanBackup/c-3057090325-20170902-02
  SPFILE Included: Modification time: 02-SEP-17
  SPFILE db_unique_name: ANUJ
  Control File Included: Ckp SCN: 854026       Ckp time: 02-SEP-17
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8       291.50K    DISK        00:00:00     02-SEP-17
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    5       853145     02-SEP-17 853302     02-SEP-17
  1    6       853302     02-SEP-17 853310     02-SEP-17
  1    7       853310     02-SEP-17 854039     02-SEP-17
  1    8       854039     02-SEP-17 854047     02-SEP-17
 
 
RMAN> list archivelog all;
 
specification does not match any archived log in the repository
 
RMAN> list backup of archivelog all;
 
 
List of Backup Sets
===================
 
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8       291.50K    DISK        00:00:00     02-SEP-17
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    5       853145     02-SEP-17 853302     02-SEP-17
  1    6       853302     02-SEP-17 853310     02-SEP-17
  1    7       853310     02-SEP-17 854039     02-SEP-17
  1    8       854039     02-SEP-17 854047     02-SEP-17
 
 
RMAN> list backup of archivelog from sequence 4;
 
 
List of Backup Sets
===================
 
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ------------------
8       291.50K    DISK        00:00:00     02-SEP-17 13:10:54
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time           Next SCN   Next Time
  ---- ------- ---------- ------------------ ---------- ---------
  1    5       853145     02-SEP-17 12:54:24 853302     02-SEP-17 12:58:27
  1    6       853302     02-SEP-17 12:58:27 853310     02-SEP-17 12:58:27
  1    7       853310     02-SEP-17 12:58:27 854039     02-SEP-17 13:10:52
  1    8       854039     02-SEP-17 13:10:52 854047     02-SEP-17 13:10:54
 
 
RMAN> list incarnation;
 
 
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ANUJ     3057090325       PARENT  1          25-DEC-16
2       2       ANUJ     3057090325       CURRENT 825576     25-DEC-16
 
RMAN> list backup of datafile 1;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 854000     02-SEP-17 +DATA/anuj/datafile/system.268.931544353
 
 
RMAN> list backup of controlfile ;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
7       Full    9.36M      DISK        00:00:01     02-SEP-17 13:10:50
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20170902T131049
        Piece Name: /u01/app/RmanBackup/c-3057090325-20170902-02
  Control File Included: Ckp SCN: 854026       Ckp time: 02-SEP-17 13:10:49
 
 
RMAN> list backup of spfile;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
7       Full    9.36M      DISK        00:00:01     02-SEP-17 13:10:50
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20170902T131049
        Piece Name: /u01/app/RmanBackup/c-3057090325-20170902-02
  SPFILE Included: Modification time: 02-SEP-17 12:53:04
  SPFILE db_unique_name: ANUJ
 
 
 
RMAN> list backup by file;
 
 
List of Datafile Backups
========================
 
File Key     TY LV S Ckp SCN    Ckp Time           #Pieces #Copies Compressed Tag
---- ------- -  -- - ---------- ------------------ ------- ------- ---------- ---
1    6       B  F  A 854000     02-SEP-17 13:09:54 1       1       YES        ORCLEE_FULL
2    6       B  F  A 854000     02-SEP-17 13:09:54 1       1       YES        ORCLEE_FULL
3    6       B  F  A 854000     02-SEP-17 13:09:54 1       1       YES        ORCLEE_FULL
4    6       B  F  A 854000     02-SEP-17 13:09:54 1       1       YES        ORCLEE_FULL
 
List of Archived Log Backups
============================
 
Thrd Seq     Low SCN    Low Time           BS Key  S #Pieces #Copies Compressed Tag
---- ------- ---------- ------------------ ------- - ------- ------- ---------- ---
1    5       853145     02-SEP-17 12:54:24 8       A 1       1       NO         ORCLEE_ARCHIVE
1    6       853302     02-SEP-17 12:58:27 8       A 1       1       NO         ORCLEE_ARCHIVE
1    7       853310     02-SEP-17 12:58:27 8       A 1       1       NO         ORCLEE_ARCHIVE
1    8       854039     02-SEP-17 13:10:52 8       A 1       1       NO         ORCLEE_ARCHIVE
 
List of Control File Backups
============================
 
CF Ckp SCN Ckp Time           BS Key  S #Pieces #Copies Compressed Tag
---------- ------------------ ------- - ------- ------- ---------- ---
854026     02-SEP-17 13:10:49 7       A 1       1       NO         TAG20170902T131049
List of SPFILE Backups
======================
 
Modification Time  BS Key  S #Pieces #Copies Compressed Tag
------------------ ------- - ------- ------- ---------- ---
02-SEP-17 12:53:04 7       A 1       1       NO         TAG20170902T131049
 
 
 
RMAN> LIST backup tag 'ORCLEE_FULL';
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17 13:10:39
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time           Name
  ---- -- ---- ---------- ------------------ ----
  1       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/system.268.931544353
  2       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/sysaux.270.931544485
  3       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/undotbs1.269.931544399
  4       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/users.272.931544539
 
 
RMAN> list recoverable backup;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17 13:10:39
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time           Name
  ---- -- ---- ---------- ------------------ ----
  1       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/system.268.931544353
  2       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/sysaux.270.931544485
  3       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/undotbs1.269.931544399
  4       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/users.272.931544539
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
7       Full    9.36M      DISK        00:00:01     02-SEP-17 13:10:50
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20170902T131049
        Piece Name: /u01/app/RmanBackup/c-3057090325-20170902-02
  SPFILE Included: Modification time: 02-SEP-17 12:53:04
  SPFILE db_unique_name: ANUJ
  Control File Included: Ckp SCN: 854026       Ckp time: 02-SEP-17 13:10:49
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ------------------
8       291.50K    DISK        00:00:00     02-SEP-17 13:10:54
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time           Next SCN   Next Time
  ---- ------- ---------- ------------------ ---------- ---------
  1    5       853145     02-SEP-17 12:54:24 853302     02-SEP-17 12:58:27
  1    6       853302     02-SEP-17 12:58:27 853310     02-SEP-17 12:58:27
  1    7       853310     02-SEP-17 12:58:27 854039     02-SEP-17 13:10:52
  1    8       854039     02-SEP-17 13:10:52 854047     02-SEP-17 13:10:54
 
 
RMAN> list backup of archivelog from scn 853145 until scn 854039;
 
List of Backup Sets
===================
 
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ------------------
8       291.50K    DISK        00:00:00     02-SEP-17 13:10:54
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time           Next SCN   Next Time
  ---- ------- ---------- ------------------ ---------- ---------
  1    5       853145     02-SEP-17 12:54:24 853302     02-SEP-17 12:58:27
  1    6       853302     02-SEP-17 12:58:27 853310     02-SEP-17 12:58:27
  1    7       853310     02-SEP-17 12:58:27 854039     02-SEP-17 13:10:52
 
RMAN>  list backupset 8;
 
 
List of Backup Sets
===================
 
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ------------------
8       291.50K    DISK        00:00:00     02-SEP-17 13:10:54
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time           Next SCN   Next Time
  ---- ------- ---------- ------------------ ---------- ---------
  1    5       853145     02-SEP-17 12:54:24 853302     02-SEP-17 12:58:27
  1    6       853302     02-SEP-17 12:58:27 853310     02-SEP-17 12:58:27
  1    7       853310     02-SEP-17 12:58:27 854039     02-SEP-17 13:10:52
  1    8       854039     02-SEP-17 13:10:52 854047     02-SEP-17 13:10:54
 
 
RMAN> list backup of datafile 1 completed between '01-sep-2017' and 'sysdate';
RMAN> list backup of datafile 1 completed between '01-sep-2017' and 'sysdate';
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17 13:10:39
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time           Name
  ---- -- ---- ---------- ------------------ ----
  1       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/system.268.931544353
 
 
w
List Command with Date ( All are tested ) 
 
RMAN> list backup of datafile 1 completed between '07-jan-2012' and 'sysdate';
RMAN> list backup of archivelog  from time "to_date('01/08/2017 22:00:00', 'dd/mm/yyyy hh24:mi:ss')"   until time "to_date('08/08/2017 04:00:00', 'dd/mm/yyyy hh24:mi:ss')";
RMAN> list backup of archivelog time between "to_date('01-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('08-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS')";
RMAN> list backup of archivelog time between "to_date('01/08/2017','dd/mm/yyyy')" and "to_date('09/08/2017','dd/mm/yyyy')";
RMAN> list backup of database completed between "to_date('01/08/2017','dd/mm/yyyy')" and "to_date('08/08/2017','dd/mm/yyyy')";
RMAN> list backup of archivelog from time "to_date('01-08-2017','mm-dd-yyyy')";
RMAN> list backup of database completed between 'sysdate -7' and 'sysdate';
RMAN> list backup completed between 'sysdate -14' and 'sysdate';
RMAN> list backup completed between 'sysdate -14' and 'sysdate -7';
RMAN> list backup of spfile time between "to_date('01-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('08-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS')";
RMAN> list backup of database completed between "to_date('06/17/2010','mm/dd/yyyy')" and "to_date('06/19/2010','mm/dd/yyyy')";
RMAN> list backup of database completed between "to_date('01/08/2017','dd/mm/yyyy')" and "to_date('09/08/2017','dd/mm/yyyy')";
RMAN> list backup of archivelog time between "to_date('20-MAR-2013 00:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('25-MAR-2013 00:00:00','DD-MON-YYYY HH24:MI:SS')";
RMAN> list backup of archivelog time between "to_date('01/08/2017','dd/mm/yyyy')" and "to_date('09/09/2017','dd/mm/yyyy')";
RMAN> LIST backup of database completed after "to_date('01/08/2017','mm/dd/yyyy')";
RMAN> list backup of archivelog  from time "to_date('01/08/2017 22:00:00', 'dd/mm/yyyy hh24:mi:ss')"   until time "to_date('08/08/2017 04:00:00', 'dd/mm/yyyy hh24:mi:ss')";
RMAN> LIST BACKUP OF ARCHIVELOG  FROM TIME "TO_DATE('08/16/2017 22:00:00', 'MM/DD/YYYY hh24:mi:ss')"   UNTIL TIME "TO_DATE('08/17/2017 03:00:00', 'MM/DD/YYYY hh24:mi:ss')";



 export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
LIST BACKUP OF ARCHIVELOG  FROM TIME "TO_DATE('06/17/2023 22:00:00', 'MM/DD/YYYY hh24:mi:ss')"   UNTIL TIME "TO_DATE('06/18/2023 03:00:00', 'MM/DD/YYYY hh24:mi:ss')";


for SUMMARY

RMAN> LIST BACKUP SUMMARY COMPLETED BETWEEN "to_date('10-JAN-2022 05:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('14-JAN-2022 07:00:00','DD-MON-YYYY HH24:MI:SS')";


RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1' UNTIL TIME 'SYSDATE';
========================
 
RMAN>report need backup days=2;
RMAN> report unrecoverable;
 
REPORT NEED BACKUP RECOVERY WINDOW OF n DAYS
Displays objects requiring backup to satisfy a recovery window-based retention policy.
 
REPORT NEED BACKUP REDUNDANCY n
Displays objects requiring backup to satisfy a redundancy-based retention policy.
 
REPORT NEED BACKUP DAYS n
Displays files that require more than n days' worth of archived redo log files for recovery.
 
REPORT NEED BACKUP INCREMENTAL n
Displays files that require application of more than n incremental backups for recovery.
 
 
RMAN> report schema ;
 
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ANUJ
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     +DATA/anuj/datafile/system.268.931544353
2    0        SYSAUX               ***     +DATA/anuj/datafile/sysaux.270.931544485
3    0        UNDOTBS1             ***     +DATA/anuj/datafile/undotbs1.269.931544399
4    0        USERS                ***     +DATA/anuj/datafile/users.272.931544539
 
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2    256      TEMP2                256         +DATA/anuj/tempfile/temp2.277.931545697




=========


RMAN>  list backup of pluggable database 'PDB$SEED' summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
1967    B  0  A DISK        03-04-2022 02:08:44 1       1       YES        TAG20220403T020100
1999    B  0  A DISK        10-04-2022 02:08:41 1       1       YES        TAG20220410T020100




export NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss';
LIST BACKUP OF ARCHIVELOG TIME BETWEEN "to_date('27-MAR-2023 09:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('28-MAR-2023 09:00:00','DD-MON-YYYY HH24:MI:SS')";
LIST BACKUP SUMMARY COMPLETED BETWEEN "to_date('27-MAR-2023 09:00:00','DD-MON-YYYY HH24:MI:SS')"  and "to_date('28-MAR-2023 09:00:00','DD-MON-YYYY HH24:MI:SS')";



===

export NLS_DATE_FORMAT='DD/MM/YYYY hh24:mi:ss'


rman target / catalog prod_23/xxxx@rmancatalog


list backup of database completed between "to_date('01/04/2023','DD/MM/YYYY')" and "to_date('22/04/2023','DD/MM/YYYY')";
LIST backup of database completed after "to_date('01/04/2023','DD/MM/YYYY')";
list backup of datafile 1 completed after "to_date('01/04/2023','DD/MM/YYYY')";
list until time "to_date('01/04/2023','DD/MM/YYYY')" backupset of tablespace system;
list backup of tablespace system completed after "to_date('01/04/2023','DD/MM/YYYY')";












Posted by



at





5 comments:
  

































        

      









Subscribe to:
Posts (Atom)







Oracle DBA



My photo








Essex, United Kingdom











anuj blog Archive