Skip to main content

DB - Play with ARCHIVED LOGs



PT : Brincar com ARCHIVED LOGs
EN : Play with ARCHIVED LOGs
#Let's  distillate  a bit the +FRA  -  new db - Lets generate to 2 archived logs with 
alter system switch logfile;
SQL> set lines 200
SQL> select * from V$RECOVERY_AREA_USAGE;

 FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------------         ------------------                      -------------------------                            ---------------
CONTROL FILE                          0                         0                                            0
REDO LOG                                0                         0                                            0
ARCHIVED LOG                         0                         0                                            0
BACKUP PIECE                         0                         0                                            0
IMAGE COPY                            0                         0                                            0
FLASHBACK LOG                     0                         0                                            0
FOREIGN ARCHIVED LOG         0                         0                                           0

 7 rows selected.

 SQL> alter system switch logfile;

 System altered.

 SQL>  select * from V$RECOVERY_AREA_USAGE;

 FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------------         ------------------                      -------------------------                            ---------------
CONTROL FILE                          0                         0                                            0
REDO LOG                                0                         0                                            0
ARCHIVED LOG                      0.2                         0                                            1
BACKUP PIECE                         0                         0                                            0
IMAGE COPY                             0                        0                                            0
FLASHBACK LOG                       0                        0                                            0
FOREIGN ARCHIVED LOG          0                        0                                            0


 7 rows selected.

 SQL> alter system switch logfile;

 System altered.

 SQL>  select * from V$RECOVERY_AREA_USAGE;

 FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------------         ------------------                      -------------------------                            ---------------
CONTROL FILE                          0                         0                                            0
REDO LOG                                0                         0                                            0
ARCHIVED LOG                      0.2                         0                                            2
BACKUP PIECE                         0                         0                                            0
IMAGE COPY                             0                        0                                            0
FLASHBACK LOG                       0                        0                                            0
FOREIGN ARCHIVED LOG          0                        0                                            0

7 rows selected.

 #
#Now lets play with RMAN
#

 RMAN> list backup;

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

 RMAN> BACKUP ARCHIVELOG ALL DELETE ALL INPUT;

 Starting backup at 01-OCT-16
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=1 STAMP=924096057
input archived log thread=1 sequence=10 RECID=2 STAMP=924096084
input archived log thread=1 sequence=11 RECID=3 STAMP=924096215
channel ORA_DISK_1: starting piece 1 at 01-OCT-16
channel ORA_DISK_1: finished piece 1 at 01-OCT-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_10_01/o1_mf_annnn_TAG20161001T132335_cyz75qvp_.bkp tag=TAG20161001T132335 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_10_01/o1_mf_1_9_cyz70sgb_.arc RECID=1 STAMP=924096057
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_10_01/o1_mf_1_10_cyz71nvr_.arc RECID=2 STAMP=924096084
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_10_01/o1_mf_1_11_cyz75q4v_.arc RECID=3 STAMP=924096215
Finished backup at 01-OCT-16
RMAN> list backup;

List of Backup Sets
===================
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1       978.00K    DISK        00:00:01     01-OCT-16      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20161001T132335
        Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_10_01/o1_mf_annnn_TAG20161001T132335_cyz75qvp_.bkp

   List of Archived Logs in backup set 1
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    9       895024     01-OCT-16 896346     01-OCT-16
  1    10      896346     01-OCT-16 896357     01-OCT-16
  1    11      896357     01-OCT-16 896431     01-OCT-16

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

1       B  A  A DISK        01-OCT-16       1       1       NO         TAG20161001T132335

  
SQL> select * from V$RECOVERY_AREA_USAGE;

 FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                           0                         0               0
REDO LOG                                 0                         0               0
ARCHIVED LOG                          0                         0               0 
BACKUP PIECE                        .02                         0              1 
IMAGE COPY                             0                         0               0
FLASHBACK LOG                      0                         0               0
FOREIGN ARCHIVED LOG          0                         0               0

 7 rows selected.



 -----
Obrigado pela leitura, espero que este post o tenha ajudado :)
Thank you for reading, hope this post was helpful  :) 
Labels:

Comments

Post a Comment

Popular posts from this blog

DB - How to monitor Oracle datapump jobs

These days there is lot of work around database migrations not only but mainly to cloud providers. One of the ways to migrate Oracle databases is using data pump (expdp/impdp). In general works fine, it allows an easy way to bring the database even different OS. It is always good to monitor the progress, to know where we are and estimate how long we are from the end. In this post, I am sharing some queries to help with database monitoring progress. The queries can be easily tailed to various scenarios. Starting with a simple one - the track the number of objects loaded during an import operation. When there is a massive amount of errors during the import. Drop and run the import again. The bellow query can be useful also to monitor the number of objects if we are dropping the users. Here, I’m making the assumption there were no other database activity in progress on the last 24 hours. select owner, count(0) from dba_objects where owner in ( select username from dba_users where created...
Post a Comment
Read more

VM - Partilhar Pastas Windows - Linux

Neste post escrevo como partilhar pastas entre Windows (Host) e uma VM Guest, neste caso Linux. Ha algumas formas de fazer isto, esta parece me a mais facil. Seleccionar a Pasta Windows Na consola Virtual Box > Devices > Shared Folder Seleccionar a pasta windows para partilhar no Linux   O   Montar a pasta Windows na VM Ha duas opcoes : 1) Reiniciar a VM (Auto-mount) 2) Linha de comando [root@host ~]# mkdir -p /u01/stage/win_ebs_sw [root@host ~]# chmod 777 /u01/stage/win_ebs_sw [root@host ~]# mount -t vboxsf Oracle_EBS /u01/stage/win_ebs_sw/ [root@host ~]# ls /u01/stage/win_ebs_sw ebs_weblogic_webtier [root@host ~]# df -h /u01/stage/win_ebs_sw Filesystem Size Used Avail Use% Mounted on Oracle_EBS 895G 494G 401G 56% /u01/stage/win_ebs_sw Guardar a Configuracao no /etc/fstab para tornar as alteracoes permanentes. [root@host ~]# echo "Oracle_EBS /u01/stage/win_ebs_sw vboxsf defaults 0 0" >> /etc/fstab [root@host ~]# cat /etc/fstab # /etc/...
Post a Comment
Read more

DB – How to monitor Oracle database long ops ?

As DBAs, some questions we are often asked and to be fair we ask ourselfs when we are doing massive operations, are : Is the database doing something? How long the database takes to complete the task? There a data dictionary view, GV$SESSION_LONGOPS whom is really usefull for monitoring and to provide estimated time to get the task done. Starting with a first example, a query for general propose . set lines 240 col message form a100 col opname form a50 select * from ( select round(l.sofar/l.totalwork*100,2) as complete, --l.* l.sid, --l.opname, l.message, l.start_time, l.time_remaining/60 "minuts remaining", l.elapsed_seconds from gv$session_longops l where totalwork !=0 ) where complete < 100; The view is also usefull on specific operations such as Oracle Recovery Manager (RMAN) jobs monitoring, this query returns information about backup,restore and recovery. select sid, serial#,opname, context, sofar, totalwork, round(sofar/totalwork*100,2) "%_complet...
Post a Comment
Read more