RMAN – misc. features

Duplexed

RMAN> configure datafile backup copies for device type sbt to 3;
RMAN> configure datafile backup copies for device type disk to 2;
RMAN> configure channel device type disk 
      format '/folder1/%U', '/folder2/%U';
RMAN> backup as compressed backupset device type disk copies 2
      tablespace users
      format '/folder1/%U', '/folder2/%U';

backup of existing backups

RMAN> backup device type sbt backupset all;
RMAN> backup device type sbt backupset 
      completed before 'sysdate-14'
      delete input;

Archival

RMAN> backup as compressed backupset database format '/folder/%U'
      tag "forever"
      keep forever;
RMAN> backup as compressed backupset database format '/folder/%U'
      tag "yearly"
      keep until time 'sysdate+365';

Multi-section

RMAN> backup tablespace users section size 100M;
RMAN> validate tablespace users section size 100M;

Compressed

RMAN> configure compression algorithm 'BZIP2' | 'ZLIB';

Encrypted

RMAN> configure encryption for database on;
RMAN> set encryption identified by "passwd1", "passwd2", ..;
RMAN> set encryption identified by "***" only;

RMAN – block change tracking

enable / disable block change tracking

SQL> alter database enable block change tracking using file '+DATA';
SQL> alter database disable block change tracking;

move block change tracking file

SQL> select filename from v$block_change_tracking;
SQL> shutdown immediate;
SQL> startup mount;
SQL> host mv /path/tracking_filen /path2/tracking_file
SQL> alter database rename file '/path/tracking_file' to '/path2/tracking_file';
SQL> alter database open;

verify benefits

SQL> select file#, 
            avg(datafile_blocks) total,
            avg(blocks_read) read_blocks,
            avg(blocks) bkup_blocks
     from   v$backup_datafile
     where  used_change_tracking = 'YES'
     and    incremental_level = 1
     group by file#
     order by file#
;

sql server – add/drop user

create user

CREATE LOGIN foo WITH PASSWORD = 'xxxx';   
GO 
 
USE [sales_db] 
CREATE USER foo FOR LOGIN foo;   
GO 
 
ALTER ROLE 'db_datareader' ADD MEMBER 'foo';  
 
USE [sales_db] 
GRANT select ON dbo.table_name  TO foo; 
GO 

Drop user

USE [sales_db] 
ALTER AUTHORIZATION ON SCHEMA::[foo] TO [dbo] 
GO  

USE [sales_db] 
ALTER AUTHORIZATION ON ROLE::[foo] TO [dbo] 
GO
 
USE [sales_db] 
DROP USER foo;   
GO

DROP LOGIN foo; 
GO 

oracle locked objects

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.inst_id,
   b.serial#,
   b.status,
   b.username,
   b.osuser,
   b.machine,
   case a.locked_mode
      when 0 then '0 - NONE: lock requested but not yet obtained'
      when 1 then '1 - NULL'
      when 2 then '2 - ROWS_S (SS): Row Share Lock'
      when 3 then '3 - ROW_X (SX): Row Exclusive Table Lock'
      when 4 then '4 - SHARE  : Share Table Lock'
      when 5 then '5 - S/ROW-X (SSX): Share Row Exclusive Table Lock'
      when 6 then '6 - Exclusive  : Exclusive Table Lock'
      else to_char(a.locked_mode)
  end locked_mode
from
   gv$locked_object a ,
   gv$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id
;