Monthly Archives: September 2016

Unknown's avatar Frank 9:27 pm on September 11, 2016  

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;
Unknown's avatar Frank 8:22 am on September 10, 2016  

using merge to update table

sometimes, merge is handy and performs better,

merge into s1.table1 t
using (
      select t1.col_x, 
             t2.col_y
        from s1.table1 t1
        join s1.table2 t2
          on t1.col1 = t2.col2
       where t1.col3 = value_z
) src
on (t.col_x = src.col_x)
when matched then
  update set t.col_y = src.col_y
;
Unknown's avatar Frank 10:59 pm on September 8, 2016  

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#
;
Unknown's avatar Frank 10:24 pm on September 6, 2016  

RMAN – image copy

Backup image copy

RMAN> backup incremental level 0 as copy tablespace [tbsp_name];

Restore 

RMAN> sql "alter tablespace [tbsp_name] offline immediately";
RMAN> switch tablespace [tbsp_name] to copy;
RMAN> recover tablespace [tbsp_name];
RMAN> sql "alter tablespace [tbsp_name] online";
Unknown's avatar Frank 5:21 pm on September 5, 2016  

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