ORA-01186/ORA-01122/ORA-01110/ORA-01206

From the log, we can see that the tempfile file of this database is faulty. It should be left over when the dg is created. Although the file is not damaged, it must be switched

From the log, we can see that the tempfile file of this database is faulty. It should be left over when the dg is created. Although the file is not damaged, it must be switched

When checking a set of DG databases a few days ago, when read only opens the stanby database, the following error log is found in alert. log:

........
Wed Dec 14 15:45:19 2011
Completed: alter database recover managed standby database cancel
Wed Dec 14 15:46:37 2011
Alter database open read only
Wed Dec 14 15:46:38 2011
Errors in file/Oracle/app/admin/skatestdby/bdump/skatestdby_dbw0_11326.trc:
ORA-01186: file 201 failed verification tests
ORA-01122: database file 201 failed verification check
ORA-01110: data file 201: '/oracle/oradata/skatestdby/temp01.dbf'
ORA-01206: file is not part of this database-wrong database id
Wed Dec 14 15:46:38 2011
File 201 not verified due to error ORA-01122
Wed Dec 14 15:46:38 2011
SMON: enabling cache recovery
Wed Dec 14 15:46:40 2011
Cannot re-create tempfile/oracle/oradata/skatestdby/temp01.dbf, the same name file exists
Database Characterset is UTF8
Opening with internal Resource Manager plan
Where numa pg = 1, CPUs = 16
Replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: alter database open read only
......

From the log, we can see that the tempfile file of this database is faulty. It should be left over when the dg is created. Although the file is not damaged,
However, when you need to switch the DG, it will delay a lot of time, so you should fix this problem here.

Solution: recreate the tempfile file.

Steps:

Query the role of this database
SQL> select name, log_mode, controlfile_type, open_mode, protection_mode, database_role, force_logging from v $ database;

NAME LOG_MODE CONTROLFILE_TYPE OPEN_MODE PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING
------------------------------------------------------------------------------------------------
SKATEDB ARCHIVELOG STANDBY MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY YES

View standby database Process status
SQL> select process, status, thread #, sequence #, block #, blocks
2 from v $ managed_standby;

Process status thread # SEQUENCE # BLOCK # BLOCKS
-------------------------------------------------------------
Arch closing 1 10095 43009 1912
Arch closing 1 10096 1 149
MRP0 WAIT_FOR_LOG 1 10097 0 0
Rfs idle 0 0 0
RFS Id 1 10097 23465 827

View the recover mode of the standby Database
SQL> select dest_name, archived_thread #, archived_seq #, applied_thread #,
2 applied_seq #, db_unique_name, recovery_mode
3 from v $ archive_dest_status
4 where status = 'valid ';

DEST_NAME ARCHIVED_THREAD # ARCHIVED_SEQ # APPLIED_THREAD # APPLIED_SEQ # DB_UNIQUE_NAME RECOVERY_MODE
Certificate ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_1 1 10096 0 0 NONE MANAGED
LOG_ARCHIVE_DEST_2 0 0 0 0 skatedb MANAGED
STANDBY_ARCHIVE_DEST 1 10095 1 10095 NONE MANAGED

SQL>

Cancel standby recover
SQL> alter database recover managed standby database cancel;

Database altered.

Open the database with read only
SQL> alter database open read only;

Database altered.

View database status
SQL> select name, open_mode from v $ database;

NAME OPEN_MODE
-------------------
SKATEDB READ ONLY

Because you want to modify the data file of the standby database, you must modify the parameter "standby_file_management = manual" here.
SQL> show parameter standby

NAME TYPE VALUE
-----------------------------------------------------------------------------
Standby_archive_dest string? /Dbs/arch
Standby_file_management string AUTO
SQL>
SQL>
SQL>
SQL> alter system set standby_file_management = manual scope = memory;

System altered.

Create a new tempfile
SQL> alter tablespace temp add tempfile '/oracle/oradata/skatestdby/temp02.dbf' size 20G
2 autoextend on next 500 m maxsize 25G;

Tablespace altered.

Make the existing tempfile file offline and you are about to delete it.
SQL> alter database tempfile '/oracle/oradata/skatestdby/temp01.dbf' offline;

Database altered.

Delete A tempfile
SQL> alter database tempfile '/oracle/oradata/skatestdby/temp01.dbf' drop including datafiles;

Database altered.

Prepare to start the standby recover
SQL> alter database recover managed standby database disconnect using current logfile;
Alter database recover managed standby database disconnect using current logfile
*
ERROR at line 1:
ORA-01093: alter database close only permitted with no sessions connected

SQL> alter database close;
Alter database close
*
ERROR at line 1:
ORA-01093: alter database close only permitted with no sessions connected

Because the standby database is connected, all databases cannot be directly closed.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start database nomount
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.7180E + 10 bytes
Fixed Size 2114104 bytes
Variable Size 2214596040 bytes
Database Buffers 1.4948E + 10 bytes
Redo Buffers 14659584 bytes

Mount a database in standby mode
SQL> alter database mount standby database;

Database altered.

Start standby recover
SQL> alter database recover managed standby database disconnect using current logfile;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

Read the only database again to check whether an error is returned.
SQL> alter database open read only;

Database altered.

Check the alert. log collection. The database is started normally and no error is reported. In fact, the changes in the alertlog file have been viewed during this operation.

SQL> alter database close;
Alter database close
*
ERROR at line 1:
ORA-01093: alter database close only permitted with no sessions connected

Disconnect from the standby database and directly close the standby database.

SQL> alter database close;

Database altered.

Start real-time recover of standby
SQL> alter database recover managed standby database disconnect using current logfile;

Database altered.

You can also use the following steps to convert a Real-Time recover into a general recover.
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL>

Now OK. When the DG is switched, you can directly switch to reduce the switching time.

------ End -------