Loading...

Follow Sun Oracleworld on Feedspot

Continue with Google
Continue with Facebook
or

Valid

Force database open : _allow_resetlogs_corrution


In real world scenario many times DBA comes to a point where he can’t open a database even with resetlogs. Or when he lost current redolog file. or due to some corruption.

In many situations - recreating controlfile and open resetlogs failed even though the chaeckpoint_change# in all datafile header is having unique value.

many situations where we need to tweak more after first run. but almost all time we opened a database using below method. This is the last way to open the database as per my knowledge.

Force open steps are:

1)      Backup the database while the database is closed.

THE INSTRUCTIONS HERE ARE DESTRUCTIVE. YOU ARE STRONGLY ADVISED TO BACKUP THE
DATABASE BEFORE PROCEEDING. IF YOU DO NOT DO THIS YOU MAY LOSE THE CHANCE TO
TRY OTHER OPTIONS.

2) Disable this database from EM if running. We also need to disable RAC to avoid Auto restart.

3) If your datafiles are from different points in time, it is best to try to
use system tablespace datafiles at a similar timestamp to the OLDEST files
you have online in the database. This reduces the chance that you will get
problems during the bootstrap phase of opening the database.

4) Edit your init.ora file to change undo_management and add two parameters.

* Change UNDO_MANAGEMENT=AUTO to
UNDO_MANAGEMENT=MANUAL
* Remove or comment out UNDO_TABLESPACE and UNDO_RETENTION.

* Add
CLUSTER_DATABASE=FALSE
JOB_QUEUE_PROCESSES=0
_ALLOW_RESETLOGS_CORRUPTION = TRUE

* If you only have a spfile available, you can from the closed, nomount or the
mount stage create an init.ora file as follows:

SQL> CREATE PFILE FROM SPFILE;

Do NOT edit the SPFILE.

5) Invoke SQL*Plus, startup mount, check that correct init.ora was used and
all datafiles are in the status of online or system.

$ sqlplus "/as sysdba"

SQL> startup mount pfile = (full path / file name to init.ora)
Confirm that the hidden parameters from step 3 were used:

SQL> show parameters corrupt

You should see both hidden parameters listed. If not, the wrong init.ora
may have been modified. Do not continue until "show parameters corrupt" shows
both hidden parameters.

SQL> show parameters undo

You should see undo management set to manual. If not, the wrong init.ora
may have been modified. Do not continue until "show parameters undo" shows
undo management as manual.

Check that all files you want to open with are listed as ONLINE or as SYSTEM.

SQL> select name, file#, status from v$datafile where status not in
('SYSTEM', 'ONLINE');

If any rows are returned from the query above, bring the file(s) online with:

SQL> ALTER DATABASE DATAFILE file# ONLINE;

and repeat until there are no files in an OFFLINE status. If any file remains or
changes into "recover" status after you try to online the file proceed to step 6.

6) Perform a fake incomplete recovery then open the database with resetlogs.

SQL> recover database using backup controlfile until cancel;

WHEN PROMPTED FOR AN ARCHIVELOG FILE TYPE cancel THEN PRESS ENTER.

SQL> ALTER DATABASE OPEN RESETLOGS;

7) If the database opens try selecting from a table. For example:

SQL> SELECT SYSDATE FROM DUAL;

If you get a row back the database is open and "functional". If you wish, you
may try to select from a other tables to make sure the database is functional
enough for the required export.

With the database open and functional you should attempt to export the database
IMMEDIATELY. Since database is unstable, don't try another shutdown/startup unless needed.
Once you have an export the database MUST be recreated from scratch.
This means dropping and deleting ALL datafiles and creating a new database from
scratch.

A database which has been opened in this way but not rebuilt will not be
supported by Oracle. Any delay in exporting the contents or any attempt to
use the system may cause irreparable damage.

NOTE: BE SURE TO REVERSE / REMOVE THE INIT.ORA PARAMETERS ADDED IN STEP 3
OTHERWISE YOU MAY ACCIDENTALLY CORRUPT ANY NEW DATABASE CREATED USING THE SAME
INIT.ORA FILE.

8) If the instance crashed in the open phase of step 5, check for trace files
in the background dump destination. If you find a trace file, check to see if
the trace file has an ORA-00600 [2662] or ORA-00600 [4000] error in it.
Either of these errors may also be seen in the alert.log file.

If you see the ORA-00600 [2662] or ORA-00600 [4000] error, provide Oracle Support
Services the full error message. Oracle Support Services will provide steps to advance
the SCN using a hidden parameter.



