Resize redo logs in standby configuration oracle dba. Pro oracle database 11g rac on linux provides fulllifecycle guidance on implementing oracle real application clusters in a linux environment. Aug 12, 2012 when the oracle database archives redo log files, the archived log retains its log sequence number. Of course, you can change the size of redo logs for your needs in dbca if you know where to make the change. Basically, we need to add new redolog groups in required diskgroup and drop the old ones. How to resize online redo log files in rac database with asm. Drop and recreate online redolog files oracle dba tips and. Mar 05, 20 how to resize redo log and standby redo log in oracle dataguard environment. Delete archivelog files without using rman oracle dba and. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. You drop the online redo log group using the alter database drop logfile group command as seen in this example. If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.
If you have a 100 log switches an hour with 50mb redo logs, you need e. Rac redo log thread maintenance burleson oracle consulting. Jan 30, 2017 now the current redo log file will turns into active redo log file which means that file information are written into archive log through arcn once that written process is over an archive log then it turns into inactive redo log file creating group and member in redo log. The redo logfile contains the history of changes data in database alter system switch logfile. When a redo log group is dropped from the database, and you are not using the oraclemanaged files feature, the operating system files are not deleted from disk. Every oracle database must have at least 2 redo logfile groups. Mar 02, 2008 resizing the online redologs on a rac environment is a simple task, that require a little more attention than that of a single instance. What are the possible causes of premature redo log switching in oracle other than reaching the specified file size and executing alter system switch logfile we have a situation where some but not all of our nodes are prematurely switching redo log files before filling up. The source and target databases are 2node oracle rac 11gr2. Maintaining standby database as rac with active dataguard and a realtime apply feature allows using it for reading, backup, and testing by converting it over to a snapshot standby database and back after the completion. Richard niemiec two potential problems are possible that should be addressed when considering whether to increase the size of oracle log files. When you use dbca to create the database, dbca allocates redo log files to instances, as required, automatically. In an oracle rac database, each instance must have at least two groups of redo log files.
Disabling an old rac instance thread the great grandson of. I needed to resize the redo log as it takes about 4 days to do a log switch. Oracle writes all statements except, select statement, to the logfiles. Redo log files are written by redo log writter process. Renamingdropping and recreating online redo logs and standby. Each online or archived redo log file is uniquely identified by its log sequence number. Resizing the online redologs on a rac environment is a simple task, that require a little more attention than that of a single instance. You can buy it direct from the publisher for 30%off and get instant access to the code depot of oracle tuning scripts. Redo group 3 is active on instance 2 and instance acts as recovery instance once instance 2 is down, therefore redo group 1 is active on instance 2. Youre required to have at least two online redo log groups in your. Oct 23, 2012 to add or resize redo logfiles in a rac steps are. There are exception to this rule, suppose, your redo size is high and two log files were filled up quickly.
So while adding redolog group, we need to mention the thread parameter. How to resize redo on rac instance with asm option helioss. Intention is to drop and recreate these online logs with a different size. It is similar to that of multiplexing in standalone, only change is rac will have threads for node. Each database consists of two or more redo log files. How to resize the redo logfile online redo logfile in oracle 11g. Select the database which needs to be dropped, if multiple databases are running. Delete archivelog files without using rman here i am explaining two methods to delete archive logs and other database files with out using rman from asm disk group in oracle 10g11g. The recommendation is to have log switches occur a few times an hour. It is available on linux, windows, solaris, hpux and aix platforms as well as the oracle cloud.
Rac redo log thread maintenance burleson consulting. Recovering from the loss of redo log files in the following post well go through every possible scenario where we loose redo, one by one. For example, you want to reduce the number of groups in an instance redo log. If you have multiple log files present in your redo log group and one of them is corrupted, you dont need to drop the whole log group. Generally speaking, redo and undo are handled on a perinstance basis. Redo is the most crucial part when we talk about recovery and contains the current transaction details, hence if the data is redo is lost point time recovery is impossible depending on the scenario. Recovering after the loss of online redo log files. Disconnected from oracle database 10g enterprise edition release 10. How to determine if the oracle redo log file size is a problem. Jan 02, 2008 redo log files are written by redo log writter process.
Sep 01, 2016 steps to add redo log groups to oracle rac setup. Dec 21, 2009 redo group 3 is active on instance 2 and instance acts as recovery instance once instance 2 is down, therefore redo group 1 is active on instance 2. Your course of action will depend on other factors you can read about in the documentation. Recovering after the loss of all members of an online redo log group. How to multiplex redo log files in oracle 11g blogger. Now the current redo log file will turns into active redo log file which means that file information are written into archive log through arcn once that written process is over an archive log then it turns into inactive redo log file creating group and member in redo log. However there are simple command for reading the logfile. During crash, instance, or media recovery the oracle database applies redo log files in ascending order, by using the log sequence number of the archived and. Now you can safely delete inactive redo log group from the same thread.
Recreate online redo logs and standby logs oracle dba. Recognizing his efforts and contribution towards the oracle community, oracle awarded him the prestigious best dba of the year, 2011 and oracle ace director status. Drop and recreate online redologs on the primary site. Jun 29, 2009 in a dataguard environment, if online redologs are resized, the standby logs also need to be resized to match the online redo logs in the primary database. How to change the default session timeout for database controldb control. This is commonly done if you need to increase or reduce the size of the online redo logs. How to resize the redo logfile online redo logfile in oracle.
Of course, there are times that you will want to drop a given redo log group. To get immediate access to the code depot of working rac scripts, buy it directly from the. If you have two diskgroup, you want to multiplex redo in different diskgroup, you just add redo log desitnation diskgroup. For example, a file might be stored in an inappropriate location.
You can drop an inactive log group with the alter database. To change the status of a log group from current to inactive, simply switch a logfile with this command sql alter system switch logfile. In a different case, you may want to drop one or more specific redo log members. Run the command 2 or 3 times if the group status does not change. How to resize the online redologs on a rac environment. For dropping an online redo log group, use the alter database statement with the. Mar 08, 2018 recognizing his efforts and contribution towards the oracle community, oracle awarded him the prestigious best dba of the year, 2011 and oracle ace director status. Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. Premature redo log switching in oracle rac stack overflow. Mar 09, 2011 switch until we are into log group 5,6,7,8 so we can drop log groups 1, 2,3 and 4. Rather, the control files of the associated database are updated to drop the members of the group from the database structure. Dumping redo log file information oracle database 10g.
Usually we do resize when redolog switches happen too frequently. In particular the section titled recovering after losing of all members of an online redo log group. Therefore, if an oracle rac database comprises three instances, each instance must have two groups of redo logs, one set for each instance, for a minimum of six online redo log groups. This is done because oracle performs deferred batch writes i. In some cases, you may want to drop an entire group of redo log members. Each time switch log file and make sure the new redo log member is used. Oracle database 19c, is the long term support release of the oracle database 12c and 18c family of products, offering customers premier and extended support through to march 2023 and march 2026 respectively. Oracle dataguard how to resize redo log and standby redo. This is not a strict rule, but generally happens most of the time. I had a situation where i wanted to read the blocks of online redo log files. This parameter is commonly used in oracle data guard environments to force log switches after the. Firstly oracle will never allow you to drop the current online redolog file.
The recommended log switch frequency is at least once an hour. How to resize the online redo logfiles oracle rac dba. Jul 29, 2009 to change the status of a log group from current to inactive, simply switch a logfile with this command sql alter system switch logfile. Perform above mentioned steps one by one for each thread. Now drop standby redo logs, leave the one which refuses to drop. To drop a redo log group, you must have the alter database system. Note in an oracle real application cluster rac database, each. How to sync standby database which is lagging behind from primary database. Logfile group 1 u01oracleredofile1a, u02oracleredofile1b size 50m.
Drop and recreate online redolog files oracle dba tips. The files are written in oracle proprietary format and cannot be read directly. Managing the redolog in oracle11g ampersand academy. See the oracle database backup and recovery users guide. To drop a logfile, give command for example, i am using mine. Now to drop and recreate online redolog files in the same directory. Deletedrop a redo log file or a redo log group in oracle. How to determine if the oracle redo log file size is a problem by. Firstly oracle will never allow you to drop the current. Nov 29, 2007 especially to benefit from migrations most to remove the cbo parameters and let them to take their version specific default values and then monitor and tune is a better strategy. New log group member files may need to be added, data files may need to be.
Delete archivelog files without using rman here i am explaining two methods to delete archive logs and other database files with out using rman from asm disk group in oracle 10g 11g. Resize redo logs the default size of redo logs during installation is 50mb which is too small for production databases. Clear logfile is similar to adding and dropping a redo log except that the. Jul 23, 2019 every oracle database must have at least 2 redo logfile groups. Recovering from the loss of redo log files oracle database. In a dataguard environment, if online redologs are resized, the standby logs also need to be resized to match the online redo logs in the primary database. Method to drop and recreate online redolog files with 2 members to each group. Let us we discuss how to resize the redo logfile online redo logfile in oracle 11g. Moreover, try to query both sql commands that are written above after each time you add and drop. It is not possible to resize the redo log or standby redo log srl dynamically. Database recovery on loss of all online redo log files with.
When the oracle database archives redo log files, the archived log retains its log sequence number. Before resize each thread was having 3 redo log groups with 1 member of 50mb in each group. May 08, 2012 if you have two diskgroup, you want to multiplex redo in different diskgroup, you just add redo log desitnation diskgroup. When do i use the alter database add logfile to add an online redo log can i use the alter database add logfile when adding a logfile group. First, i have to drop group 2 because group ones status is current.
1019 1581 124 1169 558 125 1621 247 111 198 131 1023 1568 297 319 189 537 898 418 1569 136 301 780 663 1102 1392 761 309 1146