Expert Database Consultancy & Solutions. All the post in this blog is my own opinion and all the steps are tested in test environment. Kindly review and test it before implementing in production. This Blog is intend to share & gain the knowledge of Oracle Technologies.
useful script to collect the database recovery information:
spool /tmp/current_status_info_new_2.txt set pagesize 20000 set linesize 180 set pause off set serveroutput on set feedback on set echo on set numformat 999999999999999 alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; archive log list; select name,dbid,controlfile_type,open_mode,log_mode,checkpoint_change#,archive_change# from v$database; select * from v$database_incarnation; col name for a75 select * from v$restore_point; select flashback_on from v$database; select parallel from v$instance; select protection_level from v$database; select * from dba_streams_administrator; select file#,name,status,checkpoint_change#,enabled from v$datafile; select file#,name,status,enabled from v$tempfile; select TS#,NAME,INCLUDED_IN_DATABASE_BACKUP,FLASHBACK_ON from v$tablespace; select * from v$recover_file; select * from v$backup; select * from v$log; select * from v$logfile; select file#,name,recover,fuzzy,checkpoint_change#,creation_change#,checkpoint_time,creation_time,RESETLOGS_TIME,status from v$datafile_header; select status, to_char(checkpoint_change#), to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, count(*) from v$datafile_header group by status, checkpoint_change#, checkpoint_time order by status, checkpoint_change#, checkpoint_time; select count(*),fhsta from x$kcvfh group by fhsta; select count(*),fhrba_seq from x$kcvfh group by fhrba_seq; select count(*),fhscn from x$kcvfh group by fhscn; select count(*),fhafs from x$kcvfh group by fhafs; select min(FHSCN) "LOW FILEHDR SCN" , max(FHSCN) "MAX FILEHDR SCN", max(FHAFS) "Min PITR ABSSCN" from X$KCVFH ; select fhdbn,fhdbi,hxfil,fhsta,fhscn,fhafs,fhrba_seq,fhtnm tbs_name from x$kcvfh; select name, status , sequence#, thread#, TO_CHAR(first_change#, '999999999999999999') as first_change#, TO_CHAR(next_change#, '999999999999999999') next_change#, to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') completion_time from v$archived_log where (select min(checkpoint_change#) from v$datafile_header) between first_change# and next_change#; spool off exit
We are using 12c six node primary. For this we have configured three node standby database. We validate the things from the configuration perspective and we are good.
Post this we configured data guard broker for the same and post data guard configiguration below error is frequently reporting to the alert of standby database.
standby alert log ============= Fatal NI connect error 12521, connecting to: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XX.XXX.XXX.XX1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=XX.XXX.XXX.XX2)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=XX.XXX.XXX.XX3)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=XX.XXX.XXX.XX4)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=XX.XXX.XXX.XX5)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=XX.XXX.XXX.XX6)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dba_DGB)(INSTANCE_NAME=dba4)(CID=(PROGRAM=oracle)(HOST=ctsttbadm03.cts.com)(USER=orahcm)))) VERSION INFORMATION: TNS for Linux: Version 188.8.131.52.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 184.108.40.206.0 - Production Time: 19-FEB-2018 22:02:26 Tracing not turned on. Tns error struct: ns main err code: 12564
TNS-12564: TNS:connection refused
This is caused by incorrect DGConnectIdentifier property in the broker's configuration (DGMGRL)
When Primary is a RAC database, the Standby Single Instance's DGConnectIdentifier property in the broker/dgmrl should be set to the SCAN name of the cluster database.
In this scenario, the DGConnectIdentifier was configured to use the cluster's VIP address, caused the connection problem from the Standby to the Primary.
Check the database configuration in the broker using DGMGRL, as in example below:
The ALERT LOG from the STANDBY continuously reports TNS-12521 while connecting to the (HOST=<vip hostname>)
Fatal NI connect error 12521, connecting to: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<vip hostname>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbaa_DGB)(INSTANCE_NAME=dba2)(SERVER=DEDICATED)(CID=(PROGRAM=oracle)(HOST=<hostname>)(USER=oracle))))
It should not use the VIP address 'vip hostname' as DGConnectIdentifier but the SCAN address for the PRIMARY cluster database instead.
Fix the DGConnectIdentifier property in the Broker configuration to use SCAN name of the cluster database
How to change SYSDATE to future - FIXED_DATE In real world application is dependent on the SYSDATE for measuring current date-time for the processing.
Whenever there might be a situation where application want to process a complete data only on the month end and for testing or UAT purpose you need to do the processing at adhoc basis. In this FIXED_DATE parameter help to set the SYSDATE value to the desired any future date you want to set.
Changing current date to future is an option but there may be some other databases also exist on same server. Changing date on exadata server then its very complex job.
Please find below inputs on the change date to future date–
Changing the time on Exadata server is difficult way to achieve the goal as It involve complexity, downtime and If we change the sysdate on server again ntp will try to correct it. We can’t stop ntp as its having dependencies on cell node.
If you want to check it further with this option then need to check with Product Support Team through SR.
We can change the SYSDATE on database for testing purpose by setting FIXED_DATE to <future date>
FIXED_DATE enables you to set a constant date that SYSDATE will always return instead of the current date.
This parameter only change the SYSDATE not the other like LOCALTIMESTAMP, CURRENT_TIMESTAMP etc. Please refer below MOS doc for the further reference and use of this parameter.
Init.ora Parameter "FIXED_DATE" Reference Note (Doc ID 30724.1)
Clarification CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP ,SYSDATE, SYSTIMESTAMP, FIXED_DATE (Doc ID 1624595.1)
Test Case Done –
SQL> select SESSIONTIMEZONE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP ,SYSDATE, SYSTIMESTAMP from dual;