Loading...
SPFILE is a small but very important file and if we lose the spfile, we cannot start the database even if all the other database files are present and intact.You might need to restore the spfile for one of several reasons:
• You accidentally deleted your server parameter file.
• You want to view an old copy of the spfile.
• You can’t start your instance with the current spfile.

If you’re using an spfile, you can have it automatically backed up for you by enabling the autobackup of the control file.By default, automatic control file backups are disabled. Even when the auto backup feature is disabled, RMAN will back up the current control file and the server parameter file whenever any backup command includes data file 1 from the data files that belong to the target database. In an Oracle database, data file 1 is always part of the system tablespace, which contains the data dictionary. You can configure RMAN to automatically back up the control file
following every backup and any database structural change by using the configure command.

When does the SPFILE gat backed up with AUTOBACKUP tuned on?
Once you configure automatic control file backup, RMAN will automatically back up your target database control file, as well as the current server parameter file, when any of the following events occurs:
• Successful completion of either a backup or the copy command
• After a create catalog command from the RMAN prompt is successfully completed
• Any structural changes to the database modify the contents of the control file

After a backup or copy command completes and the recovery catalog—if you are using one—is successfully updated, RMAN will then back up the control file to its own backup piece. In addition, any changes to the physical structure of your database, even if they are made through SQL*Plus, will trigger a control file auto backup. (For example, the following actions will trigger an auto backup of the control file: adding a tablespace or data file, dropping a data file, placing a tablespace offline or online, adding an online redo log, and renaming a data file.)

When automatic backup is triggered by a structural change, an Oracle server process (not an RMAN process) will automatically create the auto backup of your control file.

Check Autobackup Status 






Enable Autobackup of controlfile











Where does the SPFILE autobackup goes?
You’ve just enabled the auto backup of the control file feature, but you don’t know where the files are physically being written. You want to ensure that these critical backups are being written to a location you know about so that you can maintain and monitor that location.

1. If you have enabled a fast area as well as the auto backup of the control file, then RMAN will write the backup to the directory defined for the fast area. By default, RMAN creates these files as Oracle managed files.

2. If you haven’t enabled a fast recovery area, RMAN will create the auto backups in an operating system–specific location ($ORACLE_HOME/dbs on Unix and %ORACLE_HOME%\database on Windows).

3. You can also configure the auto backup to back up the control file to an automatic storage management (ASM) disk group, as shown in the following example:
RMAN> configure controlfile autobackup format for device type disk to '+DATA/%F';

4. You can override where RMAN will write the auto backup control file and its name using the configure command.
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/12.1.0.2/backup/%F';

Note : The format variable %F yields a unique combination of the database ID, day, month, year, and sequence.

5. To set the directory and file format back to the default value, run this command:
RMAN> configure controlfile autobackup format for device type disk clear;

6. To list backup of spfile use command  : "list backup of spfile;"

Recovery scenarios involving loss of SPFILE

Case 1 : Using a Recovery Catalog
If you’re using a recovery catalog, then restoring the spfile is fairly straightforward.

$ rman target / catalog rman/xxxx@racdb
RMAN> startup nomount;
RMAN> restore spfile;
RMAN> startup force; # startup using restored spfile

If you receive an error such as this when running the restore command:
RMAN-20001: target database not found in recovery catalog

Then first set the DBID
RMAN> set dbid 961393455;

Case 2 : Not Using a Recovery Catalog, RMAN Auto Backup in Default Location
In this case we assume that auto backups of the spfile are going to the default location.
The default location depends on your operating system. For Linux/Unix, the default location is ORACLE_HOME/dbs.On Windows systems, it’s usually ORACLE_HOME\database.

$ rman target /
RMAN> startup force nomount; # start instance for retrieval of spfile
RMAN> set dbid 961393455;
RMAN> restore spfile from autobackup;
RMAN> startup force; # startup using restored spfile

Case 3 : Not Using a Recovery Catalog, RMAN Auto Backup Not in Default Location

If you’re either using an FRA or have the auto backup of your control file configured to a nondefault location, then the spfile will not be backed up to what Oracle calls the default location. In these situations, you have to specifically tell RMAN where to retrieve the backup from.

If you’re using an FRA, your spfiles will be backed up in an auto backup directory in the FRA. You’ll have to find that directory and backup piece name before you can restore your spfile.You’ll also need to know your database identifier before you can proceed.

$ rman target /
RMAN> set dbid 961393455;
RMAN> startup force nomount; # start instance for retrieval of spfile
RMAN> restore spfile from '/u01/12.1.0.2/backup/c-961393455-20180130-01';
RMAN> startup force; # startup using restored spfile

Suppose you are not sure which backup file contains the SPFILE backup. So to work around this, we tried to fool RMAN by creating the directory structure when using a FRA. We create the directory structure RACDB/autobackup/2018_01_30 under the to level location /u01/12.1.0.2/backup/ and copy the backup pieces to this location.

$ cd /u01/12.1.0.2/backup/
$mkdir -p RACDB/autobackup/2018_01_30
$ mv /u01/12.1.0.2/backup/o1* /u01/12.1.0.2/backup/RACDB/autobackup/2018_01_30

