Dec 02 2015

Move Datafiles Of Tablespaces With RMAN Set Newname in Oracle 11g

Category: Administration,Backup And RecoveryFatih Acar @ 17:30

You can move datafiles of tablespaces. Scenarios can be asm to file system or file system to file system or file system to file system or asm to asm. Below operations show how to move datafiles. You have to take a backup and database has to be archivelog mode before this operations because of that you do restore and recover operation.

Steps

1 – Database has to be Archivelog Mode
2 – Take Full Rman Backup
3 – Execute Rman Run block

Run Block Description:
Offline Tablespace.
Set Newname.
Restore Datafile To New Location.
Write New Location information to controlfile with switch datafile all.
Recover Datafile.
Online Tablespace.

articleIcon

List Tablespaces

SQL> select tablespace_name from dba_tablespaces;

List Datafile Directories

SQL> select tablespace_name,file_name from dba_data_files;

Move Datafile From Asm To File System

RMAN> RUN {
SQL ‘ALTER TABLESPACE USERS OFFLINE IMMEDIATE’;
SET NEWNAME FOR DATAFILE ‘+DATA/dg/datafile/USERS.329.896267113’ TO ‘/tmp/USERS.329.896267113’;
RESTORE TABLESPACE USERS;
SWITCH DATAFILE ALL;
RECOVER TABLESPACE USERS;
SQL ‘ALTER TABLESPACE USERS ONLINE’;
}

Move Datafile From File System To From Asm
Continue reading “Move Datafiles Of Tablespaces With RMAN Set Newname in Oracle 11g”

7,568 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle Backup and Restore, Oracle Rman Backup, Oracle Rman Restore


Nov 15 2015

Auto Increment Primary Key With Trigger in Oracle 11g

Category: Administration,Procedure,SQLFatih Acar @ 14:50

You can provide auto increment primary with trigger by appling below operations.

Create Table

SQL> CREATE TABLE TESTUSER.TBL_TEST
(id number primary key,
name varchar2(100));

Create Sequence

SQL> CREATE SEQUENCE TESTUSER.SEQ_TEST_ID
START WITH 1
MAXVALUE 999999999
MINVALUE 1
CYCLE
NOCACHE
NOORDER;

Create Trigger For Auto Increment

SQL> CREATE OR REPLACE TRIGGER TESTUSER.TRG_AUTO_INC_TEST_ID
before insert on TESTUSER.TBL_TEST
for each row
begin
if inserting then
if :NEW.”ID” is null then
select TESTUSER.SEQ_TEST_ID.nextval into :NEW.ID from dual;
end if;
end if;
end;
/

Oracle 12c release has primary key auto increment feature with identity type column. You can use like below.
Continue reading “Auto Increment Primary Key With Trigger in Oracle 11g”

6,922 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle SQL Query


May 28 2015

How to Migrate Controlfile From File System to ASM Disk in Oracle 11g

Category: Administration,Backup And RecoveryFatih Acar @ 13:36

You can migrate controlfile from file system to asm disk. You have to stop database and you can do that.

Stop Database

SQL> shutdown immediate;
SQL> startup nomount;

Migrate Controlfile

RMAN> restore controlfile to ‘+DATA’ from ‘/tmp/controlfile_currentlocation.ctl’;
RMAN> restore controlfile to ‘+FRA’ from ‘/tmp/controlfile_currentlocation.ctl’;

Continue reading “How to Migrate Controlfile From File System to ASM Disk in Oracle 11g”

7,480 total views, no views today

Tags: Oracle, Oracle Administration, Oracle Backup and Restore


Mar 09 2015

Oracle Cluster Service Control Commands CRSCTL

Category: AdministrationFatih Acar @ 09:54

crsctl is Oracle Clusterware management command. You can check, start, stop clusterware with crsctl. crsctl command directory is $GRID_HOME/bin.

