Oracle Facíl

martes, 12 de julio de 2016

Aplicar parche Online || 12.1.0.2

Para poder aplicar un parche de manera online, la documentación del parche indica:

Ambiente No-Rac

$ opatch apply online -connectString <SID>:<USERNAME>:<PASSWORD>:

Ejemplo:

Aplicar:

bash-4.2$ ./opatch apply -connectString p014banr:sys:oracle_4U /home1/oracle/p18759589/18759589/
Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2016, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/12.1.0
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.1.0/oraInst.loc
OPatch version : 12.1.0.1.3
OUI version : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/cfgtoollogs/opatch/opatch2016-07-12_10-35-45AM_1.log

Applying interim patch '18759589' to OH '/u01/app/oracle/product/12.1.0'
Verifying environment and performing prerequisite checks...
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12.1.0')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.rdbms, 12.1.0.2.0...

OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ld: 0711-415 WARNING: Symbol ldxdts is already exported.
ld: 0711-415 WARNING: Symbol ldxsto is already exported.
ld: 0711-415 WARNING: Symbol lnxadd is already exported.
ld: 0711-415 WARNING: Symbol lnxcopy is already exported.
ld: 0711-415 WARNING: Symbol lnxmin is already exported.
ld: 0711-415 WARNING: Symbol lnxmul is already exported.
ld: 0711-415 WARNING: Symbol lnxnur is already exported.
ld: 0711-415 WARNING: Symbol lnxren is already exported.
ld: 0711-415 WARNING: Symbol lnxsca is already exported.
ld: 0711-415 WARNING: Symbol lnxshift is already exported.
ld: 0711-415 WARNING: Symbol lnxsni is already exported.
ld: 0711-415 WARNING: Symbol sldxgd is already exported.
ld: 0711-224 WARNING: Duplicate symbol: fc_softc
ld: 0711-224 WARNING: Duplicate symbol: __fe_def_env
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//liboraolap12.a[sxstime.o], imported symbol timezone
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libgeneric12.a[sdbgrfu.o], imported symbol timezone
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libordsdo12.a[mdopp.o], imported symbol ksptblblksizexp2_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libordsdo12.a[mdopp.o], imported symbol ksptblblksizemod_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libordsdo12.a[mdutl.o], imported symbol ksudbrmseccnt_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libordsdo12.a[mdrcnc.o], imported symbol mdrcnccmt_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-783 WARNING: TOC overflow. TOC size: 254808 Maximum size: 65536
Extra instructions are being generated for each reference to a TOC
symbol if the symbol is in the TOC overflow area.
ld: 0711-786 WARNING: TLS overflow. TLS size: 232176 Maximum size: 65536
Extra instructions are being generated for some direct references
to TLS symbols.

Verifying the update...
Patch 18759589 successfully applied
OPatch Session completed with warnings.
Log file location: /u01/app/oracle/product/12.1.0/cfgtoollogs/opatch/opatch2016-07-12_10-35-45AM_1.log

OPatch completed with warnings.
bash-4.2$

Validar:

bash-4.2$ ./opatch lsinventory

Oracle Interim Patch Installer version 12.1.0.1.3

Oracle Home : /u01/app/oracle/product/12.1.0

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.1.0/oraInst.loc

OPatch version : 12.1.0.1.3

OUI version : 12.1.0.2.0

Log file location : /u01/app/oracle/product/12.1.0/cfgtoollogs/opatch/opatch2016-07-12_12-05-31PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.1.0/cfgtoollogs/opatch/lsinv/lsinventory2016-07-12_12-05-31PM.txt

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

Installed Top-level Products (1):

Oracle Database 12c 12.1.0.2.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch 18759589 : applied on Tue Jul 12 10:37:04 ECT 2016

Unique Patch ID: 20374114

Created on 5 Jul 2016, 00:45:58 hrs PST8PDT

Bugs fixed:

18759589

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

OPatch succeeded.

bash-4.2$

RollBack Online:

