Archive for the ‘Advanced’ Category

Advance/Troubleshooting/Error-Bug Fixing

News: ASMlib will not be certified or supported on Red Hat Enterprise Linux 6.

Posted by FatDBA on October 25, 2012

For More Click or go to News section of the Blog:

https://oracleant.wordpress.com/news/

Posted in Advanced | Leave a Comment »

Real-Time Apply Error: ORA-38500: ‘USING CURRENT LOGFILE option not available without stand’

Posted by FatDBA on October 25, 2012

Hi Folks,

Going to discuss about one of the most recent problem that i faced while configuring/activating the Real-Time Apply service on one of the standby database.

(Using Oracle 10g r2)
Prior Status:
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ;

RECOVERY_MODE
———————–
MANAGED

To start real-time apply i used CURRENT LOGFILE clause but received below provided error message:

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-38500: USING CURRENT LOGFILE option not available without stand

Resolution:
1. I created Log Groups and Log Files for Standby Database to try fix this issue and as per my expectations it worked.

Added Three log groups of same size 50MB each on Primary Database for Standby (Log Groups should be always be of same size – What you have on Primary DB, Mine was – 52428800 (50MB)):

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 SIZE 50M;
Database Altered

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
Database Altered

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
Database Altered

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

GROUP#         MEMBER                                                                        TYPE
———- ———————————————————————————————————————————–
4       /u01/app/oracle/flash_recovery_area/DOOM/onlinelog/o1_mf_4_88j4601r_.log      STANDBY
3       /u01/app/oracle/flash_recovery_area/DOOM/onlinelog/o1_mf_3_88j4859l_.log      STANDBY
5       /u01/app/oracle/flash_recovery_area/DOOM/onlinelog/o1_mf_5_88j4d4t5_.log                      STANDBY

On Standby Database:
2. Added a standby logfile on Std DB end:

SQL> alter database add standby logfile ‘/u01/app/oracle/admin/doom/stdy_log.log’ size 10m;
Database altered.

SQL> alter database recover managed standby database disconnect using current logfile;
Database altered.

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ;
RECOVERY_MODE
———————–
MANAGED REAL TIME APPLY

SQL> select RECOVERY_MODE, DATABASE_MODE, PROTECTION_MODE from v$archive_dest_status;

RECOVERY_MODE DATABASE_MODE PROTECTION_MODE
———————– ————— ——————–
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE

11 rows selected.

Posted in Advanced | Tagged: DataGuard, Errors, standby | Leave a Comment »

Data Guard – Adding and Resizing a datafile on the Primary database

Posted by FatDBA on October 13, 2012

Standby Database (Data Guard) is slowly and steadily turning out to be my best topic/subject among others in High Availability. It attracts me to experiment to improve the HA features.

Alright, today I’ve fixed one of the problem i was facing from past few days and discovered that i was doing a stupid mistake and that i failed to catch too till the moment i rechecked some parameters in initialization file minute ago.

I tried to add/modify some data files and was not getting at the Standby End which resulted in several errors at Sb DB and lead to Standby Database crash. Otherwise i have to manual port log files from primary destination to the Standby party to maintain the sync between the two after datafiles addition.

Below were the error messages i was getting when tested the standby status:

SQL> select message from v$dataguard_status;

MESSAGE
——————————————————————————–
ARC0: Archival stopped
ARC1: Archival stopped
Media Recovery Waiting for thread 1 seq# 197

Solution:
I’ve check and found Standby_File_Management was set to ‘MANUAL’
and both the db_file_name_convert and log_file_name_conver were different.

I’ve set all the three parameters to below provided values
Standby_File_Management=AUTO (Both on Primary and Standby)
db_file_name_convert ( I’ve left it unchanged on both sides – it was empty (default))
log_file_name_convert ( I’ve left it unchanged on both sides – it was empty (default))

Snippets from my machine:
SQL> show parameter standby_file_management (On both Primary and Standby)

NAME TYPE VALUE
———————————— ———– ——————————
standby_file_management string AUTO

SQL> show parameter db_file_name_convert

NAME TYPE VALUE
———————————— ———– ——————————
db_file_name_convert string

SQL> show parameter log_file_name_convert

NAME TYPE VALUE
———————————— ———– ——————————
log_file_name_convert string

Add a new tablespace to the primary database:

SQL> CREATE TABLESPACE new_ts DATAFILE '/disk1/oracle/oradata/payroll/t_db2.dbf'
  2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;

Archive the current online redo log file so the redo data will be transmitted to and applied on the standby database:
```
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
```

