Example 2 solution ORA

VCAppliance stops the Oracle database in standby mode. Oracle cannot be started again. Use the Starup command in SQLPLUS. The following prompt appears: since a ORA-03113 error is a common error prompt, it gives very little information. So Debug step by step, because startup is equivalent to a set of three commands, it is executed step by step

VC Appliance stops the Oracle database in standby mode. Oracle cannot be started again. Use the Starup command in SQLPLUS. The following prompt appears: since a ORA-03113 error is a common error prompt, it gives very little information. So Debug step by step, because startup is equivalent to a set of three commands, it is executed step by step

VC Appliance stops the Oracle database in standby mode. Oracle cannot be started again. Use the Starup command in SQLPLUS. The following prompt is displayed:

Since ORA-03113 errors are a common error prompt, very little information can be given.

So Debug is performed step by step. Since startup is equivalent to a set of three commands, it is executed step by step.

Run

SQL> startup nomount

No obvious failure information is found, indicating that it is okay to start the database instance. Then, perform step 2.

SQL> alter database mount;

Fault

It seems that when a fault occurs when a database file is mounted, this step mainly allows specific maintenance operations, such as renaming, adding, deleting, or renaming a data file to redo log files, enable and disable the redo archiving option to perform full database recovery. It does not allow general access to the database ."

Find the $ ORACLE_HOME/rdbms/log directory and use ls-ltr to find the latest log file. In this example, sales_ora_27010.trc

It seems that the olr. loc and ocr. loc files cannot be opened. But this file does not exist on my machine. I searched online and found that these two files are related to the Oracle cluster. I use a single node here. I do not need the Oracle Local Registry.

There are no particularly useful prompts for searching on the Internet, but I found another problem, that is, the log file I was looking for may be incorrect. I found the backup virtual machine that was exported during the original installation and used commands

SQL> show parameter background

Find the correct Log Path/usr/local/oracle/diag/rdbms/oracle/sales/trace

View the logs at startup.

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

...

Wed Aug 07 15:37:46 2013

SMON started with pid = 13, OS id = 30687

Wed Aug 07 15:37:46 2013

RECO started with pid = 14, OS id = 30689

Wed Aug 07 15:37:46 2013

MMON started with pid = 15, OS id = 30691

Starting up 1 dispatcher (s) for network address '(ADDRESS = (PARTIAL = YES) (PROTOCOL = TCP ))'...

Wed Aug 07 15:37:46 2013

MMNL started with pid = 16, OS id = 30693

Starting up 1 shared server (s )...

ORACLE_BASE from environment =/usr/local/oracle

Wed Aug 07 15:37:46 2013

ALTER DATABASE MOUNT

USER (ospid: 30698): terminating the instance

Instance terminated by USER, pid = 30698

Retry multiple times, most of the results are similar. Find the latest trc file in the same log directory and find the following content:

[Root @ localhost trace] # vi sales_ora_20498.trc

Release: 2.6.32-279. el6.x86 _ 64

Version: #1 SMP Fri Jun 22 12:19:21 UTC 2012

Machine: x86_64

Instance name: sales

Redo thread mounted by this instance: 0

Oracle process number: 19

Unix process pid: 20498, image: oracle@localhost.localdomain (TNS V1-V3)

* ** 09:23:49. 818

* ** Session id: (125.37) 09:23:49. 818

* ** Client id :() 09:23:49. 818

* ** Service name :() 09:23:49. 818

* Module name :( sqlplus@localhost.localdomain (TNS V1-V3) 09:23:49. 818

* ** Action name :() 09:23:49. 818

Error: kccpb_sanity_check_2

Control file sequence number mismatch!

Fhcsq: 38637 bhcsq: 38638 cfn 0

* ** 09:23:49. 819

USER (ospid: 20498): terminating the instance

From the above, the control file is faulty.

Now re-create the control file. First, find an example of the relevant parameters in the log file and find them in the alert_sales.log file.

Create controlfile reuse set database "oracle"

MAXINSTANCES 8

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

Datafile

'/Usr/local/oradata/ora11g/ORA11G/oracle/system01.dbf ',

'/Usr/local/oradata/ora11g/ORA11G/oracle/sysaux01.dbf ',

'/Usr/local/oradata/ora11g/ORA11G/oracle/undotbs01.dbf ',

'/Usr/local/oradata/ora11g/ORA11G/oracle/users01.dbf'

Logfile group 1 ('/usr/local/oradata/ora11g/ORA11G/oracle/redo01.log') SIZE 51200 K,

GROUP 2 ('/usr/local/oradata/ora11g/ORA11G/oracle/redo02.log') SIZE 51200 K,

GROUP 3 ('/usr/local/oradata/ora11g/ORA11G/oracle/redo03.log') SIZE 51200 K RESETLOGS

WARNING: Default Temporary Tablespace not specified in create database command

Default Temporary Tablespace will be necessary for a locally managed database in future release

Successful mount of redo thread 1, with mount id 1691519286

Completed: Create controlfile reuse set database "oracle"

Then, based on the script template in the article "how to obtain the control file creation script and create the script" on the Internet

STARTUP NOMOUNT

Create controlfile reuse database "PRIMARY" NORESETLOGS ARCHIVELOG

-SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES 5

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 1

# MAXLOGHISTORY 226

LOGFILE

GROUP 1'/opt/oracle/oradata/primary/redo01.log' SIZE 10 M,

GROUP 2'/opt/oracle/oradata/primary/redo02.log 'SIZE 10 M,

GROUP 3 '/opt/oracle/oradata/primary/redo03.log' SIZE 10 M

-STANDBY LOGFILE

DATAFILE

'/Opt/oracle/oradata/primary/system01.dbf ',

'/Opt/oracle/oradata/primary/undotbs01.dbf ',

'/Opt/oracle/oradata/primary/users01.dbf'

Character set ZHS16GBK

;

RECOVER DATABASE

Alter system archive log all;

Alter database open;

Alter tablespace temp add tempfile '/opt/oracle/oradata/primary/temp01.dbf'

SIZE 41943040 reuse autoextend on next 655360 MAXSIZE 32767 M;

Run startup nomount in SQL>, and then paste the following script into SQL>

Create controlfile reuse database "oracle" NORESETLOGS ARCHIVELOG

-SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 1

LOGFILE

GROUP 1'/usr/local/oradata/ora11g/ORA11G/oracle/redo01.log' SIZE 51200 K,

GROUP 2'/usr/local/oradata/ora11g/ORA11G/oracle/redo02.log 'SIZE 51200 K,

GROUP 3 '/usr/local/oradata/ora11g/ORA11G/oracle/redo03.log' SIZE 51200 K

-STANDBY LOGFILE

DATAFILE

'/Usr/local/oradata/ora11g/ORA11G/oracle/vpx01.dbf ',

-'/Usr/local/oradata/ora11g/ORA11G/oracle/temp01.dbf ',

'/Usr/local/oradata/ora11g/ORA11G/oracle/system01.dbf ',

'/Usr/local/oradata/ora11g/ORA11G/oracle/sysaux01.dbf ',

'/Usr/local/oradata/ora11g/ORA11G/oracle/undotbs01.dbf ',

'/Usr/local/oradata/ora11g/ORA11G/oracle/users01.dbf'

In the above script, I tried several times, one is the log size, I want to change to 10 M, but it is not successful, and the other is the prompt that temp01.dbf is not a valid database file, I only commented it out. I can only refer to the online script to run it step by step.

The last sentence

Alter tablespace temp add tempfile '/usr/local/oradata/ora11g/ORA11G/oracle/temp01.dbf' SIZE 20480 k reuse autoextend on next 640 K MAXSIZE UNLIMITED

I also found it from the log file.

Based on the results shown above, the recovery is successful.

Restart the operating system first. Start Oracle manually. Everything is normal, but the corresponding VCSA cannot be started. Use the OEM to check that there is a data block error.

As you can see, ORA-01578 error, File Corruption, Block is 45953.

Run the dbv command to check the file

[Oracle @ localhost ~] $ Dbv FILE = '/usr/local/oradata/ora11g/ORA11G/oracle/vpx01.dbf' BLOCKSIZE = 8192

The following result is displayed:

We can see that there are 452 blocks marked as upts.

Then, query the damaged content:

Select tablespace_name, segment_type, owner, segment_name From dba_extents Where file_id = 5 and 45953 between block_id and block_id + blocks-1;

The result is as follows:

It indicates that the LOBSEGMENT is damaged.

Run the command to find the damaged table name and column name.

Select table_name, column_name from dba_lobs where segment_name = 'sys _ LOB0000075100C00016 $ 'and owner = 'vpxadmin ';

Result:

Use

Select count (*) from VPXADMIN. VPX_RESOURCE_POOL;

We found that this table only contains eight rows of data.

ROWID

----------

AAASVcAAFAAACfjAAA 8

AAASVcAAFAAACfkAAB 523

AAASVcAAFAAACfkAAA 816

AAASVcAAFAAACfkAAC 826

AAASVcAAFAAACfkAAD 833

AAASVcAAFAAACfkAAE 839

AAASVcAAFAAACflAAB 1212

AAASVcAAFAAACfjAAC 1601

One row and one row

Select * from VPXADMIN. VPX_RESOURCE_POOL where id & gt; 1212;

This record with ID = 1212 is damaged.

Delete from VPXADMIN. VPX_RESOURCE_POOL where id = 1212;

After the table is deleted, the table can be accessed normally. The result of DBV check is no different.

Shutdown the immadiate database, prompting that there is no Commit. It seems that the deletion was not successful, so you can directly enter commit under the SQL command line to submit the modification. Then you can close the database. The check for restarting the database DBV has not changed, which means this is not enough.

After thinking for a long time, there is no clue. Go back to the OEM page and select "execute recovery" in "availability" to see if the tablespace VPX can be restored to before January 1, August 3.

After half a day, the system prompts that the operation failed and the VPX is offline, but it cannot be connected in the graphic interface. The problem is not complicated. In the SQL environment, execute

Recover datafile 5

Alter database datafile 5 online;

Alter tablespace VPX online;

It can be online.

Back to the original issue.

At present, there are two research directions. One is to check the log and the location and cause of the error because the vCenter server cannot start the service. Start with DBV errors.

Start with DBV and search for this article online. You may find the wrong file and block based on the error message from DBV to find the corresponding table.

DBV 201 Error Block information in the following section

...

DBV-00201: Block, DBA 21044390, marked upt for invalid redo application

DBV-00201: Block, DBA 21044391, marked upt for invalid redo application

DBV-00201: Block, DBA 21044393, marked upt for invalid redo application

DBV-00201: Block, DBA 21044394, marked upt for invalid redo application

DBV-00201: Block, DBA 21044395, marked upt for invalid redo application

DBV-00201: Block, DBA 21044397, marked upt for invalid redo application

DBV-00201: Block, DBA 21044398, marked upt for invalid redo application

DBV-00201: Block, DBA 21044399, marked upt for invalid redo application

DBV-00201: Block, DBA 21044401, marked upt for invalid redo application

DBV-00201: Block, DBA 21044402, marked upt for invalid redo application

DBV-00201: Block, DBA 21044403, marked upt for invalid redo application

DBV-00201: Block, DBA 21044406, marked upt for invalid redo application

DBV-00201: Block, DBA 21044407, marked upt for invalid redo application

DBV-00201: Block, DBA 21044410, marked upt for invalid redo application

DBV-00201: Block, DBA 21044411, marked upt for invalid redo application

DBV-00201: Block, DBA 21044412, marked upt for invalid redo application

DBV-00201: Block, DBA 21044414, marked upt for invalid redo application

DBV-00201: Block, DBA 21044415, marked upt for invalid redo application

According to the final display, there should be 452 blocks. This is part of the block, because the terminal display is incomplete.

Test the last one.

Select dbms_utility.data_block_address_file (21044415) from dual;

Select dbms_utility.data_block_address_blocks (21044415) from dual;

The result is File 5 and Block 72895. Reuse

Select tablespace_name, segment_type, owner, segment_name From dba_extents Where file_id = 5 and 72895 between block_id and block_id + blocks-1;

Get the Segment name SYS_LOB0000075068C00059 $ use

Select table_name, column_name from dba_lobs where segment_name = 'sys _ LOB0000075068C00059 $ 'and owner = 'vpxadmin'

The table name is VPX_HOST. The list is RESOURCE_INFO. Test the table:

Here, a block 10309 is displayed. The same method is used to locate the CAPABILITY of another column in The VPX_HOST table. This method can obviously find the fault table, but it is not helpful for repair, because the number is too large, there is no way to handle it manually.

Follow the restoration method mentioned above to create a table first

Create table partition upted_data (partition upted_rowid rowid );

Then, paste the following script code at the SQL prompt and run it.

Set concat off

Declare

Error_1578 exception;

Pragma exception_init (error_1578,-1578 );

N number;

Begin

For cursor_lob in (select rowid r, & lob_column from & table_owner. & table_with_lob) loop

Begin

N: = dbms_lob.instr (cursor_lob. & lob_column, hextoraw ('000000 ′));

Exception

When error_1578 then

Insert into upted_data values (cursor_lob.r );

Commit;

End;

End loop;

End;

Enter the variable values as prompted. The column name is RESOURCE_INFO and the owner is VPXADMIN. The table name is VPX_HOST. The script writes the ROWID of the problematic row to the table created in the preceding table. In the above Code, hextoraw ('000000') is a bit strange. I don't know what it means. It is mentioned at the end of the above. It turns out that this value is written casually, the main reason is that n always returns 0.

Then, set the corresponding column data in the problematic row to a null value.

Set concat off

Update & table_owner. & table_with_lob set & lob_column = empty_blob () where rowid in (select corrupted_rowid from corrupted_data );

The following error message is displayed:

ORA-00932: inconsistent datatypes: expected NCLOB got BLOB

Modify the above Code

Update & table_owner. & table_with_lob set & lob_column = empty_clob () where rowid in (select corrupted_rowid from corrupted_data );

Try again and Update is successful. (If the type is XMLTYPE, use XMLType. createXML (") instead of empty_clob ().)

Return to the command line and try again with DBV, but 452 pages are marked as bad.

However, this time, Select * from vpxadmin. vpx_host; can return a normal value. The table is fixed. However, the result of the bulk DBV is still marked as upted, and the practicality of using DBV to find bad tables is much worse. It is too troublesome to test 452 Bad blocks one by one.

Go to the vCenter server,

An error occurred while initializing VPXD during startup.

View logs in the/var/log/vmware/vpx directory. It can be roughly determined that the deserialization error occurs after the data in the database is read. However, because of the above reasons, I found no fault table content and tried several tables in the tablespace by using the OEM. It is good, and no clue can be found from the VCenter log, the problem becomes a dead problem.

It seems that only one tablespace can be re-created for the vCenter, and the data originally retained has to be abandoned.