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)

Flash Recovery Area (FRA)

Sometimes the Flash Recovery Area (FRA) is full and the Oracle DBA wants to know what is it use, size and the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).

To Enable /Disable the FRA, it’s here.

-- Use (MB) of FRA
set lines 100
col name format a60

select 
   name,
  floor(space_limit / 1024 / 1024) "Size MB",
  ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest;

-- FRA Occupants
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

-- Location and size of the FRA
show parameter db_recovery_file_dest

-- Size, used, Reclaimable 
SELECT 
  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, 
  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB, 
  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
FROM 
  V$RECOVERY_FILE_DEST A,
  V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
  SPACE_LIMIT, 
  SPACE_USED , 
  SPACE_RECLAIMABLE ;

-- After that you can resize the FRA with:
-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG;

-- Or change the FRA to a new location (new archives will be created to this new location):
-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';

FLASH RECOVERY AREA architecture

It is important to monitor regularly space usage in the fast recovery area. The Oracle DBA must make sure that the FRA is large enough to contain backups and other recovery-related files.

If the PERCENT_FULL value (V$RECOVERY_FILE_DEST) is frequently close to 100% after several recent backups; consider allocating more space for your flash recovery area or decrease the retention time of your backups.

Oracle Database provides two views to monitor fast recovery area space usage, V$RECOVERY_FILE_DEST and V$RECOVERY_AREA_USAGE.

A best practice is to be generous on FRA size!

Compatibility: Oracle 18c, 12c, 11g, 10g
Author: Vincent FENOLL – Oracle DBA Montreal

ASMSNMP password change user/ reset Oracle 12c 18c

How can I change the password of the Oracle user ASMSNMP?

Objective is authentication for accessing Oracle ASM Instances. For  example to register an ASM target in your Cloud Control 13c.

# Set variables for Unix
$ . oraenv
+ASM (or +ASM1 in RAC flexasm)

First Method  to change asmsnmp with sql*Plus

$ sqlplus / as sysasm
Alter user asmsnmp identified by my-password;

Second method useful for RAC

In a HA environment:

$ asmcmd
ASMCMD> orapwusr –modify –password asmsnmp
Enter password: my-password

For info, orapwusr can add, drop, or modify an Oracle ASM password file user (sysasm|sysdba|sysoper). pwget/pwset are other commands to set or locate the password file when it is stored on ASM.

 

Note: grant sysdba to asmsnmp;   — Before promoting the ASM target in the Cloud Control

Flashback table / Restore table from RMAN

How to restore a table in a Oracle pluggable database (PDB)?

 

— Flashback table (if the timeframe allows it and flashback enabled)
sqlplus / as sysdba
alter session set container=my-pdb;
create table my-schema.my-table-08312018 as select * from my-schema.my-table as of timestamp to_timestamp(’31-AUG-2018 10:45:52′,’DD-Mon-YYYY hh24:MI:SS’);

 

— Restore table from RMAN (from Oracle 12c)
— create directory /u01/vincent. You will need space for the tablespace of the table and also for system, sysaux
recover table my-schema.my-table OF PLUGGABLE DATABASE my-pdb until time “to_date(’08/31/2018 10:45:00′,’mm/dd/yyyy hh24:mi:ss’)”
auxiliary destination ‘/u01/vincent’ REMAP TABLE my-schema.my-table:my-table_08312018;

 

Have a nice day!

Author: Vincent Fenoll – Oracle DBA Montreal

from Montreal (Québec)