CAMBIAR NOMBRE BASE DE DATOS ORACLE

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup mount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size     2293880 bytes
Variable Size   658509704 bytes
Database Buffers   171966464 bytes
Redo Buffers     2334720 bytes

Database mounted.

LA INSTANCIA DEBE ESTAR EN MODO MOUNT


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@localhost backup]$ nid TARGET=sys/oracle@orcl DBNAME=ERP

DBNEWID: Release 12.1.0.1.0 - Production on Wed Apr 29 21:42:07 2020

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

Connected to database ORCL (DBID=1376021099)

Connected to server version 12.1.0

Control Files in database:
    /u01/app/oracle/oradata/ORCL/controlfile/o1_mf_9qwmfso4_.ctl
    /u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_9qwmft02_.ctl


Change database ID and database name ORCL to ERP? (Y/[N]) => 

OTRO PROCESO REALIZADO

[oracle@localhost dbs]$ nid

DBNEWID: Release 12.1.0.1.0 - Production on Thu Feb 28 18:35:11 2019

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

Keyword     Description                    (Default)
----------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO

HELP        Displays these messages        NO

[oracle@bddesarrollo prueba_restauracion]$ nid

DBNEWID: Release 11.2.0.3.0 - Production on Fri Nov 21 13:07:56 2014

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

Keyword     Description                    (Default)

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

TARGET      Username/Password              (NONE)

DBNAME      New database name              (NONE)

LOGFILE     Output Log                     (NONE)

REVERT      Revert failed change           NO

SETNAME     Set a new database name only   NO

APPEND      Append to output log           NO

HELP        Displays these messages        NO

[oracle@bddesarrollo prueba_restauracion]$ nid TARGET=/ DBNAME=FONPETD

DBNEWID: Release 11.2.0.3.0 - Production on Fri Nov 21 13:08:15 2014

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

Connected to database FONPETP (DBID=2150325182)

Connected to server version 11.2.0

Control Files in database:

    +DG_DATA/fonpetd/controlfile/control01.ctl

    +DG_FRA/fonpetd/controlfile/control02.ctl

Change database ID and database name FONPETP to FONPETD? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 2659586623 to 1247841337

Changing database name from FONPED to FONPETD

    Control File +DG_DATA/fonpetd/controlfile/control01.ctl - modified

    Control File +DG_FRA/fonpetd/controlfile/control02.ctl - modified

    Datafile +DG_DATA/fonpetp/datafile/system01.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/sysaux01.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/undotbs101.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/users01.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/indic_fonpet_movim_01.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/indic_fonpet_param.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/indic_fonpet_movim.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/indic_fonpet_movim_reorg0.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/ts_fonpet_param.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/ts_fonpet_movim_01.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/indic_fonpet_movim_02.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/ts_fonpet_movim_02.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/ts_fonpet_rep.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/ts_fonpet_movim.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/datafile/ts_fonpet_movim_2.db - dbid changed, wrote new name

    Datafile +DG_DATA/fonpetp/tempfile/temp01.db - dbid changed, wrote new name

    Control File +DG_DATA/fonpetd/controlfile/control01.ctl - dbid changed, wrote new name

    Control File +DG_FRA/fonpetd/controlfile/control02.ctl - dbid changed, wrote new name

    Instance shut down

Database name changed to FONPETD.

Modify parameter file and generate a new password file before restarting.

Database ID for database FONPETD changed to 1247841337.

All previous backups and archived redo logs for this database are unusable.

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

[oracle@bddesarrollo prueba_restauracion]$ orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwfonpetd force=Y password=Nuevo123 entries=3

[oracle@bddesarrollo prueba_restauracion]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 21 13:15:46 2014

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

Connected to an idle instance.

SYS@fonpetd> startup nomount;

ORACLE instance started.

Total System Global Area 1402982400 bytes

Fixed Size                  2228304 bytes

Variable Size            1073745840 bytes

Database Buffers          318767104 bytes

Redo Buffers                8241152 bytes