bash-4.2$ ./opatch rollback -id 18759589 -connectString p014banr:sys:oracle_4U
Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2016, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/12.1.0
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.1.0/oraInst.loc
OPatch version : 12.1.0.1.3
OUI version : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/cfgtoollogs/opatch/18759589_Jul_12_2016_10_32_25/rollback2016-07-12_10-32-24AM_1.log

RollbackSession rolling back interim patch '18759589' from OH '/u01/app/oracle/product/12.1.0'

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12.1.0')

Is the local system ready for patching? [y|n]
y
User Responded with: Y

Patching component oracle.rdbms, 12.1.0.2.0...

OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ld: 0711-415 WARNING: Symbol ldxdts is already exported.
ld: 0711-415 WARNING: Symbol ldxsto is already exported.
ld: 0711-415 WARNING: Symbol lnxadd is already exported.
ld: 0711-415 WARNING: Symbol lnxcopy is already exported.
ld: 0711-415 WARNING: Symbol lnxmin is already exported.
ld: 0711-415 WARNING: Symbol lnxmul is already exported.
ld: 0711-415 WARNING: Symbol lnxnur is already exported.
ld: 0711-415 WARNING: Symbol lnxren is already exported.
ld: 0711-415 WARNING: Symbol lnxsca is already exported.
ld: 0711-415 WARNING: Symbol lnxshift is already exported.
ld: 0711-415 WARNING: Symbol lnxsni is already exported.
ld: 0711-415 WARNING: Symbol sldxgd is already exported.
ld: 0711-224 WARNING: Duplicate symbol: fc_softc
ld: 0711-224 WARNING: Duplicate symbol: __fe_def_env
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//liboraolap12.a[sxstime.o], imported symbol timezone
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libgeneric12.a[sdbgrfu.o], imported symbol timezone
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libordsdo12.a[mdopp.o], imported symbol ksptblblksizexp2_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libordsdo12.a[mdopp.o], imported symbol ksptblblksizemod_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libordsdo12.a[mdutl.o], imported symbol ksudbrmseccnt_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libordsdo12.a[mdrcnc.o], imported symbol mdrcnccmt_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-783 WARNING: TOC overflow. TOC size: 254808 Maximum size: 65536
Extra instructions are being generated for each reference to a TOC
symbol if the symbol is in the TOC overflow area.
ld: 0711-786 WARNING: TLS overflow. TLS size: 232176 Maximum size: 65536
Extra instructions are being generated for some direct references
to TLS symbols.

RollbackSession removing interim patch '18759589' from inventory
--------------------------------------------------------------------------------
The following warnings have occurred during OPatch execution:
1) OUI-67215:
OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ld: 0711-415 WARNING: Symbol ldxdts is already exported.
ld: 0711-415 WARNING: Symbol ldxsto is already exported.
ld: 0711-415 WARNING: Symbol lnxadd is already exported.
ld: 0711-415 WARNING: Symbol lnxcopy is already exported.
ld: 0711-415 WARNING: Symbol lnxmin is already exported.
ld: 0711-415 WARNING: Symbol lnxmul is already exported.
ld: 0711-415 WARNING: Symbol lnxnur is already exported.
ld: 0711-415 WARNING: Symbol lnxren is already exported.
ld: 0711-415 WARNING: Symbol lnxsca is already exported.
ld: 0711-415 WARNING: Symbol lnxshift is already exported.
ld: 0711-415 WARNING: Symbol lnxsni is already exported.
ld: 0711-415 WARNING: Symbol sldxgd is already exported.
ld: 0711-224 WARNING: Duplicate symbol: fc_softc
ld: 0711-224 WARNING: Duplicate symbol: __fe_def_env
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//liboraolap12.a[sxstime.o], imported symbol timezone
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libgeneric12.a[sdbgrfu.o], imported symbol timezone
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libordsdo12.a[mdopp.o], imported symbol ksptblblksizexp2_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libordsdo12.a[mdopp.o], imported symbol ksptblblksizemod_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libordsdo12.a[mdutl.o], imported symbol ksudbrmseccnt_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u01/app/oracle/product/12.1.0/lib//libordsdo12.a[mdrcnc.o], imported symbol mdrcnccmt_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
ld: 0711-783 WARNING: TOC overflow. TOC size: 254808 Maximum size: 65536
Extra instructions are being generated for each reference to a TOC
symbol if the symbol is in the TOC overflow area.
ld: 0711-786 WARNING: TLS overflow. TLS size: 232176 Maximum size: 65536
Extra instructions are being generated for some direct references
to TLS symbols.

--------------------------------------------------------------------------------
OPatch Session completed with warnings.
Log file location: /u01/app/oracle/product/12.1.0/cfgtoollogs/opatch/18759589_Jul_12_2016_10_32_25/rollback2016-07-12_10-32-24AM_1.log

OPatch completed with warnings.
bash-4.2$

Validar

bash-4.2$ ./opatch lsinventory

Oracle Interim Patch Installer version 12.1.0.1.3

Oracle Home : /u01/app/oracle/product/12.1.0

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.1.0/oraInst.loc

OPatch version : 12.1.0.1.3

OUI version : 12.1.0.2.0

Log file location : /u01/app/oracle/product/12.1.0/cfgtoollogs/opatch/opatch2016-07-12_10-35-05AM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.1.0/cfgtoollogs/opatch/lsinv/lsinventory2016-07-12_10-35-05AM.txt

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

Installed Top-level Products (1):

Oracle Database 12c 12.1.0.2.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

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

OPatch succeeded.

martes, 21 de junio de 2016

Estimar tiempo de restauración RMAN 12c

El siguiente query permite validar el estado en el que se encuentra una restauración, si la ejecutamos con frecuencia pondemos ver el cambio en cada datafile restore debe llegar al 100

Query:

SQL> col OPNAME for a30
select OPNAME,SOFAR/TOTALWORK*100 PCT, trunc(TIME_REMAINING/60) MIN_RESTANTES,
trunc(ELAPSED_SECONDS/60) MIN_ATEAGORA
from v$session_longops where TOTALWORK>0 and OPNAME like '%RMAN%';

Salida:

SQL> /

OPNAME PCT MIN_RESTANTES MIN_ATEAGORA
------------------------------ ---------- ------------- ------------
RMAN: full datafile restore 100 0 4
RMAN: full datafile restore 100 0 2
RMAN: full datafile restore 100 0 0
RMAN: full datafile restore 100 0 14
RMAN: full datafile restore 100 0 2
RMAN: full datafile restore 100 0 2
RMAN: full datafile restore 100 0 0
RMAN: full datafile restore 41.5319266 1 1
RMAN: full datafile restore 7.18395934 15 1
RMAN: full datafile restore 1.61948326 73 1
RMAN: full datafile restore 32.6552851 1 0

OPNAME PCT MIN_RESTANTES MIN_ATEAGORA
------------------------------ ---------- ------------- ------------
RMAN: full datafile restore 100 0 3
RMAN: full datafile restore 100 0 0
RMAN: full datafile restore 56.0479477 0 1
RMAN: full datafile restore 9.61254773 2 0
RMAN: full datafile restore 100 0 2
RMAN: aggregate input .091268607 1058 0
RMAN: full datafile restore 28.9865601 2 0
RMAN: full datafile restore 100 0 3
RMAN: aggregate input 100 0 14
RMAN: full datafile restore 21.7100633 4 1

21 rows selected.

Interpretación:

La línea (RMAN: full datafile restore) es la suma del total y que está demostrando que fue ejecutado 41.53% del total de la restauración, y se llevó hasta ahora, 1 munuto y la estimación es de 1 minutoi más.

Las otras líneas son de parciales (RMAN: archivo de datos restauración completa), tenemos muchos completado (PCT = 100), y dos de restauración de piezas en la ejecución, que todavía se llevará a 15 minutos por ejemplo.

---Salida casi por terminar.

SQL>/

OPNAME PCT MIN_RESTANTES MIN_ATEAGORA
------------------------------ ---------- ------------- ------------
RMAN: full datafile restore 100 0 4
RMAN: full datafile restore 100 0 2
RMAN: full datafile restore 100 0 0
RMAN: full datafile restore 100 0 14
RMAN: full datafile restore 100 0 2
RMAN: full datafile restore 100 0 2
RMAN: full datafile restore 100 0 0
RMAN: full datafile restore 100 0 3
RMAN: full datafile restore 100 0 3
RMAN: full datafile restore 100 0 15
RMAN: full datafile restore 100 0 2

OPNAME PCT MIN_RESTANTES MIN_ATEAGORA
------------------------------ ---------- ------------- ------------
RMAN: full datafile restore 100 0 3
RMAN: full datafile restore 100 0 0
RMAN: full datafile restore 100 0 2
RMAN: full datafile restore 100 0 2
RMAN: full datafile restore 100 0 0
RMAN: full datafile restore 100 0 2
RMAN: aggregate input 99.9999873 0 16
RMAN: full datafile restore 100 0 2
RMAN: full datafile restore 100 0 3
RMAN: aggregate input 100 0 14
RMAN: full datafile restore 100 0 4

22 rows selected.

lunes, 20 de junio de 2016

Deshabilitar Log_File LISTENER

Si no es necesario en la función del servidor es mejor deshabilitar la función de log listener para evitar que la partición /u01 llegue al 100%.

En el log de listener se guardan todas las sesiones que ingresaron al servidor por el puerto que se tiene establecido por lo que no es tan recomendable quitar esta opción en ambientes productivos.

1)Validar ruta del log listener

bash-4.2$ lsnrctl show log_file;

LSNRCTL for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production on 20-JUN-2016 11:50:46

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOSTNAME>)(PORT=1521)))
LISTENER parameter "log_file" set to /u01/app/grid/diag/tnslsnr/<HOSTNAME>/listener/alert/log.xml
The command completed successfully

2) Deshabilitar el estado del log a OFF

bash-4.2$ lsnrctl set log_status off

LSNRCTL for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production on 20-JUN-2016 11:51:31

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOSTNAME>)(PORT=10521)))
LISTENER parameter "log_status" set to OFF
The command completed successfully

3) Validar

bash-4.2$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production on 20-JUN-2016 11:51:35

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOSTNAME>)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
Start Date 20-JUN-2016 11:41:13
Uptime 0 days 0 hr. 10 min. 21 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /u01/app/grid/product/12.1.0/network/admin/listener.ora
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<HOSTNAME>)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<HOSTNAME>)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service......
The command completed successfully

martes, 14 de junio de 2016

bash: /usr/bin/find: The parameter or environment lists are too long.

Siempre es molesto ver el mensaje de sistema operativo "The parameter or environment lists are too long.". Con el comando que se detalla a continuación se puede eliminar todos los archivos del directorio poco a poco el sistema operativo lo va resolviendo.

bash-4.2$ find /logdir/path/ -name *.aud -mtime +7 -exec rm {} \;
bash: /usr/bin/find: The parameter or environment lists are too long.

Solución
bash-4.2$ find /logdir/path/ -name "*.aud" -type f -exec rm {} \;

O

bash-4.2$ find /logdir/path/ -name *.aud -type f -print | xargs rm

viernes, 3 de junio de 2016

Multiplexar controlfile

Les dejo el paso a paso para realizar una copia de controlfile

Nota: Para realizar esta actividad es necesario el reinicio del motor de base de datos Oracle

1) Bajar BDD

2)Poner BDD en modo nomount

3)Con rman sacar una copia al controlfile existente - rman target /

RMAN> restore controlfile to '+FRA/' from '+DATA2/P01/CONTROLFILE/control01.ctl';

4) Con sqlplus / as sysdba registrar en el spfile el control file nuevo

SQL> alter system set control_files='+fra2/P013BANS1/CONTROLFILE/controlfile.ctl','+DATA2/P01/CONTROLFILE/control01.ctl' scope=spfile;

5) startup force

Total System Global Area 3.4360E+11 bytes
Fixed Size 6956784 bytes
Variable Size 2.2012E+10 bytes
Database Buffers 3.2105E+11 bytes
Redo Buffers 529911808 bytes
Database mounted.
Database opened.

5) Validar

p01> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+FRA/P01/CONTROLFILE/control01.ctl
+DATA/P01/CONTROLFILE/control01.ctl