Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Physical Standy Database, Standy Database clinrpt fail to add temp files on physical standby
jenkins
post Aug 21 2017, 04:53 PM
Post #1


Member
**

Group: Members
Posts: 12
Joined: 3-March 16
Member No.: 51,207



After we have cloned prod to ROPROD using the cerner scripts, the database(ROPROD) will not let us add Temp files to the temp tablespace.
Error Code/Message: Database error: ORA-01157: cannot identify/lock data file 4097 - see DBWR trace file ORA-01110: data

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database open for read-only access

ORA-06512: at line 1066

ORA-16000: database open for read-only access



ORA-00604 ORA-16000
ORA-06512 ORA-16000



ALTER TABLESPACE TEMP ADD TEMPFILE '+DG_CISRPT_REDO_01' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 30720M;





After we have cloned prod to ROPROD using the cerner scripts, the database(ROPROD) will not let us add Temp files to the temp tablespace.
Error Code/Message: Database error: ORA-01157: cannot identify/lock data file 4097 - see DBWR trace file ORA-01110: data

Connected as sysdba in read-only mode
SQL>
ALTER TABLESPACE TEMP ADD TEMPFILE '+DG_CISRPT_REDO_01' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 30720M;


ORA-00604: error occurred at recursive SQL level 1


ORA-16000: database open for read-only access


ORA-06512: at line 1066


ORA-16000: database open for read-only access
Go to the top of the page
 
+Quote Post
burleson
post Aug 22 2017, 07:26 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,529
Joined: 26-January 04
Member No.: 13



Hi Jenkins,

What is a cerner script?

What does the trace file say?

The error is being thrown because the data dictionary is in read only mode!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
jenkins
post Aug 22 2017, 08:19 AM
Post #3


Member
**

Group: Members
Posts: 12
Joined: 3-March 16
Member No.: 51,207



It is an in house script but really doesn't have anything to do with the error we are having. Below is part of what the tracefille says.....


Trace file /u02/diag/rdbms/clinrpt/clinrpt/trace/clinrpt_ora_9809.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
ORACLE_HOME = /u01/oracle/product/11.1.0.7/db
System name: Linux
Node name: pplora01.pikin.ndiba.njaka
Release: 2.6.18-371.6.1.el5
Version: #1 SMP Tue Feb 18 11:42:11 EST 2014
Machine: x86_64
Instance name: clinrpt
Redo thread mounted by this instance: 1
Oracle process number: 37
Unix process pid: 9809, image: oracle@pplora01.pikin.ndiba.njaka


*** 2017-08-15 22:45:02.678
*** SESSION ID:(4356.547) 2017-08-15 22:45:02.678
*** CLIENT ID:() 2017-08-15 22:45:02.678
*** SERVICE NAME:(clinrpt.pikin.ndiba.njaka) 2017-08-15 22:45:02.678
*** MODULE NAME:(python3.6@pdledw01.pikin.ndiba.njaka (TNS V1-V3) 2017-08-15 22:45:02.678
*** ACTION NAME:() 2017-08-15 22:45:02.678

kpoataf-fetch: [1403]
Trace file /u02/diag/rdbms/clinrpt/clinrpt/trace/clinrpt_ora_9809.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
ORACLE_HOME = /u01/oracle/product/11.1.0.7/db
System name: Linux
Node name: pplora01.pikin.ndiba.njaka
Release: 2.6.18-371.6.1.el5
Version: #1 SMP Tue Feb 18 11:42:11 EST 2014
Machine: x86_64
Instance name: clinrpt
Redo thread mounted by this instance: 1
Oracle process number: 97
Unix process pid: 9809, image: oracle@pplora01.pikin.ndiba.njaka


*** 2017-08-17 08:35:04.392
*** SESSION ID:(4218.5768) 2017-08-17 08:35:04.392
*** CLIENT ID:() 2017-08-17 08:35:04.392
*** SERVICE NAME:(clinrpt.pikin.ndiba.njaka) 2017-08-17 08:35:04.392
*** MODULE NAME:(tabprotosrv.exe) 2017-08-17 08:35:04.392
*** ACTION NAME:() 2017-08-17 08:35:04.392

kpoataf-fetch: [1403]

*** 2017-08-17 08:36:08.218
KQRCMT: Write failed with error=604 po=0x24e3389c10 cid=13
diagnostics : cid=13 hash=8506eb4b flag=2a
KQRCMT: Write failed with error=604 po=0x24e3389c10 cid=13
diagnostics : cid=13 hash=e5387e92 flag=2a
KQRCMT: Write failed with error=604 po=0x24e3389c10 cid=13
diagnostics : cid=13 hash=8506eb4b flag=2a
KQRCMT: Write failed with error=604 po=0x24e3389c10 cid=13
diagnostics : cid=13 hash=e5387e92 flag=2a

*** 2017-08-17 08:36:13.725
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
ORA-01157: cannot identify/lock data file 4100 - see DBWR trace file
ORA-01110: data file 4100: '+DG_CISRPT_DATA_01/prod/tempfile/temp_07.dbf'
Dump of memory from 0x000000249494B5E0 to 0x000000249494C45F
249494B5E0 454C4553 2A205443 4F52460A 0A28204D [SELECT *.FROM (.]
249494B5F0 69772020 63206874 6D5F6574 206E6961 [ with cte_main ]
Go to the top of the page
 
+Quote Post
burleson
post Aug 22 2017, 11:32 AM
Post #4


Advanced Member
***

Group: Members
Posts: 13,529
Joined: 26-January 04
Member No.: 13



Hi,

It is a write failed error.

Are any tablespaces in read only mode?

Is the database open?

SQL> startup mount
ORACLE instance started.

SQL> alter database open read only;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ ONLY


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
jenkins
post Aug 22 2017, 01:12 PM
Post #5


Member
**

Group: Members
Posts: 12
Joined: 3-March 16
Member No.: 51,207



The database is open and in read only mode. The issue i am having is adding a tempfile to a temp tablespace. Everytime i apply the command to add tempfile i get the error below.



Connected as sysdba in read-only mode
SQL>
ALTER TABLESPACE TEMP ADD TEMPFILE '+DG_CISRPT_REDO_01' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 30720M;


ORA-00604: error occurred at recursive SQL level 1


ORA-16000: database open for read-only access


ORA-06512: at line 1066


ORA-16000: database open for read-only access
Go to the top of the page
 
+Quote Post
burleson
post Aug 23 2017, 05:16 AM
Post #6


Advanced Member
***

Group: Members
Posts: 13,529
Joined: 26-January 04
Member No.: 13



Hi Jenkins,

Adding a tempfile updates the data dictionary, and the database must be open for read write access!

Alter database open read write;

Good luck!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd September 2017 - 05:29 PM