DATABASE

CONFIGURE OF CATALOG IN ORACLE 11G FOR RMAN

Target Database Side(hrms)

Catalog Database Side(catdb)

$export ORACLE_SID=hrms

$sqlplus / as sysdba

sql>startup

sql>archive log list

(should be enabled)

create listener.ora file manually

[oracle@mac1 admin]$ cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = mac1.oracle.com)(PORT = 7777))

#(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC=

(SID_NAME=hrms)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

)

ADR_BASE_LISTENER = /u01/app/oracle

:wq

$lsnrctl start LISTENER

create password file

$cd $ORACLE_HOME/dbs

$orapwd file=orapwhrms password=sys force=y

Catalog Database Side

=====================

$export ORACLE_SID=catdb

$sqlplus / as sysdba

sql>startup mount;

sql>alter database archivelog;

sql>alter database open;

Create tnsnames.ora file manually

+++++++++++++++++++++++++++++++++++++++

[oracle@lnx02 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

TO_HRMS =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = lnx01.oracle.com/or ip address of target database)(PORT = 7777))

)

(CONNECT_DATA =

(SERVICE_NAME = hrms)

)

$tnsping to_hrms

sql>create tablespace rmantbs datafile '/u01/app/oracle/catdb/rmantabs.dbf' size 100m;

sql>create user rmanu identified by rmanu;

sql>alter user rmanu default tablespace rmantbs;

sql>grant connect,resource,recovery_catalog_owner to rmanu;

sql>exit

$rman catalog rmanu/rmanu

rman>create catalog;

rman>exit

sql>conn rmanu/rmanu

sql>select count(*) from tab;

$rman catalog rmanu/rmanu target sys/sys@to_hrms

rman>register database;

rman>backup database;

CONVERTING TO SNAPSHOT DATABASE

Technical Articles

CONVERTING PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE
Step 1: Check if Flashback is enabled. If not, enable in mount state in Physical Standby database
i) SQL> Show parameter db_recovery_file_dest
NAME TYPE VALUE
--------------------------------------------------------------- ----------- ------------------------------
db_recovery_file_dest string u03/oradata/ODSP1/flash_recovery_area/
db_recovery_file_dest_size big integer 2G
ii) SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
iii) SQL> Shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
iv) SQL> startup mount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 162887636 bytes
Database Buffers 41943040 bytes
Redo Buffers 4096000 bytes
Database mounted.
v) SQL> alter database flashback on;
Database altered.
vi) SQL> select flashback_on from v$database;
FLASHBACK
---------
YES
vii) SQL> alter database open;
Database altered.
viii) SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
---------------------------------------- ---------- ------- ----------------
OLPTP1 READ ONLY NONE PHYSICAL STANDBY
Step 2 : Cancel recovery from Primary Database to Physical Standby Database
SQL> alter database recover managed standby database cancel;
Database altered.
Step 3 : Converting Physical Standby database to Snapshot Standby database
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.
Step 4: Shutdown the Standby Database Normal
SQL> shut immediate
ORA-01507: Database is niet aangekoppeld.
ORACLE instance shut down.
Step 5: Startup Standby database Normal
SQL> Startup
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2166288 bytes
Variable Size 2852127216 bytes
Database Buffers 1308622848 bytes
Redo Buffers 12652544 bytes
Database mounted.
Database opened.
Step 6: Check Database role
SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
-------------------------------------- ---------- ------- -------------------------------
OLPTP1 READ WRITE NONE SNAPSHOT STANDBY
CONVERTING SNAPSHOT STANDBY DATABASE TO PHYSICAL STANDBY DATABASE
Step 1: Check for current database role
SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
----------------------------------------- ---------- ------- ----------------
OLPTP1 READ WRITE NONE SNAPSHOT STANDBY
Step 2 : Shutdown Snapshot Standby Database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 3 : Start in Nomount mode
SQL> Startup nomount pfile='$ORACLE_HOME/dbs/initOLPTP1.ora'
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2166288 bytes
Variable Size 2852127216 bytes
Database Buffers 1308622848 bytes
Redo Buffers 12652544 bytes
Step 4: Mounting Snapshot Standby Database
SQL> alter database mount;
Database altered.
Step 5: Converting Snapshot Standby to Physical Standby Database
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
Step 6: Shutdown the database
SQL> shut immediate
ORA-01507: Database is niet aangekoppeld.
ORACLE instance shut down.
Step 7: Starting database in Nomount mode and mount
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initOLPTP1.ora'
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2166288 bytes
Variable Size 2852127216 bytes
Database Buffers 1308622848 bytes
Redo Buffers 12652544 bytes
SQL> alter database mount standby database;
Database altered.
Step 8 : Cancel recovery from primary database and open database
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;
Database altered.
Step 9 : Check for redo’s are applied in standby database
SQL> select process, status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
RFS IDLE
IF RFS is not there, it indicates that there is no communication between primary and standby
Check below query in primary database
Step 10: Check in primary database
SQL>select error from v$archived_dest;
The output shows "END OF FILE on COMMUNICATION FILE or ORACLE NOT FOUND".
Wait until this error get cleared (it will be cleared automatically after some minutes)
Implies that redos are transferring, but not applied as we issued recover managed standby database cancel (As per Step 8)
Step 11 : Recover Physical Standby database
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> select process, status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_GAP
RFS IDLE
6 rows selected.
Shows that archives apply is under process as we are recovering standby database
(IMPORTANT NOTE : RFS - implies redo transfer from primary to standby is under process.
MRP0 - indicates it is applying archive logs in standby)
Step 12 : Check for database role
SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
--------------------------------- ---------- ------- ----------------
OLPTP1 READ ONLY NONE PHYSICAL STANDBY