NOTE: BE SURE TO REVERSE / REMOVE THE INIT.ORA PARAMETERS ADDED IN STEP 3
OTHERWISE YOU MAY ACCIDENTALLY CORRUPT ANY NEW DATABASE CREATED USING THE SAME
INIT.ORA FILE.

*************************************************************************
* *
* CAUTION: Once the database is open, it is imperative that you export, *
* rebuild the database, and import. *
* *
* By forcing open the database in this fashion, there is a strong *
* likelihood of logical corruption, possibly affecting the data *
* dictionary. Oracle does not guarantee that all of the data will be *
* accessible nor will it support a database that has been opened by *
* this method and that the database users will be allowed to continue *
* work. All this does is provide a way to get at the contents of the *
* database for extraction, usually by export. It is up to you to *
* determine the amount of lost data and to correct any logical *
* corruption issues. *
* *
*************************************************************************





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 


I hope this helps. Happy recovery :)
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

How to uninstall Oracle Home 12c on Exadata step by step

Owner of the oracle Home is oradbxp
Oracle Home is /u01/app/oracle/product/12.1.0.2/oradbxp_dbhome
This is Exadata machine and three node RAC

Steps to uninstall Oracle Home on exadata and normal server is same. below steps uninstall Oracle home from all nodes.

[oradbxp@myexa01 database]$
[oradbxp@myexa01 database]$ cd -
/u01/app/oracle/product/12.1.0.2/oradbxp_dbhome/deinstall
[oradbxp@myexa01 deinstall]$
[oradbxp@myexa01 deinstall]$
[oradbxp@myexa01 deinstall]$ pwd
/u01/app/oracle/product/12.1.0.2/oradbxp_dbhome/deinstall
[oradbxp@myexa01 deinstall]$
[oradbxp@myexa01 deinstall]$ cd -
/u01/app/oracle/Oeda/Software/12.1.0.2/database
[oradbxp@myexa01 database]$
[oradbxp@myexa01 database]$
[oradbxp@myexa01 database]$ ls -lrt
total 36
-rwxr-xr-x  1 oracle oinstall  500 Feb  7  2013 welcome.html
-rwxr-xr-x  1 oracle oinstall 8533 Jul  7  2014 runInstaller
drwxr-xr-x  2 oracle oinstall 4096 Jul  7  2014 rpm
drwxrwxr-x  2 oracle oinstall 4096 Jul  7  2014 sshsetup
drwxrwxr-x  2 oracle oinstall 4096 Jul  7  2014 response
drwxr-xr-x  4 oracle oinstall 4096 Jul  7  2014 install
drwxr-xr-x 14 oracle oinstall 4096 Nov 13 16:06 stage
-rw-------  1 oracle oinstall    0 Nov 23 18:16 nohup.out
[oradbxp@myexa01 database]$
[oradbxp@myexa01 database]$ ./runInstaller -deinstall -home /u01/app/oracle/product/12.1.0.2/oradbxp_dbhome
Checking for required space in /tmp directory ...
Space check on /tmp directory passed
Bootstrapping the deinstall components
Please wait ...
Bootstrapping completed
Location of logs /u01/app/oraInventory/logs/

############ ORACLE DECONFIG TOOL START ############


######################### DECONFIG CHECK OPERATION START #########################
## [START] Install check configuration ##


Checking for existence of the Oracle home location /u01/app/oracle/product/12.1.0.2/oradbxp_dbhome
Oracle Home type selected for deinstall is: Oracle Real Application Cluster Database
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory
Checking for existence of the Oracle Grid Infrastructure home /u01/app/12.1.0.2/grid
The following nodes are part of this cluster: myexa01,ctsddbadm02,ctsddbadm03
Active Remote Nodes are ctsddbadm02,ctsddbadm03
Checking for sufficient temp space availability on node(s) : 'myexa01,ctsddbadm02,ctsddbadm03'

## [END] Install check configuration ##


Network Configuration check config START

Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check2017-11-29_03-57-07-PM.log

Specify all RAC listeners (do not include SCAN listener) that are to be de-configured. Enter .(dot) to deselect all. [LISTENER_XLHYPPRD]:

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_check2017-11-29_03-57-37-PM.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home [xlhypprd1]:

###### For Database 'xlhypprd1' ######

Specify the type of this database (1.Single Instance Database|2.Oracle Restart Enabled Database|3.RAC Database|4.RAC One Node Database) [1]: 3
Specify the list of nodes on which this database has instances [myexa01]: myexa01,ctsddbadm02,ctsddbadm03
Specify the list of instance names [xlhypprd1]: xlhypprd1,xlhypprd2,xlhypprd3
Specify the local instance name on node   [xlhypprd1]:
Specify the diagnostic destination location of the database [/u01/app/oracle/diag/rdbms/xlhypprd_dr]:
Specify the storage type used by the Database ASM|FS []: ASM


