RMAN Backup from Standby w Recovery Catalog Part 2

RMAN Backup from Standby w Recovery Catalog

What! There’s a part 2?

FRA from primary was getting full since archivelog deletion was not working.

1. Why is there a need to delete archivelog since FRA performs clean up?
2. If FRA performs clean up then why is the destination full?
3. Go to 1.

Reminds me of, “Who’s on first, What’s on second, I Don’t Know is on third”

Notice the old configuration.

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

Archivelog deletion.

connect target;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
list archivelog all;
delete noprompt archivelog until time 'sysdate-7/24';
list archivelog all;

Archivelog deleted.

archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31831.359.955164907 RECID=87186 STAMP=955164906
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31832.886.955166705 RECID=87189 STAMP=955166705
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31833.594.955168505 RECID=87191 STAMP=955168504
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31834.411.955170305 RECID=87193 STAMP=955170304
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31835.418.955172107 RECID=87195 STAMP=955172106
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31836.448.955173905 RECID=87197 STAMP=955173906
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31837.438.955175705 RECID=87199 STAMP=955175705
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31838.811.955176699 RECID=87201 STAMP=955176703
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31839.922.955178497 RECID=87203 STAMP=955178499
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31840.468.955180295 RECID=87205 STAMP=955180294
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31841.910.955182097 RECID=87207 STAMP=955182097
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31842.830.955183897 RECID=87209 STAMP=955183897
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31843.986.955185697 RECID=87211 STAMP=955185696
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31844.879.955186507 RECID=87212 STAMP=955186506
Deleted 14 objects

OH SH*T

RMAN> connect target;

connected to target database: QA (DBID=3476258591)

RMAN> connect catalog cat@rman

recovery catalog database Password:
connected to recovery catalog database

RMAN> list backup of archivelog from logseq=31831 until logseq=31844 thread=1 summary;

specification does not match any backup in the repository

Sign of JOY!

RMAN> list backup of archivelog from logseq=31831 until logseq=31844 thread=1 summary for db_unique_name all;


specification does not match any backup in the repository

List of Backups for database with db_unique_name QADR
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
812913  B  A  A DISK        2017-09-20:05:43:11 1       1       YES        DAILY_ARCHLOG_BKUP
812915  B  A  A DISK        2017-09-20:05:43:30 1       1       YES        DAILY_ARCHLOG_BKUP
813550  B  A  A DISK        2017-09-20:09:36:44 1       1       YES        DAILY_ARCHLOG_BKUP
813551  B  A  A DISK        2017-09-20:09:37:55 1       1       YES        DAILY_ARCHLOG_BKUP
813559  B  A  A DISK        2017-09-20:09:41:33 1       1       YES        DAILY_ARCHLOG_BKUP
813570  B  A  A DISK        2017-09-20:09:45:17 1       1       YES        DAILY_ARCHLOG_BKUP
814104  B  A  A DISK        2017-09-20:13:36:44 1       1       YES        DAILY_ARCHLOG_BKUP
814105  B  A  A DISK        2017-09-20:13:37:55 1       1       YES        DAILY_ARCHLOG_BKUP
814112  B  A  A DISK        2017-09-20:13:41:05 1       1       YES        DAILY_ARCHLOG_BKUP
814114  B  A  A DISK        2017-09-20:13:41:45 1       1       YES        DAILY_ARCHLOG_BKUP

RMAN> show all;

RMAN configuration parameters for database with db_unique_name QADR are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;

Wondering if
CONFIGURE DEFAULT DEVICE TYPE TO DISK
had anything to do with
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO ‘SBT_TAPE’ APPLIED ON ALL STANDBY
not working?

You might ask, why not just use “CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY” only.

That depends if you want accuracy or not.

Was the requirement backup to tape or to disk?

If the requirement is backup to tape, feel lucky FRA got full; otherwise, there’s a false pretense of good backup going to tape.

 

Advertisements

RMAN Backup from Standby w Recovery Catalog

Apologies as this is not a very clean post as there is too much info to disseminate.

Hopefully, the info may be useful at some point.

Try connect using connect target sys/password@tns vs connect connect target;

Otherwise, RMAN> resync catalog from db_unique_name all will fail or get the error below.

RMAN-06820: WARNING: failed to archive current log at primary database