SYS@fonpetd> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      FONPED

SYS@fonpetd> alter database mount;

alter database mount

*

ERROR at line 1:

ORA-01103: database name 'FONPETD' in control file is not 'FONPED'

SYS@fonpetd> alter system set db_name='FONPETD' scope=spfile;

System altered.

SYS@fonpetd>shu abort;

ORACLE instance shut down.

SYS@fonpetd> startup mount;

ORACLE instance started.

Total System Global Area 1402982400 bytes

Fixed Size                  2228304 bytes

Variable Size            1073745840 bytes

Database Buffers          318767104 bytes

Redo Buffers                8241152 bytes

Database mounted.

SYS@fonpetd> alter database open resetlogs;

SYS@fonpetd> alter database open resetlogs;

Database altered.

flashback database to restore point guarantee database

SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 29 20:48:28 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create restore point before_upgrade guarantee flashback database;

Restore point created.

SQL> update hr.prueba set salary=99999;

107 rows updated.

SQL> commit;

Commit complete.

SQL> drop table hr.prueba;

Table dropped.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 29 20:50:55 2020

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size     2293880 bytes

Variable Size   658509704 bytes

Database Buffers   171966464 bytes

Redo Buffers     2334720 bytes

Database mounted.

SQL> exit

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Apr 29 20:51:44 2020

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

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

RMAN> list restore point all;

using target database control file instead of recovery catalog

SCN              RSP Time  Type       Time      Name

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

5733192                    GUARANTEED 29-APR-20 BEFORE_UPGRADE

RMAN> flashback database to restore point before_upgrade;

Starting flashback at 29-APR-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

starting media recovery

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

Finished flashback at 29-APR-20

RMAN> alter database open resetlogs;

Statement processed

RMAN> 

RMAN> list restore point all;

using target database control file instead of recovery catalog

SCN              RSP Time  Type       Time      Name

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

3886948                    GUARANTEED 25-JUL-20 BEFORE_UPGRADE

RMAN> 

RMAN> drop restore point BEFORE_UPGRADE;

Statement processed

RMAN> 

MONITOREAR FLASH_RECOVERY_AREA

SELECT space_used/1024/1024/1024 AS "Space Used (Gb)",   space_reclaimable/1024/1024/1024 AS "Space Reclaimable (Gb)"  FROM v$recovery_file_dest
/

select
  round((a.space_limit / 1024 / 1024 / 1024), 2) as flash_in_gb,
  round((a.space_used / 1024 / 1024 / 1024), 2) as flash_used_in_gb,
  round((a.space_reclaimable / 1024 / 1024 / 1024), 2) as flash_reclaimable_gb,
  round((a.space_limit / 1024 / 1024 / 1024), 2) - round((a.space_used / 1024 / 1024 / 1024), 2) as flash_disponible_gb,
  sum(b.percent_space_used)  as percent_of_space_used
from
  v$recovery_file_dest a,
  v$flash_recovery_area_usage b
group by
  space_limit,
  space_used ,
  space_reclaimable
/

CREATE TABLE PERIOD FOR USER_TIME

CREATE TABLE HR.prueba2 (
empno NUMBER(6) NOT NULL
,ename VARCHAR2(20),
PERIOD FOR user_time);

SELECT column_name

,data_type

,column_id AS col_id

,segment_column_id AS seg_col_id

,internal_column_id AS int_col_id

,hidden_column

,virtual_column

FROM user_tab_cols WHERE table_name = ‘PRUEBA2’;

SQL> desc hr.prueba2;
 Name    Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO    NOT NULL NUMBER(6)
 ENAME     VARCHAR2(20)

SQL> 

ILM Information Lifecycle Management

https://es.wikipedia.org/wiki/Information_Lifecycle_Management


Information Lifecycle Management (ILM) (en español: gestión del ciclo de vida de la información) se refiere a la gestión de estrategias para administrar sistemas de almacenamiento en dispositivos informáticos, desde su inicio, hasta su finalización, con la intención de reducir costos y riesgos legales.¹​

LABORATORIOS FLASHBACK QUERY

https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns003.htm

LABORATORIO FLASHBACK QUERY


LABORATORIO FLASHBACK VERSION QUERY

select versions_startscn, versions_starttime,versions_endscn, versions_endtime,
       versions_xid, versions_operation, employee_id,salary from hr.employees
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2021-05-14 18:35:00', 'YYYY-MM-DD HH24:MI:SS')
                        AND TO_TIMESTAMP('2021-05-14 18:44:00', 'YYYY-MM-DD HH24:MI:SS')
where employee_id=200;

LABORATORIO FLASHBACK EN UNPUNTO DEL PASADO

select salary from hr.prueba

as of timestamp (systimestamp - interval '2' MINUTE)

where employee_id=200

LABORATORIO SCN 

select salary from hr.prueba

versions between scn 592900 and 5929230

where employee_id=200

select versions_starttime as "start_date",

versions_endtime as "end_date", 

salary from hr.prueba

versions between scn 5768000 and 5768584

where employee_id=200

LABORATORIO FLASHBACK TABLE

SQL> create table prueba as (select * from hr.employees);

Table created.

SQL>
SQL> show user;
USER is "HR"
SQL>
SQL> !date                     
Mon Apr 27 21:25:53 -05 2020

SQL> select employee_id,salary from hr.prueba where employee_id=200;

EMPLOYEE_ID SALARY
----------- ----------
200   4400

SQL> !date         
Mon Apr 27 21:26:44 -05 2020

SQL> update hr.prueba set salary=500 where employee_id=200;

1 row updated.

SQL> commit;

Commit complete.

SQL> !date
Mon Apr 27 21:27:20 -05 2020

SQL> flashback table hr.prueba to timestamp to_timestamp('2021-05-14 18:40:00','YYYY-MM-DD HH24:MI:SS');
flashback table hr.prueba to timestamp to_timestamp('2020-04-27 21:26:44','YYYY-MM-DD HH24:MI:SS')
                   *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


SQL> alter table hr.prueba enable row movement;

Table altered.

SQL> flashback table hr.prueba to timestamp to_timestamp('2020-10-20 07:13:00','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL> select employee_id,salary from hr.prueba where employee_id=200;

EMPLOYEE_ID SALARY
----------- ----------
200   4400

SQL>

SQL> select salary from hr.prueba where employee_id=200;

    SALARY

----------

      5000

SQL> flashback table hr.prueba to timestamp to_timestamp('2020-10-20 07:13:00','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL> flashback table hr.prueba to timestamp to_timestamp('2020-10-20 07:10:00','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL> select salary from hr.prueba where employee_id=200;

    SALARY

----------

      4400

CATALOG START WITH

RMAN>catalog start with /u01/app/oracle/backup' noprompt;

RESTORE CONTROLFILE FROM AUTOBACKUP

SQL>startup nomount;

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

RMAN> ALTER DATABASE MOUNT;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS;

EL RESTAURAR EL ARCHIVO DE CONTROL IMPLICA ABRIL LA BASE DE DATOS EN MODO OPEN RESETLOGS ESTO QUIERE DECIR QUE LA BASE DE DATOS GENERA UNA NUEVA ENCARNACION 

LA CUAL SE PUEDE VER DE LA SIGUIENTE FORMA

RMAN> list incarnation;

OBLIGATORIO REALIZAR UN BACKUP FULL

RMAN>BACKUP DATABASE PLUS ARCHIVELOG;

RMAN RESTORE POINT

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Apr 24 18:47:55 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create restore point good_data;

Restore point created.

RMAN> list restore point all;

using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
5154575                               24-APR-20 GOOD_DATA


RMAN> drop restore point good_data;

Statement processed

RMAN>

VERIFICAR PIEZA BACKUP RMAN VALIDATE BACKUPSET

RMAN> list backupset 50;
RMAN> validate backupset 50;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50      Full    2.01G      DISK        00:02:22     22-APR-20     
        BP Key: 50   Status: AVAILABLE  Compressed: NO  Tag: TAG20200422T185523
        Piece Name: +FRA/ACME/BACKUPSET/2020_04_22/nnndf0_tag20200422t185523_0.351.1038423325
  List of Datafiles in backup set 50
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 5038912    22-APR-20 +DATA/ACME/DATAFILE/system.259.1038425973
  2       Full 5038912    22-APR-20 +DATA/ACME/DATAFILE/example.257.1038425975
  3       Full 5038912    22-APR-20 +DATA/ACME/DATAFILE/sysaux.266.1038425973
  4       Full 5038912    22-APR-20 +DATA/ACME/DATAFILE/undotbs1.260.848598093
  6       Full 5038912    22-APR-20 +DATA/ACME/DATAFILE/users.258.1038425975

RMAN> validate backupset 50;

Starting validate at 24-APR-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece +FRA/ACME/BACKUPSET/2020_04_22/nnndf0_tag20200422t185523_0.351.1038423325

RESTORE VALIDATE VALIDATE DATABASE

RMAN>VALIDATE DATABASE;

RMAN>RESTORE DATABASE VALIDATE;

RMAN>VALIDATE DATAFILE 1 BLOCK 10;

RMAN>RESTORE ARCHIVELOG ALL VALIDATE;

RMAN VALIDATE DATABASE

RMAN> VALIDATE DATABASE;

Starting validate at 24-APR-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00003 name=+DATA/ACME/DATAFILE/sysaux.266.1038425973
input datafile file number=00001 name=+DATA/ACME/DATAFILE/system.259.1038425973
input datafile file number=00002 name=+DATA/ACME/DATAFILE/example.257.1038425975
input datafile file number=00004 name=+DATA/ACME/DATAFILE/undotbs1.260.848598093
input datafile file number=00006 name=+DATA/ACME/DATAFILE/users.258.1038425975


RMAN>RESTORE DATABASE VALIDATE;

RMAN>RESTORE ARCHIVELOG ALL VALIDATE;

RMAN> VALIDATE TABLESPACE EXAMPLE;

validateObject::=

adrci DIA-48494

[oracle@localhost ~]$ adrci

ADRCI: Release 12.1.0.1.0 - Production on Wed Apr 22 18:36:13 2020

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

No ADR base is set
adrci> show incident;
DIA-48494: ADR home is not set, the corresponding operation cannot be done

adrci> set base /u01/app/oracle
adrci> show incident;

ADR Home = /u01/app/oracle/diag/asm/+asm/+ASM:
*************************************************************************
0 rows fetched

ADR Home = /u01/app/oracle/diag/tnslsnr/localhost/listener:
*************************************************************************
0 rows fetched

WALLET ORACLE DATABASE ./mkstore

/u01/app/oracle/product/11.2.0/db_1/bin
[oracle@localhost bin]$ ls -ltr mkstore
-rwxr-xr-x 1 oracle oinstall 2816 Mar 20  2013 mkstore

How do I create Oracle Wallet files on my database server?

From the database server: 

1. Create a secure folder for the Oracle wallet
   mkdir -p /product/11.2.0.4/owm/wallets/ 
   
2. Give permissions to the folder
   chmod -R 700 /product/11.2.0.4/owm/wallets 
   
3. Create the wallet by specifying the secure folder path
   Path - /product/11.2.0.4/bin
sudo ./mkstore -wrl /product/11.2.0.4/owm/wallets/oracle/ -create 
   
4. Configure and confirm the password
   
5. Edit sqlnet.ora and add the newly created wallet location
   vi /product/11.2.0.4/network/admin/sqlnet.oraSQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION=(SOURCE=(METHOD=FILE)
(METHOD_DATA=(DIRECTORY=/product/11.2.0.4/owm/wallets/))

BACKUP MD_ BACKUP ASM DISKGROUPS

ASMCMD> md_backup /u01/backup/asm_metadata_data -G DATA
Disk group metadata to be backed up: DATA
Current alias directory path: ORCL
Current alias directory path: ORCL/ONLINELOG
Current alias directory path: COPIA/TEMPFILE
Current alias directory path: COPIA/DATAFILE
Current alias directory path: ORCL/TEMPFILE
Current alias directory path: COPIA/ONLINELOG
Current alias directory path: ORCL/PARAMETERFILE
Current alias directory path: ORCL/CONTROLFILE
Current alias directory path: COPIA
Current alias directory path: ORCL/DATAFILE
Current alias directory path: COPIA/CONTROLFILE

ASMCMD> md_backup /u01/app/oracle/backup/fra -G fra

ASMCMD> md_backup /u01/backup/fra.cop -G fra


ASMCMD> md_backup /u01/backup/asm_metadata_fra -G FRA
Disk group metadata to be backed up: FRA
Current alias directory path: ORCL/ARCHIVELOG/2019_10_11
Current alias directory path: ORCL/ARCHIVELOG/2020_04_02
Current alias directory path: ORCL
Current alias directory path: ORCL/BACKUPSET/2020_04_20
Current alias directory path: ORCL/BACKUPSET
Current alias directory path: ORCL/ARCHIVELOG
Current alias directory path: ORCL/ARCHIVELOG/2020_04_20
Current alias directory path: ORCL/CONTROLFILE
Current alias directory path: ORCL/ARCHIVELOG/2017_09_13
Current alias directory path: COPIA/CONTROLFILE
Current alias directory path: ORCL/AUTOBACKUP
Current alias directory path: ORCL/ARCHIVELOG/2017_03_06
Current alias directory path: ORCL/ARCHIVELOG/2017_12_13
Current alias directory path: COPIA/ONLINELOG
Current alias directory path: ORCL/ONLINELOG
Current alias directory path: ORCL/ARCHIVELOG/2020_03_24
Current alias directory path: ORCL/AUTOBACKUP/2020_04_20
Current alias directory path: ORCL/ARCHIVELOG/2020_03_21
Current alias directory path: COPIA
ASMCMD>

BACKUP AS BACKUPSET ALL

RMAN> backup as backupset backupset all;

Starting backup at 20-APR-20
using channel ORA_DISK_1
channel ORA_DISK_1: input backup set: count=7, stamp=937942921, piece=1
channel ORA_DISK_1: starting piece 1 at 20-APR-20
channel ORA_DISK_1: backup piece /u02/backups/rman/orcl/2017-03-06/full_ORCL_07rufns9_1_1_20170306.bak
piece handle=+FRA/orcl/backupset/2020_04_20/nnndf0_0.284.1038252993 comment=NONE
channel ORA_DISK_1: finished piece 1 at 20-APR-20
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:07
channel ORA_DISK_1: input backup set: count=9, stamp=937942969, piece=1
channel ORA_DISK_1: starting piece 1 at 20-APR-20

LO IDEAL ES HACER ESTE BACKUP SEMANAL Y MANDARLO A CINTA DE ESTA FORMA

RMAN>; BACKUP DEVICE TYPE sbt BACKUPSET ALL;

RMAN LIMITAR MAXPIECESIZE

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE = 200M;  

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 200 M;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1

RMAN> backup database plus archivelog;


Starting backup at 20-APR-20
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 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=6 RECID=1 STAMP=916928468
input archived log thread=1 sequence=7 RECID=2 STAMP=923564412
input archived log thread=1 sequence=8 RECID=3 STAMP=923573422
input archived log thread=1 sequence=9 RECID=4 STAMP=923672051
input archived log thread=1 sequence=10 RECID=5 STAMP=936608997
input archived log thread=1 sequence=11 RECID=6 STAMP=936609359


RMAN> BACKUP DATAFILE 1 SECTION SIZE 100M TAG 'SECTION SIZE 100M';


BACKUP AS COPY DATABASE SECTION SIZE (NO ES SOPORTADO)

RMAN> backup as copy database section size = 100M;

Starting backup at 20-APR-20
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/20/2020 19:19:52
RMAN-06580: the SECBYTES option cannot be used with AS COPY

RMAN>

RMAN MONITOR CHANGE TRACKING

MONITOREAR ESTADO BACKUP INCREMENTALES < 20%


SELECT file#, avg(datafile_blocks), avg(blocks_read), avg(blocks_read/datafile_blocks)*100 AS PCT_READ_FOR_BACKUP, avg(blocks)
FROM V$BACKUP_DATAFILE
WHERE used_change_tracking='YES' and incremental_level>0
GROUP BY file#
/

Monitoring RMAN Job Progress

Monitoring RMAN Job Progress

SQL>select sid, serial#, sofar, totalwork, opname,
2 round(sofar/totalwork*100,2) "% Complete"
3 from v$session_longops
4 where opname LIKE 'RMAN%'
5 and opname NOT LIKE '%aggregate%'
6 and totalwork != 0
7 and sofar <> totalwork;

SID SERIAL# SOFAR TOTALWORK OPNAME % Complete
---------- ---------- ---------- ---------- ---------------------------------------------------------------- ----------
2838 43105 902382 902956 RMAN: archived log backup 99,94

BACKUP AS COPY DATABASE FORMAT - BACKUP AS COPY ARCHIVELOG

RMAN>BACKUP AS COPY DATABASE FORMAT '/u01/backup/%U';

Starting backup at 14-NOV-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/ACME/DATAFILE/system.258.848598015
output file name=/u01/app/oracle/backup/data_D-ACME_I-2033062067_TS-SYSTEM_FNO-1_0dti7k0u tag=TAG20181114T195302 RECID=1 STAMP=992202797
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/ACME/DATAFILE/sysaux.257.848597951
output file name=/u01/app/oracle/backup/data_D-ACME_I-2033062067_TS-SYSAUX_FNO-3_0eti7k1e tag=TAG20181114T195302 RECID=2 STAMP=992202808
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/ACME/DATAFILE/example.266.848598231
output file name=/u01/app/oracle/backup/data_D-ACME_I-2033062067_TS-EXAMPLE_FNO-2_0fti7k1t tag=TAG20181114T195302 RECID=3 STAMP=992202818
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/ACME/DATAFILE/undotbs1.260.848598093
output file name=/u01/app/oracle/backup/data_D-ACME_I-2033062067_TS-UNDOTBS1_FNO-4_0gti7k25 tag=TAG20181114T195302 RECID=4 STAMP=992202822
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

RMAN>BACKUP AS COPY ARCHIVELOG ALL FORMAT  '/u01/backup/%U';

BACKUP AS COPY DATAFILE +ASM

RMAN> backup as copy datafile '+DATA/ACME/DATAFILE/example.266.848598231';

Starting backup at 17-APR-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/ACME/DATAFILE/example.266.848598231
output file name=+FRA/ACME/DATAFILE/example.306.1037994741 tag=TAG20200417T195220 RECID=11 STAMP=1037994759
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 17-APR-20

Starting Control File and SPFILE Autobackup at 17-APR-20
piece handle=+FRA/ACME/AUTOBACKUP/2020_04_17/s_1037994766.305.1037994767 comment=NONE
Finished Control File and SPFILE Autobackup at 17-APR-20

RMAN>

BACKUP AS BACKUPSET TABLESPACE

RMAN> backup as backupset format '/u01/backup/%U' tablespace users;

RMAN> backup as copy archivelog like '+FRA%';

Starting backup at 17-APR-20
using channel ORA_DISK_1
specification does not match any archived log in the repository
backup cancelled because there are no files to backup
Finished backup at 17-APR-20

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Fri Apr 17 19:56:31 2020

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

connected to target database: ORCL (DBID=1376021099)

RMAN> backup as copy archivelog like '/u01/app/oracle/fast_recovery_area%';

Starting backup at 17-APR-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=204 RECID=172 STAMP=1037825464
output file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_04_17/o1_mf_1_204_h9nn3bf7_.arc RECID=179 STAMP=1037995035
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=205 RECID=173 STAMP=1037825755
output file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_04_17/o1_mf_1_205_h9nn3cob_.arc RECID=180 STAMP=1037995035
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=206 RECID=174 STAMP=1037833246
output file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_04_17/o1_mf_1_206_h9nn3dwj_.arc RECID=181 STAMP=1037995037
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=207 RECID=175 STAMP=1037890848
output file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/

BACKUP AS COPY

RMAN> backup as copy incremental level 0 database;

Starting backup at 17-APR-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9qwm8fym_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_sysaux_h9ns0c5m_.dbf tag=TAG20200417T212058 RECID=7 STAMP=1038000097
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_h8bfs33d_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_system_h9ns1rdv_.dbf tag=TAG20200417T212058 RECID=8 STAMP=1038000118
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_9qwmmkbr_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_example_h9ns27hv_.dbf tag=TAG20200417T212058 RECID=9 STAMP=1038000125

RMAN>BACKUP AS COPY DATABASE FORMAT '/u01/backup/%U';

Starting backup at 14-NOV-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/ACME/DATAFILE/system.258.848598015
output file name=/u01/app/oracle/backup/data_D-ACME_I-2033062067_TS-SYSTEM_FNO-1_0dti7k0u tag=TAG20181114T195302 RECID=1 STAMP=992202797
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/ACME/DATAFILE/sysaux.257.848597951
output file name=/u01/app/oracle/backup/data_D-ACME_I-2033062067_TS-SYSAUX_FNO-3_0eti7k1e tag=TAG20181114T195302 RECID=2 STAMP=992202808
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/ACME/DATAFILE/example.266.848598231
output file name=/u01/app/oracle/backup/data_D-ACME_I-2033062067_TS-EXAMPLE_FNO-2_0fti7k1t tag=TAG20181114T195302 RECID=3 STAMP=992202818
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/ACME/DATAFILE/undotbs1.260.848598093
output file name=/u01/app/oracle/backup/data_D-ACME_I-2033062067_TS-UNDOTBS1_FNO-4_0gti7k25 tag=TAG20181114T195302 RECID=4 STAMP=992202822
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/ACME/DATAFILE/users.259.848598091
output file name=/u01/app/oracle/backup/data_D-ACME_I-2033062067_TS-USERS_FNO-6_0hti7k28 tag=TAG20181114T195302 RECID=5 STAMP=992202824
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-NOV-18

TABLESPACE NOLOGGING Y GUARANTEE

VALIDAR TABLESPACES

SQL> select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME        RETENTION

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

SYSTEM        NOT APPLY

SYSAUX        NOT APPLY

UNDOTBS1        GUARANTEE

TEMP        NOT APPLY

USERS        NOT APPLY

EXAMPLE        NOT APPLY

6 rows selected.

SQL> select tablespace_name,status,logging from dba_tablespaces;

TABLESPACE_NAME        STATUS LOGGING
------------------------------ --------- ---------
SYSTEM        ONLINE LOGGING
SYSAUX        ONLINE LOGGING
UNDOTBS1        ONLINE LOGGING
TEMP        ONLINE NOLOGGING
USERS        ONLINE LOGGING
EXAMPLE        ONLINE NOLOGGING

6 rows selected.

SQL> alter tablespace example logging;

Tablespace altered.

SQL> select tablespace_name,status,logging from dba_tablespaces;

TABLESPACE_NAME        STATUS LOGGING
------------------------------ --------- ---------
SYSTEM        ONLINE LOGGING
SYSAUX        ONLINE LOGGING
UNDOTBS1        ONLINE LOGGING
TEMP        ONLINE NOLOGGING
USERS        ONLINE LOGGING
EXAMPLE        ONLINE LOGGING

6 rows selected.

SQL> alter tablespace example nologging;

Tablespace altered.

SQL> select tablespace_name,status,logging from dba_tablespaces;

TABLESPACE_NAME        STATUS LOGGING
------------------------------ --------- ---------
SYSTEM        ONLINE LOGGING
SYSAUX        ONLINE LOGGING
UNDOTBS1        ONLINE LOGGING
TEMP        ONLINE NOLOGGING
USERS        ONLINE LOGGING
EXAMPLE        ONLINE NOLOGGING

6 rows selected.

SQL> alter tablespace example logging;

Tablespace altered.

VALIDAR TABLAS LOGGING

sql> select owner,table_name,LOGGING from dba_tables where owner='HR'

COLOCAR UNA TABLE EN MODE NOLOGGING

alter table customer nologging;

ENABLE CHANGE TRACKING

Use Block Change Tracking

The acquired bank's data warehouse runs on Oracle Database 10g, which offers a few tools John can use to achieve his objectives. For the first objective, he decides to use the Block Change Tracking feature, new in this version. Block change tracking causes the changed database blocks to be flagged in a special file.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/backup/rman/rman.trc';


NOTA: SE ACTIVA CUANDO TENGO UNA ESTRATEGIA DE BACKUP RMAN INCREMENTAL.

SQL> alter database enable block change tracking using file '/u01/backup/rman/tracking.trc';

SQL>  alter database disable block change tracking;

MONITOREAR ESTADO BACKUP INCREMENTALES < 20%


SELECT file#, avg(datafile_blocks), avg(blocks_read), avg(blocks_read/datafile_blocks)*100 AS PCT_READ_FOR_BACKUP, avg(blocks)
FROM V$BACKUP_DATAFILE
WHERE used_change_tracking='YES' and incremental_level>0
GROUP BY file#
/

BACKUP RMAN KEEP FOREVER

ESTOS COMANDOS SOLO FUNCIONAN CUANDO SE UTILIZA UNTIL TIME

RMAN> backup database format '/u01/backup/%U' tag 'BACKUP FULL' keep until time 'sysdate + 30'

2> ;

Starting backup at 30-NOV-20

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=32 device type=DISK

backup will be obsolete on date 30-DEC-20

archived logs required to recover from this backup will be backed up

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9qwm8fym_.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9qwmbsr6_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_9qwmf6kp_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_9qwmmkbr_.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_htct3xg4_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_9qwmf54t_.dbf

channel ORA_DISK_1: starting piece 1 at 30-NOV-20

ESTOS COMANDOS SOLO FUNCIONAN CUANDO SE TIENE UN CATALOGO DE RMAN 

RMAN> backup datafile 1 keep forever;

Starting backup at 30-NOV-20

current log archived

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 11/30/2020 19:03:40

RMAN-06522: KEEP FOREVER option is not supported without the recovery catalog

RMAN> 

RMAN> change backupset 77,78,79 keep until time 'sysdate + 40';

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=74 device type=DISK

keep attributes for the backup are changed

backup will be obsolete on date 09-JAN-21

backup set key=77 RECID=77 STAMP=1057863668

keep attributes for the backup are changed

backup will be obsolete on date 09-JAN-21

backup set key=78 RECID=78 STAMP=1057863673

keep attributes for the backup are changed

backup will be obsolete on date 09-JAN-21

backup set key=79 RECID=79 STAMP=1057863674

RMAN> 

RMAN ARCHIVELOG DELETION POLICY

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE SBT;

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DEVICE TYPE DISK;

RMAN CONFIGURE ARCHIVELOG DELETION POLICY CLEAR;

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO none;

RMAN> configure archivelog deletion policy to backed up 1 times to device type disk;

RMAN> backup as compressed backupset archivelog all;
RMAN> backup archivelog all delete input;

RMAN> backup archivelog all;
RMAN> delete archivelog until time 'sysdate-5';
RMAN> delete noprompt archivelog until time 'sysdate-5';
RMAN> backup archivelog all not backed up;