Verify the new datafile was added to the primary database:

SQL> SELECT NAME FROM V$DATAFILE;
NAME
----------------------------------------------------------------------
/disk1/oracle/oradata/payroll/t_db1.dbf
/disk1/oracle/oradata/payroll/t_db2.dbf

Verify the new datafile was added to the standby database:

SQL> SELECT NAME FROM V$DATAFILE;
NAME
----------------------------------------------------------------------
/disk1/oracle/oradata/payroll/s2t_db1.dbf
/disk1/oracle/oradata/payroll/s2t_db2.dbf

Posted in Advanced | Tagged: DataGuard, standby | Leave a Comment »

What is Yum in Linux and how to configure ?

Posted by FatDBA on October 1, 2012

Why to use: Many times while installing Database on Linux/Linux most of us encounters problems with the pre-installations which requires some of the Packages to be available to Oracle before the installation starts. Searching required RPM’s online is a tedious job and needs lot of patience which could ultimately lead to errors about “Dependencies Failed”. A best resolution of this problem is YUM, which resolves dependency part by itself and allow the package search and install easily from the repository created.

The Yellow dog Updater Modified (YUM) is a package management application for computers running Linux operating systems. yum is a standard method of managing the installation and removal of software.

Steps to Configure YUM Repository on RED HAT:

1) Install vsftpd (FTP Server) & createrepo Packages
Insert RHEL 5 Installation DVD

# mkdir ~/Desktop/rhel_cd
# mount /dev/cdrom ~/Desktop/rhel_cd
# cd ~/Desktop/rhel_cd/Server
# rpm –ivh vsftpd*
# rpm –ivh createrepo*

2) Copy all packages from DVD to LINUX filesystem

# cp ~/Desktop/rhel_cd/Server/ /var/ftp/pub
# cd /
# eject

3) Configure Local Yum Repository Server

# cd /var/ftp/pub/Server/repodata
# cp comps-rhel5-server-core.xml /var/ftp/pub/Server/
# cd /var/ftp/pub/Server
# createrepo -vg comps-rhel5-server-core.xml /var/ftp/pub/Server/
# service vsftpd start
# chkconfig –add vsftpd

4) Configure Clients
Run following commands on clients

# vi /etc/yum.repos.d/yum_server.repo

Add:
[Server]
name=YUM_SERVER
baseurl=ftp://192.168.2.100/pub/Server
gpgcheck=0

# vi /etc/vsftpd/vsftpd.conf

Set the following parameter (if not already set)
anonymous_enable = yes

# service vsftpd restart

Now your clients can use this yum server to download & install packages & resolve
dependencies.

Posted in Advanced | Tagged: linux | Leave a Comment »

ALTER DATABASE OPEN RESETLOGS

Posted by FatDBA on September 24, 2012

What is and why there is a need to open database in RESETLOGS mode ?

Answer:
Whenever you perform incomplete recovery or recovery with a backup control file, you must reset the online logs when you open the database.

– Archives the current online redo logs (if they are accessible) and then erases the contents of the online redo logs and resets the log sequence number to 1.

– Reinitializes the control file metadata about online redo logs and redo threads.

– The RESETLOGS prevents you from corrupting datafiles with archived logs that are not from direct parent incarnations of the current incarnation.

To reset the log sequence number when opening a database after recovery and thereby create a new incarnation of the database, execute the following statement:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1

Posted in Advanced | Tagged: recovery | Leave a Comment »

Contol File Missing – Recovery (ORA-00205: error in identifying control file)

Posted by FatDBA on September 24, 2012

Today i intentionaly deleted one of three available Control File of one of the instance ‘orcl’. Below are the steps i performed to recover the lost DB Controlfile to bring back the Database up.

This is what i recieved in SQL command line when tried to start the effected the instance.
SQL> startup
ORACLE instance started.

Total System Global Area 306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             109053400 bytes
Database Buffers          192937984 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

Pre – Checks:
* First check if the entry is available in PFILE and check status of control_files paramter.
* Veify the Control Files entries in PFILE and supply changes to the spfile if discrepancies found by using create spfile/pfile statement.

Example Entry in an PFILE (INIT.ORA)
*.control_files=’/u01/app/oracle/oradata/orcl/control01.ctl’,’/u01/app/oracle/oradata/orcl/control02.ctl’,’/u01/app/oracle/oradata/orcl/control03.ctl’

SQL> show parameter control_files

NAME                                 TYPE        VALUE
———————————— ———– ——————————
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u
01/app/oracle/oradata/orcl/con
trol03.ctl