When using RMAN recovery catalog, “from db_unique_name and for db_unique_name” are introduced.
Either specify the required db_unique_name or use ALL.

References:

RMAN-06613: Connect identifier for DB_UNIQUE_NAME not configured (Doc ID 1598653.1) 
List backup on Standby database returns no data in a Dataguard Configuration (Doc ID 1382885.1) 
ORA-17629 : RMAN Resync Catalog from db_unique_name all fails (Doc ID 1301769.1) 
RESYNC CATALOG FROM DB_UNIQUE_NAME ALL fails ORA-17629, ORA-17628 (Doc ID 1327156.1)

DEMO:

$ rman

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 18 15:26:30 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target sys@qa

target database Password:
connected to target database: QA (DBID=147966131)

RMAN> connect catalog cat@rman

recovery catalog database Password:
connected to recovery catalog database

RMAN> resync catalog from db_unique_name all;


starting full resync of recovery catalog
full resync complete

resyncing from database with DB_UNIQUE_NAME QADR

RMAN> show all for db_unique_name all;


RMAN configuration parameters for database with db_unique_name QA are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qa/%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backups/rman/qa/%U' MAXPIECEZE 20 G;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' MAXPIECEZE 5 G;
CONFIGURE MAXSETZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESON ALGORITHM 'BAC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'QA' CONNECT IDENTIFIER  'QA';
CONFIGURE DB_UNIQUE_NAME 'QADR' CONNECT IDENTIFIER  'QADR';
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/11g/dbs/snapcf_qa.f'; # default

RMAN configuration parameters for database with db_unique_name QADR are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qadr/%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO BACKUPSET PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backups/rman/qadr/%U' MAXPIECEZE 32 G;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' MAXPIECEZE 32 G;
CONFIGURE MAXSETZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESON ALGORITHM 'BAC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'QA' CONNECT IDENTIFIER  'QA';
CONFIGURE DB_UNIQUE_NAME 'QADR' CONNECT IDENTIFIER  'QADR';
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/11g/dbs/snapcf_qa.f'; # default

RMAN> exit

ALL controlfile autobackup going to disk, will probably needs further investigation.

$ rman

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Sep 18 20:08:27 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target;

connected to target database: QA (DBID=3476258591)

RMAN> connect catalog cat@rman

recovery catalog database Password:
connected to recovery catalog database

RMAN> list backup of controlfile summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
787503  B  F  A SBT_TAPE    2017-09-14:17:10:41 1       1       NO         TAG20170914T171041

RMAN> list backupset 787503;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
787503  Full    13.75M     SBT_TAPE    00:00:00     2017-09-14:17:10:41
        BP Key: 787508   Status: AVAILABLE  Compressed: NO  Tag: TAG20170914T171041
        Handle: c-3476258591-20170914-05   Media: 758343
  Control File Included: Ckp SCN: 9869799255   Ckp time: 2017-09-14:17:10:41
  SPFILE Included: Modification time: 2017-08-16:17:19:20

RMAN> list backup of controlfile summary for db_unique_name all;


List of Backups for database with db_unique_name QA
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
787503  B  F  A SBT_TAPE    2017-09-14:17:10:41 1       1       NO         TAG20170914T171041

List of Backups for database with db_unique_name QADR
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
795620  B  F  A DISK        2017-09-16:20:12:59 1       1       NO         TAG20170916T201259
796112  B  F  A DISK        2017-09-16:21:35:35 1       1       NO         TAG20170916T213535
796981  B  F  A DISK        2017-09-17:01:38:27 1       1       NO         TAG20170917T013827
798063  B  F  A DISK        2017-09-17:05:38:42 1       1       NO         TAG20170917T053841
799073  B  F  A DISK        2017-09-17:09:40:17 1       1       NO         TAG20170917T094016
800391  B  F  A DISK        2017-09-17:13:40:52 1       1       NO         TAG20170917T134052
801182  B  F  A DISK        2017-09-17:17:41:18 1       1       NO         TAG20170917T174118
801914  B  F  A DISK        2017-09-17:19:55:17 1       1       NO         TAG20170917T195517
802532  B  F  A DISK        2017-09-17:21:35:26 1       1       NO         TAG20170917T213526
803350  B  F  A DISK        2017-09-18:01:35:55 1       1       NO         TAG20170918T013555
804191  B  F  A DISK        2017-09-18:05:36:21 1       1       NO         TAG20170918T053620
805093  B  F  A DISK        2017-09-18:09:37:49 1       1       NO         TAG20170918T093749
805935  B  F  A DISK        2017-09-18:13:38:14 1       1       NO         TAG20170918T133814
807696  B  F  A DISK        2017-09-18:17:38:31 1       1       NO         TAG20170918T173831
808143  B  F  A DISK        2017-09-18:19:51:45 1       1       NO         TAG20170918T195145

