SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 3887
Session ID: 5644 Serial number: 5
Solution
After restore database another server appear this error, because on older server database version was 11.2.0.1.0 and on new server database version is 11.2.0.4.0, therefore we need:
1) open with upgrade option
2) upgrade database – it means run script $ORACLE_HOME/rdbms/admin/catupgrd.sql
3) shutdown clearly and startup
4) run script $ORACLE_HOME/rdbms/admin/utlrp.sql – for recompile all invalid PL/SQL packages
sqlplus / as sysdba
SQL> startup upgrade;
SQL> @/soft/app/oracle/product/11.2.0.4.0/dbhome_1//rdbms/admin/catupgrd.sql
SQL> shutdown immediate;
SQL> startup;
SQL> @/soft/app/oracle/product/11.2.0.4.0/dbhome_1//rdbms/admin/utlrp.sql
Test Server Implement Log:
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 7482626048 bytes
Fixed Size 2267792 bytes
Variable Size 6023021936 bytes
Database Buffers 1442840576 bytes
Redo Buffers 14495744 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> @/soft/app/oracle/product/11.2.0.4.0/dbhome_1//rdbms/admin/catupgrd.sql
.................
................running....
...............
DOC> Next restart for normal operation, and then run utlrp.sql to
DOC> recompile any invalid application objects.
DOC>
DOC> If the source database had an older time zone version prior to
DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade
DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
DOC> with Oracle.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> REM END OF CATUPGRD.SQL
SQL>
SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SQL> REM This forces user to start a new sqlplus session in order
SQL> REM to connect to the upgraded db.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bkuptest ~]$
[oracle@bkuptest ~]$
[oracle@bkuptest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 16 17:58:53 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 7482626048 bytes
Fixed Size 2267792 bytes
Variable Size 6023021936 bytes
Database Buffers 1442840576 bytes
Redo Buffers 14495744 bytes
Database mounted.
Database opened.
SQL> @/soft/app/oracle/product/11.2.0.4.0/dbhome_1//rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-05-16 17:59:33
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
...............
................
.....................
Function dropped.
Warning: XDB now invalid, could not find xdbconfig
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110:
data file 201: '/u01/app/oracle/oradata/remitdb/temp01.dbf'
ORA-01403: no data
found
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> exit
[oracle@bkuptest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 16 18:00:09 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
SQL>
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 3 thread 1: '/data/bkuptest/redo03a.log'
ORA-00312: online log 3 thread 1: '/data/bkuptest/redo03b.log'
Solution
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CLEARING
2 CLEARING
5 CLEARING
4 CLEARING
3 CLEARING_CURRENT
SQL>
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database clear logfile group 4;
Database altered.
SQL> alter database clear logfile group 5;
Database altered.
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 UNUSED
5 UNUSED
4 UNUSED
3 CURRENT
SQL>
SQL> alter database open resetlogs;
Database altered.
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/bkuptest/system01.dbf'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/16/2016 13:57:21
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 961622 and starting SCN of 649440238 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 961621 and starting SCN of 649432402 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 961620 and starting SCN of 649423457 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 961619 and starting SCN of 649413447 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 961618 and starting SCN of 649405456 found to restore
RMAN>
Solution
Copy following specified Archivelog (sequence 961618,961619,961620,961621,961622) to following Specified directory and recover database.
Archivelog directory for my case : /fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/
After copying archivelog to specified directory, run below recover database command from RMAN.
RMAN> RECOVER DATABASE;
Starting recover at 16-MAY-16
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
archived log for thread 1 with sequence 961619 is already on disk as file
/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961619_cmj84crz_.arc
archived log for thread 1 with sequence 961620 is already on disk as file
/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961620_cmj8kj68_.arc
archived log for thread 1 with sequence 961621 is already on disk as file
/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961621_cmj8xllx_.arc
archived log for thread 1 with sequence 961622 is already on disk as file
/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961622_cmj93x7z_.arc
archived log file name=/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961619_cmj84crz_.arc thread=1
sequence=961619
archived log file name=/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961620_cmj8kj68_.arc thread=1
sequence=961620
archived log file name=/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961621_cmj8xllx_.arc thread=1
sequence=961621
archived log file name=/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961622_cmj93x7z_.arc thread=1
sequence=961622
unable to find archived log
archived log thread=1 sequence=961623
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/16/2016 15:32:28
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 961623 and starting SCN of 649445097
Note: this error can be ignore as RMAN has applied all the available archivelog files and is now trying to apply a non-existent archivelog file.
This document will detail the steps to Rename OR Moving datafile using Oracle 11g R2.
The datafiles for the CREDITTESTDATA tablespace are following. We will rename following datafiles name.
SQL> select file_name from dba_data_files where tablespace_name='CREDITTESTDATA';
FILE_NAME
--------------------------------------------------------------------------------
/area_db/monthenddata01.dbf
/area_db/monthenddata02.dbf
/area_db/monthenddata03.dbf
/area_db/monthenddata04.dbf
/area_db/monthenddata05.dbf
/area_db/monthenddata06.dbf
/areadb2/monthenddata07.dbf
/area_db/monthenddata08.dbf
/area_db/monthenddata09.dbf
/areadb2/monthenddata10.dbf
10 rows selected.
Step-1: First take the tablespace offline in which the datafiles to rename or move.
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue May 10 11:54:43 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> alter tablespace credittestdata offline;
Tablespace altered.
Step-2: Rename/move the file using operating system commands.
$ mv /area_db/monthenddata01.dbf /area_db/credittestdata01.dbf
$ mv /area_db/monthenddata02.dbf /area_db/credittestdata02.dbf
$ mv /area_db/monthenddata03.dbf /area_db/credittestdata03.dbf
$ mv /area_db/monthenddata04.dbf /area_db/credittestdata04.dbf
$ mv /area_db/monthenddata05.dbf /area_db/credittestdata05.dbf
$ mv /area_db/monthenddata06.dbf /area_db/credittestdata06.dbf
$ mv /areadb2/monthenddata07.dbf /areadb2/credittestdata07.dbf
$ mv /area_db/monthenddata08.dbf /area_db/credittestdata08.dbf
$ mv /area_db/monthenddata09.dbf /area_db/credittestdata09.dbf
$ mv /areadb2/monthenddata10.dbf /areadb2/credittestdata10.dbf
Step-3: Rename datafiles using command "ALTER DATABASE RENAME FILE".
SQL> alter database rename file '/area_db/monthenddata01.dbf' to '/area_db/credittestdata01.dbf';
Database altered.
SQL> alter database rename file '/area_db/monthenddata02.dbf' to '/area_db/credittestdata02.dbf';
Database altered.
SQL> alter database rename file '/area_db/monthenddata03.dbf' to '/area_db/credittestdata03.dbf';
Database altered.
SQL> alter database rename file '/area_db/monthenddata04.dbf' to '/area_db/credittestdata04.dbf';
Database altered.
SQL> alter database rename file '/area_db/monthenddata05.dbf' to '/area_db/credittestdata05.dbf';
Database altered.
SQL> alter database rename file '/area_db/monthenddata06.dbf' to '/area_db/credittestdata06.dbf';
Database altered.
SQL> alter database rename file '/areadb2/monthenddata07.dbf' to '/areadb2/credittestdata07.dbf';
Database altered.
SQL> alter database rename file '/area_db/monthenddata08.dbf' to '/area_db/credittestdata08.dbf';
Database altered.
SQL> alter database rename file '/area_db/monthenddata09.dbf' to '/area_db/credittestdata09.dbf';
Database altered.
SQL> alter database rename file '/areadb2/monthenddata10.dbf' to '/areadb2/credittestdata10.dbf';
Database altered.
SQL>
Step-4: Bring the tablespace online.
SQL> alter tablespace credittestdata online;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name='CREDITTESTDATA';
FILE_NAME
--------------------------------------------------------------------------------
/area_db/credittestdata01.dbf
/area_db/credittestdata02.dbf
/area_db/credittestdata03.dbf
/area_db/credittestdata04.dbf
/area_db/credittestdata05.dbf
/area_db/credittestdata06.dbf
/areadb2/credittestdata07.dbf
/area_db/credittestdata08.dbf
/area_db/credittestdata09.dbf
/areadb2/credittestdata10.dbf
10 rows selected.
The ps (i.e., process status) command is used to provide information about the currently running processes, including their process identification numbers (PIDs).
Show all Server processes
ps -ef |grep oracledcpdb1 --here, dcpdb1 is instance name
Total count of server processes
ps -ef |grep oracledcpdb1 |grep -v grep |wc -l ---here, dcpdb1 is instance name
Show background processes
ps -ef |grep dcpdb1 --here, dcpdb1 is instance name
