Essential Maintance Of MGMTDB

Today I want to talk about the most basic maintenance we can do of the MGMTDB (MGMTDB is new database instance which is used for storing Cluster Health Monitor (CHM) data). Let’s see the minimum commands that we must keep in mind to work with this database;

To check database;

srvctl status mgmtdb

To start database;

srvctl start mgmtdb [-startoption start_options] [-node node_name]

To set up database;

srvctl config mgmtdb

Like an Oracle database, it has its own listener, and you can perform the same operations, for example (from the owner of the GRID);

lsnrctl status MGMTLSNR

To connect to the database with sqlplus we must first point to the ORACLE_SID (from the owner of the GRID);

export ORACLE_SID=-MGMTDB
sqlplus “/ as sysdba”

HTH – Antonio NAVARRO

 

Advertisements

How To Change dbsnmp’s Password In MGMTDB

For set up the OEM 12C I ned to change the dbsnmp into the MGMTDB database. Follow the next steps to change it;For set up the OEM 12C I ned to change the dbsnmp into the MGMTDB database. Follow the next steps to change it;
connect to the machine with grid’s owner

export ORACLE_SID=-MGMTDB 
sqlplus / as sysdba
SQL> ALTER USER DBSNMP IDENTIFIED BY XXXXXX ACCOUNT UNLOCK;

HTH – Antonio NAVARRO

ORA-19511, ORA-19870, ORA-19501 And ORA-27190 Errors

Today when I arrived to the work I saw a email from Backup department about a restore failing the last Saturday. The error was the next;

 
channel aux12: ORA-27192: skgfcls: sbtclose2 returned error - failed to close file
ORA-19511: non RMAN, but media manager or vendor specific failure, error text:
   We could not read the checksum. (0:3:2)
ORA-19870: error while restoring backup piece WEBP_k3sq8u31_1_1
ORA-19501: read error on file "CLOUD_k3sf8t32_1_1", block number 1 (block size=512)
ORA-27190: skgfrd: sbtread2 returned error
ORA-19511: non RMAN, but media manager or vendor specific failure, error text:
   asdf_

After a bit of research I discovered the problem was in the network. This backup connect to serveral servers to work (Legato Server, a recover catalog and other database becouse of be a duplicate). Maybe a cut down of miliseconds order was enough to crash the restore. To verify the network stability and repeat the Rman Script solve the problem.

HTH – Antonio NAVARRO

 

How To Install JAVA VM (12c) Manually

In this post I show the manually way of install Java VM into a database 12c. I install XML component too because of is very requeried by applications who working with XML.

 
set lines 1000 pages 1000
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;

@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
@?/rdbms/admin/catjava.sql
@?/rdbms/admin/catexf.sql

shutdown immediate

startup
REM Recompile all 
@?/rdbms/admin//utlrp.sql

column comp_name format a50
SELECT COMP_ID, COMP_NAME, STATUS FROM DBA_REGISTRY;

HTH – Antonio NAVARRO

 

How To See Privileges In Oracle Directories

In this post I show a simple query for get privileges for users on directories. You can query those privileges by using the next query;

 
COLUMN GRANTOR       FORMAT A10
COLUMN GRANTEE       FORMAT A20
COLUMN TABLE_SCHEMA  FORMAT A10
COLUMN TABLE_NAME    FORMAT A20
COLUMN PRIVILEGE     FORMAT A10

SELECT *
FROM ALL_TAB_PRIVS
WHERE 
  TABLE_NAME IN (SELECT DIRECTORY_NAME FROM DBA_DIRECTORIES);

GRANTOR    GRANTEE              TABLE_SCHE TABLE_NAME           PRIVILEGE  GRA HIE COM TYPE
__________ ____________________ __________ ____________________ __________ ___ ___ ___ _______________
SYS        EXP_FULL_DATABASE    SYS        DATA_PUMP_DIR        READ       NO  NO  NO  DIRECTORY
SYS        EXP_FULL_DATABASE    SYS        DATA_PUMP_DIR        WRITE      NO  NO  NO  DIRECTORY
SYS        IMP_FULL_DATABASE    SYS        DATA_PUMP_DIR        READ       NO  NO  NO  DIRECTORY
SYS        IMP_FULL_DATABASE    SYS        DATA_PUMP_DIR        WRITE      NO  NO  NO  DIRECTORY

 

HTH – Antonio NAVARRO

 

How To Deinstall SQLTRACE

In this post I like show the correct way to remove the sqltrace schemas and procedures. First of all move to <location_for_sqltrace_packages>/install and connect / as sysdba to the database and execute @sqdrop.sql;

output for the command;

 

 
shone-1/cloud/prod/server102/ADM/SQLT/install> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jan 29 12:17:22 2018

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> @sqdrop.sql

PL/SQL procedure successfully completed.

... uninstalling SQLT, please wait

TADOBJ completed.

PL/SQL procedure successfully completed.

SQDOLD completed. Ignore errors from this script

PL/SQL procedure successfully completed.

SQDOBJ completed. Ignore errors from this script

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    my_count INTEGER;
  3
  4  BEGIN
  5    SELECT COUNT(*)
  6      INTO my_count
  7      FROM sys.dba_users
  8     WHERE username = 'TRCADMIN';
  9
 10    IF my_count = 0 THEN
 11      BEGIN
 12        EXECUTE IMMEDIATE 'DROP PROCEDURE sys.sqlt$_trca$_dir_set';
 13      EXCEPTION
 14        WHEN OTHERS THEN
 15          DBMS_OUTPUT.PUT_LINE('Cannot drop procedure sys.sqlt$_trca$_dir_set. '||SQLERRM);
 16      END;
 17
 18      FOR i IN (SELECT directory_name
 19                  FROM sys.dba_directories
 20                 WHERE directory_name IN ('SQLT$UDUMP', 'SQLT$BDUMP', 'SQLT$STAGE', 'TRCA$INPUT1', 'TRCA$INPUT2', 'TRCA$STAGE'))
 21      LOOP
 22        BEGIN
 23          EXECUTE IMMEDIATE 'DROP DIRECTORY '||i.directory_name;
 24          DBMS_OUTPUT.PUT_LINE('Dropped directory '||i.directory_name||'.');
 25        EXCEPTION
 26          WHEN OTHERS THEN
 27            DBMS_OUTPUT.PUT_LINE('Cannot drop directory '||i.directory_name||'. '||SQLERRM);
 28        END;
 29      END LOOP;
 30    END IF;
 31  END;
 32  /
Dropped directory SQLT$STAGE.
Dropped directory TRCA$STAGE.
Dropped directory SQLT$UDUMP.
Dropped directory SQLT$BDUMP.
Dropped directory TRCA$INPUT1.
Dropped directory TRCA$INPUT2.

PL/SQL procedure successfully completed.

SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.
About to DROP users SQLTXPLAIN and SQLTXADMIN. Press RETURN to continue.

SQL>
SQL> DROP USER &&tool_administer_schema. CASCADE;
old   1: DROP USER &&tool_administer_schema. CASCADE
new   1: DROP USER SQLTXADMIN CASCADE

User dropped.

SQL> DROP USER &&tool_repository_schema. CASCADE;
old   1: DROP USER &&tool_repository_schema. CASCADE
new   1: DROP USER SQLTXPLAIN CASCADE

User dropped.

SQL> DROP ROLE &&role_name.;
old   1: DROP ROLE &&role_name.
new   1: DROP ROLE SQLT_USER_ROLE

Role dropped.

SQL>
SQL> SET ECHO OFF;

SQDUSR completed.

SQDROP completed.
SQL>
SQL>

 

HTH – Antonio NAVARRO

ORA-31693, ORA-29913, ORA-29400, KUP-11011, KUP-11014 When Executing Impdp

Today I get the next error when perform a full import.

 
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-31693: Table data object "CLOUD"."HCO_TELMEM_BRO" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11011: the following file is not valid for this load operation
KUP-11014: internal metadata in file /temporary/uploads/fullcloud33.dmp is not valid

It is a bug (20690515), by use multiples dump files, generated in 10g, maybe trying reduce their number by create them more bigger work. If you hava support you can download from ML/MOS. In other case, you can use like workaround, if your tables support it, set up ACCESS_METHOD=DIRECT_PATH.

HTH – Antonio NAVARRO