miércoles, 26 de diciembre de 2018

DUPLICATE LOCATION UNTIL TIME

DUPLICATE target DATABASE TO 'TRGT'
UNTIL TIME "TO_DATE('03/03/2011 20:04:00','MM/DD/YYYY HH24:MI:SS')"  #after controlfile backup
SPFILE
set memory_max_target='4g'
set memory_target='4g'
set control_files='/u02/oracle/TRGT/db/apps_st/data/cntrl01.dbf','/u10/oracle/TRGT/db/apps_st/data/cntrl02.dbf'
set db_file_name_convert='/u10/oracle/SRCDB/db/apps_st/data','/u10/oracle/TRGT/db/apps_st/data','/u11/oracle/SRCDB/db/apps_st/data','/u11/oracle/TRGT/db/apps_st/data'
set log_file_name_convert='/u10/oracle/SRCDB/db/apps_st/data','/u10/oracle/TRGT/db/apps_st/data','/u11/oracle/SRCDB/db/apps_st/data','/u11/oracle/TRGT/db/apps_st/data'
set audit_file_dest='/u02/oracle/TRGT/admin/adump'
set db_recovery_file_dest='/arch/flash_recovery_area'
set diagnostic_dest='/u02/oracle/TRGT'
BACKUP LOCATION '/arch/flash_recovery_area/SRCDB';

viernes, 30 de noviembre de 2018

CONFIGURE EXCLUDE FOR TABLESPACE

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE EXAMPLE;

RMAN> SHOW ALL;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE EXCLUDE FOR TABLESPACE 'EXAMPLE';
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/db_1/dbs/snapcf_orcl.f'; # default

RMAN>


RMAN> CONFIGURE EXCLUDE FOR TABLESPACE EXAMPLE CLEAR;

Tablespace EXAMPLE will be included in future whole database backups
old RMAN configuration parameters are successfully deleted

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/db_1/dbs/snapcf_orcl.f'; # default

RMAN>


RMAN> BACKUP DATABASE NOEXCLUDE;


lunes, 26 de noviembre de 2018

SESIONES ESPERANDO

select
a.inst_id,
a.sid,
a.serial#,
a.username,
a.status,
a.osuser,
a.process,
a.machine,
a.program,
a.type,
a.sql_id,
a.client_info,
a.logon_time,
b.PLSQL_EXEC_TIME,
b.rows_processed,
b.optimizer_cost,
b.cpu_time,
b.elapsed_time/1000000 as ELAPSED_TIME_SECS,
b.elapsed_time/60000000 as ELAPSED_TIME_MINS,
b.elapsed_time/3600000000 as ELAPSED_TIME_HRS,
b.SQL_TEXT,
'ALTER SYSTEM KILL SESSION ' || CHR(39) ||sid || ',' || serial# || CHR(39) || ' IMMEDIATE; '
from
gv$session a,
gv$sql b
where
a.sql_id = b.sql_id
AND b.elapsed_time/3600000000 > 1
order by elapsed_time desc;

miércoles, 3 de octubre de 2018

Displaying Index Code

Displaying Index Code


From time to time you’ll need to drop an index. This could be because of an obsolete application or

you’ve established that an index is no longer used. Prior to dropping an index, we recommend that you

generate the data definition language (DDL) that would be required to re-create the index. This allows

you to re-create the index (as it was before it was dropped) in the event that dropping the index has a

detrimental impact on performance and needs to be re-created.


Use the DBMS_METADATA.GET_DDL function to display an object’s DDL. Make sure you set the LONG



variable to an appropriate value so that the returned CLOB value is displayed in its entirety. For 

example,



SQL> set long 1000000

SQL> select dbms_metadata.get_ddl('INDEX','ADDR_FK1') from dual;





Here is the output:



DBMS_METADATA.GET_DDL('INDEX','ADDR_FK1')

--------------------------------------------------------------------------------

CREATE INDEX "MV_MAINT"."ADDR_FK1" ON

