Windows (powershell) counterparts of Linux commands

You may find Windows mostly powershell equivalent of frequently used Linux commands here.

I will update this post, with newer ones by the time...

tail -100f <file mask>

Via powershell, without installing new software, you may issue below command for refreshing the alert log and seeing last 100 rows displayed in the very beginning.

Get-Content alertmydb.log -Wait -tail 100

history

For history of commands executed simply issue.

Get-History

grep

select-string <string to look for> <file name>



find

Find the files having a mask alert*log up to 5 directories depth.

gci -Path alert*log -Recurse -Depth 5

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.management/get-childitem?view=powershell-6



 

Resizing online redo log

It seems an easy topic, I prefer being careful with the easy ones... so it is handy to have all you need, in one place and which will lead implementing things faster and safer.

So, here, You will find how to resize redolog files statements; exact building blocks, ie adding redo log files, grouping/mirroring them more than one disk location, specifying the file names with ASM auto generated way, making status inactive for dropping, dropping old ones, switching between log files, all in one place!

Resizing means, you need to create or add new redo log files with a desired size and drop the old ones. For example in this example I had 3 files having 50MBs, I had created 500MB redo log files first. And dropped the old 50MB ones, this way resized the redo files.

Recall that you should check for the space in disk group in ASM first, to create bigger or additional redo log group. For example I saw, 8519 and 8517 MB free for my diskgroups REDOLOGS and REDOLOGS1, so no problem to create additioal 3 redolog files having total of 1500MB files.

SQL> select name, state, free_mb from v$asm_diskgroup;

NAME                           STATE          FREE_MB
------------------------------ ----------- ----------
REDOLOGS                       CONNECTED         8519
REDOLOGS1                      CONNECTED         8517
DATA                           CONNECTED        87717
ARCHIVELOGS                    CONNECTED        40274

SQL>

Also, note the current redo log file sizes first below:

-- redolog files info
SELECT b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;
THREAD# GROUP# MEMBER BYTES
---------- ---------- ---------------------------------------------------------------------------------------------------- ----------
1 1 +REDOLOGS/IDRA/ONLINELOG/group_1.278.997880185 52428800
1 1 +REDOLOGS1/IDRA/ONLINELOG/group_1.289.998810187 52428800
1 2 +REDOLOGS/IDRA/ONLINELOG/group_2.297.998810193 52428800
1 2 +REDOLOGS1/IDRA/ONLINELOG/group_2.298.998810201 52428800
1 3 +REDOLOGS/IDRA/ONLINELOG/group_3.281.998810211 52428800
1 3 +REDOLOGS1/IDRA/ONLINELOG/group_3.280.998810219 52428800

Adding redolog file statement is fairly self descriptive. You may mirror the files to different disk groups as giving them in ('(ONLINELOG)','(ONLINELOG)') Here the ONLINELOG is the template used for creating the auto generated names. I am using new GROUP numbers 4,5,6, which will not overlap existing group numbers 1,2,3. If you use existing ones you will get ORA-01184 error as shown below. In RAC, in which multiple instance (THREAD) in place you will create additional redolog groups for each instance or thread.

--adding redolog file with mirroring to +REDOLOGS asm diskgroup, giving sizing as 500 MB.

ALTER DATABASE ADD LOGFILE THREAD 1 
GROUP 4 ('+REDOLOGS(ONLINELOG)','+REDOLOGS1(ONLINELOG)') size 500M REUSE, 
GROUP 5 ('+REDOLOGS(ONLINELOG)','+REDOLOGS1(ONLINELOG)') size 500M REUSE, 
GROUP 6 ('+REDOLOGS(ONLINELOG)', '+REDOLOGS1(ONLINELOG)') size 500M REUSE;

SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ( '+REDOLOGS1(ONLINELOG)') SIZE 500M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 3 ( '+REDOLOGS1(ONLINELOG)') SIZE 500M REUSE
*
ERROR at line 1:
ORA-01184: logfile group 4 already exists

So, we finished with adding new redo log groups. New added ones are in status UNUSED

-- for status of log files
SQL> SELECT group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
4 UNUSED
5 UNUSED
6 UNUSED

It is straightforward to drop a log file, if it is in INACTIVE mode. In case it is in use having ACTIVE or CURRENT statuses, and you want to drop it, you will probably get following error:

-- for deleting/dropping old log files
ALTER DATABASE DROP LOGFILE GROUP 1;

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance idra (thread 1)
ORA-00312: online log 1 thread 1: '+REDOLOGS/IDRA/ONLINELOG/group_1.257.907425247'
ORA-00312: online log 1 thread 1: '+REDOLOGS1/IDRA/ONLINELOG/group_1.258.907425247'

In case, the log status is CURRENT, like above, for GROUP 3 and you want to drop it. Then you should switch to next group by below statement:

-- for switching among groups
alter system switch logfile;

Even you made switches to further groups, it does not mean that it will change the status to INACTIVE, for making ACTIVE to INACTIVE, a CHECKPOINT should be  in place, which does not switch log files but writes changes to datafiles, in which you will not need REDO for recovery which are not CURRENT.

-- for making active to inactive
alter system checkpoint;

 

Oracle Features and Versions

Up-to-date feature and version list for Oracle



Source:

Wikipedia: Oracle Database

 

MS SQL Server Backup File Size Requirement

In SQLServer 2012, using below backup command, I started taking a backup and  it first created a 130G BAK file. I was expecting around 60G file and my disk space was around 150G. Because of that I started monitoring, the only information I have was it will be a compressed file, and the current percentage of completion.  Fortunately, it finished with 60G file when it was 100 percent.



:Setvar DB MYDB
:Setvar date 20190102

BACKUP DATABASE [$(DB)] TO DISK = N'C:\SQLBCK\$(DB)_prod_$(date).bak' WITH NOFORMAT, INIT, NAME = N'$(DB) prod-Full Database Backup $(date)', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

In the support note it says creating a file with an estimated file size is for performance benefit. "This size is calculated directly from the number of allocated extents in the data files that are specified for the backup. " Not surprisingly, instead of incrementally allocating disk space, it first creates a target estimate size file, and fills it, and releases unused space at the end.

https://support.microsoft.com/en-us/help/2001026/inf-space-requirements-for-backup-devices-in-sql-server

So, for less space environments, is there a way of opting out performance?

The answer is yes. Not to allocate a big target space, you may enable trace flag 3042 for incremental sizing. For details please look into below article. https://www.mssqltips.com/sqlservertip/2531/sql-server-compressed-backups-disk-space-needs/

Lessons learnt from this experience is just be cautious about the disk space in bigger size databases. It will not be greater than the database.