Database Check Configuration END
Oracle Configuration Manager check START
OCM check log file location : /u01/app/oraInventory/logs//ocm_check9896.log
Oracle Configuration Manager check END

######################### DECONFIG CHECK OPERATION END #########################


####################### DECONFIG CHECK OPERATION SUMMARY #######################
Oracle Grid Infrastructure Home is: /u01/app/12.1.0.2/grid
The following nodes are part of this cluster: myexa01,ctsddbadm02,ctsddbadm03
Active Remote Nodes are ctsddbadm02,ctsddbadm03
The cluster node(s) on which the Oracle home deinstallation will be performed are:myexa01,ctsddbadm02,ctsddbadm03
Oracle Home selected for deinstall is: /u01/app/oracle/product/12.1.0.2/oradbxp_dbhome
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Following RAC listener(s) will be de-configured: LISTENER_XLHYPPRD
The following databases were selected for de-configuration : xlhypprd1
Database unique name : xlhypprd1
Storage used : ASM
Checking the config status for CCR
myexa01 : Oracle Home exists with CCR directory, but CCR is not configured
ctsddbadm02 : Oracle Home exists with CCR directory, but CCR is not configured
ctsddbadm03 : Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2017-11-29_03-57-01-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2017-11-29_03-57-01-PM.err'

######################## DECONFIG CLEAN OPERATION START ########################
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_clean2017-11-29_03-59-07-PM.log
Database Clean Configuration START xlhypprd1
This operation may take few minutes.
Database Clean Configuration END xlhypprd1

Network Configuration clean config START

Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_clean2017-11-29_03-59-26-PM.log

De-configuring RAC listener(s): LISTENER_XLHYPPRD

De-configuring listener: LISTENER_XLHYPPRD
    Stopping listener: LISTENER_XLHYPPRD
    Listener stopped successfully.
    Unregistering listener: LISTENER_XLHYPPRD
    Listener unregistered successfully.
Listener de-configured successfully.

De-configuring Listener configuration file on all nodes...
Listener configuration file de-configured successfully.

De-configuring Naming Methods configuration file on all nodes...
Naming Methods configuration file de-configured successfully.

De-configuring Local Net Service Names configuration file on all nodes...
Local Net Service Names configuration file de-configured successfully.

De-configuring Directory Usage configuration file on all nodes...
Directory Usage configuration file de-configured successfully.

De-configuring backup files on all nodes...
Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

Oracle Configuration Manager clean START
OCM clean log file location : /u01/app/oraInventory/logs//ocm_clean9896.log
Oracle Configuration Manager clean END

######################### DECONFIG CLEAN OPERATION END #########################


####################### DECONFIG CLEAN OPERATION SUMMARY #######################
Successfully de-configured the following database instances : xlhypprd1
Following RAC listener(s) were de-configured successfully: LISTENER_XLHYPPRD
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
#######################################################################


############# ORACLE DECONFIG TOOL END #############

Using properties file /tmp/deinstall2017-11-29_03-56-52PM/response/deinstall_2017-11-29_03-57-01-PM.rsp
Location of logs /u01/app/oraInventory/logs/

############ ORACLE DEINSTALL TOOL START ############





####################### DEINSTALL CHECK OPERATION SUMMARY #######################
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2017-11-29_03-57-01-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2017-11-29_03-57-01-PM.err'

######################## DEINSTALL CLEAN OPERATION START ########################
## [START] Preparing for Deinstall ##
Setting LOCAL_NODE to myexa01
Setting REMOTE_NODES to ctsddbadm02,ctsddbadm03
Setting CLUSTER_NODES to myexa01,ctsddbadm02,ctsddbadm03
Setting CRS_HOME to false
Setting oracle.installer.invPtrLoc to /tmp/deinstall2017-11-29_03-56-52PM/oraInst.loc
Setting oracle.installer.local to false

## [END] Preparing for Deinstall ##

Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START

Detach Oracle home '/u01/app/oracle/product/12.1.0.2/oradbxp_dbhome' from the central inventory on the local node : Done

Delete directory '/u01/app/oracle/product/12.1.0.2/oradbxp_dbhome' on the local node : Done

The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is in use by Oracle Home '/u01/app/12.1.0.2/grid'.

