Recent Updates Page 5

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
Unknown's avatar Frank 5:05 am on August 30, 2016  

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
;
Unknown's avatar Frank 4:52 am on August 30, 2016  

oracle sid and spid

Find session details


select s.INST_ID, s.SID, s.USERNAME, s.OSUSER,
       s.MACHINE, p.SPID, s.PROGRAM
from gv$session s
join gv$process p on s.PADDR = p.ADDR
and s.INST_ID = p.INST_ID
where sid =
;

select s.SID, s.USERNAME, s.OSUSER,
       s.MACHINE, p.SPID, s.PROGRAM
from v$session s
join v$process p on s.PADDR = p.ADDR
where sid =
;
Unknown's avatar Frank 4:59 am on August 24, 2016
Tags: jdbc   

example jdbc program

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
class TableCount
{
public static void main(String[] args)
{
TableCount t = new TableCount();
t.run(args);
}
public void run(String[] args)
{
try
{
Connection conn = getConnection(args[0], args[1], args[2]);
getTableCount(conn, args[3]);
conn.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
public static Connection getConnection(Properties p) throws Exception
{
Connection conn = null;
String username = p.getProperty("USERNAME");
String password = p.getProperty("PASSWORD");
String url = p.getProperty("URL");
String driver = p.getProperty("DRIVER");
System.out.println(username);
System.out.println(url);
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
conn.setAutoCommit(false);
return conn;
}
public static Connection getConnection(String file, String username, String password) throws Exception
{
Properties props = loadProperties(file);
props.setProperty("USERNAME", username);
props.setProperty("PASSWORD", password);
return getConnection(props);
}
private static Properties loadProperties(String file) throws FileNotFoundException, IOException
{
Properties props = new Properties();
props.load(new FileInputStream(new File(file)));
return props;
}
public void getTableCount(Connection conn, String owner) throws SQLException
{
String sql = null;
ResultSet rs = null;
PreparedStatement stmt = null;
sql = "SELECT count(*) c from all_tables where owner = ? ";
try
{
stmt = conn.prepareStatement(sql);
stmt.setString(1, owner);
rs = stmt.executeQuery();
while (rs.next())
{
long count = rs.getLong("c");
System.out.println("~~");
System.out.println(count);
}
}
catch(SQLException exception)
{
exception.printStackTrace();
}
finally
{
}
}
}
view raw TableCount.java hosted with ❤ by GitHub
Unknown's avatar Frank 2:55 am on August 24, 2016  

perl by example

Source code,

sub build_script
{
my $cols = shift;
foreach my $col (@$cols)
{
print($col . ",\n");
}
}
sub parse_source_file
{
my $filename = shift;
my $table_name = shift;
my $file;
my $line;
my $cols = ();
if (!open($file, "<", $filename))
{
print "file not found\n";
return;
}
while ($line = <$file>)
{
$line =~ s/\W+$//;
my @fields = split(',', $line);
if ($fields[0] eq $table_name)
{
push(@cols, $fields[1]);
}
}
close($file);
return \@cols;
}
sub main
{
my $db = uc($ARGV[0]);
my $table = uc($ARGV[1]);
my $source = $ARGV[2];
my $cols = parse_source_file($source, $table);
build_script($cols);
}
main
view raw parser.pl hosted with ❤ by GitHub