All posts by Vincent

Oracle DBA OCP 10g, 9i, 8 Electronic music producer

Found 1 new persistent data failures – Cloud Control Event/error

Enterprise Manager / Cloud Control 13c raised this event:


EM Event: Critical:my-db.oracle-scripts.net – Checker run found 1 new persistent data failures.

By default the database runs the Health Check periodically to find:

  • File corruptions,
  • Physical and logical block corruptions,
  • Undo or redo corruptions,
  • data dictionary corruptions

If any failures are detected then a message is logged to the alert log.and Enterprise Manager can raise it.

To identify this failures you can follow these steps:

1- Check the list of health checks executed:

SQL> select run_id,name,check_name,start_time,end_time,status from v$hm_run;

148741 HM_RUN_148741 DB Structure Integrity Check

2- Get the report of that health check and find the failure:

SET LONG 100000 lines 256 LONGCHUNKSIZE 1000 PAGESIZE 1000
SELECT DBMS_HM.GET_RUN_REPORT(‘HM_RUN_148741’) FROM DUAL;

With ‘HM_RUN_148741’ is a value from the column “Name” retrieved in the first query.

Basic Run Information
Run Name : HM_RUN_148741
Run Id : 148741
Check Name : DB Structure Integrity Check
Mode : REACTIVE
Status : COMPLETED
Start Time : 2019-02-07 06:54:41.409009 -05:00
End Time : 2019-02-07 06:54:41.551557 -05:00
Error Encountered : 0
Source Incident Id : 0
Number of Incidents Created : 0

Input Paramters for the Run
Run Findings And Recommendations
Finding
Finding Name : Control File needs recovery
Finding ID : 148742
Type : FAILURE
Status : OPEN
Priority : CRITICAL
Message : Control file needs media recovery
Message : Database cannot be opened

Have a nice day!

Vincent Fenoll – Oracle DBA Montreal
Compatible: Oracle 18c, 12c, 11.1

Generate user DDL with dbms_metadata.get_ddl user

The oracle DBA can use these 2 scripts to generate DDL statements for a user with their roles, system and object privileges.

For Oracle 18c / 12c / 11g / 10g:

clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt  ' Output filename : '

spool &&outfile..gen

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;

spool off

More information for the package dbms_metadata, function get_ddl user in the official Oracle 18c/12c documentation :
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_METADATA.html

For Oracle <10 (runs well too with 10g, 11g, 12.2 and 18c):

clear screen

accept uname prompt 'Display the DDL for this specific user: '
accept outfile prompt  ' Output filename : '

col username noprint
col lne newline

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

spool &&outfile..gen

prompt  -- generate user ddl
SELECT username, 'CREATE USER '||username||' '||
       DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY',
              'IDENTIFIED BY VALUES '''||password||''' ') lne,
       'DEFAULT TABLESPACE '||default_tablespace lne,
       'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne
  FROM DBA_USERS
 WHERE USERNAME LIKE UPPER('%&&uname%')
    OR UPPER('&&uname') IS NULL
 ORDER BY USERNAME;

SELECT username, 'ALTER USER '||username||' QUOTA '||
       DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
       ||' ON '||tablespace_name||';' lne
  FROM DBA_TS_QUOTAS
 WHERE USERNAME LIKE UPPER('%&&uname%')
    OR UPPER('&&uname') IS NULL
 ORDER BY USERNAME;

col grantee noprint

select grantee, granted_role granted_priv,
       'GRANT '||granted_role||' to '||grantee||
       DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
  from dba_role_privs
 where grantee like upper('%&&uname%')
         UNION
select grantee, privilege granted_priv,
       'GRANT '||privilege||' to '||grantee||
       DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
  from dba_sys_privs
 where grantee like upper('%&&uname%')
 order by 1, 2;

spool off

Another use of this procedure is to copy a user account from one Oracle instance to another. With the same password, grants and roles without using the expdp/impdp tools.

 

Another method to retreive the Data Description Language for an Oracle user with all roles and Privileges:

With datapump (impdp) you can use the parameter sqlfile=My_file.sql you can easily get DDL from dumpfile:
http://www.oracle-scripts.net/standard-datapump-use/

 

Author: Vincent Fenoll
Compatibility: Oracle 18c, 12c, 11g

How to cleanup orphaned datapump jobs in Oracle

How can I cleanup old (orphaned) datapump jobs in DBA_DATAPUMP_JOBS ?

Cause: In many cases you have stop Oracle data pump jobs, shutdown database during export/import or use undocumented parameter KEEP_MASTER=Y. In these cases the master table remains in the database and it’s better to delete them.

 

Below is a step-by-step instruction on how to do this.

Step 1. Determine in SQL*Plus if Data Pump jobs exist in the dictionary

Identify these jobs and ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be ‘NOT RUNNING’ and not attached to a session:

SET lines 150
COL owner_name FORMAT a10
COL job_name FORMAT a20
COL operation FORMAT a10

SELECT owner_name, job_name, operation
FROM dba_datapump_jobs where state='NOT RUNNING' and attached_sessions=0;

 

Step 2: Drop the master tables

set head off
SELECT 'drop table ' || owner_name || '.' || job_name || ';'
FROM dba_datapump_jobs WHERE state='NOT RUNNING' and attached_sessions=0;

Execute the generated script.

 

Step 3: Identify orphan DataPump external tables

Check  and drop external tables created for datapump jobs with select  object_name, created from dba_objects where object_name like ‘ET$%’

 

Step 4: Purge recycle bin

If using recycling bin:
SELECT ‘purge table ‘ || owner_name || ‘.’ || ‘”‘ || job_name || ‘”;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and attached_sessions=0;

 

Step 8: Confirm that the job has been removed

Run sql statement from step 1.

 

Now you should be able to run your script with the same job name without any issues.

Hope this post will help!

 

Author: Vincent Fenoll – Oracle DBA

Compatibility:  Oracle Database – Standard/Enterprise Edition – Version 10g to 18c [Release 10.1 to 12.2/18]

Sql Server loop through databases

How can I run the same command on all SQL Server databases without cursors

The stored procedure sp_MSforeachdb allows us to iterate through each database in a SQL Server instance without a loop statement.
It’s usage is similar than sp_MSforeachtable; simply execute the stored procedure, passing it the command that you’d like to execute as a string.

loop all databases sql server

For this example, we will truncate the transaction log file on all databases (but exclude system DBs as master, msdb, model and tempdb).

Below is a step-by-step instruction on how to do this:

  1. Call stored procedure sp_MSForeachdb
  2. Display current db for information
  3. Set variable to transaction log name
  4. Change the database recovery model to SIMPLE for the database
  5. Shrink the truncated log file to 1 MB
EXEC master..sp_MSForeachdb '
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
-- display current db
SELECT ''?'';

-- set transaction log name
DECLARE @FileName varchar(300)
SELECT @FileName = name from sys.database_files where type=1

-- Change the database recovery model to SIMPLE.
ALTER DATABASE [?] SET RECOVERY SIMPLE;

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (@FileName, 1);

END
'

Thank you!

Author: Vincent Fenoll – SQL Server DBA Montreal

Dataguard management without dgbroker

Standby stop start apply log

I want to start or stop redo apply without dgbroker. I also want to know other queries to check Dataguard and standby status.
These commands are compatible with Oracle 18c, 12c, 11g, 10g, 9i.

Starting Redo Apply on standby database

 

To start Redo Apply in the foreground, issue the following SQL statement.

Without Real Time Apply (RTA) on standby database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

With Real Time Apply (RTA)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

 

Stopping Redo Apply on standby database

To stop Redo Apply in the foreground, issue the following SQL statement.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

Monitoring Redo Apply on Physical Standby Databases

 

Last sequence received and applied

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;

 

Standby database process status

select distinct process, status, thread#, sequence#, block#, blocks from v$managed_standby ;

 

If using real time apply

select TYPE, ITEM, to_char(TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') from v$recovery_progress where ITEM='Last Applied Redo';

or,

select recovery_mode from v$archive_dest_status where dest_id=1;

 

Author: Vincent Fenoll (Oracle DBA in Montreal)