CONFIGURATION OF PHYSICAL STANDBY DATABASE IN ORACLE 11G

configuration of physical standby database in oracle 11g

Pre-requisite

1)create 2 vm machine having OEL5 and oracle 11g install

2)let the machine name,ip and database is like below

Machine name:- lnx01.oracle.com (Primary) IP:- 192.168.100.101

Machine name:- lnx02.oracle.com(Standby) IP:- 192.168.100.102

Database name in primary side:- hrms

Primary side

==========

$export ORACLE_SID=hrms

$sqlplus / as sysdba

sql>select name,open_mode,log_mode,force_logging from v$database;

sql>alter database force logging;

sql>create pfile from spfile;

sql>shut immediate;

sql>exit

$cd $ORACLE_HOME/dbs

$mv spfilehrms.ora spfilehrms.ora.bkp

$cd

$export ORACLE_SID=hrms

$sqlplus / as sysdba

sql>startup

sql>show parameter spfile

sql>exit

$export ORACLE_SID=hrms

$sqlplus / as sysdba

sql>startup

sql>show parameter spfile

sql>select name from v$datafile;

sql>select name from v$controlfile;

sql>select member from v$logfile;

sql>archive log list

sql>shut immediate

sql>exit

$cd $ORACLE_HOME/dbs

$vi inithrms.ora

go to the last and add the below parameter

###########DATAGURAD PARAMTERS FOR Primary Role Initialization Parameters

log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'

log_archive_dest_2='SERVICE=to_hrms_stdby DB_UNIQUE_NAME=hrms_stdby'

log_archive_config='DG_CONFIG=(hrms,hrms_stdby)'

db_unique_name=hrms

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

########Standby Role Initialization Parameters

fal_server=to_hrms_stdby

standby_file_management=auto

:wq

CONFIGURE THE LISTENER IN PRIMARY SIDE

$cd $ORACLE_HOME/network/admin

$vi listener.ora

[oracle@lnx01 admin]$ cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = lnx01.oracle.com)(PORT = 7777))

#(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC=

(SID_NAME=hrms)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

)

ADR_BASE_LISTENER = /u01/app/oracle

:wq

$vi tnsnames.ora

[oracle@lnx01 admin]$ cat tnsnames.ora

TO_HRMS =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = lnx01.oracle.com)(PORT = 7777))

)

(CONNECT_DATA =

(SERVICE_NAME = hrms)

)

TO_HRMS_STDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = lnx02.oracle.com)(PORT = 7777))

)

(CONNECT_DATA =

(SERVICE_NAME = hrms_stdby)

)

[oracle@lnx01 admin]$

:wq

CONFIGURE NETWORK SERVICE IN STANDBY SIDE

[oracle@lnx02 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_home/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = lnx02.oracle.com)(PORT = 7777))

)

SID_LIST_LISTENER1 =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = hrms_stdby)

)

ADR_BASE_LISTENER1 = /u01/app/oracle

[oracle@lnx02 admin]$

:wq

[oracle@lnx02 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_home/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

TO_HRMS =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = lnx01.oracle.com)(PORT = 7777))

)

(CONNECT_DATA =

(SERVICE_NAME = hrms)

)

TO_HRMS_STDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = lnx02.oracle.com)(PORT = 7777))

)

(CONNECT_DATA =

(SERVICE_NAME = hrms_stdby)

)

[oracle@lnx02 admin]$

:wq

Primary side

======

$cd $ORACLE_HOME/dbs

$scp inithrms.ora lnx02:/home/oracle

password:oracle

Standby side

=========

$cp inithrms.ora inithrms_stdby.ora

$mv inithrms_stdby.ora $ORACLE_HOME/dbs

$cd $ORACLE_HOME/dbs

$vi inithrms_stdby.ora

[oracle@lnx02 dbs]$ cat inithrms_stdby.ora

hrms.__db_cache_size=520093696

hrms.__java_pool_size=16777216

hrms.__large_pool_size=16777216

hrms.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

hrms.__pga_aggregate_target=520093696

hrms.__sga_target=771751936

hrms.__shared_io_pool_size=0

hrms.__shared_pool_size=201326592

hrms.__streams_pool_size=0

*.audit_file_dest='/u02/app/oracle/admin/hrms/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u02/app/oracle/hrms/control01.ctl','/u02/app/oracle/fast_recovery_area/hrms/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='hrms'

*.db_recovery_file_dest='/u02/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4196401152

*.diagnostic_dest='/u02/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=hrmsXDB)'

*.memory_target=1284505600

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

################DATAGUARD PARAMETERS FOR PRIMARY ROLE##############

log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'

log_archive_dest_2='SERVICE=to_hrms_stdby DB_UNIQUE_NAME=hrms_stdby'

log_archive_config='DG_CONFIG=(hrms,hrms_stdby)'

db_unique_name=hrms_stdby

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

###############STANDBY ROLE PARAMETER############################

fal_server=to_hrms_stdby

standby_file_management=auto

db_file_name_convert=('/u01/app/oracle/hrms/','/u02/app/oracle/hrms/')

log_file_name_convert=('/u01/app/oracle/hrms/','/u02/app/oracle/hrms/')

:wq

lnx02# mkdir -p /u02/app/oracle

lnx02# mkdir -p /u02/app/oracle/hrms

lnx02# mkdir -p /u02/app/oracle/fast_recovery_area

lnx02# mkdir -p /u02/app/oracle/fast_recovery_area/hrms

lnx02# chown -R oracle:oinstall /u02/app/oracle/

lnx02# chown -R oracle:oinstall /u02/app/oracle/hrms/

lnx02# chown -R oracle:oinstall /u02/app/oracle/fast_recovery_area/

lnx02# chown -R oracle:oinstall /u02/app/oracle/fast_recovery_area/hrms/

lnx02# chmod -R 775 /u02/app/oracle/

lnx02# chmod -R 775 /u02/app/oracle/hrms/

lnx02# chmod -R 775 /u02/app/oracle/fast_recovery_area/

lnx02# chmod -R 775 /u02/app/oracle/fast_recovery_area/hrms/

lnx02#

/lnx02$ cd /u02/app/oracle/hrms/

lnx02$ mkdir adump

##############CONFIGURE PASSWORD FILE BOTH PRIMARY AND STANDBY

[oracle@lnx02 dbs]$ orapwd file=orapwhrms_stdby password=rac entries=3 force=y

[oracle@lnx02 dbs]$

[root@lnx01 ~]# su - oracle

[oracle@lnx01 ~]$ cd $ORACLE_HOME/dbs

[oracle@lnx01 dbs]$ orapwd file=orapwhrms password=rac entries=3 force=y

[oracle@lnx01 dbs]$

###########START THE PRIMARY DATABASE

[oracle@lnx01 ~]$ export ORACLE_SID=hrms

[oracle@lnx01 ~]$ sqlplus / as sysdba

SYS>startup

##########START THE STANDBY DATABASE

[oracle@lnx02 ~]$ export ORACLE_SID=hrms_stdby

[oracle@lnx02 ~]$ sqlplus / as sysdba

SQL> startup nomount

SYS>select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

hrms_stdby STARTED

[oracle@lnx01 ~]$ export ORACLE_SID=hrms

[oracle@lnx01 ~]$ rman target sys/rac auxiliary sys/rac@to_hrms_stdby

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Dec 19 18:04:35 2012

connected to target database: HRMS (DBID=850606497)

connected to auxiliary database: HRMS (not mounted)

RMAN>