You can do below operations with crsctl

  • Starting and stopping Oracle Clusterware resources
  • Enabling and disabling Oracle Clusterware daemons
  • Checking the health of the cluster
  • Managing resources that represent third-party applications
  • Integrating Intelligent Platform Management Interface (IPMI) with Oracle Clusterware to provide failure isolation support and to ensure cluster integrity
  • Debugging Oracle Clusterware components

List Status Of All Crs Services

crs_stat -t

Continue reading “Oracle Cluster Service Control Commands CRSCTL”

14,110 total views, no views today

Tags: crsctl, Oracle, Oracle Administration, Oracle Cluster Management


Apr 17 2014

What is Lightweight Job in Oracle

Category: Administration,ProcedureFatih Acar @ 14:46

Use lightweight jobs when you have many short-duration jobs that run frequently. Under certain circumstances, using lightweight jobs can deliver a small performance gain.

Lightweight jobs have the following characteristics:

  • Unlike regular jobs, they are not schema objects.
  • They have a significant improvement in create and drop time over regular jobs because they do not have the overhead of creating a schema object.
  • They have lower average session creation time than regular jobs.
  • They have a small footprint on disk for job metadata and runtime data.

You designate a lightweight job by setting the job_style job attribute to LIGHTWEIGHT. The other job style is REGULAR, which is the default.

Like programs and schedules, regular jobs are schema objects. In releases before Oracle Database 11g Release 1, regular jobs were the only job style supported by the Scheduler.

A regular job offers the maximum flexibility but does entail some overhead when it is created or dropped. The user has fine-grained control of the privileges on the job, and the job can have as its action a program or a stored procedure owned by another user.

Continue reading “What is Lightweight Job in Oracle”

14,715 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration


Jan 06 2014

Restore And Recover Oracle 11g R2 Database on Windows 2008 R2 Server

Category: Administration,Backup And Recovery,Errors and SolutionsFatih Acar @ 15:43

You can restore and recover Oracle 11g R2 Database from RMAN backup on Windows 2008 R2 Server with below operations. You have to execute Command Prompt as Administrator for all operations.

Restore and Recover Database

Install Oracle software
Install Patch

Create ORACLE_HOME and ORACLE_SID in Server Environment Path.

Copy backup files to C:\orabackup

Create Oracle service with below command

cmd> oradim -NEW -SID YOURDBSID -STARTMODE manual -PFILE “$ORACLE_HOME\dbs\init.ora”

cmd> rman target /

RMAN> set dbid=12412312 Note : DBID writes in Control File backup name, if you use %F file name parameter.

RMAN> startup nomount force;

RMAN> restore spfile from ‘C:\orabackup\controlfilebackupfile’;

RMAN> exit;

cmd> sqlplus / as sysdba

SQL> create pfile=’C:\orabackup\pfile.ora’ from spfile;

You have to create archivelog directory and other directories in pfile.ora. You have to copy archivelog file to archivelog directory.

SQL> create spfile from pfile=’C:\orabackup\pfile.ora’;

SQL> shu abort;

SQL> startup nomount;

cmd> rman target /

RMAN> restore controlfile from ‘C:\orabackup\controlfilebackupfile’;

Control files restored to directories that are in pfile.ora

SQL> alter database mount;

If you use parallel backup to take backup, you can select backups like below.

RMAN> catalog backuppiece ‘C:\orabackup\backupfile1.bck’,’C:\orabackup\backupfile2.bck’;

RMAN> list backup;

You have to create directory for datafiles. Directories show in list backup;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;

ORA-00392 log 3 of thread 1 is being cleared, operation not allowed
ORA-00312 online log 3 thread 1: ‘path’

If you get above error, you have to create path that writes in error. You get error because system can not create directory for redo logs.
You have to execute below SQL to clear logfile, you can open database.

SQL> alter database clear unarchived logfile group 3;

SQL> alter database open resetlogs;

SQL> select status from v$instance;

75,534 total views, 10 views today

Tags: Backup and Recovery, Database Administration, Oracle, Oracle Administration, Oracle Backup and Restore, Oracle Rman Backup


Dec 16 2013

ORA-03113: end-of-file on communication channel when startup

Category: Errors and SolutionsFatih Acar @ 15:37

If your fra disk or archivelog directory full, your db can not process any transaction. When you increase disk size, database does not recover itself. You have to do some process.

Error

ORA-03113

Solution
Continue reading “ORA-03113: end-of-file on communication channel when startup”

36,234 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle Error Solutions


Dec 11 2013

Oracle Control Files Content

Category: AdministrationFatih Acar @ 11:14

A control file contains information about the associated database that is required for access by an instance, both at startup and during normal operation. Control file information can be modified only by Oracle Database; no database administrator or user can edit a control file.

A control file contains

  • The database name
  • The timestamp of database creation
  • The names and locations of associated datafiles and redo log files
  • Tablespace information
  • Datafile offline ranges
  • The log history
  • Archived log information
  • Backup set and backup piece information
  • Backup datafile and redo log information
  • Datafile copy information
  • The current log sequence number
  • Checkpoint information

The database name and timestamp originate at database creation. The database name is taken from either the name specified by the DB_NAME initialization parameter or the name used in the CREATE DATABASE statement.

Each time that a datafile or a redo log file is added to, renamed in, or dropped from the database, the control file is updated to reflect this physical structure change. These changes are recorded so that:

Oracle Database can identify the datafiles and redo log files to open during database startup
Continue reading “Oracle Control Files Content”

11,166 total views, 5 views today

Tags: Database Administration, Oracle, Oracle Administration


Dec 08 2013

Oracle Flashback Technology

Category: AdministrationFatih Acar @ 11:20

When authorized people make mistakes, you need the tools to correct these errors. Oracle Database provides a family of human error correction technology called Flashback. Flashback revolutionizes data recovery. In the past, it might take minutes to damage a database but hours to recover it. With Flashback technology, the time to correct errors equals the time it took to make the error. It is also easy to use a single, short command to recover the entire database instead of following some complex procedure.

Flashback is unique to Oracle Database and provides:

  • A SQL interface to quickly analyze and repair human errors.
  • Fine-grained surgical analysis and repair for localized damage, such as when the wrong customer order is deleted.
  • Correction of more widespread damage yet does it quickly to avoid long downtime, such as when all of this month’s customer orders have been deleted.
  • Recovery at all levels including the row, transaction, table, tablespace, and database wide.

Continue reading “Oracle Flashback Technology”

21,342 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle Backup and Restore


Oct 06 2013

Oracle Cold-Offline Backup And Restore

Category: Administration,Backup And RecoveryFatih Acar @ 14:13

You have to shutdown database to take offline backup. After shutdown database, you can take backup with two way that are rman backup database or copy Data Directory that is include datafiles, redo log files and control files.

Offline-Cold Backup Database

1- Take Offline Backup With Rman

SQL> shutdown immediate;
SQL> startup mount;
RMAN> backup database;

2- Take Offline Backup With Copy Data Directory

#> cp /data/ /backup/
Note : this /data/ directory includes data files, control file and redo log files.

Restore Database From Offline-Cold Backup

1- Restore From Data Directory Copy

Install Oracle Software
Edit init.ora parameter file. That is in /dbhome/dbs/init.ora
Change
values with Oracle base path and change db name parameter.
Change Memory Target
Add directory for not exists directories where pfile.ora. For example, diagnostic directory.
Connect Oracle Db sqlplus / as sysdba
SQL> create spfile from pfile=’/pathofparameterfile/init.ora’
SQL> startup;
Restore is completed.

Continue reading “Oracle Cold-Offline Backup And Restore”

25,354 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle Backup and Restore, Oracle Rman Backup


Next Page »