"MV_MAINT"."ADDRESS" ("CUST_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255

COMPUTE STATISTICS STORAGE(INITIAL 1048576

NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0





FREELISTS 1 FREELIST GROUPS 1

sábado, 29 de septiembre de 2018

HINTS EXPLICACION

SELECT      /*+ hint --or-- text */    statement body  
            -- or --      
SELECT          --+ hint --or-- text      statement body

Where:
      • /*, */  -  These are the comment delimiters for multi-line comments
      • --  -  This is the comment delimiter for a single line comment (not usually used for hints)
      • +  -  This tells Oracle a hint follows, it must come immediately after the /*
      • hint  -  This is one of the allowed hints
      • text  -  This is the comment text

Oracle Hint
Meaning
+
Must be immediately after comment indicator, tells Oracle this is a list of hints.
ALL_ROWS
Use the cost based approach for best throughput.
CHOOSE
Default, if statistics are available will use cost, if not, rule.
FIRST_ROWS
Use the cost based approach for best response time.
RULE
Use rules based approach; this cancels any other hints specified for this statement.
Access Method Oracle Hints:

CLUSTER(table)
This tells Oracle to do a cluster scan to access the table.
FULL(table)
This tells the optimizer to do a full scan of the specified table.
HASH(table)
Tells Oracle to explicitly choose the hash access method for the table.
HASH_AJ(table)
Transforms a NOT IN subquery to a hash anti-join.
ROWID(table)
Forces a rowid scan of the specified table.
INDEX(table [index])
Forces an index scan of the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.
INDEX_ASC (table [index])
Same as INDEX only performs an ascending search of the index chosen, this is functionally identical to the INDEX statement.
INDEX_DESC(table [index])
Same as INDEX except performs a descending search. If more than one table is accessed, this is ignored.
INDEX_COMBINE(table index)
Combines the bitmapped indexes on the table if the cost shows that to do so would give better performance.
INDEX_FFS(table index)
Perform a fast full index scan rather than a table scan.
MERGE_AJ (table)
Transforms a NOT IN subquery into a merge anti-join.
AND_EQUAL(table index index [index index index])
This hint causes a merge on several single column indexes. Two must be specified, five can be.
NL_AJ
Transforms a NOT IN subquery into a NL anti-join (nested loop).
HASH_SJ(t1, t2)
Inserted into the EXISTS subquery; This converts the subquery into a special type of hash join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
MERGE_SJ (t1, t2)
Inserted into the EXISTS subquery; This converts the subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
NL_SJ
Inserted into the EXISTS subquery; This converts the subquery into a special type of nested loop join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
Oracle Hints for join orders and transformations:

ORDERED
This hint forces tables to be joined in the order specified. If you know table X has fewer rows, then ordering it first may speed execution in a join.
STAR
Forces the largest table to be joined last using a nested loops join on the index.
STAR_TRANSFORMATION
Makes the optimizer use the best plan in which a start transformation is used.
FACT(table)
When performing a star transformation use the specified table as a fact table.
NO_FACT(table)
When performing a star transformation do not use the specified table as a fact table.
PUSH_SUBQ
This causes nonmerged subqueries to be evaluated at the earliest possible point in the execution plan.
REWRITE(mview)
If possible forces the query to use the specified materialized view, if no materialized view is specified, the system chooses what it calculates is the appropriate view.
NOREWRITE
Turns off query rewrite for the statement, use it for when data returned must be concurrent and can't come from a materialized view.
USE_CONCAT
Forces combined OR conditions and IN processing in the WHERE clause to be transformed into a compound query using the UNION ALL set operator.
NO_MERGE (table)
This causes Oracle to join each specified table with another row source without a sort-merge join.
NO_EXPAND
 Prevents OR and IN processing expansion.
Oracle Hints for Join Operations:

USE_HASH (table)
This causes Oracle to join each specified table with another row source with a hash join.
USE_NL(table)
This operation forces a nested loop using the specified table as the controlling table.
USE_MERGE(table,[table, - ])
This operation forces a sort-merge-join operation of the specified tables.
DRIVING_SITE
The hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.
LEADING(table)
The hint causes Oracle to use the specified table as the first table in the join order.
Oracle Hints for Parallel Operations:

[NO]APPEND
This specifies that data is to be or not to be appended to the end of a file rather than into existing free space. Use only with INSERT commands.
NOPARALLEL (table
This specifies the operation is not to be done in parallel.
PARALLEL(table, instances)
This specifies the operation is to be done in parallel.
PARALLEL_INDEX
Allows parallelization of a fast full index scan on any index.
Other Oracle Hints:

CACHE
Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list when the table is full table scanned.
NOCACHE
Specifies that the blocks retrieved for the table in the hint are placed at the least recently used end of the LRU list when the table is full table scanned.
[NO]APPEND
For insert operations will append (or not append) data at the HWM of table.
UNNEST
Turns on the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to FALSE.
NO_UNNEST
Turns off the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to TRUE.
PUSH_PRED
 Pushes the join predicate into the view.


/*+ ALL_ROWS */ Pone la consulta a costes y la optimiza para que consuma el menor número
de recursos posibles.
/*+ FIRST_ROWS */ Pone la consulta a costes la optimiza para conseguir el mejor tiempo de
respuesta.
/*+ CHOOSE */ Pone la consulta a costes.
/*+ RULE */ Pone la consulta a reglas.
/*+ INDEX( tabla índice ) */ Fuerza la utilización del índice indicado para la tabla indicada
/*+ ORDERED */ Hace que las combinaciones de las tablas se hagan en el mismo orden en
que aparecen en el join.

EJEMPLO

SQL> select /*+ index(t1) */ v1 from t1 where n1 >0;

Execution Plan
----------------------------------------------------------
Plan hash value: 1429545322

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  4000 |   566K|  2523   (1)| 00:00:13 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |  4000 |   566K|  2523   (1)| 00:00:13 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |  4000 |       |    10   (0)| 00:00:01 |

jueves, 27 de septiembre de 2018

View Traces Actives DBA_ENABLED_TRACES - TRACES

View Enabled Traces for End to End Tracing

An Oracle Enterprise Manager report or the DBA_ENABLED_TRACES view can display outstanding traces. In the DBA_ENABLED_TRACES view, you can determine detailed information about how a trace was enabled, including the trace type. The trace type specifies whether the trace is enabled for client identifier, session, service, database, or a combination of service, module, and action.



while true; do ls -ltr | wc -l; sleep 3; done


martes, 18 de septiembre de 2018

BACKUP ARCHIVELOG ALL NOT BACKED UP


RMAN> backup archivelog all not backed up;

Starting backup at 18-SEP-18
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=404 device type=DISK
skipping archived logs of thread 1 from sequence 5 to 32; already backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=33 RECID=29 STAMP=987190933
channel ORA_DISK_1: starting piece 1 at 18-SEP-18
channel ORA_DISK_1: finished piece 1 at 18-SEP-18
piece handle=+FRA/ACME/BACKUPSET/2018_09_18/annnf0_tag20180918t194214_0.303.987190935 tag=TAG20180918T194214 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-SEP-18

Starting Control File and SPFILE Autobackup at 18-SEP-18
piece handle=+FRA/ACME/AUTOBACKUP/2018_09_18/s_987190935.304.987190937 comment=NONE
Finished Control File and SPFILE Autobackup at 18-SEP-18

RMAN>

Validate database | Restore Validate

RMAN> backup validate database;

Starting backup at 18-SEP-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/ACME/DATAFILE/sysaux.257.848597951
input datafile file number=00001 name=+DATA/ACME/DATAFILE/system.258.848598015
input datafile file number=00002 name=+DATA/ACME/DATAFILE/example.266.848598231
input datafile file number=00004 name=+DATA/ACME/DATAFILE/undotbs1.260.848598093
input datafile file number=00006 name=+DATA/ACME/DATAFILE/users.259.848598091
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              17641        101128          2022436 
  File Name: +DATA/ACME/DATAFILE/system.258.848598015
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              66131         
  Index      0              13902         
  Other      0              3446           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              34100        45760           1889464 
  File Name: +DATA/ACME/DATAFILE/example.266.848598231
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              6779           
  Index      0              1219           
  Other      0              3662           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              26868        102408          2022495 
  File Name: +DATA/ACME/DATAFILE/sysaux.257.848597951
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              19341         
  Index      0              12752         
  Other      0              43439         

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              129          9600            2022495 
  File Name: +DATA/ACME/DATAFILE/undotbs1.260.848598093
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0             
  Index      0              0             
  Other      0              9471           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              17           643             1767683 
  File Name: +DATA/ACME/DATAFILE/users.259.848598091
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              33             
  Index      0              5             
  Other      0              585           

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2             
Control File OK     0              612           
Finished backup at 18-SEP-18

RMAN>


RMAN> restore validate database;

Starting restore at 18-SEP-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=592 device type=DISK

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece +FRA/ACME/BACKUPSET/2018_09_18/nnndf0_tag20180918t183159_0.296.987186719
channel ORA_DISK_1: piece handle=+FRA/ACME/BACKUPSET/2018_09_18/nnndf0_tag20180918t183159_0.296.987186719 tag=TAG20180918T183159
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:01:05
Finished restore at 18-SEP-18

RMAN>



jueves, 13 de septiembre de 2018

dbms_tdb Tips FORMATO ENDIAN

conn sys@ora11g as sysdba

Connected to Oracle 11g Enterprise Edition Release 11.1.0.6.0
conn / as sysdba

select
   *
from
   v$db_transportable_platform;

PLATFORM_ID PLATFORM_NAME                       ENDIAN_FORMAT
----------- ----------------------------------- --------------
          7 Microsoft Windows IA (32-bit)       Little
         10 Linux IA (32-bit)                   Little
          5 HP Tru64 UNIX                       Little
         11 Linux IA (64-bit)                   Little
         15 HP Open VMS                         Little
          8 Microsoft Windows IA (64-bit)       Little
         13 Linux x86 64-bit                    Little
         12 Microsoft Windows x86 64-bit        Little
         17 Solaris Operating System (x86)      Little
         19 HP IA Open VMS                      Little
         20 Solaris Operating System (x86-64)   Little
         21 Apple Mac OS (x86-64)               Little

Now execute the check_db function to get the results:

--Execute the check_db function
set serveroutput on
declare
    v_db_is_ok boolean;
    v_db_is_ok_1 boolean := TRUE;
    begin
   v_db_is_ok := dbms_tdb.check_db(
                  target_platform_name => 'Solaris Operating System (x86-64)',
                  skip_option => dbms_tdb.skip_none);

   if  v_db_is_ok = v_db_is_ok_1 then
     dbms_output.put_line('Database Ready to be transported!' );
   else
     dbms_output.put_line('Database not Ready to be transported!' );
   end if;
 end;
/

Database is not open in read-only mode. Open the database in read-only mode and retry.
Database not ready to be transported!


Next, execute the check_external function in order to list external objects that may exist.

--Execute the check_external function
set serveroutput on
declare
v_ext_exists boolean;
v_ext_exists_1 boolean;
begin
   v_ext_exists := dbms_tdb.check_external;
  if  v_ext_exists = v_ext_exists_1 then
    dbms_output.put_line('Database without external objects!' );           
  else
    dbms_output.put_line('Database have external objects!' );          
  end if;
  
end;
/

sábado, 8 de septiembre de 2018

UPTIME BASE DE DATOS ORACLE

select 
   'Hostname : ' || host_name
   ,'Instance Name : ' || instance_name
   ,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
   ,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
   trunc( 24*((sysdate-startup_time) - 
   trunc(sysdate-startup_time))) || ' hour(s) ' ||
   mod(trunc(1440*((sysdate-startup_time) - 
   trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
   mod(trunc(86400*((sysdate-startup_time) - 
   trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from 
   sys.v_$instance; 

jueves, 6 de septiembre de 2018

RESTORE UNTIL TIME - UNTIL SCN - SEQUENCE


Recovery Manager: Release 12.1.0.1.0 - Production on Thu Sep 6 21:24:48 2018

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ACME (DBID=2033062067, not open)

SET UNTIL SCN

RMAN> run
2> {
3> set until scn = 5352316;
4> restore database;
5> recover database;
6> }


RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ACME     2033062067       PARENT  1          2013-05-24:11:51:11
2       2       ACME     2033062067       PARENT  1720082    2014-05-26:17:42:12
3       3       ACME     2033062067       CURRENT 2285882    2018-09-06:21:30:30

UNTIL SEQUENCE

RMAN> run {
2> set until sequence=4;
3> restore database;
4> recover database;
5> }

RMAN>alter database open read only;

UNTIL TIME

export NLS_DATE_FORMAT="yyyy-mm-dd:hh24:mi:ss"

RMAN> shutdown immediate
RMAN> startup mount
RMAN> run
2> {
3> set until time="to_date('2020-12-04:19:07:32','yyyy-mm-dd:hh24:mi:ss')";
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 04-DEC-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hwjdb5xz_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_hwjdb5y7_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_hwjdb5xv_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_9qwmf6kp_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_hwjdb5yc_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_9qwmf54t_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_04/o1_mf_nnndf_TAG20201204T190331_hwomlo34_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_04/o1_mf_nnndf_TAG20201204T190331_hwomlo34_.bkp tag=TAG20201204T190331
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 04-DEC-20

Starting recover at 04-DEC-20
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 265 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_04/o1_mf_1_265_hwomnodw_.arc
archived log for thread 1 with sequence 266 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_04/o1_mf_1_266_hwomys3o_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_04/o1_mf_1_265_hwomnodw_.arc thread=1 sequence=265
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_04/o1_mf_1_266_hwomys3o_.arc thread=1 sequence=266
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-DEC-20

RMAN> alter database open resetlogs;

Statement processed

RMAN> exit



jueves, 23 de agosto de 2018

ERRORES ORACLE

$ oerr ora 12544

$ oerr rman 03009

3009, 1, "failure of %s command on %s channel at %s"
// *Cause:  This message should be accompanied by other error message(s)
//          indicating the cause of the error.
// *Action: Check the accompanying errors.
[oracle@localhost ~]$ 

martes, 17 de julio de 2018

Hacking Password

SQL> connect dbauser/dbapassword@mydatabase


Connected.

SQL> select username,password from dba_users where username = 'TESTUSER';


USERNAME PASSWORD
---------- ------------------

TESTUSER AEB6397C8E7598A7


SQL> alter user TESTUSER identified by temppwd1;
User altered.
SQL> connect testuser/temppwd1@mydatabase
Connected.
SQL> --do some work as testuser
SQL> connect dbauser/dbapassword@mydatabase
Connected.

SQL> alter user TESTUSER identified by values 'AEB6397C8E7598A7';

sábado, 14 de julio de 2018

GENERAR DDL TABLE & INDICES X SQL

Listing A:
set heading off;
 set echo off;
 Set pages 999;
 set long 90000;
 
spool ddl_list.sql
 select dbms_metadata.get_ddl('TABLE','EMPLOYEES','HR') from dual; 
 select dbms_metadata.get_ddl('INDEX','EMP_MANAGER_IX','HR') from dual;
 select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual; 
 spool off;

The output is shown below.




Listing C:
set pagesize 0

 set long 90000

 set feedback off

 set echo off
 spool scott_schema.sql
 connect scott/tiger;
 SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_TABLES u;
 SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
     FROM USER_INDEXES u;
 spool off;



SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','ADDR_FK1') from dual;


Here is the output:
DBMS_METADATA.GET_DDL('INDEX','ADDR_FK1')
--------------------------------------------------------------------------------

CREATE INDEX "MV_MAINT"."ADDR_FK1" ON

"MV_MAINT"."ADDRESS" ("CUST_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255

COMPUTE STATISTICS STORAGE(INITIAL 1048576

NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0




jueves, 12 de julio de 2018

RDA ORACLE

Oracle Remote Diagnostic Agent (RDA)
Version 10.2
 
Download From Metalink
DescriptionMetalink Doc IDContents
RDA 4.x FAQ and Index314422.1RDA Frequently Asked Questions
RDA for RAC Clusters359395.1Instructions and download for RAC
 
Step 1: Unzip Files
UnixWindows
tar -xvf rda.tar
or
tar -cxvf rda.gz
use any Windows utility
Step 2: Read Instructions
UnixWindows
README_Unix.txtREADME_Windows.txt

Step 3: Verify Installation
UnixWindows
rda.sh -cvrda.cmd -cv

 

Step 4: Run Setup
UnixWindows
rda.sh -S or rda.pl -Srda -S



The script continues on for many more pages of questions as part of the configuration

Step 5: Run RDA
UnixWindows
rda.sh -v or rda.pl -vrda -v

 
To view RDAOpen RDA_INDEX.htm from the RDA output directory
 

martes, 10 de julio de 2018

VERSION DE JAVA

lrwxrwxrwx   1 root     root          15 Jan 22  2015 /usr/java -> jdk/jdk1.7.0_60
oracle@USBLDDB1010:~$ java -version
java version "1.7.0_60"
Java(TM) SE Runtime Environment (build 1.7.0_60-b19)
Java HotSpot(TM) Server VM (build 24.60-b09, mixed mode)
oracle@USBLDDB1010:~$

lunes, 9 de julio de 2018

VER TRACE ENABLE

SQL_TRACE in v$session will tell you for active 10053 or 10045 trace are enabled or not :

SQL_TRACE     VARCHAR2(8)     Indicates whether SQL tracing is enabled (ENABLED) or disabled (DISABLED)

lunes, 30 de abril de 2018

Backing Up in NOARCHIVELOG Mode: Example

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP FORCE DBA;
SQL> SHUTDOWN IMMEDIATE;

RMAN> STARTUP MOUNT;
RMAN> BACKUP COPIES 2 INCREMENTAL LEVEL 0 MAXSETSIZE 10M DATABASE; RMAN> ALTER DATABASE OPEN;
 

miércoles, 4 de abril de 2018

.PROFILE ORACLE SINGLE

export AWT_TOOLKIT=XToolkit
export ORACLE_BASE=/u01/app/oracledb
export ORACLE_HOME=/u01/app/oracledb/product/11.2.0/db_1
export ORACLE_SID=USCS90TS
export TZ=America/Bogota
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:/bin:/usr/X11/bin:/usr/dt/bin:/usr/openwin/bin:/usr/ccs/bin:/usr/local/bin::/usr/local/bin:/home/oracle:.
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/oracm/lib
export CLASSPATH=$ORACLE_HOME/jdbc:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib$ORACLE_HOME/network/jlib
export NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'
export TNS_ADMIN=/u01/app/oracledb/product/11.2.0/db_1/network/admin
export TEMP=/tmp
export TMPDIR=/tmp
ulimit -n 65536
# Alias
alias sql='sqlplus / as sysdba'

martes, 20 de marzo de 2018

Relacion de Confianza Solaris 11

Relación de confianza SSH

Este articulo se refiere a la creación de un certificado SSH para poder conectarnos a maquina unix sin necesidad de clave

1. Conectarse al solaris con el usuario oracle
2. Crear una llave de tipo DSA con una longitud de 1024 bits para el protocolo SSHv2 con una frase de paso (passphrase) nula (ingresar ENTER cuando se solicite la frase de paso):
Ejecutar el siguiente comando y seguir los pasos :
ssh-keygen -t dsa -b 1024
Generating public/private dsa key pair.
Enter file in which to save the key (/export/home/usuario/.ssh/id_dsa): [ENTER]
Created directory '/export/home/usuario/.ssh'.
Enter passphrase (empty for no passphrase): [ENTER]
Enter same passphrase again: [ENTER]
Your identification has been saved in /export/home/usuario/.ssh/id_dsa.
Your public key has been saved in /export/home/usuario/.ssh/id_dsa.pub.
The key fingerprint is:
18:c8:a8:80:32:bf:12:d3:4b:1d:f6:ae:35:d7:e4:e0 usuario@servidor
Este comando genera una llave privada contenida en el archivo $HOME/.ssh/id_dsa y una llave pública contenida en el archivo $HOME/.ssh/id_dsa.pub.
3. Transferir de forma segura la llave pública creada en el servidor y colocarla en el directorio .ssh recién creado:
cd .ssh
scp id_dsa.pub usuario@cliente:.ssh/id_dsa.pub
id_dsa.pub           100% |*************************|   600       00:00
4. Acceder al cliente con el usuario por ssh y crear el archivo authorized_keys. Este archivo debe contener la llave id_dsa.pub (y el resto de las llaves, en caso de que se requiera configurar más de una):
ssh usuario@cliente
cd .ssh
cat id_dsa.pub >> authorized_keys
chmod 600 authorized_keys
rm id_dsa.pub
5. Probar el acceso al servidor desde el cliente:
ssh usuario@servidor
The authenticity of host 'solaris' can't be established.
RSA key fingerprint in md5 is: 77:83:db:48:12:ea:15:70:04:ec:8c:22:36:de:b4:15
Are you sure you want to continue connecting(yes/no)? yes

martes, 27 de febrero de 2018

CREAR ALERTAS ENTERPRISE MANAGER

Forzamos la generación de alertas cargando el número de cursores abiertos en una sesión en OEM. Para ello ejecutamos el siguiente fragmento de código que genera 250 cursores, y esperaremos a que aparezca la alerta crítica en OEM.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DECLARE
CURSOR my_cursor IS
  SELECT level, CURSOR(SELECT dummy FROM dual) FROM dual CONNECT BY level < 250;
  l_level USER_TABLES.TABLE_NAME%TYPE;
  l_outer_count PLS_INTEGER := 0;
  l_inner_count PLS_INTEGER := 0;
  l_dummy VARCHAR2(20);
  c_dummy SYS_REFCURSOR;
BEGIN
  OPEN my_cursor;
  LOOP
    FETCH my_cursor INTO l_level, c_dummy;
    EXIT WHEN my_cursor%NOTFOUND;
    l_outer_count := l_outer_count + 1;
    DBMS_OUTPUT.PUT_LINE('Outer Loop Count: ' || l_outer_count || ', Level: ' || l_level);
 
    l_inner_count := 0;
    LOOP
      FETCH c_dummy  INTO l_dummy;
      EXIT WHEN c_dummy%NOTFOUND;
      l_inner_count := l_inner_count + 1;
      DBMS_OUTPUT.PUT_LINE('Inner Loop Count: ' || l_inner_count || ', Level: ' || l_level);
    END LOOP;
 
  END LOOP;
  DBMS_LOCK.SLEEP(600);
  CLOSE my_cursor; -- Closes all cursor resources here (c_dummy and my_cursor)
END;
/
En un intervalo de 5 minutos aparecera en la página principal de OEM la alerta con severidad Crítica. Una vez hemos comprobado que funciona correctamente y nuestro script ha terminado, podemos reevaluar la alerta manualmente para hacer que desaparezca.
En la página principal de OEM ->
-> Click Alerta “Maxium Cursor Usage (value = ###)” ->
-> Click “Reevaluate Alert”
Borrar las métricas definidas por usuario es trivial.
Click “User-Defined Metrics” ->
-> Seleccionamos nuestra alerta “Cursor Usage” ->
-> Click “Delete” ->
-> Click “Yes”
5. Además de las métricas por defecto y las que podemos definir nosotros (Métricas Definidas por Usuario), también podemos definir umbrales para métricas basadas en BASELINES. Son las llamadas ADAPTIVE METRICS. Ya haremos pruebas con BASELINES en la sección de “Performance Management”, así que en este ejercicio definiremos los umbrales para una métrica de ejemplo “Number of Transactions (per second)”. El BASELINE que utilizaremos será el de la ventana activa en movimiento correspondiente a la ventana de retención de las estadísticas de AWR.
En la Página principal de OEM ->
-> Click “Baseline Metric Thresholds” ->
-> View = “Basic Metrics” ->
-> Click “Number of Transactions (per second)” ->
-> Critical = “Very High (0.99)” ->
-> Warning = “High (0.95)” ->
-> Ocurrences = 1 ->
-> Click “Preview” (veremos los umbrales sobre la gráfica) ->
-> Click “Apply Thresholds”
Ejecutamos el siguiente script en la BD de OEM para generar un número elevado de transacciones por segundo. Mi sistema acepta hasta 10.000 transacciones por segundo (TPS). Para una BD sin apenas transacciones por segundo, un número superior a 100 TPS debería ser suficiente para forzar la alerta.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Borramos la tabla de pruebas "TEST"
DROP TABLE test PURGE;
CREATE TABLE test (
    c NUMBER
) TABLESPACE USERS;
 
-- Configuración entorno
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
 
-- PL/SQL para generar n TPS
DECLARE
    tps NUMBER := &1;
BEGIN
    tps := (tps / 2);
    FOR i IN 1..180
    LOOP
    FOR i IN 1..tps
        LOOP
            INSERT INTO test VALUES(1);
            COMMIT;
            DELETE test WHERE rownum < 2;
            COMMIT;
        END LOOP;
    DBMS_LOCK.SLEEP(1);
END LOOP;
END;
/
Al cabo de unos minutos veremos una alerta con severidad crítica en la página principal de la BD de OEM (HOME). Este tipo de alertas es muy interesante para monitorizar situaciones anómalas en base al rendimiento “habitual” de la BD.
1
2
-- Borramos la tabla de pruebas
DROP TABLE TEST PURGE;

miércoles, 17 de enero de 2018

Oracle Limits

Logical Database Limits

ItemType of LimitLimit Value
IndexesMaximum per tableUnlimited
IndexesTotal size of indexed column75% of the database block size minus some overhead
ColumnsPer table1000 columns maximum
ColumnsPer index (or clustered index)32 columns maximum
ColumnsPer bitmapped index30 columns maximum
ConstraintsMaximum per columnUnlimited
SubqueriesMaximum levels of subqueries in a SQL statementUnlimited in the FROM clause of the top-level query
255 subqueries in the WHERE clause
PartitionsMaximum length of linear partitioning key4 KB - overhead
PartitionsMaximum number of columns in partition key16 columns
PartitionsMaximum number of partitions allowed per table or index1024K - 1
SubpartitionsMaximum number of subpartitions in a composite partitioned table1024K - 1
RowsMaximum number per tableUnlimited
System Change Numbers (SCNs)Maximum281,474,976,710,656, which is 281 trillion SCNs
Stored PackagesMaximum sizeApproximately 6,000,000 lines of code.
Trigger Cascade LimitMaximum valueOperating system-dependent, typically 32
Users and RolesMaximum2,147,483,638
TablesMaximum per clustered table32 tables
TablesMaximum per databaseUnlimited
A.2 Physical Database Limits
ItemType of LimitLimit Value
Database Block SizeMinimum2048 bytes; must be a multiple of operating system physical block size
Database Block SizeMaximumOperating system dependent; never more than 32 KB
Database BlocksMinimum in initial extent of a segment2 blocks
Database BlocksMaximum per datafilePlatform dependent; typically 222 - 1 blocks
ControlfilesNumber of control files1 minimum; 2 or more (on separate devices) strongly recommended
ControlfilesSize of a control fileMaximum of 201031680 logical blocks
Database filesMaximum per tablespaceOperating system dependent; usually 1022
Database filesMaximum per database65533
May be less on some operating systems
Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extentsMaximum per dictionary managed tablespace4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extentsMaximum per locally managed (uniform) tablespace2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file sizeMaximumOperating system dependent. Limited by maximum operating system file size. See the Bigfile Tablespaces and Smallfile (traditional) Tablespaces rows for more information about the maximum database file size in these types of tablespaces.
MAXEXTENTSDefault valueDerived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTSMaximumUnlimited
Redo Log FilesMaximum number of logfilesLimited by value of MAXLOGFILES parameter in the CREATE DATABASE statement
Control file can be resized to allow more entries; ultimately an operating system limit
Redo Log FilesMaximum number of logfiles per groupUnlimited
Redo Log File SizeMinimum size4 MB
Redo Log File SizeMaximum SizeOperating system limit; typically 2 GB
TablespacesMaximum number per database64 K
Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile TablespacesNumber of blocksA bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) TablespacesNumber of blocksA smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
External Tables fileMaximum sizeDependent on the operating system.
An external table can be composed of multiple files.