Now we can restore spfile using command :-
restore spfile from autobackup db_recovery_file_dest='/u01/12.1.0.2/backup/' db_name='RACDB';

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Most of the time DBAs get issues on ORA-1114 but the file id mentioned in error message becomes an unknown file id. Error message looks like :-
ORA-01114: IO error writing block to file 201 (block # 458754)
SVR4 Error: 28: No space left on device
What Oracle Doc says on this error :-
Cause:
The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time.
Action: Restore access to the device or remove unnecessary files to free up space.

But in my case file id reported by error is unknown file id. First we'll check "DBA_DATA_FILES" to find out if issue is with any datafile.
SYS@racdb1> select file_id, RELATIVE_FNO, file_name from dba_data_files where file_id='201';
no rows selected
SYS@racdb1> select file_id, RELATIVE_FNO, file_name from dba_data_files where RELATIVE_FNO=201;
no rows selected

This error is being reported on a temp file. For TEMPFILES, the file numbering starts after the value of db_files. Run the following query to get the file name.
select file#, name from v$tempfile where file#=<file id reported in ora-1114 error >-<value of db_files>;

In this case :-
SYS@racdb1> show parameter db_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200
SYS@racdb1> select file#, name from v$tempfile where file#=(201-200);
FILE# NAME
---------- --------------------------------------------------
1 +DATA/RACDB/TEMPFILE/temp.287.963776695
So i got issue with my tempfile 1. We need to check this tempfile and take actions accordingly.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
In this post we'll try to distinguish the rules of service failover to another instance.

If using sqlplus to shutdown instance
  • In pre-11.2.0.4 (11.2.0.2), the service  will failover to available instance
  • In 11.2.0.4 or above (12.1.*), the service will not failover but stop because SQLPLUS abort is detected by the database agent as PLANNED_OFFLINE with label "Instance Shutdown, Stable", thus clusterware does not failover the services. For a real crash, it would be OFFLINE with "Abnormal Termination".
If shutdown instance using srvctl
  • In pre-11.2, the service will failover to available instance.
  • In 11.2, the service will not failover but stop
  • In 11.2, you need to use "-f " option if you want service failover to available instance when stopping instance using srvctl.
  • You may relocate service to another instance before stopping instance through srvctl. e.g."srvctl relocate service -d racdb -s temp_serv -i racdb1 -t racdb2"
  • In 12c, you need to use "-failover" option to failover service to available instance.
New Changes in 12c
1) If stopping instance without -force or -failover option while you have service running on the stoping instance, errors (PRCD-1315,PRCR-1014, PRCR-1065, CRS-2529) are reported
[oracle@rac121 ~]$ srvctl stop instance -d racdb -i racdb1
PRCD-1315 : failed to stop instances for database racdb
PRCR-1014 : Failed to stop resource ora.racdb.db
PRCR-1065 : Failed to stop resource ora.racdb.db
CRS-2529: Unable to act on 'ora.racdb.db' because that would require stopping or relocating 'ora.racdb.temp_serv.svc', but the force option was not specified

2) If you want to stop both instance AND services running on the instance, use '-force' option.

[oracle@rac121 ~]$ srvctl stop instance -d racdb -i racdb1 -force 

3) If you want to stop instance but failover the services to another instance, you need to use '-failover' option instead '-force'. Click Here to get more details on this.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
We can use srvctl to failover the running services to another instance, before shutting down the instance. In this demonstration we'll use "-failover" option to move services to another node before shutting down the node.

Step1 : Check the status of database
[oracle@rac121 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node rac121
Instance racdb2 is running on node rac122

Step2 : Check status of service
[oracle@rac121 ~]$ srvctl status service -d racdb
Service temp_serv is running on instance(s) racdb1

Step 3: Stop the instance but use "-failover" option
[oracle@rac121 ~]$ srvctl stop instance -d racdb -i racdb1 -failover

Step 4: Check status of service, now my service is running on another node
[oracle@rac121 ~]$ srvctl status service -d racdb
Service temp_serv is running on instance(s) racdb2

Step 5: Check database status
[oracle@rac121 ~]$  srvctl status database -d racdb
Instance racdb1 is not running on node rac121
Instance racdb2 is running on node rac122

So if you want to stop instance but failover the services to another instance, you need to use '-failover' option.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
You might be in a situation where you are administering a database that supports an OLTP workload. An application regularly creates global temporary tables and a large number of transactions are performed on them. You notice that performance is degraded because of excessive generation of undo due to a large number of transactions on the global temporary tables.

Oracle database 12c Release 1 (12.1) introduced the concept of temporary undo, with this the undo segments for global temporary tables can be stored in the temporary tablespace.
By default we used to store undo segments of global temporary tables in regular undo tablespace, which is protected by REDO. This can cause multiple issues :-
  • Database should be in read-write mode to writing into undo tablespace. So global temporary tables can't be used in read-only databases and physical standby databases. 
  • Transient data ( contained by global temporary tables) is not required in database recovery scenarios. So protecting them with redo represents an unnecessary additional load on the system. 
  • The undo associated with global temporary tables adds to the total space needed to meet the undo retention period.

Key Points :-
  • When temporary undo is enabled, it might be necessary to increase the size of the temporary tablespaces to account for the undo records. 
  • When a session uses temporary objects for the first time, the current value of the TEMP_UNDO_ENABLED initialization parameter is set for the rest of the session. Therefore, if temporary undo is enabled for a session and the session uses temporary objects,then temporary undo cannot be disabled for the session. Similarly, if temporary undo is disabled for a session and the session uses temporary objects, then temporary undo cannot be enabled for the session. 
  • The temporary undo functionality is only available if the COMPATIBLE parameter is set to 12.0.0 or higher. 
  • Temporary undo is the default for standby databases, so any alteration to the TEMP_UNDO_ENABLED parameter is ignored on standby databases. 
  • Temporary undo is enabled by default for a physical standby database with the Oracle Active Data Guard option. 
  • You can use the TEMP_UNDO_ENABLED initialization parameter to separate undo for temporary tables from undo for persistent tables. When this parameter is set to TRUE, the undo for temporary tables is called temporary undo.
  • Temporary undo records are stored in the database's temporary tablespaces and thus are not logged in the redo log. When temporary undo is enabled, some of the segments used by the temporary tablespaces store the temporary undo, and these segments are called temporary undo segments.

Enable/Disable Temporary Undo :-
In Oracle 12c, undo segments for global temporary tables are still managed using the conventional undo tablespace by default. Temporary undo is enabled/disabled using the TEMP_UNDO_ENABLED parameter at session or system level.
--Session Level
ALTER SESSION SET TEMP_UNDO_ENABLED=TRUE/FALSE;
--System Level
ALTER SYSTEM SET TEMP_UNDO_ENABLED=TRUE/FALSE

Monitor Temporary Undo :-
  • V$UNDOSTAT -Regular undo activity
  • V$TEMPUNDOSTAT -temporary undo activity
Hands-On :-
Step 1 : Create Temporary Table
Create a global temporary table for testing.
SYS@racdb1> CREATE GLOBAL TEMPORARY TABLE test_table (
  id           NUMBER,
  details        VARCHAR2(10)
)
ON COMMIT DELETE ROWS;
Table created.
Make sure the user can access the V$TEMPUNDOSTAT view.Here I'm using SYS else you need to execute below grant statement
GRANT SELECT ON v_$tempundostat TO <user>;

Step 2 : Load data into GTT

Load some data into the GTT, then checks the redo and undo usage. This example is not using temporary undo.
SYS@racdb1> SET AUTOTRACE ON STATISTICS;
SYS@racdb1> INSERT INTO test_table
WITH data AS (
  SELECT 10 AS id
  FROM   dual
  CONNECT BY level < 20000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 2000000; 
2000000 rows created.
Statistics
----------------------------------------------------------
         58  recursive calls
      32706  db block gets
       5012  consistent gets
         32  physical reads
    5930248  redo size
        855  bytes sent via SQL*Net to client
        984  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    2000000  rows processed

Step 3 : Verify UNDO generated by transaction
SYS@racdb1> SET AUTOTRACE OFF
SYS@racdb1> -- Check undo used by transaction.
SYS@racdb1> SELECT t.used_ublk,
       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); 
 USED_UBLK  USED_UREC
---------- ----------
       614      12767
So this insert produces 614 blocks of undo, which is why the statistics show a redo size of 5.9 million bytes of redo, generated to protect the undo.

Now we'll enable temporary undo,checks the redo and undo usage.
SYS@racdb1> ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
Session altered.
SYS@racdb1> SET AUTOTRACE ON STATISTICS;
SYS@racdb1> INSERT INTO test_table
WITH data AS (
  SELECT 10 AS id
  FROM   dual
  CONNECT BY level < 20000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 2000000;   
2000000 rows created.
Statistics
----------------------------------------------------------
         48  recursive calls
      32760  db block gets
       4979  consistent gets
         31  physical reads
        980  redo size
        845  bytes sent via SQL*Net to client
        984  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    2000000  rows processed
SYS@racdb1> SET AUTOTRACE OFF
SYS@racdb1> -- Check undo used by transaction.
SYS@racdb1> SELECT t.used_ublk,
       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); 
 USED_UBLK  USED_UREC
---------- ----------
         1          1
SYS@racdb1> -- Check temporary undo usage.
SYS@racdb1> SET LINES 1000
SYS@racdb1> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
Session altered.
SYS@racdb1> SELECT *
FROM   v$tempundostat
WHERE  end_time >= SYSDATE - INTERVAL '1' MINUTE;  2    3
BEGIN_TIME           END_TIME                UNDOBLKCNT   
-------------------- -------------------- ---------- ---------- --------------
16-JAN-2018 19:35:54 16-JAN-2018 19:45:57         648  

Here we can see only a single byte of conventional undo is generated, which results in approximately 980 bytes of redo. The UNDOBLKCNT of the V$TEMPUNDOSTAT view shows us 648 blocks of temporary undo have been used.

So here with help of "Temporary Undo" we can address the issue causing by excessive generation of undo due to a large number of transactions on the global temporary tables.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
The Data Recovery Advisor tool was added to RMAN in Oracle Database 11g.This tool automatically detects problems with your database that may require you to perform restore and recovery operations. The Data Recovery Advisor will list failures, give you advice on how to resolve issues, and allow you to push the button that instructs RMAN to run the commands required to fix the problem.

Below RMAN commands are associated with the Data Recovery Advisor:
  • list failure
  • advise failure
  • repair failure

Listing Failures

RMAN> list failure;
Here is some sample output:
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2 HIGH OPEN 13-JAN-18 One or more non-system data files are missing

The prior output indicates that the database has at least one data file that is missing. At this point you should next run the advise failure. To get more details on this failure:-
RMAN> list failure 52 detail;

The Data Recovery Advisor stores its information outside the database in the Automatic Diagnostic Repository (ADR). This allows you to run the list failure command even when your database is started in nomount mode and is also not dependent on the availability of the recovery catalog (if using one). You can determine the location of the base directory for the ADR by viewing the diagnostic_dest initialization parameter.

If you suspect there is a problem with your database and the Data Recovery Advisor is not reporting a failure, you can proactively initiate a database health check by running the following RMAN command:
RMAN> validate database;

If you want to see all failures that have been resolved and closed, then use the closed clause:
RMAN> list failure closed;

Advise Failure

You’ve experienced a media failure. You want to get advice from the Data Recovery Advisor about how to restore and recover your database.
 RMAN> advise failure;

OR
 RMAN> advise failure 52;

Depending on your situation, the advise failure output will contain one or both of the following sections:

  • Manual Checklist
  • Automated Repair Options

The Manual Checklist section gives you advice for manually resolving the issue.

The Automated Repair Options section will list the location and name of a repair script that contains RMAN commands to resolve the problem. At this point, you may want to open another terminal session and inspect the contents of the repair script (with a text editor such as vi or Notepad).

By analyzing the script you can gain a greater understanding of the failure and how RMAN intends to resolve the problem. If you want RMAN to automatically repair the failure, then run the repair failure.

Repair Failure

By running list failure and advise failure, you will gain an understanding as to what the problem is and how to fix it. You can use the repair failure command to run the repair script generated by the Data Recovery Advisor:
RMAN> repair failure;

It’s recommend that you run the repair failure command only after you have run the list failure and advise failure commands. Repairing the problem should be the last step performed. You should use the Data Recovery Advisor to repair failures only after you thoroughly understand what the failure is and what commands will be run to repair the failure.

If you want to inspect what the repair failure command will do without running the commands, then use the preview clause:
RMAN> repair failure preview;

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Make sure we have core database ready which will be converted to RAC environment. We will be using database duplication of RMAN to move file from system core database to ASM enabled RAC environment. Once after moving from file system to ASM conversion from single instance to multiple instance database takes place. Database would be on ASM shared storage and each instance is start on each cluster node.Make multi-instance database, cluster aware so that failover and load balancing can be achieved. Make sure we have a core database ready to convert to RAC environment on server to complete the practice.


Scenario:-

Single Instance Database Name :- orcl

RAC Database Name :- sorcl

Nodes :- rac1.dba.com
             rac2.dba.com


Perform Cloning and create single instance target database

In very first step we'll perform simple database cloning on rac1.dba.com. For this we have multiple options, suppose we use RMAN duplicate command e.g. duplicate target database to 'sorcl'. Below is sample init file of my database


$vi $ORACLE_HOME/dbs/initsorcl.ora
# Set the RAC database name
db_name ="sorcl"
# set the location of the duplicate clone control file.
control_files ='+DATA/dev/controlfile/current.288.822251901'
#set the below parameters for default location of data files
db_create_file_dest='+DATA'
# set below parameter to create two members for each redo
db_create_online_log_dest_1='+DATA'
# set the location as per your environment
log_archive_dest_1='LOCATION=+DATA'
log_archive_format='arch_%r_%s_%t.arc'
# set below parameter as per your environment
diagnostic_dest= ='/u01/app/oracle'
#Set the below to the same as the production target
db_block_size = 8192
sga_target=537919488
remote_login_passwordfile=exclusive
undo_management =AUTO
undo_retention =10800
undo_tablespace =UNDOTBS1
audit_file_dest='/u01/app/oracle/admin/sorcl/adump'


After completing database duplication successfully, Now database is available to convert to RAC environment, but still single database.

Create UNDO and REDO for second instance

In order to convert single database to cluster database we need to create one UNDO for each instance and two redo log groups minimum as each instance changes are unique.
Create second thread of online redo logs in order to start instance 2 and enable thread 2.It is always recommended to create redo log groups across disk groups for redundancy


SYS@orcl>alter database add logfile thread 2 group 4('+DATA') size 50m reuse;

SYS@orcl>alter database add logfile thread 2 group 5('+DATA') size 50m reuse;

#Enable thread
SYS@orcl>alter database enable thread 2;

#Create UNDO tablespace for second instance
SYS@orcl>create undo tablespace UNDOTBS2 datafile '+DATA' size 500M;


Add Cluster Related parameters

The duplicated instance does not have any cluster related parameters, we need to add the cluster parameter in order to convert single instance to RAC. Add below cluster related parameters to initsorcl.ora


*.cluster_database_instances=2
*.cluster_database=true
sorcl1.instance_number=1
sorcl2.instance_number=2
sorcl 1.thread=1
sorcl 2.thread=2
sorcl 1.undo_tablespace='UNDOTBS1'
sorcl 2.undo_tablespace='UNDOTBS2'


Copy the updated init.ora file to node2 e.g. rac2.dba.com and rename the files as per instance name


scp initsorcl.ora oracle@rac2.dba.com:/u01/app/oracle/product/11.2.0/db_1/dbs/initsorcl.ora

on RAC1: $cp initsorcl.ora initsorcl1.ora

on RAC2: $cp initsorcl.ora initsorcl2.ora


Stop Listener and Database

As we are going to start sorcl instance as sorcl1 and sorcl2 let us stop sorcl instance from node1 e.g. rac1.dba.com and listener also.


Stop the listener.

Shutdown the sorcl database.


Start first instance


Export ORACLE_SID=sorcl1

#Create password file
$orapwd file=orapw$ORACLE_SID password=oracle

> startup
select instance_name from v$instance;


Start Second Instance


#create directory structure for audit_file_dest in node2 as well

export ORACLE_SID=sorcl2

#create password file
$orapwd file=orapw$ORACLE_SID password=oracle

> startup
select instance_name from v$instance;


Register the RAC database (sorcl) and instances (sorcl1 & sorcl2) with CRS

Now sorcl database is having two instance one on each RAC nodes. In order manage these instances and database let us make sorcl db and instance cluster aware.


export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

srvctl add database -d sorcl -o /u01/app/oracle/product/11.2.0/db_1

srvctl add instance -d sorcl -i sorcl1 -n rac1

srvctl add instance -d sorcl -i sorcl2 -n rac2

crsctl stat res -t


Update SPFILE Location

Let us create spfile from pfile and place spfile on shared location. Use pfile to point to spfile.


[oracle@rac3 ~]$ export ORACLE_SID=sorcl1

[oracle@rac3 ~]$ sqlplus / as sysdba

SYS@sorcl1>create spfile='+DATA' from pfile;
File created.

#Keeps an entry on each node in initsorcl1.ora initsorcl2.ora
spfile=' +DATA/SORCL/PARAMETERFILE/spfile.491.940617585'

#Restart the database in order to take effect of spfile
srvctl stop database -d sorcl

srvctl modify database -d sorcl -p '+DATA/SORCL/PARAMETERFILE/spfile.491.940617585'

srvctl start database -d sorcl

[oracle@rac1 ~]$ srvctl status database -d sorcl
Instance sorcl2 is running on node rac1
Instance sorcl1 is running on node rac2


Check clusterware and Database Status


[oracle@rac1 ~]$ crsctl stat res -t
[oracle@rac1 ~]$ cluvfy comp crs -n all -verbose



Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
You recently added a data file to a tablespace and had a failure before the data file was backed up. For example, a backup is taken:

RMAN> backup database;

Sometime after the backup has finished, a new tablespace is added:
CREATE TABLESPACE test_tbs DATAFILE '/u01/datafile/spd/test_tbs01.dbf' SIZE 100m;

Now we have the situation where there currently is no backup of the newly created data file. Suppose there is a media failure that damages the data file before a fresh backup is taken.

Conceptual Details

For this solution to work, you need to have a good baseline backup of your database and any subsequently generated redo up to the point where the data file was created. If you have your current control file, you can restore and recover at the data file, tablespace, or database level. If you’re using a backup control file that has no information about the data file, you must restore and recover at the database level.

Starting with Oracle Database 10g, there is enough information in the redo stream for RMAN to automatically re-create a data file that was never backed up. It doesn’t matter whether the control file has a record of the data file.

Prior to Oracle Database10g, manual intervention from the DBA was required to recover a data file that had not been backed up yet. If Oracle identified that a data file was missing that had not been backed up, the recovery process would halt, and you would have to identify the missing data file and re-create it:
SQL> alter database create datafile '/u01/datafile/spd/test_tbs01.dbf'
as '/u01/datafile/spd/test_tbs01.dbf'size 10485760 reuse;

After re-creating the missing data file, you had to manually restart the recovery session. If you are using an old version of the Oracle database, see MOS note 1060605.6 for details on how to re-create a data file in this scenario.

In Oracle Database 10g and newer, this is no longer the case. RMAN automatically detects that there isn’t a backup of a data file being restored and re-creates the data file from information retrieved from the control file and/or redo information as part of the restore and recovery operations.

Using a Current Control File

In this example, we use the current control file and are recovering the test_tbs01.dbf datafile in the newly added tools tablespace.


$ rman target /

RMAN> startup mount;

RMAN> restore tablespace test_tbs;

You should see a message like the following in the output as RMAN re-creates the data file:
creating datafile file number=10 name=/u01/datafile/spd/test_tbs01.dbf

Now issue the recover command and open the database:
RMAN> recover tablespace test_tbs;

RMAN> alter database open;



Using a Backup Control File

This scenario is applicable anytime you use a backup control file to restore and recover a data file that has not yet been backed up. First, we restore a control file from a backup taken prior to when the data file was created:


$ rman target /

RMAN> startup nomount;

RMAN> restore controlfile from '/backup/bkp_ctl';

RMAN> alter database mount;

Now you can verify the control file has no record of the tablespace that was added after the backup was taken:
RMAN> report schema;

When the control file has no record of the data file, RMAN will throw an error if you attempt to recover at the tablespace or data file level. In this situation, you must use the restore database and recover database commands as follows:

RMAN> restore database;

RMAN> recover database;

Next, you should see quite a bit of RMAN output. Near the end of the output you should see a line similar to this indicating that the data file has been re-created:
creating datafile file number=10 name=/u01/datafile/spd/test_tbs01.dbf

Since you restored using a backup control file, you are required to open the database with the resetlogs command:

RMAN> alter database open resetlogs;






Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
This article describes the installation of Oracle Database 12c release 1(64 bit)  RAC on Linux (Oracle Linux 6.9 64-bit) using VirtualBox (5.1)
Introduction
System Requirements
Download Software
VirtualBox Installation
Virtual Machine Setup
Guest Operating System Installation
Check Internet Access
Oracle Installation Prerequisites
Automatic Setup
Manual Setup
Additional Setup
Install Guest Additions
Create Shared Disks
Clone the Virtual Machine
Install the Grid Infrastructure
Install the Database Software
Create a Database
Check the Status of the RAC

Introduction
If you want to get through all steps of the Oracle RAC installation and your laptop or desktop computer has 8 GB or more of RAM (16 GB is recommended), then this is entirely feasible using Oracle VirtualBox as demonstrated in this article. You can get a running RAC system which can host a small test database. The created system is not, and should not be considered, a production-ready system. It's simply to allow you to get used to installing and using RAC and test various administration procedures.
This article uses the 64-bit versions of Oracle Linux, version 6.4, and Oracle 12c Release 1, version 12.1.0.1.0. Using VirtualBox you can run multiple Virtual Machines (VMs) on a single server, allowing you to run both RAC nodes on a single machine. In addition, it allows you to set up shared virtual disks. The finished system includes two guest operating systems, two sets of Oracle Grid Infrastructure (Clusterware + ASM) and two Database instances all on a single server.

System Requirements
  • 12 GB of RAM, 16GB recommended
  • 100 GB of free space on the hard disk + staging area for Oracle Linux and RAC downloads, about 9 GB;
  • This procedure was tested on 64-bit Windows 10
Download the following software
Depending on your version of VirtualBox and Oracle Linux, there may be some slight variation in how the screen shots look.

VirtualBox Installation
First, install the VirtualBox software. On RHEL and its clones you do this with the following type of command as the root user.
# rpm -Uvh VirtualBox-4.3-4.3.16_95972_el6-1.x86_64.rpm
The package name will vary depending on the host distribution you are using. If you are doing this on windows system then, you have to just execute *.exe file. Once complete, VirtualBox is started from the menu.

Virtual Machine Setup
We must define two virtual RAC nodes. We can save time by defining one VM, then cloning it when it is installed.
Start VirtualBox and click the "New" button on the toolbar. Enter the
Name "rac121", Type "Linux" and Version "Oracle (64 bit)", and then click the "Next" button:

Enter "4096" as the base memory size, then click the "Next" button. Use more memory if you have enough physical memory on your machine as it will make the process much quicker!

Accept the default option to create a new virtual hard disk by clicking the "Create" button.

Accept the default VDI type and click the "Next" button on the Create Virtual Hard Drive screen:

Accept the default "Dynamically allocated" option by clicking the "Next" button:

Accept the default file name and set size to "40G", then click the "Create" button:

The "rac121" VM will appear on the left-hand pane. Select your machine and Click on the "Settings" link on the right side:

Make Network changes

Adapter 1- Host-only Adapter

Adapter 2- Internal Network

Adapter 3 – Bridged Adapter - This adapter is enabled for internet access in VM. We will disable this adapter, after yum update

System Changes
Move "Hard Disk" to the top of the boot order and uncheck the "Floppy" option, then click the "OK.


The virtual machine is now configured so we can start the guest operating system installation.

Guest Operating System Installation

With the new VM highlighted, click the "Start" button on the toolbar. On the "Select start-up disk" screen, choose the relevant Oracle Linux ISO image and click the "Start".





The resulting console window will contain the Oracle Linux boot screen.





Do not perform the media test. Choose "Skip" button:




On the "Welcome" screen, click the "Next" button.







Select the appropriate language, then click the "Next" button.

Select the relevant keyboard setting, then click the "Next" button.

Select the storage option "Basic Storage Devices" for the installation, then click the "Next" button.

Click on Yes, discard any data and proceed to next.

Enter a fully qualified host name - rac121.localdomain, then click the "Configure Network" button.

Highlight the relevant connection and click the "Edit" button for each connection.



Note : IPv6 Settings are “Ignored” for each connections

Select the relevant time zone by clicking on your nearest city on the map. Click on the "Next" button to proceed.

Enter a root password for the server, then click the "Next" button to proceed.

Check the partitioning type you require. If you want to amend the default partitioning layout, check the "Review and modify partitioning layout" option. Click the "Next" button.

The installer will list the default partitioning scheme for your size disk. Amend them as required and click the "Next" button, then the "Format" and "Write changes to disk" buttons on the following dialogs.




Accept the boot loader settings by clicking the "Next" button.
                          

Accept the "Basic Server" installation and check the "Customize now" option, then click the "Next" button.

The "Package Group Selection" screen allows you to select the required package groups, and individual packages within the details section. When you've made your selection, click the "Next" button. Make sure to install below packages :-
following package groups installed:
·       Base System > Base
·       Base System > Compatibility libraries
·       Base System > Hardware monitoring utilities
·       Base System > Large Systems Performance
·       Base System > Network file system client
·       Base System > Performance Tools
·       Base System > Perl Support
·       Servers> Server Platform
·       Servers> System administration tools
·       Desktops> Desktop
·       Desktops> Desktop Platform
·       Desktops> Fonts
·       Desktops> General Purpose Desktop
·       Desktops> Graphical Administration Tools
·       Desktops> Input Methods
·       Desktops> X Window System
·       Applications> Internet Browser
·       Development> Additional Development
·       Development> Development Tools

Wait for the installation to complete.

Click on Reboot.


On the "Welcome" screen, click the "Forward" button


Accept the license agreement and click the "Forward" button.

Pick the desired ULN Registration option, then click the "Forward" button. In this case we will pick the register later option. Click the "No thanks, I'll connect later." button.

Finish the software updates setup by clicking the "Forward" button

Enter the details for system user, then click the "Forward" button.

Adjust the Date and Time settings if necessary, and click the "Forward" Button.

uncheck the default settingon the Kdump screen by clicking the "Finish" button

Next, you are presented with the login screen. Log in by clicking on the user on the login screen.

Firewall
To turn off the firewall do the following as the "root" user.

# service iptables stop
# chkconfig iptables off

So your machine is ready. You are free to change the IP addresses to suit your network, but remember to stay consistent with those adjustments throughout the..
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Rename database is a very frequent task for a DBA. Here i'll demonstrate the steps to rename a database on ASM.

Existing Name : mydb
New Name : mypoc

Step1 : start database in no-mount mode



SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             448793680 bytes
Database Buffers          381681664 bytes
Redo Buffers                2371584 bytes
Database mounted.



Step2 : Use NID to rename database



$ ./nid target=/ setname=yes dbname=mypoc
DBNEWID: Release 11.2.0.4.0 - Production on Sat Feb 13 11:31:19 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to database MYDB (DBID=29293929)
Connected to server version 11.2.0
Control Files in database:
    +DG1/mydb/controlfile/current.320.903446127
    +REDO/mydb/controlfile/current.264.903446127
    +TEST/mydb/controlfile/current.256.903446127
Change database name of database MYDB to MYPOC? (Y/[N]) => Y
Proceeding with operation
Changing database name from MYDB to MYPOC
    Control File +DG1/mydb/controlfile/current.320.903446127 - modified
    Control File +REDO/mydb/controlfile/current.264.903446127 - modified
    Control File +TEST/mydb/controlfile/current.256.903446127 - modified
    Datafile +DG1/mydb/datafile/system.322.90344616 - wrote new name
    Datafile +DG1/mydb/datafile/sysaux.321.90344616 - wrote new name
    Datafile +DG1/mydb/datafile/undo_t01.324.90344617 - wrote new name
    Datafile +DG1/pocdgs/tempfile/temp_t02.323.90369692 - wrote new name
    Control File +DG1/mydb/controlfile/current.320.903446127 - wrote new name
    Control File +REDO/mydb/controlfile/current.264.903446127 - wrote new name
    Control File +TEST/mydb/controlfile/current.256.903446127 - wrote new name
    Instance shut down
Database name changed to MYPOC.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.



Step3 : Generate new spfile and password file



$ cp spfilemydb.ora spfilemypoc.ora

$ mv orapwmydb orapwmypoc

$ . oraenv
ORACLE_SID = [mydb] ? mypoc
ORACLE_HOME = [/oracle] ? /u01/app/oracle/product
The Oracle base has been set to /oracle

$ ./sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 13 11:53:41 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.
Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             448793680 bytes
Database Buffers          381681664 bytes
Redo Buffers                2371584 bytes

SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/dbs/spfilemypoc.ora

SQL> create pfile from spfile;
File created.

$ vi initmypoc.ora
>>CHANGES ALL THE OCCURANCE OF "MYDB" TO "MYPOC", INCLUDING CONTROL FILE LOCATION

SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> create spfile from pfile;
File created.

$ mkdir -p /oracle/mypoc

SQL> startup nomount
ORACLE instance started.
Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             448793680 bytes
Database Buffers          381681664 bytes
Redo Buffers                2371584 bytes



Step4 : Create new controlfiles from existing controlfile



$ ./rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Feb 13 12:03:06 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MYPOC (not mounted)

RMAN> restore controlfile from '+DG1/mydb/controlfile/current.320.903446127';

[This command will create new controlfiles in all those diskgroups where it was previously existing, it’ll just change the file location from +dg_name/mydb to +dg_name/mypoc]

Starting restore at 13-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=416 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DG1/mypoc/controlfile/current.325.903701019
output file name=+REDO/mypoc/controlfile/current.311.903701019
output file name=+TEST/mypoc/controlfile/current.257.903701019
Finished restore at 13-FEB-16

RMAN> shutdown
Oracle instance shut down
Recovery Manager complete.

$ ./sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 13 12:04:17 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.
Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             448793680 bytes
Database Buffers          381681664 bytes
Redo Buffers                2371584 bytes
Database mounted.

SQL> select name from v$controlfile;
NAME
+DG1/mypoc/controlfile/current.325.903701019
+REDO/mypoc/controlfile/current.311.903701019
+TEST/mypoc/controlfile/current.257.903701019



Step5 : Rename datafiles



$ ./rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Feb 13 12:04:54 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MYPOC (DBID=2114307438, not open)

RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name MYPOC
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    500      SYSTEM               ***     +DG1/mydb/datafile/system.322.903446163
2    500      SYSAUX               ***     +DG1/mydb/datafile/sysaux.321.903446169
3    500      UNDO_T01             ***     +DG1/mydb/datafile/undo_t01.324.903446177
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    500      TEMP_T02             500         +DG1/mydb/tempfile/temp_t02.323.903696927

RMAN> LIST COPY OF DATABASE;
specification does not match any datafile copy in the repository

RMAN> BACKUP AS COPY DATABASE FORMAT '+DG1';
Starting backup at 13-FEB-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DG1/mydb/datafile/system.322.903446163
output file name=+DG1/mypoc/datafile/system.328.903701701 tag=TAG20160213T121459 RECID=23 STAMP=903701701
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DG1/mydb/datafile/sysaux.321.903446169
output file name=+DG1/mypoc/datafile/sysaux.327.903701701 tag=TAG20160213T121459 RECID=24 STAMP=903701702
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DG1/mydb/datafile/undo_t01.324.903446177
output file name=+DG1/mypoc/datafile/undo_t01.326.903701705 tag=TAG20160213T121459 RECID=25 STAMP=903701705
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DG1/mypoc/controlfile/backup.331.903701707 tag=TAG20160213T121459 RECID=26 STAMP=903701707
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-FEB-16
channel ORA_DISK_1: finished piece 1 at 13-FEB-16
piece handle=+DG1/mypoc/backupset/2016_02_13/nnsnf0_tag20160213t121459_0.332.903701709 tag=TAG20160213T121459 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-FEB-16

RMAN> LIST COPY OF DATABASE;
List of Datafile Copies
=======================
Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
23      1    A 13-FEB-16       499369     13-FEB-16
        Name: +DG1/mypoc/datafile/system.328.903701701
        Tag: TAG20160213T121459
24      2    A 13-FEB-16       499369     13-FEB-16
        Name: +DG1/mypoc/datafile/sysaux.327.903701701
        Tag: TAG20160213T121459
25      3    A 13-FEB-16       499369     13-FEB-16
        Name: +DG1/mypoc/datafile/undo_t01.326.903701705
        Tag: TAG20160213T121459

RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DG1/mypoc/datafile/system.328.903701701"
datafile 2 switched to datafile copy "+DG1/mypoc/datafile/sysaux.327.903701701"
datafile 3 switched to datafile copy "+DG1/mypoc/datafile/undo_t01.326.903701705"

RMAN> report schema;
Report of database schema for database with db_unique_name MYPOC
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    500      SYSTEM               ***     +DG1/mypoc/datafile/system.328.903701701
2    500      SYSAUX               ***     +DG1/mypoc/datafile/sysaux.327.903701701
3    500      UNDO_T01             ***     +DG1/mypoc/datafile/undo_t01.326.903701705
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    500      TEMP_T02             500         +DG1/pocdgs/tempfile/temp_t02.323.903696927

RMAN> alter database open;
database opened

$ ./sqlplus / as sysdba

SQL> alter tablespace TEMP_T02 add tempfile;
Tablespace altered.

SQL> alter tablespace TEMP_T02 drop tempfile '+DG1/pocdgs/tempfile/temp_t02.323.903696927';
Tablespace altered.

SQL> select name from v$database;
MYPOC

SQL> select instance_name from v$instance;
mypoc

SQL> select name from v$datafile;
+DG1/mypoc/datafile/system.328.903701701
+DG1/mypoc/datafile/sysaux.327.903701701
+DG1/mypoc/datafile/undo_t01.326.903701705



Step 6 :Re-create redo logfile



SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP# MEMBER                                                                 STATUS
---------- ---------------------------------------------------------------------- ----------------
         1 +REDO/mydb/onlinelog/group_1.306.903446197                           INACTIVE
         2 +REDO/mydb/onlinelog/group_2.307.903446197                           CURRENT

SQL> ALTER DATABASE ADD LOGFILE group 3 ('+REDO');
Database altered.

SQL> ALTER DATABASE ADD LOGFILE group 4 ('+REDO');
Database altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP# MEMBER                                                                 STATUS
---------- ---------------------------------------------------------------------- ----------------
         1 +REDO/mydb/onlinelog/group_1.306.903446197                           INACTIVE
         2 +REDO/mydb/onlinelog/group_2.307.903446197                           CURRENT
         3 +REDO/mypoc/onlinelog/group_3.310.903702355                            UNUSED
         4 +REDO/mypoc/onlinelog/group_4.309.903702377                            UNUSED

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP# MEMBER                                                                 STATUS
---------- ---------------------------------------------------------------------- ----------------
         1 +REDO/mydb/onlinelog/group_1.306.903446197                           INACTIVE
         2 +REDO/mydb/onlinelog/group_2.307.903446197                           ACTIVE
         3 +REDO/mypoc/onlinelog/group_3.310.903702355                            CURRENT
         4 +REDO/mypoc/onlinelog/group_4.309.903702377                            UNUSED

SQL> ALTER SYSTEM CHECKPOINT;
System altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP# MEMBER                                                                 STATUS
---------- ---------------------------------------------------------------------- ----------------
         1 +REDO/mydb/onlinelog/group_1.306.903446197                           INACTIVE
         2 +REDO/mydb/onlinelog/group_2.307.903446197                           INACTIVE
         3 +REDO/mypoc/onlinelog/group_3.310.903702355                            CURRENT
         4 +REDO/mypoc/onlinelog/group_4.309.903702377                            UNUSED

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.

SQL> ALTER DATABASE DROP..
Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview