Oracle Recovery scenario with Conventional hot backup

-
Oracle Recovery scenario with Conventional hot backup

Though ,Most of the Oracle DBA use RMAN to take backup but sometime understanding and knowing conventional hot backup recovery scenario will help to understand actual recovery process and improve knowledge and troubleshooting skills. 

In case of a restore from hot backup, please ensure you have taken care of all temporary files. It has been observed in the past that all data files were restored but temporary files were ignored.


Scenario 1 -- loss of INACTIVE online Redo Log Group


SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'C:\ORACLASS\ORADATA\U03\LOG01A.RDO'

ORA-00312: online log 1 thread 1: 'C:\ORACLASS\ORADATA\U04\LOG01B.RDO'


NOTE: Assumption is that there is least 3 redo log group in order to do the following.

 

SQL> alter database drop logfile group 1;

Database altered.

 

SQL> alter database open;

Database altered.

 

SQL> select group#, thread#,bytes, members from v$log;

    GROUP#    THREAD#      BYTES    MEMBERS

      ----------        ----------      ----------          ----------

                 2                    1   10485760                      2

                 3                    1   10485760                      2

SQL> select member from v$logfile;

MEMBER

-----------------------------------

C:\ORACLASS\ORADATA\U03\LOG02A.RDO

C:\ORACLASS\ORADATA\U04\LOG02B.RDO

C:\ORACLASS\ORADATA\U03\LOG03A.RDO

C:\ORACLASS\ORADATA\U04\LOG03B.RDO

 

SQL> alter database add logfile group 1

  2  ('c:\oraclass\oradata\u03\log01a.rdo', 'c:\oraclass\oradata\u04\log01b.rdo') size 10485760;

Database altered.


Scenario 2 -- loss of CURRENT online Redo Log Group

If you created your logfile groups with duplicate online log files -- that is two or more logfiles per group, then you can copy the good (remaining) log file to the missing log file and you will be able to open the db fine. This is our standard.
Check from select * from v$logfile;


If you created only one log file per group then you have two choices. You can open a TAR with oracle support for help in forcing the database open when you have lost your active log file. Or, judging by the low sequence numbers of your log files, you may be able to recreate the database from scratch and reapply any transactions. This is shown below:


SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'C:\ORACLASS\ORADATA\U03\LOG01A.RDO'

 

SQL> alter database drop logfile group 1;

alter database drop logfile group 1

*

ERROR at line 1:

ORA-01623: log 1 is current log for thread 1 - cannot drop

ORA-00312: online log 1 thread 1: 'C:\ORACLASS\ORADATA\U03\LOG01A.RDO'

 

SQL> select group#, status, sequence# from v$log;

    GROUP#      STATUS           SEQUENCE#

     ---------- ----------------                      ---------

                1    CURRENT                              69

                2    INACTIVE                              68

                3    INACTIVE                              67

 

log group #1 is the current logfile.

 

SQL> shutdown immediate;

Database dismounted.

ORACLE instance shut down.


Restore all Datafile (listed in V$DATAFILE)

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.


SQL> recover automatic database until cancel;
ORA-00279: change 193738 generated at 11/18/2002 12:31:03 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00069.001
ORA-00280: change 193738 for thread 1 is in sequence #69
ORA-00278: log file 'C:\ORACLE\ORA92\RDBMS\ARC00069.001' no longer needed for
this recovery
ORA-00308: cannot open archived log 'C:\ORACLE\ORA92\RDBMS\ARC00069.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\ora92\RDBMS
Oldest online log sequence      1
Next log sequence to archive   1
Current log sequence                1
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Do a full database backup


Scenario 3 -- loss of Control Files

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

ORA-00205: error in identifying controlfile, check alert log for more info

SQL> shutdown

ORA-01507: database not mounted

ORACLE instance shut down.

At this point I refer to the backup done in the past of the controlfile done

            via the following command:

                        SQL> alter database backup controlfile to trace;

                        Database altered.

 

Find the file created in the directory listed below and edit the file removing

all the lines above the STARTUP NOMOUNT, and also change the

# to -- (comments).  NOTE: there are two STARTUP MOUNT’s in the file.

                        SQL> show parameter user

                        NAME                       TYPE        VALUE

                        ------------------------- ----------- ---------------------------

                        user_dump_dest         string      c:\oraclass\ADMIN\UDUMP

 

SQL> @controlfile

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Control file created.

ORA-00283: recovery session canceled due to errors

ORA-00264: no recovery required

 

ALTER SYSTEM ARCHIVE LOG ALL

*

ERROR at line 1:

ORA-00271: there are no logs that need archiving

Database altered.

Database altered.

Tablespace altered.

Tablespace altered.


Scenario 4 – Loss of Media

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: 'C:\ORACLASS\ORADATA\U02\SAMPLE01.DBF'

 

SQL> select file#, error from v$recover_file;

   FILE#  ERROR

----------  ---------------------------------------------------------

           2  FILE NOT FOUND

           5  FILE NOT FOUND

 

SQL> select file#,name from v$datafile;

     FILE#   NAME

 ----------   ----------------------------------------

            1   C:\ORACLASS\ORADATA\U01\SYSTEM01.DBF

            2   C:\ORACLASS\ORADATA\U02\UNDOTBS.DBF

            3   C:\ORACLASS\ORADATA\U03\USERS01.DBF

            4   C:\ORACLASS\ORADATA\U03\INDX01.DBF

            5   C:\ORACLASS\ORADATA\U02\SAMPLE01.DBF

            6   C:\ORACLASS\ORADATA\U01\QUERYDATA01.DBF

 

6 rows selected.

 

Looking at the OS you find that the mount point has been lost and all of files on it.

This one also has the UNDO tablespace, So a closed recovery is required.

 

I restore all lost files.

 

@@@@@ alter statements!!!

 

SQL> recover automatic database;

Media recovery complete.

 

SQL> alter database open;

Database altered.


Scenario 5 -- loss of an Online UNDO Segment Tablespace Datafile

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: 'C:\ORACLASS\ORADATA\U02\UNDOTBS.DBF'

 

A closed recover in my only option, so I restore the backup of the

UNDO segment tablespace file.

 

SQL> recover automatic database;

Media recovery complete.

 

SQL> alter database open;

Database altered.


Scenario 6 -- loss of SYSTEM Tablespace Datafile

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: 'C:\ORACLASS\ORADATA\U01\SYSTEM01.DBF'

 

A closed recover in my only option, so I restore the backup of the

SYSTEM tablespace file.

 

SQL> recover automatic database;

Media recovery complete.

 

SQL> alter database open;

Database altered.


Scenario 7 -- Loss of a Non-System, Non-Rollback Segment Datafile

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: 'C:\ORACLASS\ORADATA\U03\USERS01.DBF'

 

SQL> alter database datafile 3 offline;

Database altered.

 

SQL> alter database open;

Database altered.

 

   At this point I restore the USERS01.DBF file from the backup.

 

SQL> recover automatic datafile 3;

Media recovery complete.

 

SQL> alter database datafile 3 online;

Database altered.

Scenario 8 – Time Based Recovery

 

 

Example table created

10:01:10 SQL> create table sales as select * from sh.sales;

Table created.

10:02:50 SQL> select count(*) from sales;

COUNT(*)

--------------

    1016271

 

13:04:58 SQL> drop table sales;

Table dropped.

 

This assumes that you have discovered the problem and have restored all the datafiles.

 

SQL> startup mount

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

13:20:33 SQL> recover automatic database until time '2002-11-18:13:03:00';

Media recovery complete.

 

13:15:41 SQL> alter database open resetlogs;

Database altered.

 

13:17:20 SQL> select count(*) from sales;

 

COUNT(*)

    ----------

    1016271


Scenario 9 -- Failure During Hot Backup

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5: 'C:\ORACLASS\ORADATA\U02\SAMPLE01.DBF'

 

SQL> select * from v$backup;

 

     FILE# STATUS                CHANGE#         TIME

  ---------- ------------------            ----------       ---------

             1 NOT ACTIVE                        0

             2 NOT ACTIVE                        0

             3 NOT ACTIVE                        0

             4 NOT ACTIVE                        0

             5 ACTIVE                         87244  15-NOV-02

             6 NOT ACTIVE                        0

 

6 rows selected.

 

SQL> alter database end backup;

Database altered.

 

SQL> alter database open;

Database altered.


Scenario 11 – Non-Essential datafile with no backup and no archivelog on

 

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 8 - see DBWR trace file

ORA-01110: data file 8: 'C:\ORACLASS\ORADATA\U05\JUNK01.DBF'

 

SQL> alter database datafile 8 offline drop;

Database altered.

 

SQL> alter database open;

Database altered.

 

SQL> select d.name,t.name tblspace from

  2  v$datafile d, v$tablespace t

  3  where d.ts# = t.ts#;

  

NAME                                                                                    TBLSPACE

----------------------------------------                                              --------------------

C:\ORACLASS\ORADATA\U01\SYSTEM01.DBF           SYSTEM

C:\ORACLASS\ORADATA\U02\UNDOTBS01.DBF        UNDOTBS

C:\ORACLASS\ORADATA\U03\USERS01.DBF              USERS

C:\ORACLASS\ORADATA\U03\INDX01.DBF                INDX

C:\ORACLASS\ORADATA\U02\SAMPLE01.DBF           SAMPLE

C:\ORACLASS\ORADATA\U01\QUERYDATA01.DBF             QUERY_DATA

C:\ORACLASS\ORADATA\U02\EXAMPLE01.DBF        EXAMPLE

C:\ORACLASS\ORADATA\U05\JUNK01.DBF                JUNK

 

8 rows selected.

 

SQL> drop tablespace junk including contents;

Tablespace dropped.


Scenario 12 – Loss of a datafile and missing archivelog file

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: 'C:\ORACLASS\ORADATA\U03\USERS01.DBF'

 

Restore missing Datafile

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

 

SQL> recover automatic database;

ORA-00279: change 217990 generated at 11/19/2002 10:13:57 needed for thread 1

ORA-00289: suggestion : C:\ORACLASS\ORADATA\ARCHIVE1\ARC00002.001

ORA-00280: change 217990 for thread 1 is in sequence #2

ORA-00278: log file 'C:\ORACLASS\ORADATA\ARCHIVE1\ARC00002.001' no longer

needed for this recovery

ORA-00308: cannot open archived log 'C:\ORACLASS\ORADATA\ARCHIVE1\ARC00002.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

 

SQL> shutdown

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.


Incomplete Recover is required at this point

Restore all Datafile (listed in V$DATAFILE)

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: 'C:\ORACLASS\ORADATA\U01\SYSTEM01.DBF'

 

SQL> recover automatic until cancel;

ORA-00279: change 217990 generated at 11/19/2002 10:13:57 needed for thread 1

ORA-00289: suggestion : C:\ORACLASS\ORADATA\ARCHIVE1\ARC00002.001

ORA-00280: change 217990 for thread 1 is in sequence #2

ORA-00278: log file 'C:\ORACLASS\ORADATA\ARCHIVE1\ARC00002.001' no longer

needed for this recovery

ORA-00308: cannot open archived log 'C:\ORACLASS\ORADATA\ARCHIVE1\ARC00002.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

Do a full database backup




Scenario 13 -- Recover a lost Datafile with No Backup

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: 'C:\ORACLASS\ORADATA\U04\NEW01.DBF'

 

   A tablespace and table were created in the past with the following commands:

      SQL> create tablespace new_sample datafile

         2  'c:\oraclass\oradata\u04\new01.dbf' size 1m;

      Tablespace created.

      SQL> create table new_tables (col1 number ,col2 number)

        2  tablespace new_sample;

      Table created.

 

SQL> alter database datafile 7 offline;

Database altered.

 

SQL> alter database open;

Database altered.

 

SQL> alter database create datafile  'c:\oraclass\oradata\u04\new01.dbf';

Database altered.

 

SQL> recover automatic datafile 7;

Media recovery complete.

 

SQL> alter database datafile 7 online;

Database altered.

 

SQL> select * from new_tables;

no rows selected



Scenario 14 -- Missing Mirrored Online Redo Log Files

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

Database opened.

 

SQL> select group#, status, member from v$logfile;

    GROUP#      STATUS  MEMBER

     ----------            -------   ----------------------------------------

                3                         C:\ORACLASS\ORADATA\U03\LOG03A.RDO

                3      INVALID   C:\ORACLASS\ORADATA\U04\LOG03B.RDO

                2                         C:\ORACLASS\ORADATA\U03\LOG02A.RDO

                2      INVALID  C:\ORACLASS\ORADATA\U04\LOG02B.RDO

                1                         C:\ORACLASS\ORADATA\U03\LOG01A.RDO

                1      INVALID  C:\ORACLASS\ORADATA\U04\LOG01B.RDO

 

SQL> select group#, sequence#, archived, status from v$log;

    GROUP#   SEQUENCE# ARC  STATUS

     ----------             ----------     ---   ----------------

                 1                      17  YES  INACTIVE

                 2                      18  YES  INACTIVE

                 3                      19   NO  CURRENT

 

SQL> alter database drop logfile member

  2 'C:\ORACLASS\ORADATA\U04\LOG01B.RDO';

Database altered.

SQL> alter database drop logfile member

  2 'C:\ORACLASS\ORADATA\U04\LOG02B.RDO';

Database altered.

SQL> alter database drop logfile member

  2  'C:\ORACLASS\ORADATA\U04\LOG03B.RDO';

alter database drop logfile member 'C:\ORACLASS\ORADATA\U04\LOG03B.RDO'

*

ERROR at line 1:

ORA-01609: log 3 is the current log for thread 1 - cannot drop members

ORA-00312: online log 3 thread 1: 'C:\ORACLASS\ORADATA\U03\LOG03A.RDO'

ORA-00312: online log 3 thread 1: 'C:\ORACLASS\ORADATA\U04\LOG03B.RDO'

 

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile member

  2  'C:\ORACLASS\ORADATA\U04\LOG03B.RDO';

Database altered.

SQL> alter database add logfile member

  2  'C:\ORACLASS\ORADATA\U04\LOG01B.RDO' to group 1;

Database altered.

SQL> alter database add logfile member

  2  'C:\ORACLASS\ORADATA\U04\LOG02B.RDO' to group 2;

Database altered.

SQL> alter database add logfile member 

   2 'C:\ORACLASS\ORADATA\U04\LOG03B.RDO' to group 3;

Database altered.

SQL> select group#, status, member from v$logfile;

    GROUP#      STATUS  MEMBER

     ----------            -------   ----------------------------------------

                3                         C:\ORACLASS\ORADATA\U03\LOG03A.RDO

                3      INVALID   C:\ORACLASS\ORADATA\U04\LOG03B.RDO

                2                         C:\ORACLASS\ORADATA\U03\LOG02A.RDO

                2      INVALID  C:\ORACLASS\ORADATA\U04\LOG02B.RDO

                1                         C:\ORACLASS\ORADATA\U03\LOG01A.RDO

                1      INVALID  C:\ORACLASS\ORADATA\U04\LOG01B.RDO

 

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#, status, member from v$logfile;

 

    GROUP#      STATUS  MEMBER

     ----------            -------   ----------------------------------------

                3                         C:\ORACLASS\ORADATA\U03\LOG03A.RDO

                3                         C:\ORACLASS\ORADATA\U04\LOG03B.RDO

                2                         C:\ORACLASS\ORADATA\U03\LOG02A.RDO

                2                         C:\ORACLASS\ORADATA\U04\LOG02B.RDO

                1                         C:\ORACLASS\ORADATA\U03\LOG01A.RDO

                1                         C:\ORACLASS\ORADATA\U04\LOG01B.RDO


Scenario 15 – Missing Controlfile and Read Only Datafile

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

ORA-00205: error in identifying controlfile, check alert log for more info

 

SQL> @c:\oraclass\backup\ctl

Control file created.

ORA-00283: recovery session canceled due to errors

ORA-00264: no recovery required

 

System altered.

Database altered.

 

ALTER DATABASE RENAME FILE 'MISSING00006'

*

ERROR at line 1:

ORA-01511: error in renaming log/data files

ORA-01141: error renaming data file 6 - new file

'C:\ORACLASS\ORADATA\U01\QUERYDATA01.DBF' not found

ORA-01111: name for data file 6 is unknown - rename to correct file

ORA-01110: data file 6: 'C:\ORACLE\ORA92\DATABASE\MISSING00006'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

 

 

ALTER TABLESPACE "QUERY_DATA" ONLINE

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01111: name for data file 6 is unknown - rename to correct file

ORA-01110: data file 6: 'C:\ORACLE\ORA92\DATABASE\MISSING00006'

Tablespace altered.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.



Copy the backup of the QUERYDATA01.DBF datafile

 

SQL> @c:\oraclass\backup\ctl

ORACLE instance started.

 

Total System Global Area  118037284 bytes

Fixed Size                   453412 bytes

Variable Size             113246208 bytes

Database Buffers            4194304 bytes

Redo Buffers                 143360 bytes

 

Control file created.

ORA-00283: recovery session canceled due to errors

ORA-00264: no recovery required

 

System altered.

Database altered.

Database altered.

Tablespace altered.

Tablespace altered.



Scenario 16 -- Lost of Datafile used as Default Temporary Tablespace

 

 

SQL> startup

ORACLE instance started.

Total System Global Area   26286444 bytes

Fixed Size                                452972 bytes

Variable Size                        20971520 bytes

Database Buffers                    4194304 bytes

Redo Buffers                            667648 bytes

Database mounted.

Database opened.

SQL> select * from dba_tab_columns order by 1,2,3,4;

ERROR:

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: 'C:\ORACLASS\ORADATA\U02\TEMP01.DBF'

no rows selected

 

SQL> create temporary tablespace temp2 tempfile  'c:\oraclass\oradata\u02\temp02.ora' size 2m;

Tablespace created.

 

SQL>  alter database default temporary tablespace temp2;

Database altered.

 

SQL>  drop tablespace temp including contents and datafiles;

Tablespace dropped.

 

SQL> create temporary tablespace temp tempfile 'c:\oraclass\oradata\u02\tem01p.ora' size 20m;

Tablespace created.

 

SQL>  alter database default temporary tablespace temp;

Database altered.

 

SQL> drop tablespace temp2 including contents and datafiles;

Tablespace dropped.




Scenario 17-Changes to the SPFILE that are valid

 

 

SQL> alter system set pga_aggregate_target=5m scope=spfile;

System altered.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-00093: pga_aggregate_target must be between 10M and 4096G-1

 

The only way I could the fix the problem was to delete the spfile and create an init.ora file from the non-default parameters from the alert log file.


You can read below book to increase your knowledge on oracle database:
================================================
You can also , Visit our Blogs for related topic on backup and  Recovery:
Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restore MySQL Database from mysqlbackup

-
Image
 Restore MySQL Database from mysqlbackup  In todays world, One of the most common activity is to perform database restore using old backup or performing PIT recovery for database.  In this blog , We will show you how to perform restore and recovery of the MySQL database using backup taken via mysqlbackup. In order to restore Mysql database from MySQL backup , We will follow below steps: 1) Shut down MySQL Database Server/Service Check the status of the mysql service using command : systemctl status mysql Shut down mysql service using command : systemctl stop mysql 2) Delete all existing file of MySQL Server Delete all files inside the server's data directory. Also delete all files inside the directories specified by the --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory options for restore, if the directories are different from the data directory. 3) Restore full Backup mysqlbackup  --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --datadir=/var/lib
Read more

Oracle 19c New Features

-
Oracle 19c New Features As Oracle Support have made 12.2 version of Oracle ,its has become important for us to upgrade our 12c database to 19c.And before upgrade any database to latest version , We need to know the latest new features of that particular version.  In the current blog , We are going to discuss about new features of the Oracle 19C database : 1)Automatic Indexing  This is one of the important Oracle database 19c New feature. Automatic Indexing is one of the great feature included in 19c . It automates index management tasks, such as creating, rebuilding, and dropping indexes in an Oracle Database based on changes in the application workload. This feature improves database performance by managing indexes automatically in an Oracle Database. Oracle has provided new package DBMS_AUTO_INDEX for this. This enables automatic indexing in a database, but creates any new auto indexes as invisible indexes, so that they cannot be used in SQL statements: EXEC DBMS_AUTO_INDEX.CONFIGURE
Read more

Install & Configure MySQL Router - MySQL InnoDB Cluster

-
Image
Install & Configure MySQL Router -  MySQL InnoDB Cluster MySQL Router is one of the important component of MySQL InnoDB Cluster as it's used by applications to connect to the MySQL DBs running on cluster.  In this blog, We will show How we can install and configure MySQL Router either on application server or on dedicated server for MySQL Router. Below are the step to install and configure MySQL Router: 1) Install MySQL Router on the Server(In our case , We are restoring MySQL Router on the ubuntu server). Run command as root user : apt-get install MySQL-router 2) Bootstrap or Configure MySQL Router mysqlrouter --bootstrap clusteradmin@mysqlhost01:3306 --user=mysqlrouter In Case of any issue in configuring MySQL Router ,We can use -force option of the bootstrap. mysqlrouter --bootstrap clusteradmin@n4ldbdev5030:3306 --directory=/home/myrouter --user=mysqlrouter --force 3) Test Connectivity via MySQL Router. mysqlsh --uri clusteradmin@localhost:6446 you can learn more about
Read more