Posts Tagged ‘database’

Oracle Database : How to Backup Oracle Database using RMAN with Examples

October 28th, 2018, posted in Oracle Queries
Share

Even if you are not an Oracle DBA, you’ll still encounter a situation where you may have to take a backup of an Oracle database.

Using Oracle RMAN, you can take a hot backup for your database, which will take a consistent backup even when your DB is up and running.

This tutorial gives you an introduction on how to perform Oracle DB backup using RMAN.

For the impatient, here is the quick snippet, that takes RMAN backup of both database and archive logs.

RMAN> BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;

Oracle Database : How to Backup Oracle Database using RMAN with Examples,Oracle Database,How to Backup Oracle Database, using RMAN with Examples,How to Backup Oracle,Oracle,Database,Oracle Database DBA,Oracle DBA,Database DBA,Oracle Database using RMAN,RMAN Oracle Database ,Oracle Database RMAN,Backup Oracle Database,Backup,Oracle Backup

1. View Current RMAN Configuration

Before we take the backup, we have to configure certain RMAN parameters. For example, how long you want to reatain the RMAN backup, etc.

Before we modify any configuration, execute the following command to view all current RMAN configuration settings.

To connect to RMAN, do the following from command line. This will take you to RMAN> command prompt, from here you can execute all RMAN commands.

$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Sat Aug 10 11:21:29 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: DEVDB (DBID=821773)
RMAN>

To view current RMAN configurations, execute “show all”.

RMAN> SHOW ALL;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO "/backup/rman/ctl_%F";
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   "/backup/rman/full_%u_%s_%p" MAXPIECESIZE 2048 M;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/dbs/snapcf_devdb.f'; # default

As you see above, it displays various RMAN parameters and their current values.

2. Change Few RMAN Configuration Parameters

Location: One of the important configuration parameters to set will be, where you want to save the RMAN backup. In the following example, I’m settting the RMAN backup loacation as “/backup/rman/”

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/full_%u_%s_%p';

Retention Period: Next, you should specify how long you want to retain the backup for. When RMAN takes a backup, it automatically deletes all the old backups that are older than the retention period. In the following example, I’m setting the retention period as 7 days, which will keep the DB backup for a week.

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Verify that the above two changes are done.

RMAN> SHOW ALL;
..
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backup/rman/full_%u_%s_%p';
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
..

Clear a Parameter: If you want to clear a parameter and set its value to default, use CLEAR at the end of the configuration as shown below.

RMAN> CONFIGURE RETENTION POLICY CLEAR;

In this example, since we cleared the retention policy’s value, it was set to the default value, which is 1. So, the retention policy is set to 1 day as shown below.

RMAN> SHOW ALL;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

 

3. Backup Oracle Database

Make sure the directory mentioned in the CHANNEK DEVICE TYPE DISK FORMAT is created. i.e /backup/rman/

$ mkdir -p /backup/rman

Currently this directory is empty. We’ll see what this has after the backup is taken.

$ ls -l /backup/rman
total 0

We can take a backup using image copy or in backup set. It is strongly recommended to use RMAN backup sets to backup the database.

RMAN stores the backup in backup sets, which are nothing but whole bunch of files which contains the backed-up data. Only RMAN understands the format of these files. So, if you backup an Oracle DB using RMAN, only RMAN knows how to read the backup and restore it.

Typically we’ll use “BACKUP AS BACKUPSET” to backup a database. So, to take a full backup of the database without the archive logs, do the following.

RMAN> BACKUP AS BACKUPSET DATABASE

To take a full backup of the database with the archive logs, do the following:

RMAN> BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;

You can also take a backup of only a specific table space. The following example takes backup of only PRD01 tablespace.

RMAN> BACKUP AS BACKUPSET TABLESPACE PRD01;

The RMAN backup output will be something similar to the following:

RMAN> BACKUP AS BACKUPSET DATABASE
Starting backup at 10-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=193 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=192 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00025 name=/u03/oradata/devdb/devuser07.dbf
input datafile fno=00003 name=/u02/oradata/devdb/temp01.dbf
channel ORA_DISK_1: starting piece 1 at 10-AUG-13
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00008 name=/u03/oradata/devdb/devusers05.dbf
channel ORA_DISK_2: starting piece 1 at 10-AUG-13
...
..

piece handle=/backup/rman/full_4dogpd0u_4237_1 tag=TAG20130808T114846 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 10-AUG-13
...
Starting Control File and SPFILE Autobackup at 10-AUG-13
piece handle=/backup/rman/ctl_c-758818131-20130808-00 comment=NONE
Finished Control File and SPFILE Autobackup at 10-AUG-13

Once the backup is completed, do an ls on the /backup/rman directory, you’ll now see RMAN backup files.

$ ls -l /backup/rman
total 14588
-rw-r----- 1 oracle dba 14585856 Aug  8 11:48 ctl_c-758818131-20130808-00
-rw-r----- 1 oracle dba   327680 Aug  8 11:48 full_4dogpd0u_4237_1

Note: Once a backup is taken, to view all available database backups from RMAN, you need to use “list” command that is shown further down in one of the examples.

While this may be obvious, it is worth repeating again: Since we are taking hotbackup, the Oracle database can be up and running. Make sure your Oracle database is running before you execute any of the above RMAN backup commands.

4. Assign Backup TAG Name for Quick Identification

If you are taking lot of backups, it will be easier to assign a tag to a particular backup, which we’ll later use during Oracle recovery (or while using list command to view it).

The following example assign a backup tag called “WEEEKLY_PRD01_TBLS_BK_ONLY” to this particular backup.

RMAN> BACKUP AS BACKUPSET TAG 'WEEEKLY_PRD01_TBLS_BK_ONLY' TABLESPACE PRD01;
Starting backup at 10-AUG-13
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00002 name=/u03/oradata/devdb/PRD01_1.dbf
channel ORA_DISK_1: starting piece 1 at 10-AUG-13
channel ORA_DISK_1: finished piece 1 at 10-AUG-13
piece handle=/backup/rman/full_4fogpdb3_4239_1 tag=WEEEKLY_PRD01_TBLS_BK_ONLY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-AUG-13
Starting Control File and SPFILE Autobackup at 10-AUG-13
piece handle=/backup/rman/ctl_c-758818131-20130808-01 comment=NONE
Finished Control File and SPFILE Autobackup at 10-AUG-13

Once the backup is finished, if you view the files from rman directory, you’ll not see the tag name here. Tag name is used only from RMAN repositories to view and restore backups. So, now you see there are more files in this directory, as we’ve taken couple of backups.

$ ls -l /backup/rman/
total 29176
-rw-r----- 1 oracle dba 14585856 Aug  8 11:48 ctl_c-758818131-20130808-00
-rw-r----- 1 oracle dba 14585856 Aug  8 11:54 ctl_c-758818131-20130808-01
-rw-r----- 1 oracle dba   327680 Aug  8 11:48 full_4dogpd0u_4237_1
-rw-r----- 1 oracle dba   327680 Aug  8 11:54 full_4fogpdb3_4239_1

 

5. Change Oracle RMAN Backup File Name Format

If you want the backup files itself will be in a specific format, you need to change the format in the RMAN configuration as shown below. In this example, we’ve appended the tag “full_devdb_bk_” prefix to all our backup files.

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   "/backup/rman/full_devdb_bk_%u_%s_%p" MAXPIECESIZE 2048 M;

Now, let us take another backup with this modified configuration.

RMAN> BACKUP AS BACKUPSET TAG 'WEEEKLY_PRD01_TBLS_BK_ONLY' TABLESPACE PRD01;

Now when you view the RMAN files, you’ll see the new RMAN backup file has this new file name format for the files. This is easier to identify certain information about the backup just by looking at the file names.

$ ls -l /backup/rman/
total 43764
-rw-r----- 1 oracle dba 14585856 Aug  8 11:48 ctl_c-758818131-20130808-00
-rw-r----- 1 oracle dba 14585856 Aug  8 11:54 ctl_c-758818131-20130808-01
-rw-r----- 1 oracle dba 14585856 Aug  8 11:56 ctl_c-758818131-20130808-02
-rw-r----- 1 oracle dba   327680 Aug  8 11:48 full_4dogpd0u_4237_1
-rw-r----- 1 oracle dba   327680 Aug  8 11:54 full_4fogpdb3_4239_1
-rw-r----- 1 oracle dba   327680 Aug  8 11:55 full_devdb_bk_4hogpdef_4241_1

 

6. Compress a RMAN Backup

If you are taking a backup of a big database, you’ll notice that the RMAN backup files are bigger (almost same size as the database itself).

So, for most situation, you should always tak ea compressed backup of the database.

The following example take a compressed backup of the tablepsace PRD01.

RMAN> BACKUP AS COMPRESSED BACKUPSET TAG 'WEEEKLY_PRD01_TBLS_BK_ONLY' TABLESPACE PRD01;

When you view the backup files from the file system level, you will not see any .gz (or .zip, or .bz2) to indicate that the RMAN has taken a compressed backup. The file naming convention will still follow the same as a non-compressed backup.

$ ls -l /backup/rman/
total 58352
-rw-r----- 1 oracle dba 14585856 Aug  8 11:48 ctl_c-758818131-20130808-00
-rw-r----- 1 oracle dba 14585856 Aug  8 11:54 ctl_c-758818131-20130808-01
-rw-r----- 1 oracle dba 14585856 Aug  8 11:56 ctl_c-758818131-20130808-02
-rw-r----- 1 oracle dba 14585856 Aug  8 11:59 ctl_c-758818131-20130808-03
-rw-r----- 1 oracle dba   327680 Aug  8 11:48 full_4dogpd0u_4237_1
-rw-r----- 1 oracle dba   327680 Aug  8 11:54 full_4fogpdb3_4239_1
-rw-r----- 1 oracle dba   327680 Aug  8 11:55 full_devdb_bk_4hogpdef_4241_1
-rw-r----- 1 oracle dba   127680 Aug  8 11:59 full_devdb_bk_4jogpdl0_4243_1

Note: The way to tell whether RMAN has take a compressed backup or not, it by looking at the size, and by looking at the output of the RMAN “list” command which is shown in one of the section below.

7. View all RMAN Backups

To view all the RMAN backups, execute “list backup summary” as shown below.

RMAN> LIST BACKUP SUMMARY;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
..
4215    B  F  A DISK        10-AUG-13       1       1       NO         TAG20130808T114846
4216    B  F  A DISK        10-AUG-13       1       1       NO         TAG20130808T114849
4217    B  F  A DISK        10-AUG-13       1       1       NO         WEEEKLY_PRD01_TBLS_BK_ONLY
4218    B  F  A DISK        10-AUG-13       1       1       NO         TAG20130808T115413
4219    B  F  A DISK        10-AUG-13       1       1       NO         WEEEKLY_PRD01_TBLS_BK_ONLY
4220    B  F  A DISK        10-AUG-13       1       1       NO         TAG20130808T115600
4221    B  F  A DISK        10-AUG-13       1       1       YES        WEEEKLY_PRD01_TBLS_BK_ONLY

As you see above, it displays various information about the backups. In the above output, it show 7 RMAN backups. The last column shows the “Tag” that we specified when we took a backup. If we didn’t specify any TAG, RMAN creates a default tag with the prefix “TAG” followed by some numbers. You can also see that under the column “Compressed”, the last RMAN backup shows “YES”, which indicates that out of all the 7 RMAN backups, only the last one was compressed.

Also, when the RMAN backup is running, if you want to see the proress, you can query the V$RMAN_STATUS table from sql*plus as shown below.

SQL> SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from V$RMAN_STATUS;

OPERATION                         STATUS                  MBYTES_PROCESSED START_TIM END_TIME
--------------------------------- ----------------------- ---------------- --------- ---------
CONTROL FILE AND SPFILE AUTOBACK  COMPLETED                             14 07-NOV-12 07-NOV-12
RMAN                              COMPLETED                              0 07-NOV-12 07-NOV-12
RESTORE VALIDATE                  COMPLETED                              0 07-NOV-12 07-NOV-12
RMAN                              COMPLETED WITH ERRORS                  0 07-NOV-12 07-NOV-12
DELETE                            COMPLETED                              0 08-NOV-12 08-NOV-12
BACKUP                            COMPLETED                              0 10-AUG-13 10-AUG-13
CONTROL FILE AND SPFILE AUTOBACK  COMPLETED                             14 10-AUG-13 10-AUG-13
RMAN                              COMPLETED WITH ERRORS               1832 10-AUG-13 10-AUG-13
RMAN                              COMPLETED                              0 10-AUG-13 10-AUG-13
...

There you have it.
That is how you take an Oracle RMAN backup.

Share

adcfgclone.pl dbTechStack – RC-50004 Error occurred in CloneContext Target System Domain Name

February 4th, 2018, posted in Oracle, Solaris
Share

RC-50004: Error occurred in CloneContext – Target System Domain Name in

adcfgclone.pl dbTechStack :

Error:

Target System Domain Name : worths.co.za
RC-50004: Error occurred in CloneContext: 
null
Check Clone Context logfile
/ebsdbprd_app/oracle/product/ebsdbprd/db/tech_st/11.2.0/appsutil/clone/bin/CloneContext_0418120037.log for details.

ERROR: Context creation not completed successfully.
For additional details review the file /tmp/adcfgclone_4849738.err if present.


adcfgclone.pl,oracle apps,oracle dba,oracle apps dba,oracle,dba,oracle error,ora error,oracle errors,adcfgclone.pl dbTechStack RC-50004 Error occurred in CloneContext Target System Domain Name,Target System Domain Name,adcfgclone.pl dbTechStack,RC-50004 Error,RC-50004,Error,clone error,oracle clone error,apps error,database error,oracle database,database oracle error,database,APPS-domain-50004,APPS domain 50004,RC 50004,RC 50004 Error

 

Solution :
Ensure that your hostname is fully populated in the /etc/hosts file

Not like this:

3 $ grep ebsdbprdp7 /etc/hosts
19.36.148.157   ebsdbprdp7
w@ebsdbprdp7:/
4 $

Should look like this

3 $ grep ebsdbprdp7 /etc/hosts
19.36.148.157   ebsdbprdp7.domain.co.za  ebsdbprdp7
w@ebsdbprdp7:/
4 $
Share

ORA-01194 file 1 needs more recovery to be consistent

January 10th, 2018, posted in Oracle Queries
Share

Enabling And Checking the Status of Flashback On Database,Oracle Database,Oracle DBA,Enabling Flashback On Database,Checking the Status of Flashback On Database, Status of Flashback On Database, Enable Flashback On Database, Enabling Flashback On Database,Enable Flashback On Oracle Database, Enabling Flashback On Oracle Database,ORA-01153: an incompatible media recovery is active,ORA-01153,Oracle Error,Ora Error,Oracle DBA,Oracle Database Error,Oracle Database Solution,an incompatible media recovery is active,Ora : an incompatible media recovery is active,Oracle an incompatible media recovery is active,Oracle Error Solution,Database DBA,oracle data guard,oracle data guard issues,oracle data guard error


ORA-01194: file 1 needs more recovery to be consistent

 

Method 1:

1) Start the database in mount state

SQL> startup mount;

2) Recover the database.

SQL> recover database;

If you come across below error

ORA-00283: recovery session canceled due to errors
 ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
 ORA-01194: file 1 needs more recovery to be consistent
 ORA-01110: data file 1: ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\SYSTEM01.DBF’

then, do the following

1)

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 766152 generated at 03/16/2013 12:12:04 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arc

ORA-00280: change 766152 for thread 1 is in sequence #14

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_10_8n43no4v_.arc

ORA-00310: archived log contains sequence 10; sequence 14 required

ORA-00334: archived log:

'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_10_8n43no4v_.arc'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01195: online backup of file 1 needs more recovery to be consistent

ORA-01110: data file 1:

'/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7qm3ck4o_.dbf'

2)
SQL> recover database using backup controlfile until cancel;

ORA-00279: change 766152 generated at 03/16/2013 12:12:04 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arc

ORA-00280: change 766152 for thread 1 is in sequence #14

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_11_8n43qq5j_.arc

ORA-00310: archived log contains sequence 11; sequence 14 required

ORA-00334: archived log:

'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_11_8n43qq5j_.arc'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01195: online backup of file 1 needs more recovery to be consistent

ORA-01110: data file 1:

'/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7qm3ck4o_.dbf'

3)

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 766152 generated at 03/16/2013 12:12:04 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arc

ORA-00280: change 766152 for thread 1 is in sequence #14

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_16/o1_mf_1_14_8n875owh_.arc

ORA-00279: change 769526 generated at 03/16/2013 12:48:13 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_15_%u_.arc

ORA-00280: change 769526 for thread 1 is in sequence #15

ORA-00278: log file

'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_16/o1_mf_1_14_8n875

owh_.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

3) Open the database in resetlog mode

SQL> alter database open resetlogs;

4) Check the status

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS

————————   ————-

DUPDB                  OPEN

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE

———-    ——————

DUPDB     READ WRITE





Problem sys@standby> startup mount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2217952 bytes Variable Size 490735648 bytes Database Buffers 335544320 bytes Redo Buffers 6606848 bytes Database mounted. sys@standby> alter database recover managed standby database using current logfile disconnect; alter database recover managed standby database using current logfile disconnect * ERROR at line 1: ORA-01153: an incompatible media recovery is active Cause This indicates a currently running media recovery process. Action sys@standby> alter database recover managed standby database cancel; sys@standby> alter database recover managed standby database using current logfile disconnect; Note When shutting down physical standby database, firstly turn off media recovery process. Otherwise the next time when starting up redo apply again, you will encounter error ORA-01153.

Method 2:

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  530288640 bytes

Fixed Size                  2131120 bytes

Variable Size             310381392 bytes

Database Buffers          209715200 bytes

Redo Buffers                8060928 bytes

Database mounted.

SQL> ALTER SYSTEM SET “_allow_resetlogs_corruption”= TRUE SCOPE = SPFILE;

SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  530288640 bytes

Fixed Size                  2131120 bytes

Variable Size             310381392 bytes

Database Buffers          209715200 bytes

Redo Buffers                8060928 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> CREATE UNDO TABLESPACE undo1 datafile ‘<ora_data_path>undo1_1.dbf’ size 200m autoextend on maxsize unlimited;

Tablespace created.

SQL> ALTER SYSTEM SET undo_tablespace = undo1 SCOPE=spfile;

System altered.

SQL> alter system set undo_management=auto scope=spfile;

System altered.

SQL> shutdown immediate

SQL> startup

Share

RC-00118 Error Occurred During Creation Of Database

December 10th, 2017, posted in Oracle
Share

I was cloning PROD to DEV instance … when I am configuring DEV instance , got following errors.

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

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2235208 bytes
Variable Size             427820216 bytes
Database Buffers          624951296 bytes
Redo Buffers               13930496 bytes

Control file created.

alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/oracle/DEV/oradata/DEV/system01.dbf'


alter tablespace TEMP1 add tempfile '/u02/oracle/DEV/oradata/DEV/temp01.dbf' REUSE
*
ERROR at line 1:
ORA-01109: database not open


alter tablespace TEMP2 add tempfile '/u02/oracle/DEV/oradata/DEV/temp02.dbf' REUSE
*
ERROR at line 1:
ORA-01109: database not open


ALTER DATABASE RENAME GLOBAL_NAME TO "DEV.localhost"
                                     *
ERROR at line 1:
ORA-01109: database not open

ORA-01109: database not open


Error Running create/replace library with adupdlib.sql
ORA-01109: database not open

PL/SQL procedure successfully completed.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
exit_code=0

Checking for errors ...

The database has not been successfully created. Shutting down the instance ...

SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 1 01:26:22 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected.
ORACLE instance shut down.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

.end std out.

.end err out.

---------------------------------------------------------------
                   ADX Database Utility
---------------------------------------------------------------

getConnectionUsingAppsJDBCConnector() -->
    APPS_JDBC_URL='null'
    Trying to get connection using SID based connect descriptor
getConnection() -->
    sDbHost    : localhost
    sDbDomain  : localhost.domain.com
    sDbPort    : 1530
    sDbSid     : DEV
    sDbUser    : apps
    Trying to connect using SID...
getConnectionUsingSID() -->
    JDBC URL: jdbc:oracle:thin:@localhost:1530:DEV
    Exception occurred: java.sql.SQLException: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

    Trying to connect using SID as ServiceName
getConnectionUsingServiceName() -->
    JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=DEV)))
    Exception occurred: java.sql.SQLException: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

    Trying to connect using SID as ServiceName.DomainName
getConnectionUsingServiceName() -->
    JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=DEV.localhost)))
    Exception occurred: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

    Connection could not be obtained; returning null

-------------------ADX Database Utility Finished---------------

RC-00118: Error occurred during creation of database
Raised by oracle.apps.ad.clone.ApplyDatabase


StackTrace:
java.lang.Exception: Control file creation failed
        at oracle.apps.ad.clone.ApplyDatabase.doConf(ApplyDatabase.java:635)
        at oracle.apps.ad.clone.ApplyDatabase.doApply(ApplyDatabase.java:473)
        at oracle.apps.ad.clone.ApplyDatabase.<init>(ApplyDatabase.java:366)
        at oracle.apps.ad.clone.ApplyDBTier.<init>(ApplyDBTier.java:110)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:501)
        at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:67)
        at java.lang.Thread.run(Thread.java:637)

                                                                   

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

In my case, control file were created successfully, but above error indicates that database was not created successfully.

I found root of this issue that when i had copied PROD binaries and data files at same time some services were running using ORACLE user.

Note: You would get this error if oracle database was not properly shutdown when you copied the files.

All my stuff were wasted.
So, I have copied only datafiles again from PROD to DEV.
And ran adcfgclone.pl dbTier again and cloning has been completed successfully without any error !!

Share

Find A String In Database

July 1st, 2017, posted in Oracle Queries
Share

I’ve tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results :

SELECT * from dba_objects WHERE
object_name like '%DTN%'

A column isn't an object. If you mean that you expect the column name to be like '%DTN%', the query you want is:

SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%';
Share