Renaming or Moving Oracle Files
https://oracle-base.com/articles/misc/renaming-or-moving-oracle-files
Problem Description
Users cant able to login to database server. Though it allow sysdba to login, it does not allow to run any query. Sometimes it will show database is connected to an idle instance. But when we startup database it shall throw error ORA-01081: cannot start already-running .
[oracle@mezon01 ~]$ sqlplus sys/System_2018@IIRDB
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 08:03:07 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server
Enter user-name: sys
Enter password:
ERROR:
ORA-00020: maximum number of processes (1500) exceeded
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@mezon01 ~]$ sqlplus -prelim “/as sysdba”
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 08:04:06 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> show parameter sga_t;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup;
ORA-01012: not logged on
SQL> shutdown immediate;
ORA-01012: not logged on
SQL> show parameter sga_max;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
SQL> EXİT
SP2-0042: unknown command “EXİT” – rest of line ignored.
SQL> exit
Disconnected from ORACLE
[oracle@mezon01 ~]$ ps -ef | grep IIRDB
oracle 6096 1 0 08:05 ? 00:00:00 ora_pmon_IIRDB
oracle 6098 1 0 08:05 ? 00:00:00 ora_psp0_IIRDB
oracle 6136 1 1 08:05 ? 00:00:01 ora_vktm_IIRDB
oracle 6140 1 0 08:05 ? 00:00:00 ora_gen0_IIRDB
oracle 6142 1 0 08:05 ? 00:00:00 ora_diag_IIRDB
oracle 6144 1 0 08:05 ? 00:00:00 ora_dbrm_IIRDB
oracle 6146 1 0 08:05 ? 00:00:00 ora_ping_IIRDB
oracle 6148 1 0 08:05 ? 00:00:00 ora_acms_IIRDB
oracle 6150 1 0 08:05 ? 00:00:00 ora_dia0_IIRDB
oracle 6152 1 0 08:05 ? 00:00:00 ora_lmon_IIRDB
oracle 6162 1 0 08:05 ? 00:00:00 ora_lmd0_IIRDB
oracle 6164 1 0 08:05 ? 00:00:00 ora_rms0_IIRDB
oracle 6166 1 0 08:05 ? 00:00:00 ora_lmhb_IIRDB
oracle 6172 1 4 08:05 ? 00:00:04 ora_mman_IIRDB
oracle 6174 1 0 08:05 ? 00:00:00 ora_dbw0_IIRDB
oracle 6176 1 0 08:05 ? 00:00:00 ora_dbw1_IIRDB
oracle 6178 1 0 08:05 ? 00:00:00 ora_dbw2_IIRDB
oracle 6180 1 0 08:05 ? 00:00:00 ora_dbw3_IIRDB
oracle 6182 1 0 08:05 ? 00:00:00 ora_lgwr_IIRDB
oracle 6184 1 0 08:05 ? 00:00:00 ora_ckpt_IIRDB
oracle 6186 1 0 08:05 ? 00:00:00 ora_smon_IIRDB
oracle 6188 1 0 08:05 ? 00:00:00 ora_reco_IIRDB
oracle 6190 1 0 08:05 ? 00:00:00 ora_rbal_IIRDB
oracle 6192 1 0 08:05 ? 00:00:00 ora_asmb_IIRDB
oracle 6194 1 0 08:05 ? 00:00:00 ora_mmon_IIRDB
oracle 6198 1 0 08:05 ? 00:00:00 ora_mmnl_IIRDB
oracle 6200 1 0 08:05 ? 00:00:00 ora_d000_IIRDB
oracle 6202 1 0 08:05 ? 00:00:00 ora_s000_IIRDB
oracle 6204 1 0 08:05 ? 00:00:00 ora_mark_IIRDB
oracle 6206 1 0 08:05 ? 00:00:00 ora_ocf0_IIRDB
oracle 6325 1 0 08:05 ? 00:00:00 ora_o000_IIRDB
oracle 10089 28710 0 08:07 pts/1 00:00:00 grep IIRDB
oracle 20902 25965 0 May23 ? 00:04:20 /oracle/product/11.2.0.4/db/jdk/bin/java -server -Xmx384M -XX:MaxPermSize=400M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/oracle/product/11.2.0.4/db -Doracle.home=/oracle/product/11.2.0.4/db/oc4j -Doracle.oc4j.localhome=/oracle/product/11.2.0.4/db/mezon01_IIRDB/sysman -DEMSTATE=/oracle/product/11.2.0.4/db/mezon01_IIRDB -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/jazn.xml -Djava.security.policy=/oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/java2.policy -Djavax.net.ssl.KeyStore=/oracle/product/11.2.0.4/db/sysman/config/OCMTrustedCerts.txt-Djava.security.properties=/oracle/product/11.2.0.4/db/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/oracle/product/11.2.0.4/db/mezon01_IIRDB -Dsysman.md5password=true -Drepapi.oracle.home=/oracle/product/11.2.0.4/db -Ddisable.checkForUpdate=true -Doracle.sysman.ccr.ocmSDK.websvc.keystore=/oracle/product/11.2.0.4/db/jlib/emocmclnt.ks -Dice.pilots.html4.ignoreNonGenericFonts=true -Djava.awt.headless=true -jar /oracle/product/11.2.0.4/db/oc4j/j2ee/home/oc4j.jar -config /oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/server.xml
oracle 25965 1 0 May23 ? 00:00:12 /oracle/product/11.2.0.4/db/perl/bin/perl /oracle/product/11.2.0.4/db/bin/emwd.pl dbconsole /oracle/product/11.2.0.4/db/mezon01_IIRDB/sysman/log/emdb.nohup
————————————————————————————————————————————-
[oracle@mezon01 ~]$ sysresv
IPC Resources for ORACLE_SID “IIRDB” :
Shared Memory:
ID KEY
100270098 0x00000000
100302867 0x00000000
100335643 0x14203f64
Semaphores:
ID KEY
136445992 0xf1e96e54
136478764 0xf1e96e55
136511533 0xf1e96e56
136544302 0xf1e96e57
136577071 0xf1e96e58
136609840 0xf1e96e59
136642609 0xf1e96e5a
136675378 0xf1e96e5b
136708147 0xf1e96e5c
Oracle Instance alive for sid “IIRDB”
[oracle@mezon01 ~]$ ipcrm -m 100270098
[oracle@mezon01 ~]$ ipcrm -m 100302867
[oracle@mezon01 ~]$ ipcrm -m 100335643
[oracle@mezon01 ~]$
[oracle@mezon01 ~]$
Changes
Oracle has been forcefully shutdown at OS level or crashed.
CAUSE
An orphaned shared memory segment belonging to the ORACLE_SID still exists from a previous instance startup.
The command
ps -ef | grep $ORACLE_SID
shows no processes but with ORACLE_SID set the Oracle ‘sysresv’ utility shows a shared memory segment for a non-existing instance, e.g.
$ sysresv
IPC Resources for ORACLE_SID “IIRDB” :
Shared Memory:
ID KEY
100270098 0x00000000
100302867 0x00000000
100335643 0x14203f64
Semaphores:
ID KEY
No semaphore resources used
Oracle Instance not alive for sid “IIRDB”
Solution
On OS level, remove the orphaned shared memory segment using:
ipcrm -m <problem shared memory id>
ipcrm -m 100270098
ipcrm -m 100302867
ipcrm -m 100335643
$sqlplus ‘/as sysdba’
Connected to an idle instance.
SQL> startup Oracle instance started
Total System Global Area 10689474560 bytes
Fixed Size 2237776 bytes
Variable Size 6375344816 bytes
Database Buffers 4294967296 bytes
Redo Buffers 16924672 bytes
Oracle Instance Started.
Oracle Database Opened.
Cause: There are some client shadow processes hanging. Although the lk<SID> file is deleted the hanging processes still have a lock on the open file handle. This prevents the database to startup although a new lk<SID> file can be created successfully.
An oracle process (background or shadow process) that exists while the instance is not started (crashed or not cleanly stopped) can have a lock on a file while this file is actually removed from the system. This is because on UNIX there is still a lock on the open file handle.
Solution:
Verify if there are existing oracle processes for the database:
[oracle@servername ~]$ ps -ef | grep $ORACLE_SID
oracle 1525 1 0 10:13 ? 00:00:03 ora_j000_GRIDDB3
oracle 1527 1 0 10:13 ? 00:00:01 ora_j001_GRIDDB3
oracle 4198 1 0 10:27 ? 00:00:00 oracleGRIDDB3 (LOCAL=NO)
oracle 7479 1 0 May30 ? 00:00:00 oracleGRIDDB3 (LOCAL=NO)
.
.
Kill the hanging processes to release the lock on the file handle:
[oracle@servername ~]$ kill -9 `ps -ef | grep $ORACLE_SID | grep -v grep | awk '{print $2}'`
eg: kill -9 `ps -ef | grep testcdb | grep -v grep | awk '{print $2}'`
[oracle@sservername ~]$ ps -ef | grep $ORACLE_SID
oracle 11990 11927 0 10:50 pts/1 00:00:00 grep GRIDDB3
ORA-01194: file 1 needs more recovery to be consistent
[ _allow_resetlogs_corruption ]
===Problem ====
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
===========================================================
RMAN-03002: failure of Duplicate Db command at 08/25/2021
18:25:45
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: Oracle error from auxiliary database: ORA-01152:
file 3 was not restored from a sufficiently old backup
ORA-01110: data file 3:
'/u03/oradata/TESTCDB/sslcdb/datafile/sysaux.291.1050880731'
or
SQL> alter database open resetlogs;
alter database open resetlogs
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/RTS_NEW/system_new.dbf'
====== Solution =============================
Change “_allow_resetlogs_corruption” parameter to TRUE and undo_management parameter to MANUAL:
SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;
SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;
SQL> STARTUP MOUNT
SQL> alter database open resetlogs;
Database altered.
--------------------------------------------------
SQL> shutdown immediate
SQL> startup
-------------------------------------------------
SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= FALSE SCOPE = SPFILE;
TNS-12516 TNS:listener could not find instance with matching protocol stack Cause: PMON update listener with information about instance such as load and dispatcher information. PROCESS parameter determines the maximum load for dedicated connection in database. The interval at which PMON provides SERVICE_UPDATE information differs according to the workload of the instance. The maximum interval between these service updates is 10 minutes. When the threshold exceeds the limit then listener become “Blocked” and no new session can be made since it refusing incoming connection. Once, listener gets the information from PMON that the thresholds are below the configured limit then listener resume accepting connection. Since SERVICE_UPDATE can take maximum 10 minutes, therefore, there can be a difference between the current instance load according to the listener and the actual instance load. The listener counts the number of connections it has established to the instance but does not immediately get information about connections that have terminated. Solution: · check if the number of current connections by using the following command in Unix: ps -ef | grep oracleSID | grep -v grep | wc –l
· Check process parameter in database
SQL> select * from v$version;
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 – Production
SQL> show parameter processes
NAME TYPE VALUE ------------------ ----------- ------- processes integer 200
SQL> select count(*) from v$process;
COUNT(*) ---------- 199
Increase process: processes=x session=(1.5 * PROCESSES) + 22 SQL> alter system set processes=300 scope=spfile; Processes is not a dynamic parameter, so you need to restart database to take the effect. |
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 07/08/2014 18:12:40 RMAN-05501: aborting duplication of target database RMAN-05541: no archived logs found in target database |
run { set until time "to_date('08-OCT-2014 10:15:00','DD-MON-YYYY HH24:MI:SS')"; DUPLICATE DATABASE TO targetDB BACKUP LOCATION '/backup_Location' NOREDO; } |
In Standby Environment of dataguard Server, We are getting following alert log error:
RFS[4]: No standby redo logfiles created for T-1
RFS[4]: Opened log for T-1.S-57586 dbid 618415567 branch 953210132
It show that No standby redo log file of thread 1 is present as per message, it also mention Thread number as 1.
On checking the Primary and Secondary database with thread column in views:
1. Check Primary Redo Thread Number:
SELECT thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS ------- ------ --------- --------- --- ---------- 1 1 57586 209715200 NO CURRENT 1 2 57584 209715200 YES INACTIVE 1 3 57585 209715200 YES INACTIVE
2. Check Standby Thread number for Standby redo logs configured for DR Server.
SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS ------- ------ --------- ---------- --- ---------- 0 4 0 52428800 YES UNASSIGNED 0 5 0 52428800 YES UNASSIGNED 0 6 0 52428800 YES UNASSIGNED 0 7 0 52428800 YES UNASSIGNED 0 8 0 52428800 YES UNASSIGNED 0 9 0 52428800 YES UNASSIGNED
Note:Both Thread Number is different in value, so we need to drop and recreate the Standby with specify the THREAD caluse.
3. Stop the dataguard recovery process in Standby database:
-- for standby db which is under recovery, recovery needs to be stopped first
alter database recover managed standby database cancel;
4. Drop the existing Standby redo files:
Note: There is rule to configure standby redo log file Standby redolog file: N+1 and N stand for redo log files in primary.
In this example we configure 6 standby redo files.
ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 8;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 9;
5. Create new Standby logfile with THREAD Clauses.
alter database add standby logfile thread 1 group 4 size 200m;
alter database add standby logfile THREAD 1 group 4 (‘D:\ORACLEXE\STANDBYREDO04.log’) SIZE 200M;
alter database add standby logfile THREAD 1 group 5 (‘D:\ORACLEXE\STANDBYREDO05.log’) SIZE 200M;
alter database add standby logfile THREAD 1 group 6 (‘D:\ORACLEXE\STANDBYREDO05.log’) SIZE 200M;
alter database add standby logfile THREAD 1 group 7 (‘D:\ORACLEXE\STANDBYREDO06.log’) SIZE 200M;
–Example for two members
–alter database add standby logfile THREAD 1 group 5 (‘D:\ORACLEXE\STANDBYREDO01A.log’,’D:\ORACLEXE\STANDBYREDO01B.log’) SIZE 200M;
–Example for ASM
–alter database add standby logfile THREAD 1 group 7 (‘+DATA(ONLINELOG)’,’+FRA(ONLINELOG)’) SIZE 200M;
Note: in my case standby redo size(50M) is different with primary redo size(200M). so I corrected that one also while created new standby.
6. Now verify the thread number and redo log
SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
7. Start the Recovery of the dataguard in oracle.
alter database recover managed standby database disconnect from session;