RMAN> duplicate target database for standby from active database;

CREATION OF STANDBY REDO LOG FILE IN BOTH THE SIDE

PRIMARY SIDE

==========

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo1.log') size 50m;

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo2.log') size 50m;

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo3.log') size 50m;

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo4.log') size 50m;

sql>select member from v$logfile where type = 'STANDBY';

sql>select member from v$logfile;

STANDBY SIDE

============

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo1.log') size 50m;

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo2.log') size 50m;

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo3.log') size 50m;

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo4.log') size 50m;

sql>select member from v$logfile where type = 'STANDBY';

sql>select member from v$logfile;

Standby side

sql>select name,open_mode from v$database;

sql>archive log list

sql>alter database recover managed standby database disconnect from session;

sql>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

Primary side

sql>select name,open_mode from v$database;

sql>archive log list

sql>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

THE SEQUENCE AND APPLIED COLUMN SHOULD BE MATCHED IN BOTH THE SIDE OF THE SERVER.

ORA-600 complete resolution

Resolution of ORA-600 error ;

This is one of the solution using which you can correct ORA-600 error.

1) check SGA_TARGET parameter
SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
sga_target                           big integer 40G
----- change value from 40g to 60g

SQL> ALTER SYSTEM SET SGA_TARGET=60G SCOPE=SPFILE;
SQL> ALTER SYSTEM SET SGA_TARGET=15G SCOPE=SPFILE;

2) check parameter pga_aggregate_target.
SQL> show parameter pga_aggregate_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
pga_aggregate_target                 big integer 60G

----- change the value from 60g to 40g
SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET=40G SCOPE=SPFILE;
SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET=5G SCOPE=SPFILE;

SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      D:\ORACLE\PRODUCT\11.2.0\DB_1\DATABASE\SPFILEDEVL.ORA

SQL> create pfile='D:\oracle\product\11.2.0\db_1\database\init_devl1.ora' from spfile='D:\oracle\product\11.2.0\db_1\database\SPFILEDEVL.ORA';

*.pga_aggregate_target=42949672960
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=660
*.sga_max_size=64424509440
*.sga_target=64424509440
*.standby_file_management='AUTO'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'

3) Now shutdown the Production database.

4) Now Create pfile from spfile.
SQL> create pfile='D:\oracle\product\11.2.0\db_1\database\init_prod1.ora' from spfile='D:\oracle\product\11.2.0\db_1\database\SPFILEPROD.ORA';

5) Remove *._pga_max_size=10737418240 parameter from pfile.

6) Now start database using pfile.
c:\> set ORACLE_SID=prod
c:\> sqlplus / as sysdba
SQL> startup pfile='D:\oracle\product\11.2.0\db_1\database\init_prod1.ora'

7) Now create spfile from pfile.
SQ> create spfile='D:\oracle\product\11.2.0\db_1\database\SPFILEPROD.ORA' from pfile='D:\oracle\product\11.2.0\db_1\database\init_prod1.ora';
File Created.

8) If database open successfully , then shutdown the database.
SQL> shut immediate;

9) Now start database again using spfile.
SQL> startup
Database open.

10) Now check following two views, if both view show same result then ensure the database is open with spfile.
SQL> show parameter pfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      D:\ORACLE\PRODUCT\11.2.0\DB_1\DATABASE\SPFILEPROD.ORA

SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      D:\ORACLE\PRODUCT\11.2.0\DB_1\DATABASE\SPFILEPROD.ORA

MANUAL RENAMING THE DATABASE

[oracle@demo ~]$ export ORACLE_SID=testdb

[oracle@demo ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 19 08:57:42 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME

---------

TESTDB

SQL> show parameter dump

NAME TYPE VALUE

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

background_core_dump string partial

background_dump_dest string /u01/app/oracle/diag/rdbms/tes

tdb/testdb/trace

core_dump_dest string /u01/app/oracle/diag/rdbms/tes

tdb/testdb/cdump

max_dump_file_size string unlimited

shadow_core_dump string partial

user_dump_dest string /u01/app/oracle/diag/rdbms/tes

tdb/testdb/trace

SQL>

SQL> alter database backup controlfile to trace;

Database altered.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@demo ~]$

[oracle@demo ~]$ cd /u01/app/oracle/diag/rdbms/testdb/testdb/trace

[oracle@demo trace]$ls -ltr

(pick up the last trace file)

[oracle@demo trace]$ cp testdb_ora_5776.trc ~/control.sql

