Amazon RDS for Oracle Specific RMAN Tasks

-- Common parameters for RMAN procedures
p_directory_name:         The name of the directory to contain the backup files.
p_label:                  A unique string that is included in the backup file names.
p_compress:               Specify TRUE to enable BASIC backup compression. (No Advanced Compression Option required)
p_include_archive_logs:   Specify TRUE to include archived redo logs in the backup. (default is False)
p_include_controlfile:    Specify TRUE to include the control file in the backup. (default is False)
p_optimize:               Specify TRUE to enable backup optimization, if archived redo logs are included, to reduce backup size. (default is True)
p_parallel:               Number of channels. (default is 1)
p_rman_to_dbms_output     When TRUE, the RMAN output is sent to the DBMS_OUTPUT plus a file in the BDUMP directory. (Default is False)
p_section_size_mb         The section size in megabytes (MB).
p_validation_type         Specify 'PHYSICAL+LOGICAL' to check for logical inconsistencies in addition to physical corruption. (Default is PHYSICAL)
*/

/* 
In the backend, these procedure triggers RMAN commands similar as below:
RUN_RMAN_CMD: /rdsdbbin/oracle/bin/rman TARGET / 
			LOG /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace/rds-rman-validate-DATAFILE-2021-05-07.01-18-56.035585000.txt 
			@/rdsdbdata/tmp/rds-rman-validate-DATAFILE-2021-05-07.01-18-56.035585000.input 
To check the logfiles for these validate rman commands, use below query: 
*/
select * from table(rdsadmin.rds_file_util.listdir('BDUMP')) where filename like '%rds-rman-validate%';
select * from table(rdsadmin.rds_file_util.listdir('BDUMP')) order by mtime desc;
select text from table(rdsadmin.rds_file_util.read_text_file('BDUMP','rds-rman-validate-DATAFILE-2021-05-07.01-18-56.035585000.txt'));


-- Validating DB instance files
--  Validates the DB instance using the default values for the parameters.
exec rdsadmin.rdsadmin_rman_util.validate_database;
-- Validate dataabase with customized parameters 
set serveroutput on
begin
    rdsadmin.rdsadmin_rman_util.validate_database(
        p_validation_type     => 'PHYSICAL+LOGICAL', 
        p_parallel            => 4,  
        p_section_size_mb     => 10,
        p_rman_to_dbms_output => TRUE);
end;
/

-- Validating a tablespace
set serveroutput on
begin
    rdsadmin.rdsadmin_rman_util.validate_tablespace(
        p_validation_type     => 'PHYSICAL+LOGICAL', 
        p_parallel            => 4,  
        p_section_size_mb     => 10,
        p_rman_to_dbms_output => TRUE,
        p_tablespace_name     => 'USERS');
end;
/

-- Validating a control file
set serveroutput on
begin
    rdsadmin.rdsadmin_rman_util.validate_current_controlfile(
        p_validation_type     => 'PHYSICAL+LOGICAL', 
        p_rman_to_dbms_output => TRUE);
end;
/

-- Validating a SPFILE
set serveroutput on
begin
    rdsadmin.rdsadmin_rman_util.validate_spfile(
        p_validation_type     => 'PHYSICAL+LOGICAL', 
        p_rman_to_dbms_output => TRUE);
end;
/

-- Validating a data file
/* get the file_id or file_name from below query, both can be used as p_datafile
-- select file_id, file_name from dba_data_files;
-- select file#,name from v$datafile;
*/

set serveroutput on
begin
    rdsadmin.rdsadmin_rman_util.validate_datafile(
        p_validation_type     => 'PHYSICAL+LOGICAL', 
        p_parallel            => 4,  
        p_section_size_mb     => 10,
        p_rman_to_dbms_output => TRUE,
        p_datafile            => '/rdsdbdata/db/ORCL_A/datafile/o1_mf_users_j43s2nl4_.dbf',  
        p_from_block          => NULL,
        p_to_block            => NULL);
end;
/

-- Enabling and disabling block change tracking
select status, filename from v$block_change_tracking;
EXEC rdsadmin.rdsadmin_rman_util.enable_block_change_tracking;
EXEC rdsadmin.rdsadmin_rman_util.disable_block_change_tracking;

-- Crosschecking archived redo logs
-- The following example marks archived redo log records in the control file as expired, but does not delete the records.
begin
    rdsadmin.rdsadmin_rman_util.crosscheck_archivelog(
        p_delete_expired      => FALSE,  
        p_rman_to_dbms_output => FALSE);
end;
/
-- The following example deletes expired archived redo log records from the control file.
begin
    rdsadmin.rdsadmin_rman_util.crosscheck_archivelog(
        p_delete_expired      => TRUE,  
        p_rman_to_dbms_output => FALSE);
end;
/

-- Backing up all archived redo logs
-- ORA-20001: archivelog retention hours must be at least 1, 
-- may be set with rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours', HOURS) followed by a COMMIT.
-- If you include archived redo logs in the backup, set retention to one hour or greater using the 
-- rdsadmin.rdsadmin_util.set_configuration procedure. 
-- Also, call the rdsadmin.rdsadmin_rman_util.crosscheck_archivelog procedure immediately before running the backup. Otherwise, 
-- the backup might fail due to missing archived redo log files that have been deleted by Amazon RDS management procedures.

BEGIN
    rdsadmin.rdsadmin_rman_util.backup_archivelog_all(
        p_owner               => 'SYS', 
        p_directory_name      => 'RMAN',
        p_parallel            => 4,  
        p_rman_to_dbms_output => FALSE);
END;
/

-- Backing up an archived redo log from a date range
BEGIN
    rdsadmin.rdsadmin_rman_util.backup_archivelog_date(
        p_owner               => 'SYS', 
        p_directory_name      => 'RMAN',
        p_from_date           => '03/01/2019 00:00:00',
        p_to_date             => '03/02/2019 00:00:00',
        p_parallel            => 4,  
        p_rman_to_dbms_output => FALSE);
END;
/

-- Backing up an archived redo log from an SCN range
BEGIN
    rdsadmin.rdsadmin_rman_util.backup_archivelog_scn(
        p_owner               => 'SYS', 
        p_directory_name      => 'RMAN',
        p_from_scn            => 1533835,
        p_to_scn              => 1892447,
        p_parallel            => 4,  
        p_rman_to_dbms_output => FALSE);
END;
/

-- Backing up an archived redo log from a sequence number range
BEGIN
    rdsadmin.rdsadmin_rman_util.backup_archivelog_sequence(
        p_owner               => 'SYS', 
        p_directory_name      => 'RMAN',
        p_from_sequence       => 122,
        p_to_sequence         => 125,
        p_parallel            => 4,  
        p_rman_to_dbms_output => FALSE);
END;
/ 

-- Performing a full database backup
/*
exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours', 1);
commit;
*/
BEGIN
    rdsadmin.rdsadmin_rman_util.backup_database_full(
        p_owner               => 'SYS', 
        p_directory_name      => 'RMAN',
        p_parallel            => 4,  
        p_section_size_mb     => 10,
        p_rman_to_dbms_output => FALSE);
END;
/

-- list RMAN backup files
select * from table(rdsadmin.rds_file_util.listdir('RMAN')) order by mtime desc;
-- Check RMAN Backup logs
select * from table(rdsadmin.rds_file_util.listdir('BDUMP')) order by mtime desc;
select text from table(rdsadmin.rds_file_util.read_text_file('BDUMP','rds-rman-backup-database-2021-05-07.01-37-56.161968000.txt'));
/*  
Following RMAN codes executed for the backup_database_full procedure
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
2> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rdsdbdata/userdirs/01/BACKUP-2021-05-07-01-37-56-%F';
3> CONFIGURE BACKUP OPTIMIZATION ON;
4> RUN {
5>   ALLOCATE CHANNEL d1 DEVICE TYPE DISK  FORMAT '/rdsdbdata/userdirs/01/BACKUP-2021-05-07-01-37-56-backup-%T-%U';
6>   ALLOCATE CHANNEL d2 DEVICE TYPE DISK  FORMAT '/rdsdbdata/userdirs/01/BACKUP-2021-05-07-01-37-56-backup-%T-%U';
7>   ALLOCATE CHANNEL d3 DEVICE TYPE DISK  FORMAT '/rdsdbdata/userdirs/01/BACKUP-2021-05-07-01-37-56-backup-%T-%U';
8>   ALLOCATE CHANNEL d4 DEVICE TYPE DISK  FORMAT '/rdsdbdata/userdirs/01/BACKUP-2021-05-07-01-37-56-backup-%T-%U';
9> CROSSCHECK ARCHIVELOG ALL;
10> BACKUP DATABASE SECTION SIZE 10M;
11>  RELEASE CHANNEL d1;
12>  RELEASE CHANNEL d2;
13>  RELEASE CHANNEL d3;
14>  RELEASE CHANNEL d4;
15> }
*/

-- Performing an incremental database backup
BEGIN
    rdsadmin.rdsadmin_rman_util.backup_database_incremental(
        p_owner               => 'SYS', 
        p_directory_name      => 'MYDIRECTORY',
        p_level               => 1,
        p_parallel            => 4,  
        p_section_size_mb     => 10,
        p_rman_to_dbms_output => FALSE);
END;
/

-- Performing a tablespace backup
BEGIN
    rdsadmin.rdsadmin_rman_util.backup_tablespace(
        p_owner               => 'SYS', 
        p_directory_name      => 'MYDIRECTORY',
        p_tablespace_name     => MYTABLESPACE,
        p_parallel            => 4,  
        p_section_size_mb     => 10,
        p_rman_to_dbms_output => FALSE);
END;
/   

Reference:

• https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.RMAN.html

How to force syntax highlighting in github source code file

 By default github performs syntax highlighting without extra work. But occasional, some code files appear in plaintext without any syntax highlighting.

How to fix?