* If you have ASM setup on the same machine, it is recommended that you should always check ASM state. It should be always running without any errors during startup (i.e. DISK Groups errors etc.) otherwise would cause issues while restoring/recovering files that are created using ASM signatures.

Scenario 1: Corrupt or Missing control file

When attempting to start the database you see the following error.

SQL> startup

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 528485968 bytes

Database Buffers 314572800 bytes

Redo Buffers 5132288 bytes

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

SQL>

Looking the alert log you see that the /u02/app/oracle/oradata/orcl/ctl/control01.ctl is corrupt.

ALTER DATABASE MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u02/app/oracle/oradata/orcl/ctl/control01.ctl'

ORA-27048: skgfifi: file header information is invalid

ORA-205 signalled during: ALTER DATABASE MOUNT...

Mon Apr 26 10:11:23 2010

Checker run found 1 new persistent data failures

ORA-205 signalled during: ALTER DATABASE MOUNT...

Or you may see the file is missing when looking in the alert log.

ALTER DATABASE MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u02/app/oracle/oradata/orcl/ctl/control01.ctl'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Mon Apr 26 10:28:28 2010

Checker run found 1 new persistent data failures

ORA-205 signalled during: ALTER DATABASE MOUNT...

In either case, to recover from this problem is simple and does not require going to the last backup provided your control files are multiplexed. To resolve this problem we simply copy a control file from one of the other locations.

Identify the control files:

SQL> startup

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 528485968 bytes

Database Buffers 314572800 bytes

Redo Buffers 5132288 bytes

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

SQL> show parameter control_files

NAME TYPE VALUE

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

control_files string /u02/app/oracle/oradata/orcl/c

tl/control01.ctl, /u01/app/ora

cle/flash_recovery_area/orcl/c

ontrol02.ctl, /u03/app/oracle/

oradata/orcl/ctl/control03.ctl

SQL>

We know from the alert log that /u02/app/oracle/oradata/orcl/ctl/control01.ctl is the control file that is having problems so we will copy the one from the /u01/app/oracle/flash_recovery_area/orcl/control02.ctl and then mount and open the database.

SQL> host

[oracle@ora1 ~]$ cp /u01/app/oracle/flash_recovery_area/orcl/control02.ctl /u02/app/oracle/oradata/orcl/ctl/control01.ctl

[oracle@ora1 ~]$ exit

exit

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL>

By having a multiplexed copy of the control file we were able to recover from a corrupt or missing control file without having to go to a database backup.

Scenario 2: All control files are corrupt or missing

When starting the database we see the following error.

SQL> startup

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 528485968 bytes

Database Buffers 314572800 bytes

Redo Buffers 5132288 bytes

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

SQL>

In anticipation of restoring a control we get a list of the control file locations.

SQL> show parameter control_files

NAME TYPE VALUE

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

control_files string /u02/app/oracle/oradata/orcl/c

tl/control01.ctl, /u01/app/ora

cle/flash_recovery_area/orcl/c

ontrol02.ctl, /u03/app/oracle/

oradata/orcl/ctl/control03.ctl

SQL>

Looking at the alert log we see that none of the control files are available.

ALTER DATABASE MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u03/app/oracle/oradata/orcl/ctl/control03.ctl'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u02/app/oracle/oradata/orcl/ctl/control01.ctl'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-205 signalled during: ALTER DATABASE MOUNT...

Mon Apr 26 11:07:53 2010

Checker run found 2 new persistent data failures

Since all of the control files are missing we will need to restore them from a backup using RMAN.

[oracle@ora1 ctl]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 26 11:24:32 2010

RMAN> connect target /

connected to target database: ORCL (not mounted)

RMAN>

Currently none of the control files are mounted so RMAN does not know about the backups or any pre-configured RMAN settings. All settings are at their default.

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

RMAN>

In order to use the backups we will need to tell RMAN the Database ID. If you do not have or know the database you have two options available.

Option 1: Shutdown the database and re-start it in RMAN

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> host

[oracle@ora1 ctl]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 26 11:30:49 2010

RMAN> connect target /

connected to target database (not started)

RMAN> startup

Oracle instance started

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of startup command at 04/26/2010 11:31:00

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

You will again receive an error stating that the control files could not be found. That is OK because in the next step we are going to restore them. Because the RMAN configuration is back to the default we are going to have to pass the entire path along with the file name of the latest control file autobackup to the restore control file command.

RMAN> restore controlfile from '/u03/app/oracle/oradata/orcl/backup/c-1239150297-20100426-00';