Detach Oracle home '/u01/app/oracle/product/12.1.0.2/oradbxp_dbhome' from the central inventory on the remote nodes 'ctsddbadm03,ctsddbadm02' : Done

Delete directory '/u01/app/oracle/product/12.1.0.2/oradbxp_dbhome' on the remote nodes 'ctsddbadm02,ctsddbadm03' : Done

The Oracle Base directory '/u01/app/oracle' will not be removed on node 'ctsddbadm03'. The directory is in use by Oracle Home '/u01/app/12.1.0.2/grid'.

The Oracle Base directory '/u01/app/oracle' will not be removed on node 'ctsddbadm02'. The directory is in use by Oracle Home '/u01/app/12.1.0.2/grid'.

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END


## [START] Oracle install clean ##

Clean install operation removing temporary directory '/tmp/deinstall2017-11-29_03-56-52PM' on node 'myexa01'
Clean install operation removing temporary directory '/tmp/deinstall2017-11-29_03-56-52PM' on node 'ctsddbadm02,ctsddbadm03'

## [END] Oracle install clean ##


######################### DEINSTALL CLEAN OPERATION END #########################


####################### DEINSTALL CLEAN OPERATION SUMMARY #######################
Successfully detached Oracle home '/u01/app/oracle/product/12.1.0.2/oradbxp_dbhome' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/12.1.0.2/oradbxp_dbhome' on the local node.
Successfully detached Oracle home '/u01/app/oracle/product/12.1.0.2/oradbxp_dbhome' from the central inventory on the remote nodes 'ctsddbadm03,ctsddbadm02'.
Successfully deleted directory '/u01/app/oracle/product/12.1.0.2/oradbxp_dbhome' on the remote nodes 'ctsddbadm02,ctsddbadm03'.
Oracle Universal Installer cleanup was successful.

Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################


############# ORACLE DEINSTALL TOOL END #############

[oradbxp@myexa01 database]$

[oradbxp@myexa01 database]$
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

TNS-12564: TNS:connection refused


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 12.1.0.2.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.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



Cause :


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.


Solution:

Check the database configuration in the broker using DGMGRL, as in example below:
For the Standby Database - "dba", it shows it as:
DGMGRL> show database verbose 'dba';

Properties:
DGConnectIdentifier = ' DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dba)(SERVER=DEDICATED)))'
  

For the Primary Cluster Database - "dbaa", it shows it as:
DGMGRL> show database verbose 'dbaa';

Properties:
DGConnectIdentifier  = ' DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<vip hostname>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbaa)(SERVER=DEDICATED)))'
  


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
for example:
DGMGRL>edit database dbaa set property DGConnectIdentifier = '(DESCRIPTION = (LOAD_BALANCE = ON)(ADDRESS = (PROTOCOL = TCP)(HOST = <scan host address>)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = dbaa)))'


Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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–

  1. 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.

  1. We can change the SYSDATE on database for testing purpose by setting FIXED_DATE to <future date>
    1. FIXED_DATE enables you to set a constant date that SYSDATE will always return instead of the current date.
    2. 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;
SESSIONTIMEZONE                                                                                                             CURRENT_D CURRENT_TIMESTAMP
--------------------------------------------------------------------------- --------- ---------------------------------------------------------------------------
LOCALTIMESTAMP                                                                                                                              SYSDATE   SYSTIMESTAMP
--------------------------------------------------------------------------- --------- ---------------------------------------------------------------------------
+00:00                                                                                                                                       19-JAN-18 19-JAN-18 08.16.14.860064 AM +00:00
19-JAN-18 08.16.14.860064 AM                                                                                    19-JAN-18 19-JAN-18 08.16.14.860061 AM +00:00


SQL>
SQL> Alter System Set Fixed_Date='31-Jan-2018' scope=both ;

System altered.

SQL>
SQL> select SESSIONTIMEZONE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP ,SYSDATE, SYSTIMESTAMP from dual;

SESSIONTIMEZONE                                                                                                             CURRENT_D CURRENT_TIMESTAMP
--------------------------------------------------------------------------- --------- ---------------------------------------------------------------------------
LOCALTIMESTAMP                                                                                                                              SYSDATE   SYSTIMESTAMP
--------------------------------------------------------------------------- --------- ---------------------------------------------------------------------------
+00:00                                                                                                                                       19-JAN-18 19-JAN-18 08.16.51.732553 AM +00:00
19-JAN-18 08.16.51.732553 AM                                                                                    31-JAN-1819-JAN-18 08.16.51.732543 AM +00:00

SQL> !date
Fri Jan 19 08:24:02 GMT 2018