Create a file .gitattributes in the same directory, and specify the highlighting requirements inside. Below is one for SQL language.

*.sql linguist-language=sql

Amazon RDS for Oracle Specific Logs Tasks

 -- Setting force logging

exec rdsadmin.rdsadmin_util.force_logging(p_enable => true);

-- Setting supplemental logging

/* Minimal supplemental logging logs the minimal amount of information needed for 
LogMiner to identify, group, and merge the redo operations associated with DML changes. 
It ensures that LogMiner (and any product building on LogMiner technology) has 
sufficient information to support chained rows and various storage arrangements, 
such as cluster tables and index-organized tables. 
To enable minimal supplemental logging, execute the following SQL statement:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
*/
-- The following example enables minimal supplemental logging.
begin
    rdsadmin.rdsadmin_util.alter_supplemental_logging(
        p_action => 'ADD');
end;
/

/*
p_action: 'ADD' to add supplemental logging, 'DROP' to drop supplemental logging.
p_type: The type of supplemental logging. Valid values are 'ALL', 'FOREIGN KEY', 'PRIMARY KEY', 'UNIQUE', or PROCEDURAL.

  ALL: when a row is updated, all columns of that row (except for LOBs, LONGS, and ADTs) are placed in the redo log file.

  FOREIGN KEY: place all columns of a row's foreign key in the redo log file if any column belonging to the foreign key is modified

  PRIMARY KEY: database to place all columns of a row's primary key in the redo log file 
  			   whenever a row containing a primary key is updated (even if no value in the primary key has changed).
  			   If a table does not have a primary key, but has one or more non-null unique index key constraints or index keys, 
  			   then one of the unique index keys is chosen for logging as a means of uniquely identifying the row being updated.
  			   If the table has neither a primary key nor a non-null unique index key, then all columns 
  			   except LONG and LOB are supplementally logged; this is equivalent to specifying ALL supplemental logging

  UNIQUE: place all columns of a row's composite unique key or bitmap index in the redo log file 
  		  if any column belonging to the composite unique key or bitmap index is modified	
 
  PROCEDURAL: Procedural supplemental logging must be enabled for rolling upgrades and Oracle GoldenGate to 
              support replication of AQ queue tables, hierarchy-enabled tables, and tables with SDO_TOPO_GEOMETRY or SDO_GEORASTER columns
 */

-- The following example enables supplemental logging for all fixed-length maximum size columns.
begin
    rdsadmin.rdsadmin_util.alter_supplemental_logging(
        p_action => 'ADD',
        p_type   => 'ALL');
end;
/

-- To check currently supplemental logging enabled status
select name,
supplemental_log_data_min,        
supplemental_log_data_all,      -- ALL
supplemental_log_data_fk,       -- FOREIGN KEY
supplemental_log_data_pk,       -- PRIMARY KEY
supplemental_log_data_ui,       -- UNIQUE
supplemental_log_data_pl,       -- PROCEDURAL
supplemental_log_data_sr       -- Indicates whether the database is enabled for subset database replication
from v$database;

select minimal, all_column, foreign_key, primary_key, unique_index, procedural, subset_rep
from dba_supplemental_logging;

-- Switching online log files
exec rdsadmin.rdsadmin_util.switch_logfile;

-- Check current online logs
-- default is 4 online redolog groups, each with 128MB size and 1 file per group.
select group#, sequence#,  bytes/1024/1024 MBbyte from v$log order by group#;
select group#,member from v$logfile order by group#;

-- Adding online redo logs
-- The size of the log file. You can specify the size in kilobytes (K), megabytes (M), or gigabytes (G).
exec rdsadmin.rdsadmin_util.add_logfile(p_size => '100M');

-- Dropping online redo logs
exec rdsadmin.rdsadmin_util.drop_logfile(grp => 3);

-- Retaining archived redo logs
-- The following example shows the log retention time. (default is 0 hour)
set serveroutput on
exec rdsadmin.rdsadmin_util.show_configuration;
-- The following example retains 24 hours of redo logs.
begin
    rdsadmin.rdsadmin_util.set_configuration(
        name  => 'archivelog retention hours',
        value => '24');
end;
/
commit;

-- Accessing transaction logs
-- access your online and archived redo log files for mining with external tools such as 
-- GoldenGate, Attunity, Informatica, and others. 
-- If you want to access your online and archived redo log files, 
-- you must first create directory objects that provide read-only access to the physical file paths.
exec rdsadmin.rdsadmin_master_util.create_archivelog_dir;
exec rdsadmin.rdsadmin_master_util.create_onlinelog_dir;

exec rdsadmin.rdsadmin_master_util.drop_archivelog_dir;
exec rdsadmin.rdsadmin_master_util.drop_onlinelog_dir;
-- The following code grants and revokes the DROP ANY DIRECTORY privilege.
exec rdsadmin.rdsadmin_master_util.grant_drop_any_directory;
exec rdsadmin.rdsadmin_master_util.revoke_drop_any_directory;

Tested: RDS for Oracle 19c DBEE
Referecence: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Log.html