Starting restore at 26-APR-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/u02/app/oracle/oradata/orcl/ctl/control01.ctl

output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

output file name=/u03/app/oracle/oradata/orcl/ctl/control03.ctl

Finished restore at 26-APR-10

RMAN>

After restoring the control files we can see mount the database.

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN>

Now that the database has been mounted we can see that our custom RMAN settings are back.

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/app/oracle/oradata/orcl/backup/%F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u03/app/oracle/oradata/orcl/backup/%U' MAXPIECESIZE 2 G;

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

RMAN>

Now it would appear we are ready to open the database.

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 04/26/2010 11:38:40

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN>

We just restored the control files. In order to open the database we have to use the RESETLOGS option.

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 04/26/2010 11:39:08

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/u02/app/oracle/oradata/orcl/system01.dbf'

RMAN>

The restored control files are older than the data files. Hence in this case we need to restore the database.

RMAN> restore database;

Starting restore at 26-APR-10

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/orcl/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/orcl/example01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u02/app/oracle/oradata/orcl/test.dbf

channel ORA_DISK_1: reading from backup piece /u03/app/oracle/oradata/orcl/backup/1olc324t_1_1

channel ORA_DISK_2: starting datafile backup set restore

channel ORA_DISK_2: specifying datafile(s) to restore from backup set

channel ORA_DISK_2: restoring datafile 00004 to /u02/app/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_2: reading from backup piece /u03/app/oracle/oradata/orcl/backup/1plc326b_1_1

channel ORA_DISK_2: piece handle=/u03/app/oracle/oradata/orcl/backup/1plc326b_1_1 tag=TAG20100426T092133

channel ORA_DISK_2: restored backup piece 1

channel ORA_DISK_2: restore complete, elapsed time: 00:00:15

channel ORA_DISK_2: starting datafile backup set restore

channel ORA_DISK_2: specifying datafile(s) to restore from backup set

channel ORA_DISK_2: restoring datafile 00001 to /u02/app/oracle/oradata/orcl/system01.dbf

channel ORA_DISK_2: restoring datafile 00002 to /u02/app/oracle/oradata/orcl/sysaux01.dbf

channel ORA_DISK_2: restoring datafile 00007 to /u03/app/oracle/oradata/orcl/dbfs01.dbf

channel ORA_DISK_2: reading from backup piece /u03/app/oracle/oradata/orcl/backup/1nlc324t_1_1

channel ORA_DISK_1: piece handle=/u03/app/oracle/oradata/orcl/backup/1olc324t_1_1 tag=TAG20100426T092133

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:34

channel ORA_DISK_2: piece handle=/u03/app/oracle/oradata/orcl/backup/1nlc324t_1_1 tag=TAG20100426T092133

channel ORA_DISK_2: restored backup piece 1

channel ORA_DISK_2: restore complete, elapsed time: 00:02:09

Finished restore at 26-APR-10

RMAN>

Next we recover the database.

RMAN> recover database;

Starting recover at 26-APR-10

using channel ORA_DISK_1

using channel ORA_DISK_2

starting media recovery

archived log for thread 1 with sequence 13 is already on disk as file /u03/app/oracle/oradata/orcl/redo/red01_b.log

archived log for thread 1 with sequence 14 is already on disk as file /u02/app/oracle/oradata/orcl/redo/redo02.log

archived log file name=/u03/app/oracle/oradata/orcl/redo/red01_b.log thread=1 sequence=13

archived log file name=/u02/app/oracle/oradata/orcl/redo/redo02.log thread=1 sequence=14

media recovery complete, elapsed time: 00:00:02

Finished recover at 26-APR-10

RMAN>

Now that the database has been restored and recovered we can finally open the database, again with the RESETLOGS option.

RMAN> alter database open resetlogs;

database opened

RMAN>

I think after seeing what is required to restore the control files you will agree that multiplexing the control files is a good thing to do.

* Thanks Dilip Kumar Singh for highlighting the errors in the post and helped me to rectify 🙂

Posted in Advanced | Tagged: RMAN | Leave a Comment »

Data Guard: ORA-16789: missing standby redo logs

Posted by FatDBA on September 17, 2012

Error message while changing ‘logxptmode’ using DGMGRL CLI on Primary Database:

DGMGRL> edit database ‘orcl’ set property ‘logxptmode’=’SYNC’;
edit database ‘orcl’ set property ‘logxptmode’=’SYNC’;
Error: ORA-16789: missing standby redo logs
Solution:
As it is clearly asking about missing standby redo logs, Hence we have to create a redo log entry for standby

SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/orcl/log1.rdo’) size 20m;

Database altered.

DGMGRL> edit database orcl set property logxptmode=”SYNC”;
edit database orcl set property logxptmode=”SYNC”;
Property “logxptmode” updated

Posted in Advanced | Tagged: DataGuard, DGMGRL | Leave a Comment »

Data Guard – Error

Posted by FatDBA on September 14, 2012

ERROR MESSAGE recieved while enabling Primary Database:

DGMGRL> enable database qrcl;
enable database qrcl;
Warning: ORA-16614: object has an ancestor that is disabled
Working to find the resolution of the problem. Answers Soon!!!

Posted in Advanced | Tagged: DGMGRL | Leave a Comment »

SYNC Vs ASYNC (Data Guard – Logxptmode)

Posted by FatDBA on September 14, 2012

Data Guard synchronous (SYNC) vs asynchronous (ASYNC) modes

Oracle Data Guard redo log transport offers synchronous log transport mode (LogXptMode = ‘SYNC’) or asynchronous log transport mode (LogXptMode = ‘ASYNC’). Systems with a network bottleneck would get better overall response time with the ASYNC mode, while shops that cannot tolerate a stale standby server (i.e. maximum availability or maximum protection are best with the SYNC mode.

The difference is all about when the COMMIT happens.

LogXptMode = (‘SYNC‘): As the name implies, SYNC mode synchronizes the primary with the standby database and all DML on the primary server will NOT be committed until the logs have been successfully transported to the standby servers. The synchronous log transport mode is required for the Maximum Protection and Maximum Availability data protection modes.
LogXptMode = (‘ASYNC‘): Conversely, asynchronous mode (ASYNC) allows updates (DML) to be committed on the primary server before the log file arrives on the standby servers. The asynchronous log transport mode is required for the Maximum Performance data protection mode.

To check status of log export mode on database.

DGMGRL> show database qrcl logxptmode;
show database qrcl logxptmode;
LogXptMode = ‘ASYNC’
DGMGRL>

To edit logxptmode:

DGMGRL> edit database ‘qrcl’ set property ‘logxptmode’=’ASYNC’;
edit database ‘qrcl’ set property ‘logxptmode’=’ASYNC’;
Property “logxptmode” updated

Posted in Advanced | Tagged: DataGuard | Leave a Comment »

What are Oracle Managed File (OMF) ?

Posted by FatDBA on September 11, 2012

What are Oracle-Managed Files?
Using Oracle-managed files simplifies the administration of an Oracle database. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle database. You specify operations in terms of database objects rather than filenames.

– Provides default location, name and size
– OMF is still optional. Normal file creation techniques still available

Oracle internally uses standard file system interfaces to create and delete files as needed for the following database structures:

Tablespaces
Online redo log files
Control files

First you have to enable OMF by altering parameter db_create_file_dest.
Example:
SQL> show parameter db_create

NAME TYPE VALUE
———————————— ———– ——————————
db_create_file_dest string

Here i’m trying to create a tablespace with name ‘tb1’ but you’ll recieve error message asking you to provide DATAFILE/TEMPFILE clause.

SQL> create tablespace tbi;
create tablespace tbi
*
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause
Alter parameter db_create_file_dest.
SQL> alter system set db_create_file_dest=’/u01/app/oracle/oradata/’ scope=both;

System altered.
SQL> show parameter db_create_file_dest;

NAME TYPE VALUE
———————————— ———– ——————————
db_create_file_dest string /u01/app/oracle/oradata/

Let’s try to create Tablespace in same fashion we tried earlier.
SQL> create tablespace tb1;

Tablespace created.
Done.

Posted in Advanced | Tagged: Dailies, Database, SQL | 2 Comments »

« Previous Entries

Next Entries »

Tales From A Lazy Fat DBA

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Likes

Archive for the ‘Advanced’ Category

News: ASMlib will not be certified or supported on Red Hat Enterprise Linux 6.

Real-Time Apply Error: ORA-38500: ‘USING CURRENT LOGFILE option not available without stand’

Data Guard – Adding and Resizing a datafile on the Primary database

What is Yum in Linux and how to configure ?

ALTER DATABASE OPEN RESETLOGS

Contol File Missing – Recovery (ORA-00205: error in identifying control file)

Data Guard: ORA-16789: missing standby redo logs

Data Guard – Error

SYNC Vs ASYNC (Data Guard – Logxptmode)

What are Oracle Managed File (OMF) ?

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Likes

Archive for the ‘Advanced’ Category

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: