Recovering database after loss of current controlfile

Recovering database after loss of current controlfile

In this scenario we will be recovering the database after loss of current control file. We assume that database is in archive log mode and all the datafile, online redo log files and archive log files are intact.
Same steps holds good even if the database is in no archive log mode.
1) Database is in archive log mode here. But archivelog mode does not matter. These steps holds good even if the database is not in archive log mode.

bash-2.05$ sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jun 12 05:51:04 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /dy/oracle/product/db10g/archive/htmldb
Oldest online log sequence 71
Next log sequence to archive 73
Current log sequence 73
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
2) Remove the control files from file system.
bash-2.05$ ls
htmldb redo02.log system01.dbf test_control2
htmldb01.dbf redo03.log temp01.dbf undotbs01.dbf
redo01.log sysaux01.dbf test_control1
bash-2.05$ rm test_control*
After doing this database will crash after some time, when ever any background process needs to update the control file, it wont find the same and DB will crash.
3) Create a “create control file script”. This can be obtained using “Alter database backup controlfile to trace” command if used some time back. Its a good practice to backup the control file to trace regularly when ever the structure if changed (Example when ever a datafile is added).
Create control file command is stored in crdb.sql
bash-2.05$ cat crdb.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “HTMLDB” NORESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/dy/oracle/product/db10g/dbf/redo01.log’ SIZE 100M,
GROUP 2 ‘/dy/oracle/product/db10g/dbf/redo02.log’ SIZE 100M,
GROUP 3 ‘/dy/oracle/product/db10g/dbf/redo03.log’ SIZE 100M
— STANDBY LOGFILE
DATAFILE
‘/dy/oracle/product/db10g/dbf/system01.dbf’,
‘/dy/oracle/product/db10g/dbf/undotbs01.dbf’,
‘/dy/oracle/product/db10g/dbf/sysaux01.dbf’,
‘/dy/oracle/product/db10g/dbf/htmldb01.dbf’
CHARACTER SET US7ASCII
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMPTS1 ADD TEMPFILE ‘/dy/oracle/product/db10g/dbf/temp01.dbf’
SIZE 20971520 REUSE AUTOEXTEND OFF;
4) Run the “Create control file” script crdb.sql.
bash-2.05$ sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jun 12 05:55:43 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> @crdb.sql
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 243276104 bytes
Database Buffers 822083584 bytes
Redo Buffers 6397952 bytes
Control file created.
Media recovery complete.
System altered.
Database altered.
Tablespace altered.
SQL>
We are done. Database is now up and running. Note that this is a complete recovery. There is no loss of data, because all the data files and redo log files were intact.

ORA-12712: new character set must be a superset of old character set

  ORA-12712: new character set must be a superset of old          character set             

  how to change oracle nls_characterset in 11g

Solution for ORA-12712: new character set must be a superset of old character set

Change Oracle character set from WE8MSWIN1252 to AL32UTF8 in oracle 11g R2, do the following steps:
 first Shut down the database, using either a SHUTDOWN IMMEDIATE .

[warning:Take  full backup of  your database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.]

Change Oracle character set from WE8MSWIN1252 to AL32UTF8  step by step:
 
 

SQL> SHUTDOWN IMMEDIATE .
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;--if fail this command just use internal_use

in case of failure of this command

SQL>Alter database character set INTERNAL_USE AL32UTF8;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT ;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
SQL>ALTER DATABASE OPEN;

SQL > select * from nls_database_parameters ORDER BY PARAMETER;



Practical Solution :

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  763363328 bytes
Fixed Size                  2929064 bytes
Variable Size             528485976 bytes
Database Buffers          226492416 bytes
Redo Buffers                5455872 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set


SQL> Alter database character set INTERNAL_USE AL32UTF8;

Database altered.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT ;
ORACLE instance started.

Total System Global Area  763363328 bytes
Fixed Size                  2929064 bytes
Variable Size             528485976 bytes
Database Buffers          226492416 bytes
Redo Buffers                5455872 bytes
Database mounted.
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only


SQL> alter database disable restricted session;
alter database disable restricted session

ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> alter system disable restricted session;
alter system disable restricted session
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only


SQL> alter database open;

Database altered.

expdp ora-39213: metadata processing is not available expdp 11g

expdp ora-39213: metadata processing is not available expdp 11g

 
In Windows,maybe missing, invalid some registry key, we need to check the registry entriesin windows machine to ensure that they (key) are in a valid status.  In case you have INVALID status in dba_registry, yu have check it and try to find a solution for it.IN THIS CASE when we try expdp but oracle throw this error ORA-39213 metadata processing is not available . java, xml file error .

Symptoms

When running expdp the following errors are raised:

ORA-39006: internal error
ORA-39213: Metadata processing is not available

This error was detected while executing the DataPump in any platform.

Cause

DataPump export uses DBMS_METADATA_UTIL package but this package does not successfully run. Executing sys.dbms_metadata_util.load_stylesheets procedure failed with following error:

ORA-31609: error loading file "kualter.xsl" from file system directory "/rdbms/xml/xsl"
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 1807
ORA-06512: at line 1

This package needs XML being installed and XML needs JAVA being installed.

The select from view dba_registry shows neither Java nor XML were installed:

SQL> SELECT comp_id, version, status FROM dba_registry; 

COMP_ID                      VERSION                     STATUS
---------------------------  --------------------------- -----------
CATALOG                      10.2.0.2.0                  VALID
CATPROC                      10.2.0.2.0                  VALID

Solution

1. Install JVM and XML  executing the following scripts from sqlplus:

SQL> connect / as sysdba
SQL> @$ORACLE_HOME/javavm/install/initjvm.sql

SQL> connect / as sysdba
SQL> @$ORACLE_HOME/xdk/admin/initxml.sql

SQL> connect / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/catjava.sql

SQL> connect / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

2. Check if the scripts successfully ran:

select comp_name, version, status from dba_registry;

COMP_NAME                          VERSION                     STATUS
---------------------------------- --------------------------- -------
Oracle Database Catalog Views      10.2.0.2.0                 VALID
Oracle Database Packages and Types 10.2.0.2.0              VALID
JServer JAVA Virtual Machine       10.2.0.2.0                  VALID
Oracle XDK                         10.2.0.2.0                             VALID
Oracle Database Java Packages      10.2.0.2.0                  VALID

3. Run again sys.dbms_metadata_util.load_stylesheets from sqlplus:

connect / as sysdba
execute sys.dbms_metadata_util.load_stylesheets;

 

 

expdp bbb/ddd@ORCL schemas=niger directory=data_pump_dir dumpfile=140617.dmp logfile=exp140617.log