RMAN> list backupset 808143;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 09/18/2017 20:10:47
RMAN-06160: no backup pieces found for backup set key: 808143

RMAN> list backupset 808143 for db_unique_name QADR;

List of Backup Set for database with db_unique_name QADR
===================

--- Notice %d is db_name and not db_unique_name which is disappointing

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
808143  Full    13.80M     DISK        00:00:00     2017-09-18:19:51:45
        BP Key: 808152   Status: AVAILABLE  Compressed: NO  Tag: TAG20170918T195145
        Piece Name: /backups/rman/qadr/QA_3476258591_c-3476258591-20170918-05.ctl
  Standby Control File Included: Ckp SCN: 9902230362   Ckp time: 2017-09-18:19:45:07
  SPFILE Included: Modification time: 2017-09-16:22:12:41

RMAN> exit

When configurations are the same for primary and standby, do this:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl' FOR DB_UNIQUE_NAME ALL

versus

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%d_%I_%F.ctl' FOR DB_UNIQUE_NAME 'QA';
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%d_%I_%F.ctl' FOR DB_UNIQUE_NAME 'QADR';

RMAN> show CONTROLFILE AUTOBACKUP FORMAT for db_unique_name all;

RMAN configuration parameters for database with db_unique_name QA are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qa/%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';

RMAN configuration parameters for database with db_unique_name QADR are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qadr/%d_%I_%F.ctl';

RMAN>

Thoughts on RMAN Backup Strategy Part 3

Thoughts on RMAN Backup Strategy Part 2

Thoughts on RMAN Backup Strategy

Linux Locking using flock

From cron:

00 03 * * 0          rman_db_backup.sh 0     > /tmp/rman_l0.log 2>&1
00 03 * * 1,2,3,4,5  rman_db_backup.sh 1     > /tmp/rman_l1.log 2>&1
30 * * * *           rman_archive_backup.sh  > /tmp/rman_arch.log 2>1

Notice from cron, DB backup starts at 3am and achivelog backup starts at 30m after the hour.

What happens when DB performs backup database plus archivelog delete input?

What happens when failed backup does not exits from shell script and continue with delete noprompt obsolete;?

Just a few thoughts:

1. Use flock to prevent 2 backups for running at the same time.
2. User error trapping to exit from shell script vs continuing to next step.
3. Don’t delete archivelog from DB backup.
4. Delete archivelog from ARC backup or schedule MW tasks.

 

Protection From Equifax Data Breach

Place fraud alert – this only needs to be done from one credit bureau and it will be forwared to all others.

To be safe, I have sucessfully placed fraud alert for TransUnion and Equifax. Was not successful with Experian.

Place credit freeze for Equifax only. Freezing all 3 bureaus will be a pain in the arse for when establishing credit.

Should the business use Equifax exclusively, then there’s a choice to unfreze credit from Equifax or take the business elsewhere.

Preference would be take business elsewhere. At this moment, credit freeze at Equifax is planned to be for life.

After placing fraud alert, I got alerted from CreditWise, a free basic credit monitoring from Capital One credit card.

Heads up: there was some activity on your TransUnion credit report.

Visit the Alerts section of CreditWise® from Capital One® to review your recent TransUnion credit report change.
To protect your privacy, we aren’t including any confidential details here.
Sign in soon—staying on top of your credit can help you defend yourself against potential fraud or identity theft.

ProtectMyID Essential, Exclusively for AAA Members – covers credit monitoring from Experian

https://www.annualcreditreport.com/
Get a free copy of your credit report every 12 months from each credit reporting company.

Every 4 month, get free credit report from 1 credit reporting company.