[oracle@demo trace]$vi control.sql

-->d+g+g

startup nomount

create controlfile set database "TEST"............................

characterset US7SCII

;

->d+G

:wq

[oracle@demo ~]$ cat control.sql

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS ARCHIVELOG

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/testdb/redo1.log' SIZE 10M BLOCKSIZE 512,

GROUP 2 '/u01/testdb/redo2.log' SIZE 10M BLOCKSIZE 512,

GROUP 3 '/u01/testdb/redo3.log' SIZE 10M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/testdb/system.dbf',

'/u01/testdb/sysaux.dbf',

'/u01/testdb/undo1.dbf'

CHARACTER SET AL32UTF8

;

[oracle@demo ~]$

SQL> SELECT NAME,STATUS FROM V_$CONTROLFILE;

NAME

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

STATUS

-------

/u01/app/oracle/testdb/control01.ctl

/u01/app/oracle/fast_recovery_area/testdb/control02.ctl

SQL> SHUT IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@demo ~]$ cd /u01/app/oracle/testdb/

[oracle@demo testdb]$ ls

control01.ctl

[oracle@demo testdb]$ mkdir backup

[oracle@demo testdb]$ cp *.ctl backup

[oracle@demo testdb]$ rm *.ctl

[oracle@demo testdb]$ ls

backup

[oracle@demo testdb]$ cd backup/

[oracle@demo backup]$ ls

control01.ctl

[oracle@demo backup]$ cd ..

[oracle@demo testdb]$ cd ..

[oracle@demo oracle]$ cd fast_recovery_area/testdb/

[oracle@demo testdb]$ ls

control02.ctl

[oracle@demo testdb]$ mkdir backup

[oracle@demo testdb]$ cp *.ctl backup

[oracle@demo testdb]$ ls

backup control02.ctl

[oracle@demo testdb]$ rm *.ctl

[oracle@demo testdb]$ ls

backup

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 171581440 bytes

Fixed Size 1343668 bytes

Variable Size 117444428 bytes

Database Buffers 50331648 bytes

Redo Buffers 2461696 bytes

SQL> @ control.sql

ORA-01081: cannot start already-running ORACLE - shut it down first

CREATE CONTROLFILE SET DATABASE "TEST" NORESETLOGS ARCHIVELOG

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01223: RESETLOGS must be specified to set a new database name

SQL> @ control.sql

ORA-01081: cannot start already-running ORACLE - shut it down first

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$database;

NAME

---------

TEST

MANUAL ENTRY IN ORATAB/ENABLE DBSTART UTILITY TO START DB FROM OS LEVEL

[oracle@demo ~]$ cat /etc/oratab

# This file is used by ORACLE utilities. It is created by root.sh

# and updated by either Database Configuration Assistant while creating

# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator. A new line terminates

# the entry. Lines beginning with a pound sign, '#', are comments.

# Entries are of the form:

# $ORACLE_SID:$ORACLE_HOME:<N|Y>:

# The first and second fields are the system identifier and home

# directory of the database respectively. The third filed indicates

# to the dbstart utility that the database should , "Y", or should not,

# "N", be brought up at system boot time.

# Multiple entries with the same $ORACLE_SID are not allowed.

prod:/u01/app/oracle/product/11.2.0/dbhome_1:N

[oracle@demo ~]$ cat /etc/oratab

# This file is used by ORACLE utilities. It is created by root.sh

# and updated by either Database Configuration Assistant while creating

# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator. A new line terminates

# the entry. Lines beginning with a pound sign, '#', are comments.

# Entries are of the form:

# $ORACLE_SID:$ORACLE_HOME:<N|Y>:

# The first and second fields are the system identifier and home

# directory of the database respectively. The third filed indicates

# to the dbstart utility that the database should , "Y", or should not,

# "N", be brought up at system boot time.

# Multiple entries with the same $ORACLE_SID are not allowed.

prod:/u01/app/oracle/product/11.2.0/dbhome_1:Y

testdb:/u01/app/oracle/product/11.2.0/dbhome_1:Y

HOW TO ENABLE THE ARCHIVE MODE

HOW TO ENABLE THE ARCHIVE MODE

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 60

Current log sequence 62

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 171581440 bytes

Fixed Size 1343668 bytes

Variable Size 117444428 bytes

Database Buffers 50331648 bytes

Redo Buffers 2461696 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

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 60

Next log sequence to archive 62

Current log sequence 62