Tag: archive log

LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_1

Posted on by Osama Mustafa in Database

LOG_ARCHIVE_DEST 
is applicable only if we are running the database in ARCHIVELOG mode. LOG_ARCHIVE_DEST parameter are used to specified the archiving location. The Location specified by log_archive_dest must be local . We choose to archive only two local location  i.e,  primary and a secondary destination ( using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST ) .

LOG_ARCHIVE_DEST_n 
initialization parameter defines up to ten (where n = 1, 2, … 10) destinations in oracle 10gand thirty one (n=1,2….31)  destination in oracle 11g , each of which must specify either the  LOCATION or  the SERVICE  attribute to specify where to archive the redo data. All other attributes are optional.We set the attributes for the LOG_ARCHIVE_DEST_n initialization parameter to control different aspects of how redo transport services transfer redo data from a production or primary database destination to another (standby) database destination.For every LOG_ARCHIVE_DEST_n initialization parameter that we define, we must specify corresponding LOG_ARCHIVE_DEST_STATE_n parameter. The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 10) initialization parameter specifies whether the corresponding destination is currently enabled or disabled.
 

LOG_ARCHIVE_FORMAT 

Syntax : LOG_ARCHIVE_FORMAT = ‘log%t_%s_%r.arc’

is applicable only if you are using the redo log in ARCHIVELOG mode. Use a text string and variables to specify the default filename format when archiving redo log files. The string generated from this format is appended to the string specified in the LOG_ARCHIVE_DEST parameter.

The following variables can be used in the format:

%s log sequence number
%S log sequence number, zero filled
%tthread number
%Tthread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.

Reference Document :
1-Oracle Log_Archive_log Documentation.
2-Oracle Log_arcchive_Dest

Unable to login to Database Because Archivelog

Posted on by Osama Mustafa in Database
Regarding to heavy duty On Database , Database start Generates Archive log with time size for flash recovery become full , so what i have to do :

first you will not be able login for database or rman

Solution :

Step1 :

-Go to archivelog detestation and delete archivelog (old first) , if you have space on your server you can move them from direcotry to another .

-Flash Recovery On ASM , Follow the below steps

export ORACLE_SID=+ASM1
asmcmd 

now you are inside ASM head to Archivelog destation and delete archive log manually (old first )

 Step 2 :

Now you will be able to login to rman :

Run
{

ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT '/u01/'; 
backup archivelog all delete input ;
release c1 ; }

 Now you clean Archivelog .

THis solution for Both Cluster and Single Node Database

Enjoy

Thank you
Osama mustafa

Clone Oracle Database Steps

Posted on by Osama Mustafa in Database
First :  What is Oracle Database Clone :

complete and separate copy of a database system that includes the business data, the DBMS software and any other application tiers that make up the environment. Cloning is a different kind of operation to replication and backups in that the cloned environment is both fully functional and separate in its own right. Additionally the cloned environment may be modified at its inception due to configuration changes or data subsetting.

Second : Benefit Of Clone : 

1-useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas. 
2-quickly migrate a system from one server to another .
3-fastest way to copy a Oracle database .

How We do it :


Step-1 : On the old system, go into SQL*Plus, sign on as SYSDBA and issue: 

SQL>alter database backup controlfile to trace;

copy and paste the below lines from your user trace file and save it as dbclone_controlfile_creation.sql in any location in your system .


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “OLDLSQ” NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 (‘/u01/oradata/oldlsq/log1a.dbf’,
‘/u01/oradata/olslsq/log1b.dbf’) SIZE 30M,
GROUP 2 (‘/u01/oradata/oldlsq/log2a.dbf’,
‘/u01/oradata/oldlsq/log2b.dbf’) SIZE 30M
DATAFILE
‘/u01/oradata/oldlsq/system01.dbf’,
‘/u01/oradata/oldlsq/mydatabase.dbf’
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

 

 Step-2 : Shutdown Old Database

SQL>Shutdown immediate ;

Step-3 : Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following: 


CREATE CONTROLFILE REUSE DATABASE “OLDLSQ” NORESETLOGS

CREATE CONTROLFILE SET DATABASE “NEWLSQ” RESETLOGS

 Step-4 :Remove the “recover database” and “alter database open” syntax 


Step-5:Re-names of the data files location to new clone location in a dbclone_controlfile_creation.sql.

Old:

DATAFILE
‘/u01/oradata/oldlsq/system01.dbf’,
‘/u01/oradata/oldlsq/mydatabase.dbf’

New:

DATAFILE
‘/u01/oradata/newlsq/system01.dbf’,
‘/u01/oradata/newlsq/mydatabase.dbf’

Step-6: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

Step-7 : Create the bdump, udump and cdump directories
  

 cd /u01/admin/
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile

Step-8 : Copy-over the old init.ora file 


open the primary database pfile in a notepad or text editor and change the udump,bdump,pfile,controlfile destination and save it in new clone pfile location newlsq folder and save as newlsq.ora 
eg: primary database location /u01/admin/oldlsq/pfile/oldlsq.ora
open that oldlsq.ora file in a text editor or notepad and change the required destinations cdump,udump,bdump,controlfile destinations and save as newlsq.ora in the below destination
/u01/admin/newlsq/pfile/newlsq.ora

Step-9 : Start the new database

SQL>startup nomount pfile=’ /u01/admin/newlsq/pfile/newlsq.ora ‘ ;
SQL> @dbclone_controlfile_creation.sql
SQL>alter database open resetlogs;

SQL>create spfile from pfile;

 Step-10 : Place the new database in archivelog mode.




Thank you 
osama mustafa

Change to Archive Log Mode In Oracle Rac

Posted on by Osama Mustafa in Database
1. Disable clustering putting cluster_database parameter FALSE.

$export ORACLE_SID=ORCL1
$sqlplus “/ as sysdba”

Check the status of archive mode of the database:

SQL>archive log list

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     407
Current log sequence           408

SQL> alter system set cluster_database=false scope=spfile sid=’ORCL1′;

2. Shutdown all instances using srvctl utilty

$ srvctl stop database -d cobra

3. Mount tha database using one of the instance:

$ sqlplus “/ as sysdba”

SQL> startup mount

4. Enable archivelog using following command:

SQL> alter database archivelog;

5. Re-enable clustering putting instance parameter cluster_database to TRUE from the current instance:

SQL> alter system set cluster_database=true scope=spfile sid=’ORCL1′;

6. Shutdown the local instance:

SQL> shutdown immediate

7. Startup all instances using srvctl utility:

#srvctl start database -d orcl

8. If any service is not up then get up those using srvctl utility:

#srvctl start service -dorcl

9. And now check archivelog mode is enabled or not using following:

$sqlplus “/ as sysdba”

SQL> archive log list

Database log mode            Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     407
Current log sequence           408

Thank you
Osama mustafa