It’s not fool proof, but better than nothing.

At this point, looks like all bases are covered.

Good Luck to you.

Thoughts on RMAN Backup Strategy Part 2

Cron backup schedule.

00 03 * * 0          rman_db_backup.sh 0     > /tmp/rman_l0.log 2>&1
00 03 * * 1,2,3,4,5  rman_db_backup.sh 1     > /tmp/rman_l1.log 2>&1
30 * * * *           rman_archive_backup.sh  > /tmp/rman_arch.log 2>&1

FAILED: Archive RMAN backup.
It’s the dreaded archivelog does not exist.
We can simply put crosscheck archivelog all, delete noprompt expired archivelog all, and this should fix the problem, right?

WRONG! Don’t do this. I know there will be disagreement and this is my opinion.

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file +FRA/ARCHIVELOG/2017_09_10/thread_1_seq_786844
ORA-17503: ksfdopn:2 Failed to open file +FRA/ARCHIVELOG/2017_09_10/thread_1_seq_786844
ORA-15012: ASM file '+FRA/ARCHIVELOG/2017_09_10/thread_1_seq_786844' does not exist

Scripts did have crosscheck and I have commented out prior because error was being ignored since next backup was successful.

$ grep crosscheck *.sh
rman_archive_backup.sh:#crosscheck archivelog all;
rman_db_backup.sh:#crosscheck archivelog all;

The error happened again and next backup was successful. Why?

Archivelog backup does not exists.

RMAN> list backup of archivelog from logseq=786840 until logseq=786850 thread=1;

specification does not match any backup in the repository

RMAN> 

Archived log shown as backed up and deleted. What happened and is it safe to ignore?

SQL> select FIRST_TIME,SEQUENCE#,THREAD#,STATUS,DELETED,BACKUP_COUNT
from V$ARCHIVED_LOG 
where SEQUENCE# between 786840 and 786850;

FIRST_TIME	     SEQUENCE#	  THREAD# S DEL BACKUP_COUNT
------------------- ---------- ---------- - --- ------------
2017-09-10 02:25:12	786840		1 D YES 	   1
2017-09-10 02:25:15	786841		1 D YES 	   1
2017-09-10 02:25:18	786842		1 D YES 	   1
2017-09-10 02:25:21	786843		1 D YES 	   1
2017-09-10 02:25:24	786844		1 D YES 	   1
2017-09-10 02:25:27	786845		1 D YES 	   1
2017-09-10 02:25:30	786846		1 D YES 	   1
2017-09-10 02:25:33	786847		1 D YES 	   1
2017-09-10 02:25:36	786848		1 D YES 	   1
2017-09-10 02:25:39	786849		1 D YES 	   1
2017-09-10 02:25:42	786850		1 D YES 	   1

11 rows selected.

SQL> exit

Please ponder for a moment and will post findings next week.

Assign Pseudo Random Password for Oracle DB Users

Due to password verification function, I had to hard code some of  the requirements since I did not want to spend too much time on the matter.

ORA-20024: Password must contain at least 2 lowercase character(s)
ORA-20026: Password must contain at least 1 special character(s)
ORA-20025: Password must contain at least 2 digit(s)

DBMS_RANDOM.STRING opt IN CHAR,len IN NUMBER) RETURN VARCHAR2;

opt:
'u', 'U' - returning string in uppercase alpha characters
'l', 'L' - returning string in lowercase alpha characters
'a', 'A' - returning string in mixed case alpha characters
'x', 'X' - returning string in uppercase alpha-numeric characters
'p', 'P' - returning string in any printable characters.
Otherwise the returning string is in uppercase alpha characters.

len:
Length of the returning string

Modify DB user password:

declare
 pwd varchar2(10);
 l_sql varchar (10000);
begin
for x in (
select username from dba_users where username not in ('XS$NULL') order by 1
)
loop
 pwd := dbms_random.string('X',10);
 l_sql := 'alter user '||x.username||' identified by "ab!'||pwd||'yz90"';
 dbms_output.put_line(l_sql);
 execute immediate l_sql;
end loop;
end;
/

 

Check password modification date.

select name, ctime, ptime from sys.user$
where name in (select username from dba_users)
order by 1; 

 

Thoughts on RMAN Backup Strategy

Do you label your backupset to provide meaningful name?

rman_l0_gosctvna_1_1
rman_arc_h4scu0u7_1_1
rman_arc_h9scu4en_1_1
DBNAME_c-593812450-20170827-07.ctl

For Level 0 backup, do you perform database backup separately from archivelog and tag them differently, e.g. LEVEL0, ARCHIVELOG?

How do you tag your backup?

Do you backup controlfile to trace or backup init parameter or backup password file?

Just some thoughts after have been through hell with restore and lucky came out of hell.

Of course, there are pros and cons to everything.
With too much customization, cannot simply use RMAN configure to set it and forget it.

FYI: 12c syntax for SQL

connect target;
set echo on 
set command id to "LEVEL0";
show all;
run {
set controlfile autobackup format for device type disk to '/oradata/backup/%d_%I_%F.ctl';
allocate channel d1 device type disk format '/oradata/backup/%d_%I_%T_%U.LEVEL0' maxopenfiles 1;
backup as compressed backupset 
incremental level 0 check logical database 
#filesperset 1 tag="LEVEL0"
tag="LEVEL0"
include current controlfile
plus archivelog not backed up 1 times
#filesperset 8 tag="LEVEL0"
tag="LEVEL0"
;
alter database backup controlfile to trace as '/oradata/backup/cf_@.sql' REUSE RESETLOGS;
create pfile='/oradata/backup/init@.ora' from spfile;
create pfile from spfile;
}
report schema;
list backup summary;
exit

 

Windows Datapump Export

The purpose of the script is to perform full database export keeping 3 export copies.
If export is successful, then fullexp*.dmp will be renamed with _1.dmp suffix added to filename.
If export is unsuccessful, then code will exit, skipping rename operations.

Note: there should never be .dmp file without # suffix unless export is unsuccessful.

In hindsight, directoryName should be using variable (ORACLE_SID) versus hardcode.

SET ORACLE_SID=DB01
SET directoryName=D:\DB01\export

expdp ‘/ as sysdba’ full=y directory=DATA_PUMP_DIR dumpfile=fullexp_%ORACLE_SID%_%COMPUTERNAME%.dmp logfile=fullexp_%ORACLE_SID%_%COMPUTERNAME%.log flashback_time=SYSTIMESTAMP REUSE_DUMPFILES=YES
IF %ERRORLEVEL% NEQ 0 GOTO ERROR

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.dmp” (DEL “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.*”)

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.dmp” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.dmp” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.dmp”)
IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.log” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.log” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.log”)

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.dmp” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.dmp” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.dmp”)
IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.log” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.log” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.log”)

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.dmp” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.dmp” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.dmp”)
IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.log” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.log” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.log”)

EXIT 0

:ERROR
EXIT 1

Results after 4 runs.

08/03/2017  07:53 PM     2,680,008,704 fullexp_DB01_CMWPHV1_1.dmp
08/03/2017  07:53 PM           161,707 fullexp_DB01_CMWPHV1_1.log
08/03/2017  07:46 PM     2,680,008,704 fullexp_DB01_CMWPHV1_2.dmp
08/03/2017  07:46 PM           161,707 fullexp_DB01_CMWPHV1_2.log
08/03/2017  07:37 PM     2,680,008,704 fullexp_DB01_CMWPHV1_3.dmp
08/03/2017  07:37 PM           161,707 fullexp_DB01_CMWPHV1_3.log

Notes on datapatch

Mike Dietrich is the God father of all Oracle upgrades.

Here are just a few reference and should read

DBUA 12c and “datapatch.pl” – things to know

DBUA misses the post-upgrade datapatch execution in Oracle 12.1.0.2. The solution is to apply the SQL changes manually after DBUA has completed the database upgrade to Oracle Database 12c:

cd $ORACLE_HOME/OPatch
./datapatch -verbose

Does DBCA execute “datapatch” in Oracle 12.2?

My notes: when it doubt check it out.

$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 on Thu Jan 19 10:35:27 2017
Copyright (c) 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_93223_2017_01_19_10_35_27/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 24315824 (Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)):
  Installed in the binary registry only
Bundle series DBBP:
  ID 161018 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    24315824 (Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016))
    24340679 (DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679))

Installing patches...
Patch installation complete.  Total patches installed: 2

Validating logfiles...
Patch 24315824 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24315824/20676808/24315824_apply_DBFS_2017Jan19_10_35_43.log (no errors)
Patch 24340679 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_DBFS_2017Jan19_10_35_47.log (no errors)
SQL Patching tool complete on Thu Jan 19 10:37:16 2017

SQL> set serveroutput on
SQL> exec dbms_qopatch.get_sqlpatch_status;

-- Logfile is provided. How convenient is that?
Patch Id : 24315824
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:14
        Description : Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24315824/20676808/24315824_apply_DBFS_2017Jan19_10_35_43.log
        Status : SUCCESS

Patch Id : 24340679
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:16
        Description : DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_DBFS_2017Jan19_10_35_47.log
        Status : SUCCESS

PL/SQL procedure successfully completed.

SQL>


$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose


SQL> set serveroutput on
SQL> exec dbms_qopatch.get_sqlpatch_status;


Patch Id : 24315824
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:14
        Description : Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24315824/20676808/24315824_apply_DBFS_2017Jan19_10_35_43.log
        Status : SUCCESS

Patch Id : 24340679
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:16
        Description : DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_DBFS_2017Jan19_10_35_47.log
        Status : SUCCESS

PL/SQL procedure successfully completed.

SQL>

12c DataGuard Validate and More

12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt) (Doc ID 1582837.1)	

Configuration

db_name=hawk
db_unique_name=hawka (primary)
db_unique_name=hawkb (standby)

Configuration - hawkdg
  Protection Mode: MaxPerformance
  Members:
    hawka - Primary database
      hawkb - Physical standby database 

Validate DataGuard Configurations.

Monitorable (Read-Only) Properties

show configuration verbose

show database verbose hawka
show database verbose hawkb

validate database verbose hawka
validate database verbose hawkb

There is no need to use on database if the instance names are unique across primary and standby environments.

You might ask, why are instance name not the  same on primary and standby?

I don’t know.

show instance verbose hawka1 on database hawka
show instance verbose hawka2 on database hawka

show instance verbose hawkb1 on database hawkb
show instance verbose hawkb2 on database hawkb

The InconsistentProperties monitorable property returns a table that shows all properties whose values contained in the broker configuration file are inconsistent with the values in the corresponding server parameter file or the runtime values.

show database hawka InconsistentProperties
show database hawkb InconsistentProperties

The InconsistentLogXptProps monitorable property returns a table that shows all properties related to redo transport services whose values are inconsistent between the broker configuration file and the runtime value.

show database hawka InconsistentLogXptProps
show database hawkb InconsistentLogXptProps

The LogXptStatus monitorable property returns a table that contains the error status of redo transport services for each of the enabled configuration members. This property pertains to the primary database, a physical standby database that ships redo data, or a far sync instance.

show database hawka LogXptStatus

The SendQEntries monitorable property returns a table that shows all log files on the primary database that were not successfully archived to one or more standby databases. This property pertains to the primary database

show database hawka SendQEntries

The RecvQEntries monitorable property returns a table indicating all log files that were received by the standby database but have not yet been applied. If no rows are returned, it implies all log files received have been applied. This property pertains to a standby database.

show database hawkb RecvQEntries

The TopWaitEvents monitorable property specifies the 5 events with the longest waiting time in the specified instance.

show instance hawkb1 TopWaitEvents

How to edit database properties for all instances.

edit instance * on database hawka set property logarchivetrace=0;

Lastly, all commands and be run from shell script with example below.

echo "***** Checking Data Guard Broker Configuration ...."
dgmgrl -echo << END
connect /
show configuration verbose
show configuration TraceLevel
show database hawklas
show database hawksan
show instance hawklas DGConnectIdentifier
show instance hawksan DGConnectIdentifier
show instance hawklas StaticConnectIdentifier
show instance hawksan StaticConnectIdentifier
show instance hawklas InconsistentProperties
show instance hawksan InconsistentProperties
show instance hawklas LogArchiveMaxProcesses
show instance hawksan LogArchiveMaxProcesses
show instance hawklas DelayMins
show instance hawksan DelayMins
show instance hawklas LogArchiveTrace
show instance hawksan LogArchiveTrace
show instance hawklas statusreport
show instance hawksan statusreport
exit
END
exit