Posted in 2016

filebeat and logstash – first setup

First, let’s install logstash, I have changed my yum repository to have access to their server.

root@serv-1: ~
> yum install logstash

Installed:
  logstash.noarch 1:5.2.1-1

Complete!

root@serv-1: /etc/logstash/conf.d
> cat /etc/logstash/conf.d/input.yml
input {
        beats {
                port => 5044
        }
}

output {
        file {
              path => "/tmp/output.log"
        }
}

root@serv-1: /etc/logstash/conf.d
> service logstash start
Redirecting to /bin/systemctl start  logstash.service

Now install and configure filebeat on the database host, I have to install an Oracle database too, I found somewhere on my disk SE2 install files so I used them and configured a very simple database

[root@dbhost01 oracle]# yum install filebeat

Installed:
  filebeat.x86_64 0:5.2.1-1

Complete!

[root@dbhost01 filebeat]# cd /etc/filebeat/
[root@dbhost01 filebeat]# cat filebeat.yml
filebeat.prospectors:
- input_type: log
  paths:
    - "/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log"

output.logstash:
  hosts: ["192.168.56.102:5044"]

logging:
  level: debug
  to_syslog: false
  to_files: true
  files:
    path: /var/log/filebeat
    name: filebeat.log

[root@dbhost01 ~]# service filebeat start

Installing database…

[oracle@dbhost01 database]$ ./runInstaller -silent -responseFile 
/u01/app/oracle/install/database/response/db_install.rsp -ignoreSysPrereqs

[oracle@dbhost01 dbs]$ cat initorcl.ora
db_name='orcl'
db_unique_name='orcl'
sga_target=300m
processes = 150
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='none'
db_block_size=8192
db_domain=''
db_create_file_dest='/u01/app/oracle/oradata'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
[oracle@dbhost01 dbs]$ mkdir -p /u01/app/oracle/admin/orcl/adump
[oracle@dbhost01 dbs]$ mkdir -p /u01/app/oracle/oradata
[oracle@dbhost01 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area
[oracle@dbhost01 dbs]$
[oracle@dbhost01 dbs]$
[oracle@dbhost01 dbs]$
[oracle@dbhost01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 21 15:02:18 2017

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

Connected to an idle instance.

SQL> create spfile from pfile ;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  2923920 bytes
Variable Size             121635440 bytes
Database Buffers          184549376 bytes
Redo Buffers                5464064 bytes
SQL> create database orcl
  2  character set AL32UTF8
  3  national character set AL16UTF16
  4  extent management local
  5  undo tablespace undotbs1 ;

Database created.

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> connect system/oracle
SQL> @?/sqlplus/admin/pupbld.sql

Our database and filebeat is configured, so let’s generate a message

[oracle@dbhost01 trace]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 21 15:53:04 2017

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


Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> exec dbms_system.ksdwrt(2,'ORA-1000 This message is not an error')

PL/SQL procedure successfully completed.

Check on logstash server the output file.

root@serv-1: /etc/logstash
> tail -1f /tmp/output.log
{"@timestamp":"2017-02-21T14:53:32.536Z","offset":18825,"@version":"1","input_type":"log","beat":
{"hostname":"dbhost01","name":"dbhost01","version":"5.2.1"},
"host":"dbhost01","source":"/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log",
"message":"ORA-1000 This message is not an error","type":"log","tags":["beats_input_codec_plain_applied"]}

Yes, it works. Now you can start to read about these programs and configure it with elasticsearch and also somehow parsing the messages coming from the database.

Advertisements
Posted in 2016

RAC to single standby – revisited

Hi, today I want to show how I think it’s possible to migrate a RAC to single.

  1. First let’s identify free disks
    [root@vmware oracle]# lsblk 
    NAME                  MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
    sda                     8:0    0   20G  0 disk 
    ├─sda1                  8:1    0  500M  0 part /boot
    └─sda2                  8:2    0 19.5G  0 part 
      ├─ol_vmware-root    252:0    0 17.5G  0 lvm  /
      └─ol_vmware-swap    252:1    0    2G  0 lvm  [SWAP]
    sdb                     8:16   0  100G  0 disk 
    └─sdb1                  8:17   0  100G  0 part 
      └─vg_oracle-product 252:2    0  100G  0 lvm  /u01
    sdc                     8:32   0   30G  0 disk 
    sdd                     8:48   0   20G  0 disk 
    sr0                    11:0    1 56.3M  0 rom 
    [root@vmware oracle]# fdisk /dev/sdc 
    [root@vmware oracle]# fdisk /dev/sdd
    [root@vmware ~]$ cat /etc/udev/rules.d/99-oracle.rules 
    KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="1ATA_VBOX_HARDDISK_VB2060caf8-3d055279", SYMLINK+="asm-disk1", OWNER="oracle", GROUP="oinstall", MODE="0660"
    KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="1ATA_VBOX_HARDDISK_VB0d4b5c6c-5b81abe5", SYMLINK+="asm-disk2", OWNER="oracle", GROUP="oinstall", MODE="0660"
    [root@vmware rules.d]# systemctl restart systemd-udevd

  2. Install GI and DB Home

  3. Prepare your spfile

    [oracle@vmware dbs]$ strings spfileORCL.ora 
    *.audit_file_dest='/u01/app/oracle/admin/ORCL_SITE2/adump'
    *.thread=1
    *.audit_trail='db','extended'
    *.cluster_database=false
    *.compatible='12.1.0.2.0'
    *.control_file_record_keep_time=90
    *.db_block_size=8192
    *.standby_file_management='AUTO'
    *.db_create_file_dest='+DATA'
    *.db_file_name_convert='ORCL_SITE1','ORCL_SITE2'
    *.db_name='ORCL'
    *.db_recovery_file_dest='+FRA'
    *.db_recovery_file_dest_size=10g
    *.db_securefile='ALWAYS'
    *.db_unique_name='ORCL_SITE2'
    *.diagnostic_dest='/u01/app/oracle'
    *.log_file_name_convert='ORCL_SITE1','ORCL_SITE2'
    *.pga_aggregate_target=200m
    *.remote_login_passwordfile='exclusive'
    *.sga_target=600m
    *.undo_tablespace='UNDOTBS1'
    

  4. Prepare listener

    [oracle@vmware admin]$ cat listener.ora
    # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2_grid/network/admin/listener.ora
    # Generated by Oracle configuration tools.
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = vmware.example.com)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )
    
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = ORCL_SITE2_DGMGRL.example.com)
          (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2)
          (SID_NAME = ORCL)
        )
      )
    
    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
    VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET		# line added by Agent
    
    [oracle@vmware admin]$ srvctl stop listener
    [oracle@vmware admin]$ srvctl start listener
    [oracle@vmware admin]$ lsnrctl services
    
    LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-FEB-2017 11:37:56
    
    Copyright (c) 1991, 2014, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmware.example.com)(PORT=1521)))
    Services Summary...
    Service "ORCL_SITE2_DGMGRL.example.com" has 1 instance(s).
      Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:0 refused:0
             LOCAL SERVER
    The command completed successfully
    

  5. Create a passwordfile

  6. Define your connect identifiers

    [oracle@vmware admin]$ cat tnsnames.ora 
    ORCL_SITE1 = (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL_SITE1_DGMGRL))(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan.example.com)(PORT=1521)))
    ORCL_SITE2 = (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL_SITE2_DGMGRL))(ADDRESS=(PROTOCOL=TCP)(HOST=vmware.example.com)(PORT=1521)))
    

  7. Once you can login from these aliases without problems you can duplicate the DB

    [oracle@vmware dbs]$ tnsping orcl_site1
    
    TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 14-FEB-2017 11:46:22
    
    Copyright (c) 1997, 2014, Oracle.  All rights reserved.
    
    Used parameter files:
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL_SITE1_DGMGRL))(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan.example.com)(PORT=1521)))
    OK (0 msec)
    [oracle@vmware dbs]$ rman target=sys/oracle@orcl_site1 auxiliary=sys/oracle@orcl_site2
    
    Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 14 11:46:24 2017
    
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: ORCL (DBID=145047211)
    connected to auxiliary database: ORCL (not mounted)
    
    RMAN> duplicate target database for standby from active database dorecover ;
    Starting Duplicate Db at 14-FEB-17
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=22 device type=DISK
    current log archived
    
    contents of Memory Script:
    {
       backup as copy reuse
       targetfile  '+DATA/ORCL_SITE1/PASSWORD/pwdorcl_site1.260.935837221' auxiliary format 
     '/u01/app/oracle/product/12.1.0.2/dbs/orapwORCL'   ;
    }
    executing Memory Script
    
    Starting backup at 14-FEB-17
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=57 instance=ORCL2 device type=DISK
    Finished backup at 14-FEB-17
    
    contents of Memory Script:
    {
       sql clone "alter system set  control_files = 
      ''+DATA/ORCL_SITE2/CONTROLFILE/current.257.935927295'', ''+FRA/ORCL_SITE2/CONTROLFILE/current.256.935927295'' comment=
     ''Set by RMAN'' scope=spfile";
       restore clone from service  'orcl_site1' standby controlfile;
    }
    executing Memory Script
    
    sql statement: alter system set  control_files =   ''+DATA/ORCL_SITE2/CONTROLFILE/current.257.935927295'', ''+FRA/ORCL_SITE2/CONTROLFILE/current.256.935927295'' comment= ''Set by RMAN'' scope=spfile
    
    Starting restore at 14-FEB-17
    using channel ORA_AUX_DISK_1
    
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring control file
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
    output file name=+DATA/ORCL_SITE2/CONTROLFILE/current.259.935927295
    output file name=+FRA/ORCL_SITE2/CONTROLFILE/current.258.935927295
    Finished restore at 14-FEB-17
    
    contents of Memory Script:
    {
       sql clone 'alter database mount standby database';
    }
    executing Memory Script
    
    sql statement: alter database mount standby database
    RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
    
    contents of Memory Script:
    {
       set newname for tempfile  1 to 
     "+DATA";
       switch clone tempfile all;
       set newname for datafile  1 to 
     "+DATA";
       set newname for datafile  2 to 
     "+DATA";
       set newname for datafile  3 to 
     "+DATA";
       set newname for datafile  4 to 
     "+DATA";
       set newname for datafile  5 to 
     "+DATA";
       restore
       from service  'orcl_site1'   clone database
       ;
       sql 'alter system archive log current';
    }
    executing Memory Script
    
    executing command: SET NEWNAME
    
    renamed tempfile 1 to +DATA in control file
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    Starting restore at 14-FEB-17
    using channel ORA_AUX_DISK_1
    
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    Finished restore at 14-FEB-17
    
    sql statement: alter system archive log current
    current log archived
    
    contents of Memory Script:
    {
       restore clone force from service  'orcl_site1' 
               archivelog from scn  445938;
       switch clone datafile all;
    }
    executing Memory Script
    
    Starting restore at 14-FEB-17
    using channel ORA_AUX_DISK_1
    
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=1 sequence=35
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=1 sequence=36
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=2 sequence=14
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=2 sequence=15
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=2 sequence=16
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
    Finished restore at 14-FEB-17
    
    datafile 1 switched to datafile copy
    input datafile copy RECID=6 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/system.260.935927305
    datafile 2 switched to datafile copy
    input datafile copy RECID=7 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/sysaux.261.935927319
    datafile 3 switched to datafile copy
    input datafile copy RECID=8 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/undotbs2.262.935927327
    datafile 4 switched to datafile copy
    input datafile copy RECID=9 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/undotbs1.263.935927335
    datafile 5 switched to datafile copy
    input datafile copy RECID=10 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/users.264.935927337
    
    contents of Memory Script:
    {
       set until scn  446200;
       recover
       standby
       clone database
        delete archivelog
       ;
    }
    executing Memory Script
    
    executing command: SET until clause
    
    Starting recover at 14-FEB-17
    using channel ORA_AUX_DISK_1
    
    starting media recovery
    
    archived log for thread 1 with sequence 35 is already on disk as file +FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_1_seq_35.259.935927347
    archived log for thread 1 with sequence 36 is already on disk as file +FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_1_seq_36.260.935927349
    archived log for thread 2 with sequence 15 is already on disk as file +FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_2_seq_15.262.935927351
    archived log for thread 2 with sequence 16 is already on disk as file +FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_2_seq_16.263.935927353
    archived log file name=+FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_1_seq_35.259.935927347 thread=1 sequence=35
    archived log file name=+FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_2_seq_15.262.935927351 thread=2 sequence=15
    archived log file name=+FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_1_seq_36.260.935927349 thread=1 sequence=36
    archived log file name=+FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_2_seq_16.263.935927353 thread=2 sequence=16
    media recovery complete, elapsed time: 00:00:00
    Finished recover at 14-FEB-17
    Finished Duplicate Db at 14-FEB-17
    
    RMAN> 

  8. Register this database

    srvctl add database -db ORCL_SITE2 -oraclehome /u01/app/oracle/product/12.1.0.2 -domain example.com -spfile /u01/app/oracle/product/12.1.0.2/dbs/spfileORCL.ora -pwfile /u01/app/oracle/product/12.1.0.2/dbs/orapwORCL -role PHYSICAL_STANDBY -startoption mount -dbname ORCL -diskgroup "DATA,FRA"
    srvctl enable database -db ORCL_SITE2

  9. Start dg broker

    SQL> show parameter dg_broker
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    dg_broker_config_file1		     string	 /u01/app/oracle/product/12.1.0
    						 .2/dbs/dr1ORCL_SITE2.dat
    dg_broker_config_file2		     string	 /u01/app/oracle/product/12.1.0
    						 .2/dbs/dr2ORCL_SITE2.dat
    dg_broker_start 		     boolean	 FALSE
    SQL> alter system set dg_broker_start=true ;
    
    System altered.

  10. Create standby logfiles

    alter database add standby logfile thread 1 ;
    alter database add standby logfile thread 1 ;
    alter database add standby logfile thread 1 ;
    
    alter database add standby logfile thread 2 ;
    alter database add standby logfile thread 2 ;
    alter database add standby logfile thread 2 ;

  11. Create DG Broker Configuration

    [oracle@vmware dbs]$ dgmgrl sys/oracle@orcl_site1
    DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
    
    Copyright (c) 2000, 2013, Oracle. All rights reserved.
    
    Welcome to DGMGRL, type "help" for information.
    Connected as SYSDBA.
    DGMGRL> create configuration dg_orcl as PRIMARY DATABASE IS orcl_site1 connect identifier is orcl_site1 ;
    Configuration "dg_orcl" created with primary database "orcl_site1"
    DGMGRL> add database orcl_site2 as connect identifier is orcl_site2 ;
    Database "orcl_site2" added
    DGMGRL> enable configuration ;
    Enabled.

  12. Check Configuration

    DGMGRL> validate database orcl_site1
    
      Database Role:    Primary database
    
      Ready for Switchover:  Yes
    
      Flashback Database Status:
        orcl_site1:  Off
    
    DGMGRL> validate database orcl_site2
    
      Database Role:     Physical standby database
      Primary Database:  orcl_site1
    
      Ready for Switchover:  Yes
      Ready for Failover:    Yes (Primary Running)
    
      Capacity Information:
        Database     Instances        Threads        
        orcl_site1   2                2              
        orcl_site2   1                2              
        Warning: the target standby has fewer instances than the
        primary database, this may impact application performance
    
      Flashback Database Status:
        orcl_site1:  Off
        orcl_site2:  Off
    

And you have now a standby database.

Posted in 2017

Writing data to textfiles – UTL_FILE

DECLARE
 dir_exists NUMBER;
 dir_name VARCHAR2(50);
 file_name VARCHAR2(50);
 v_exists BOOLEAN;
 v_file_length NUMBER;
 v_blk_size BINARY_INTEGER;
 file_h UTL_FILE.file_type;
BEGIN
 dir_name := 'DATA_PUMP_DIR';
 -- get file_name
 select dbms_random.string('A',5)||'_'|| sys_context('userenv','db_name') || '.txt'
 into file_name
 from dual;

 -- Check if file exists and drop if it is
 UTL_FILE.FGETATTR (dir_name, file_name, v_exists, v_file_length, v_blk_size);
 IF v_exists THEN
   DBMS_OUTPUT.PUT_LINE('PURGING FILE: '||file_name) ;
   UTL_FILE.fremove(dir_name, file_name);
 END IF;

 -- open the file
 file_h := UTL_FILE.fopen(dir_name, file_name, 'W', 32767);

 -- get and write the data
FOR rec in (select username||';'||password as data from dba_users ) LOOP
 UTL_FILE.put_line (file_h, rec.data);
END LOOP;
 UTL_FILE.fclose (file_h);

END;
/
Posted in 2017

Snapshot Standby Sandbox

SQL@orcl_stby> alter database recover managed standby database cancel ;

Database altered.

SQL@orcl_stby> alter database convert to snapshot standby ;

Database altered.

SQL@orcl_stby> alter database open ;

Database altered.

SQL@orcl_stby> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/db1/orcl/system01.dbf
/u02/app/oracle/db1/orcl/sysaux01.dbf
/u02/app/oracle/db1/orcl/undotbs01.dbf
/u02/app/oracle/db1/orcl/users01.dbf

SQL@orcl_stby> create tablespace TEST datafile '/u02/app/oracle/db1/orcl/test.dbf' size 100m ;

Tablespace created.

SQL@orcl_stby> alter user psorger default tablespace TEST quota unlimited on test ;

User altered.

SQL@orcl_stby> connect psorger/psorger
Connected.
SQL@orcl_stby> create table TEST (ID number PRIMARY KEY, text clob) ;

Table created.

SQL@orcl_stby> insert into test select rownum, dbms_random.string('c',6000) from dual connect by level<=1000 ;

1000 rows created.

SQL@orcl_stby> commit ;

Commit complete.

SQL@orcl_stby> select index_name from user_indexes ;    

INDEX_NAME
------------------------------
SYS_C004727
SYS_IL0000070440C00002$$

SQL@orcl_stby> alter index SYS_C004727 rename to PK_ID_TEST ;

Index altered.

SQL@orcl_stby> select index_name from user_indexes ;

INDEX_NAME
------------------------------
SYS_IL0000070440C00002$$
PK_ID_TEST

SQL@orcl_stby> delete from test where rownum<=500 ;

500 rows deleted.

SQL@orcl_stby> commit ;

Commit complete.

SQL@orcl_stby> connect / as sysdba
Connected.
SQL@orcl_stby> create table psorger.test_interim as select * from psorger.test where rownum<=0 
  2  ;

Table created.

SQL@orcl_stby> BEGIN
  2  dbms_redefinition.can_redef_table('psorger','test',DBMS_REDEFINITION.CONS_USE_PK);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL@orcl_stby> 
SQL@orcl_stby> BEGIN
  2  DBMS_REDEFINITION.START_REDEF_TABLE('psorger','test','test_interim',dbms_redefinition.cons_use_pk);
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1498
ORA-06512: at line 2


SQL@orcl_stby> desc test_interim
ERROR:
ORA-04043: object test_interim does not exist


SQL@orcl_stby> desc psorger.test_interim
 Name								   Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ID									    NUMBER
 TEXT									    CLOB

SQL@orcl_stby> desc psorger.test       
 Name								   Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ID								   NOT NULL NUMBER
 TEXT									    CLOB

SQL@orcl_stby> drop table psorger.test_interim ;

Table dropped.

SQL@orcl_stby> create table psorger.test_interim (id number primary key, text clob) ;

Table created.

SQL@orcl_stby> BEGIN
  2  DBMS_REDEFINITION.START_REDEF_TABLE('psorger','test','test_interim',dbms_redefinition.cons_use_pk);
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1498
ORA-06512: at line 2

SQL@orcl_stby> begin
  2  dbms_redefinition.start_redef_table(uname=>'PSORGER',orig_table=>'TEST',int_table=>'TEST_INTERIM',options_flag=>dbms_redefinition.cons_use_pk) ;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL@orcl_stby> insert into psorger.test values (1,'KIK') ;

1 row created.

SQL@orcl_stby> commit ;

Commit complete.

SQL@orcl_stby> select count(*) from psorger.test ;

  COUNT(*)
----------
       501

1 row selected.

SQL@orcl_stby> select count(*) from psorger.test_interim ;

  COUNT(*)
----------
       500

1 row selected.

SQL@orcl_stby> DECLARE
  2  errors pls_integer ;
  3  BEGIN
  4  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('PSORGER','TEST','TEST_INTERIM',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, errors) ;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL@orcl_stby> select object_name, base_table_name, ddl_txt from
         DBA_REDEFINITION_ERRORS;  2   

OBJECT_NAME		       BASE_TABLE_NAME
------------------------------ ------------------------------
DDL_TXT
--------------------------------------------------------------------------------
PK_ID_TEST		       TEST
CREATE UNIQUE INDEX "PSORGER"."TMP$$_PK_ID_TEST0" ON "PSORGER"."TEST_INTERIM" ("

SYS_C004727		       TEST
ALTER TABLE "PSORGER"."TEST_INTERIM" ADD CONSTRAINT "TMP$$_SYS_C0047270" PRIMARY


2 rows selected.

SQL@orcl_stby> desc psorger.test_interim
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 ID						       NOT NULL NUMBER
 TEXT								CLOB

SQL@orcl_stby> select index_name from dba_indexes where owner='PSORGER';

INDEX_NAME
------------------------------
PK_ID_TEST
SYS_IL0000070440C00002$$
I_MLOG$_TEST
SYS_C004728
SYS_IL0000070447C00002$$

5 rows selected.

SQL@orcl_stby> exec dbms_redefinition.sync_interim_table('PSORGER','TEST','TEST_INTERIM') 

PL/SQL procedure successfully completed.

SQL@orcl_stby> select count(*) from PSORGER.TEST ;

  COUNT(*)
----------
       501

1 row selected.

SQL@orcl_stby> select count(*) from PSORGER.TEST_interim ;

  COUNT(*)
----------
       501

1 row selected.

SQL@orcl_stby> exec dbms_redefinition.finish_redef_table('PSORGER','TEST','TEST_INTERIM')

PL/SQL procedure successfully completed.

SQL@orcl_stby> select index_name from dba_indexes where owner='PSORGER';

INDEX_NAME
------------------------------
PK_ID_TEST
SYS_IL0000070440C00002$$
SYS_C004728
SYS_IL0000070447C00002$$

4 rows selected.

SQL@orcl_stby> select segment_name, bytes/1024/1024 mb from dba_segments where owner='PSORGER';

SEGMENT_NAME										  MB
--------------------------------------------------------------------------------- ----------
SYS_LOB0000070447C00002$$								   5
SYS_LOB0000070440C00002$$								   9
SYS_C004728									       .0625
SYS_IL0000070447C00002$$							       .0625
PK_ID_TEST									       .0625
SYS_IL0000070440C00002$$								.125
TEST										       .0625
TEST_INTERIM										.125

8 rows selected.


SQL@orcl_stby> drop table psorger.TEST_INTERIM purge ;

Table dropped.

SQL@orcl_stby> select index_name from dba_indexes where owner='PSORGER';

INDEX_NAME
------------------------------
SYS_C004728
SYS_IL0000070447C00002$$

2 rows selected.

SQL@orcl_stby>

SQL@orcl_stby> SELECT 
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;  2    3    4  

PERCENT_FULL
------------
	 5.8

	 
SQL@orcl_stby> select * from v$recovery_file_dest ;

NAME
------------------------------------------------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/u02/app/oracle/arch1/recovery
 3221225472  186630144		       0	       5

SQL@orcl_stby> !ls -l /u02/app/oracle/arch1/recovery/ORCL/flashback
total 153616
-rw-r-----. 1 oracle dba 78651392 Apr 29 12:28 o1_mf_bn1dh6tq_.flb
-rw-r-----. 1 oracle dba 78651392 Apr 29 12:26 o1_mf_bn1dh9qs_.flb

SQL@orcl_stby>

SQL@orcl_stby> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL@orcl_stby> startup mount
ORACLE instance started.

Total System Global Area  367439872 bytes
Fixed Size		    2253344 bytes
Variable Size		  117444064 bytes
Database Buffers	  243269632 bytes
Redo Buffers		    4472832 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode


SQL@orcl_stby> !
Classic layout determined.

+++   MSH - Version 2014.06   +++

cFollowing aliases were set:
cdscr  - change to scriptdir
cdscrl  - change to local scriptdir
cdlog  - change to logdir
oram   - set environment for ORACLE_SID
         interactive or with the ORACLE_SID as parameter
cdoh   - change to ORACLE_HOME of current DB
cdnet  - change to directory ../network/admin of current DB
cdXX   - change to dedicated directories, XX stands for:
         bd(bdump),ud(udump),aud(audit_file_dest),dbs(ORAHOME/dbs)
cddat  - change to parent datafile directory of current DB
cdarc  - change to archive directory of current DB
talert - continous view on alert-Log of current DB
valert - open alert-Log of current DB (view)
vinit  - edit init.ora/spfile.ora of current DB
s+     - command: sqlplus '/ as sysdba' (inkl. Status)
         remote connection via SQL*NET: s+ <ORACLE_SID>
syspw  - set environment variable SYSPW
vicron - editing crontab, with backupfile
do_sql   - SQL Shellfunc / as sysdba, do_sql "select...;" pipe-able
amon     - Oracle Performance Tool AMON
s++      - MySQLPLUS - Comfortable SQL-Plus
rman++   - Comfortable rman
dgmgrl++ - Comfortable dgmgrl
asmcmd++ - Comfortable ascmd
adrci++  - Comfortable adrci
dCurrent ORACLE_SID is orcl_stby.
oracle@test$ cddbs
current directory: /u01/app/oracle/product/11.2.0.4/dbs
oracle@test$ rm lkORCL # lebo mam standby s tym istym dbnamemom na servri a vytvara mi vzdy ten isty lock file - workaround
oracle@test$ exit
exit

SQL@orcl_stby> alter database mount standby database ;
alter database mount standby database
*
ERROR at line 1:
ORA-01665: control file is not a standby control file


SQL@orcl_stby> alter database mount ;

Database altered.

SQL@orcl_stby> alter database convert to physical standby ;

Database altered.

SQL@orcl_stby> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL@orcl_stby> startup mount
ORACLE instance started.

Total System Global Area  367439872 bytes
Fixed Size		    2253344 bytes
Variable Size		  117444064 bytes
Database Buffers	  243269632 bytes
Redo Buffers		    4472832 bytes
Database mounted.
SQL@orcl_stby> alter database recover managed standby database using current logfile disconnect ;

Database altered.

oracle@test$ oram
oram runs with /etc/oratab
SID-SETUP
Choose SID:
NR   SID      RUNNING  # COMMENT
 1 - orcl_stby <*>      
 2 - orcl     <*>      

Enter number or SID (type 'end' to exit):
ORACLE_SID =  ? 1
ORACLE_SID:  orcl_stby
ORACLE_HOME: /u01/app/oracle/product/11.2.0.4
oracle@test$ oram 2
oram runs with /etc/oratab
Temporary init.ora /tmp/pfileorcl_oracle.tmp created
ORACLE_SID:  orcl
ORACLE_HOME: /u01/app/oracle/product/11.2.0.4
oracle@test$ s+
SQL@orcl> select table_name from dba_tables where owner='PSORGER';

no rows selected

SQL@orcl>