SQL> ALTER SYSTEM SET FIXED_DATE=NONE;

System altered.

SQL> select SESSIONTIMEZONE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP ,SYSDATE, SYSTIMESTAMP from dual;

SESSIONTIMEZONE                                                                                                             CURRENT_DATE           CURRENT_TIMESTAMP
--------------------------------------------------------------------------- -------------------- ---------------------------------------------------------------------------
LOCALTIMESTAMP                                                                                                                              SYSDATE                          SYSTIMESTAMP
--------------------------------------------------------------------------- -------------------- ---------------------------------------------------------------------------
+00:00                                                                                                                                       19-JAN-2018 08:26:53 19-JAN-18 08.26.53.209598 AM +00:00
19*JAN*2018 08:26:53                                                                                                       19-JAN-2018 08:26:53 19-JAN-18 08.26.53.209595 AM +00:00



Thanks. I hope this helps.


Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
ORA-00600: internal error code, arguments: [kclgeneratecr_2]


Symptoms:
This is two node RAC system (12.1.0.2) and One of the node rebooted with below error.
Errors in file /oracle/app/oracle12c/diag/rdbms/myrpt/MYRPT2/trace/MYRPT2_lms3_59442.trc (incident=320169):
ORA-00600: internal error code, arguments: [kclgeneratecr_2], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle12c/diag/rdbms/myrpt/MYRPT2/incident/incdir_320169/MYRPT2_lms3_59442_i320169.trc
Use ADRCI or Support Workbench to package the incident.

Solutions:
This is known bug, Patch 22454326 available for your version please download and apply

Patch 22454326 is one off patch and base PSU 12.1.0.2.170418 is required (similer to this Patch 22454326 available for the other base versions too).

LMS terminates the instance with ORA-00600: [kclgeneratecr_2] (Doc ID 2232271.1)

Steps Performed:

MYRPT1:/home/oracle>
MYRPT1:/home/oracle>srvctl status database -d MYRPT
Instance MYRPT1 is running on node myserver01
Instance MYRPT2 is running on node myserver02
MYRPT1:/home/oracle>
MYRPT1:/home/oracle>srvctl stop database -d MYRPT
MYRPT1:/home/oracle>
MYRPT1:/home/oracle>srvctl status database -d MYRPT
Instance MYRPT1 is not running on node myserver01
Instance MYRPT2 is not running on node myserver02
MYRPT1:/home/oracle>


MYRPT1:/home/oracle>
MYRPT1:/home/oracle>/bin/umount -t acfs /backup
umount: only root can do that
MYRPT1:/home/oracle>
MYRPT1:/home/oracle>logout
[root@myserver01 ngetadmin]#


[root@myserver01 ngetadmin]# df -kh
Filesystem                                       Size  Used Avail Use% Mounted on
/dev/mapper/vg_dbsrvr1-lv_root                    29G   17G   11G  62% /
tmpfs                                            127G  2.8G  124G   3% /dev/shm
/dev/mapper/vg_dbsrvr2-lv_agent                   20G  1.1G   18G   6% /agent
/dev/mapper/36005076043c14c381a9f09ba1ad22492p2  488M   94M  369M  21% /boot
/dev/mapper/36005076043c14c381a9f09ba1ad22492p1  200M  256K  200M   1% /boot/efi
/dev/mapper/vg_dbsrvr1-lv_dump                    29G   12G   17G  42% /dump
/dev/mapper/vg_dbsrvr2-lv_grid                    48G   38G  8.2G  83% /grid
/dev/mapper/vg_dbsrvr1-lv_home                    20G  8.0G   11G  44% /home
/dev/mapper/vg_dbsrvr2-lv_oracle                  96G   41G   51G  45% /oracle
/dev/mapper/vg_dbsrvr1-lv_tmp                     20G   13G  6.1G  67% /tmp
/dev/mapper/vg_dbsrvr1-lv_var                    9.5G  4.5G  4.6G  50% /var
/dev/asm/oggvol-358                               99G   30G   70G  30% /ogg
/dev/asm/backup-380                              2.0T  1.2T  837G  59% /backup
[root@myserver01 ngetadmin]#
[root@myserver01 ngetadmin]# /bin/umount -t acfs /backup
umount: /backup: device is busy.
        (In some cases useful info about processes that use
         the device is found by lsof(8) or fuser(1))
umount: /backup: device is busy.
        (In some cases useful info about processes that use
         the device is found by lsof(8) or fuser(1))
[root@myserver01 ngetadmin]#


stop os watcher

[root@myserver01 ngetadmin]# ps -ef | grep 134211
monitor  134211 115386  0 11:35 pts/1    00:00:00 /bin/bash
root     137648  55145  0 14:12 pts/4    00:00:00 grep 134211
[root@myserver01 ngetadmin]#
[root@myserver01 ngetadmin]# kill -9 134211
[root@myserver01 ngetadmin]#
[root@myserver01 ngetadmin]# lsof | grep /backup
bash      136145      root  cwd       DIR         252,194561      8192       2250 /backup/node1/oswbb
[root@myserver01 ngetadmin]# kill -9 136145
[root@myserver01 ngetadmin]#
[root@myserver01 ngetadmin]# lsof | grep /backup
[root@myserver01 ngetadmin]#
[root@myserver01 ngetadmin]#
[root@myserver01 ngetadmin]# /bin/umount -t acfs /backup
[root@myserver01 ngetadmin]#

[root@myserver01 ngetadmin]#
[root@myserver01 ngetadmin]# /bin/umount -t acfs /ogg
[root@myserver01 ngetadmin]#
[root@myserver01 ngetadmin]# df -kh
Filesystem                                       Size  Used Avail Use% Mounted on
/dev/mapper/vg_dbsrvr1-lv_root                    29G   17G   11G  62% /
tmpfs                                            127G  2.8G  124G   3% /dev/shm
/dev/mapper/vg_dbsrvr2-lv_agent                   20G  1.1G   18G   6% /agent
/dev/mapper/36005076043c14c381a9f09ba1ad22492p2  488M   94M  369M  21% /boot
/dev/mapper/36005076043c14c381a9f09ba1ad22492p1  200M  256K  200M   1% /boot/efi
/dev/mapper/vg_dbsrvr1-lv_dump                    29G   12G   17G  42% /dump
/dev/mapper/vg_dbsrvr2-lv_grid                    48G   38G  8.2G  83% /grid
/dev/mapper/vg_dbsrvr1-lv_home                    20G  8.0G   11G  44% /home
/dev/mapper/vg_dbsrvr2-lv_oracle                  96G   41G   51G  45% /oracle
/dev/mapper/vg_dbsrvr1-lv_tmp                     20G   13G  6.1G  67% /tmp
/dev/mapper/vg_dbsrvr1-lv_var                    9.5G  4.5G  4.6G  50% /var
[root@myserver01 ngetadmin]#

[root@myserver02 ngetadmin]#
[root@myserver02 ngetadmin]# /bin/umount -t acfs /backup
umount: /backup: device is busy.
        (In some cases useful info about processes that use
         the device is found by lsof(8) or fuser(1))
umount: /backup: device is busy.
        (In some cases useful info about processes that use
         the device is found by lsof(8) or fuser(1))
[root@myserver02 ngetadmin]#
[root@myserver02 ngetadmin]# lsof | grep /backup
bash       78137      root  cwd       DIR         252,194561      4096        722 /backup/node2/oswbb/archive
[root@myserver02 ngetadmin]#
[root@myserver02 ngetadmin]# kill -9 78137
[root@myserver02 ngetadmin]#
[root@myserver02 ngetadmin]#  /bin/umount -t acfs /backup
[root@myserver02 ngetadmin]#
[root@myserver02 ngetadmin]# /bin/umount -t acfs /ogg
[root@myserver02 ngetadmin]#
[root@myserver02 ngetadmin]# df -kh
Filesystem                                       Size  Used Avail Use% Mounted on
/dev/mapper/vg_dbsrvr1-lv_root                    29G   13G   15G  46% /
tmpfs                                            127G  639M  126G   1% /dev/shm
/dev/mapper/vg_dbsrvr2-lv_agent                   20G  1.1G   18G   6% /agent
/dev/mapper/36005076043c14f781a9f0df1505843c8p2  488M   94M  369M  21% /boot
/dev/mapper/36005076043c14f781a9f0df1505843c8p1  200M  256K  200M   1% /boot/efi
/dev/mapper/vg_dbsrvr1-lv_dump                    29G  4.0G   24G  15% /dump
/dev/mapper/vg_dbsrvr2-lv_grid                    48G   40G  6.0G  88% /grid
/dev/mapper/vg_dbsrvr1-lv_home                    20G  4.0G   15G  22% /home
/dev/mapper/vg_dbsrvr2-lv_oracle                  96G   35G   57G  38% /oracle
/dev/mapper/vg_dbsrvr1-lv_tmp                     20G   57M   19G   1% /tmp
/dev/mapper/vg_dbsrvr1-lv_var                    9.5G  3.2G  5.9G  35% /var
[root@myserver02 ngetadmin]#







[root@myserver01 ngetadmin]# ps -ef | grep d.bin
root       1416  55145  0 14:18 pts/4    00:00:00 grep d.bin
root      27051      1  0 Jun15 ?        06:30:08 /grid/12c/app/12.1.0.2/grid/bin/ohasd.bin reboot
root      27100      1  0 Jun15 ?        04:27:00 /grid/12c/app/12.1.0.2/grid/bin/orarootagent.bin
grid      27166      1  0 Jun15 ?        03:39:48 /grid/12c/app/12.1.0.2/grid/bin/oraagent.bin
grid      27179      1  0 Jun15 ?        01:31:09 /grid/12c/app/12.1.0.2/grid/bin/mdnsd.bin
grid      27181      1  0 Jun15 ?        05:26:56 /grid/12c/app/12.1.0.2/grid/bin/evmd.bin
grid      27197      1  0 Jun15 ?        01:41:36 /grid/12c/app/12.1.0.2/grid/bin/gpnpd.bin
grid      27209  27181  0 Jun15 ?        01:29:08 /grid/12c/app/12.1.0.2/grid/bin/evmlogger.bin -o /grid/12c/app/12.1.0.2/grid/log/[HOSTNAME]/evmd/evmlogger.info -l /grid/12c/app/12.1.0.2/grid/log/[HOSTNAME]/evmd/evmlogger.log
grid      27215      1  1 Jun15 ?        09:05:42 /grid/12c/app/12.1.0.2/grid/bin/gipcd.bin
root      27274      1  0 Jun15 ?        03:26:25 /grid/12c/app/12.1.0.2/grid/bin/cssdmonitor
root      27294      1  0 Jun15 ?        03:47:05 /grid/12c/app/12.1.0.2/grid/bin/cssdagent
grid      27307      1  5 Jun15 ?        1-09:20:40 /grid/12c/app/12.1.0.2/grid/bin/ocssd.bin
root      27454      1  0 Jun15 ?        05:31:30 /grid/12c/app/12.1.0.2/grid/bin/octssd.bin reboot
root      27672      1  5 Jun15 ?        1-10:05:43 /grid/12c/app/12.1.0.2/grid/bin/osysmond.bin
root      27687      1  1 Jun15 ?        10:13:34 /grid/12c/app/12.1.0.2/grid/bin/crsd.bin reboot
grid      27760      1  0 Jun15 ?        06:12:06 /grid/12c/app/12.1.0.2/grid/bin/oraagent.bin
root      27764      1  1 Jun15 ?        11:41:07 /grid/12c/app/12.1.0.2/grid/bin/orarootagent.bin
root      27942      1  2 Jun15 ?        19:08:54 /grid/12c/app/12.1.0.2/grid/bin/ologgerd -M -d /grid/12c/app/12.1.0.2/grid/crf/db/myserver01
grid      27974      1  0 Jun15 ?        01:47:48 /grid/12c/app/12.1.0.2/grid/bin/scriptagent.bin
grid      27978      1  0 Jun15 ?        00:02:03 /grid/12c/app/12.1.0.2/grid/bin/tnslsnr MGMTLSNR -no_crs_notify -inherit
oracle    28435      1  0 Jun15 ?        03:36:24 /grid/12c/app/12.1.0.2/grid/bin/oraagent.bin
grid      28441      1  0 Jun15 ?        00:02:23 /grid/12c/app/12.1.0.2/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
grid      28495      1  0 Jun15 ?        00:04:27 /grid/12c/app/12.1.0.2/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
grid      28500      1  0 Jun15 ?        00:03:24 /grid/12c/app/12.1.0.2/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
[root@myserver01 ngetadmin]#
[root@myserver01 ngetadmin]# cd /grid/12c/app/12.1.0.2/grid/bin/
[root@myserver01 bin]#
[root@myserver01 bin]# ./crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'myserver01'
CRS-2673: Attempting to stop 'ora.crsd' on 'myserver01'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'myserver01'
CRS-2673: Attempting to stop 'ora.CTRLDG2.dg' on 'myserver01'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'myserver01'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'myserver01'
CRS-2673: Attempting to stop 'ora.LISTENER_RDB.lsnr' on 'myserver01'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'myserver01'
CRS-2673: Attempting to stop 'ora.mgmtdb' on 'myserver01'
CRS-2673: Attempting to stop 'ora.OGGDG.OGGVOL.advm' on 'myserver01'
CRS-2673: Attempting to stop 'ora.oc4j' on 'myserver01'
CRS-2673: Attempting to stop 'ora.REDOC.dg' on 'myserver01'
CRS-2673: Attempting to stop 'ora.REDOB.dg' on 'myserver01'
CRS-2673: Attempting to stop 'ora.cvu' on 'myserver01'
CRS-2677: Stop of 'ora.OGGDG.OGGVOL.advm' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.cvu' on 'myserver01' succeeded
CRS-2672: Attempting to start 'ora.cvu' on 'myserver02'
CRS-2676: Start of 'ora.cvu' on 'myserver02' succeeded
CRS-2677: Stop of 'ora.LISTENER_RDB.lsnr' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'myserver01' succeeded
CRS-2673: Attempting to stop 'ora.myserver01.vip' on 'myserver01'
CRS-2677: Stop of 'ora.REDOC.dg' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'myserver01' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'myserver01'
CRS-2677: Stop of 'ora.CTRLDG2.dg' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'myserver01' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'myserver01'
CRS-2677: Stop of 'ora.REDOB.dg' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.myserver01.vip' on 'myserver01' succeeded
CRS-2672: Attempting to start 'ora.myserver01.vip' on 'myserver02'
CRS-2677: Stop of 'ora.scan3.vip' on 'myserver01' succeeded
CRS-2672: Attempting to start 'ora.scan3.vip' on 'myserver02'
CRS-2677: Stop of 'ora.scan2.vip' on 'myserver01' succeeded
CRS-2672: Attempting to start 'ora.scan2.vip' on 'myserver02'
CRS-2677: Stop of 'ora.oc4j' on 'myserver01' succeeded
CRS-2672: Attempting to start 'ora.oc4j' on 'myserver02'
CRS-2677: Stop of 'ora.mgmtdb' on 'myserver01' succeeded
CRS-2673: Attempting to stop 'ora.MGMTLSNR' on 'myserver01'
CRS-2677: Stop of 'ora.MGMTLSNR' on 'myserver01' succeeded
CRS-2672: Attempting to start 'ora.MGMTLSNR' on 'myserver02'
CRS-2676: Start of 'ora.myserver01.vip' on 'myserver02' succeeded
CRS-2676: Start of 'ora.scan3.vip' on 'myserver02' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN3.lsnr' on 'myserver02'
CRS-2676: Start of 'ora.scan2.vip' on 'myserver02' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN2.lsnr' on 'myserver02'
CRS-2676: Start of 'ora.MGMTLSNR' on 'myserver02' succeeded
CRS-2672: Attempting to start 'ora.mgmtdb' on 'myserver02'
CRS-2676: Start of 'ora.LISTENER_SCAN3.lsnr' on 'myserver02' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN2.lsnr' on 'myserver02' succeeded
CRS-2676: Start of 'ora.oc4j' on 'myserver02' succeeded
CRS-2676: Start of 'ora.mgmtdb' on 'myserver02' succeeded
CRS-2673: Attempting to stop 'ora.ARCH.dg' on 'myserver01'
CRS-2673: Attempting to stop 'ora.OCRV.dg' on 'myserver01'
CRS-2673: Attempting to stop 'ora.BACKUP.BACKUP.advm' on 'myserver01'
CRS-2673: Attempting to stop 'ora.CTRLDG1.dg' on 'myserver01'
CRS-2673: Attempting to stop 'ora.CTRLDG3.dg' on 'myserver01'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'myserver01'
CRS-2673: Attempting to stop 'ora.DATA1.dg' on 'myserver01'
CRS-2673: Attempting to stop 'ora.DATA2.dg' on 'myserver01'
CRS-2673: Attempting to stop 'ora.OGGDG.dg' on 'myserver01'
CRS-2673: Attempting to stop 'ora.REDOA.dg' on 'myserver01'
CRS-2677: Stop of 'ora.BACKUP.BACKUP.advm' on 'myserver01' succeeded
CRS-2673: Attempting to stop 'ora.BACKUP.dg' on 'myserver01'
CRS-2677: Stop of 'ora.OCRV.dg' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.DATA2.dg' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.CTRLDG3.dg' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.DATA1.dg' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.CTRLDG1.dg' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.ARCH.dg' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.REDOA.dg' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.OGGDG.dg' on 'myserver01' succeeded
CRS-2677: Stop of 'ora.BACKUP.dg' on 'myserver01' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'myserver01'
CRS-2677: Stop of 'ora.asm' on 'myserver01' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'myserver01'
CRS-2677: Stop of 'ora.ons' on 'myserver01' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'myserver01'
CRS-2677: Stop of 'ora.net1.network' on 'myserver01' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on..
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview