RMAN - full Database Backup

RMAN Script for Full Database Backup

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

Below is the code for RMAN full backup of a database ( between the horizontal lines).

Note : Make sure the Database is running in archive log mode before you proceed.

1. We need to create the directory structure for RMANBKUP,RMANLOGS variables as per space availability in the server.

2. Replace the word “oracle_sid” with the present ORACLE_SID value , This value can be obtained by the command ( echo $ORACLE_SID)

3. Place this code in “fullbackup.sh” file in any path and run this script by one of the two ways as listed below(go to the path where .sh file is placed).   

        >./fullbackup.sh  (or)  >sh fullbackup.sh

4.After issuing the above command , nee to chek the log file from other session, So that we can confirm script is being successfully run. ( if we set the wrong directory path script will fail and same will be recorded in this log).

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

RMANBKUP="/export/home/oracle/rman_script/rmanbkp"

RMANLOGS="/export/home/oracle/rman_script/rmanlog"

TODAYD=`date '+%d%m%y_%H:%M:%S'`

mkdir -p $RMANBKUP/oracle_sidFULLORABKUP_$TODAYD

chmod 777 $RMANBKUP/oracle_sidFULLORABKUP_$TODAYD

 rman log=$RMANLOGS/weekly_full_$TODAYD.log<<EOF

connect target

backup as compressed backupset incremental level = 0 database maxsetsize=20g  format='$RMANBKUP/oracle_sidFULLORABKUP_$TODAYD/%U_%T_.bkp';

sql "alter database backup controlfile to ''/export/home/oracle/rman_script/rmanbkp/oracle_sidFULLORABKUP_$TODAYD/controlfile$TODAYD.ctl''";

crosscheck backupset;

delete noprompt expired backupset;

EOF

exit

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

---------------------   END OF DOCUMENT ----------------------

Enable Archive log mode

Enabling Archive Mode for a Database
---------------------------------------------------------------------

This activity requires down time . Please get the approval for enabling the archive mode if it is a production server.

Step 1.

Please bring down the application.

login: oracle
password:

>sqlplus '/as sysdba'
sql>show user
       ------output should be 'SYS'
sql>show parameter log_archive_format
sql>show parameter log_archive_dest_1
sql>show parameter log_archive_dest_state_1
sql>select username,status from v$session;

        -----please ensure that no user is connected to the database.

sql>create pfile from spfile;
sql>shutdown immediate
sql>exit

Step 2.

>cd $ORACLE_HOME/dbs
– in this location “ initsid.ora” file is here ,
 (to find the sid in the above init file put this command ----- >echo $ORACLE_SID )

Now open this initsid.ora file in vi mode and add the below three line code at the end, save & exit

 ( Give the location as per space availability in the server and make sure that given path is existed)

>vi initsid.ora

*.log_archive_format=%t_%r_%s.arc
*.log_archive_dest_1='LOCATION=/export/home/oracle/oradg/arch'
*.log_archive_dest_state_1=enable

 

Step 3.

>sqlplus '/as sysdba'
sql>startup pfile='$ORACLE_HOME/dbs/initsid.ora';

sql> create spfile from pfile;
sql>shutdown immediate;

Step 4.

sql>startup;
sql>alter system checkpoint;
sql>shutdown immediate;
sql>startup mount exclusive;
sql>alter database archivelog;
sql>shutdown immediate;

 

Step 5.

sql>startup;
sql>alter system archive log current;

check for the archive log in the given path.

( if the archive files are created in this path we can confirm that the bove activity was successfulf )

-------------------   END OF DOCUMENT  -----------------------

How to change Database ID

                      How to change Database ID

Step 1: Backup the database.
Step 2: Mount the database after a clean shutdown:

bash$ SHUTDOWN IMMEDIATE
bash$ STARTUP MOUNT

Step 3 :Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege. Do not specify a new DBNAME:

bash$ nid TARGET=sys/password@SID

(below out put may expected)
DBNEWID: Release 10.2.0.1.0 - Production on Sat Feb 5 13:56:00 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to database MYDB (DBID=2730365025)
Connected to server version 10.2.0
Control Files in database:
    /export/home/oracle/oradg/oradata/control01.ctl
    /export/home/oracle/oradg/oradata/control02.ctl
    /export/home/oracle/oradg/oradata/control03.ctl
Change database ID of database MYDB? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2730365025 to 2657293041
    Control File /export/home/oracle/oradg/oradata/control01.ctl - modified
    Control File /export/home/oracle/oradg/oradata/control02.ctl - modified
    Control File /export/home/oracle/oradg/oradata/control03.ctl - modified
    Datafile /export/home/oracle/oradg/oradata/system01.dbf - dbid changed
    Datafile /export/home/oracle/oradg/oradata/undotbs01.dbf - dbid changed
    Datafile /export/home/oracle/oradg/oradata/sysaux01.dbf - dbid changed
    Datafile /export/home/oracle/oradg/oradata/users01.dbf - dbid changed
    Datafile /export/home/oracle/oradg/oradata/temp01.dbf - dbid changed
    Control File /export/home/oracle/oradg/oradata/control01.ctl - dbid changed
    Control File /export/home/oracle/oradg/oradata/control02.ctl - dbid changed
    Control File /export/home/oracle/oradg/oradata/control03.ctl - dbid changed
    Instance shut down
Database ID for database MYDB changed to 2657293041.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
Step 4 :Shutdown and open the database with RESETLOGS:

bash$ SHUTDOWN IMMEDIATE
bash$ STARTUP MOUNT
bash$ ALTER DATABASE OPEN RESETLOGS;

Step 5 : Backup the database.

How to change a Database Name

              How to change a Database Name                                                            --Ramesh Togara (04/02/11)

Step 1 : startup the database and note down the dbname.
SQL> startup

ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 1977976 bytes
Variable Size 138416520 bytes
Database Buffers 121634816 bytes
Redo Buffers 2211840 bytes
Database mounted.
Database opened.

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
ram

SQL> select name from v$database;
NAME
---------

NEWDB1

Step 2 : shutdown the database and open in mount state
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 1977976 bytes
Variable Size 142610824 bytes
Database Buffers 117440512 bytes
Redo Buffers 2211840 bytes
Database mounted.

Step 3 : now exit from sql promt and use nid oracle utility as specified below .

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

bash-3.00$ nid target=sys/ramesh dbname=mydb setname=y /* maximum database name length is 8 characters */

(below out put will be expected )

DBNEWID: Release 10.2.0.1.0 - Production on Fri Feb 4 13:55:29 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database NEWDB1 (DBID=2730365025)

Connected to server version 10.2.0

Control Files in database:
/export/home/oracle/oradg/oradata/control01.ctl
/export/home/oracle/oradg/oradata/control02.ctl
/export/home/oracle/oradg/oradata/control03.ctl

Change database name of database NEWDB1 to MYDB? (Y/[N]) => Y /* enter Y for promting */

Proceeding with operation
Changing database name from NEWDB1 to MYDB
Control File /export/home/oracle/oradg/oradata/control01.ctl - modified
Control File /export/home/oracle/oradg/oradata/control02.ctl - modified
Control File /export/home/oracle/oradg/oradata/control03.ctl - modified
Datafile /export/home/oracle/oradg/oradata/system01.dbf - wrote new name
Datafile /export/home/oracle/oradg/oradata/undotbs01.dbf - wrote new name
Datafile /export/home/oracle/oradg/oradata/sysaux01.dbf - wrote new name
Datafile /export/home/oracle/oradg/oradata/users01.dbf - wrote new name
Datafile /export/home/oracle/oradg/oradata/temp01.dbf - wrote new name
Control File /export/home/oracle/oradg/oradata/control01.ctl - wrote new name
Control File /export/home/oracle/oradg/oradata/control02.ctl - wrote new name
Control File /export/home/oracle/oradg/oradata/control03.ctl - wrote new name
Instance shut down

Database name changed to MYDB.

Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.

DBNEWID - Completed succesfully.

Step 4 : change the db_name parameter value in initsid.ora file as new database name

bash-3.00$ pwd
/export/home/u01/app/oracle/product/10.2/dbs

bash-3.00$ vi initram.ora /* here change db_name prameter to 'mydb' */

"initram.ora" 34 lines, 1254 characters
oradg.__db_cache_size=117440512
oradg.__java_pool_size=4194304
oradg.__large_pool_size=4194304
oradg.__shared_pool_size=79691776
oradg.__streams_pool_size=0
*.audit_file_dest='/export/home/oracle/oradg/admin/adump'
*.background_dump_dest='/export/home/oracle/oradg/admin/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/export/home/oracle/oradg/oradata/control01.ctl','/export/home/oracle/oradg/oradata/control02.ctl','/export/home/oracle/oradg/oradata/control03.ctl'
*.core_dump_dest='/export/home/oracle/oradg/admin/cdump'
*.cursor_sharing='SIMILAR'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='newdb1' ----------------------------------------------------> this is need to be changed to 'mydb'
*.instance_name='ram'
*.log_archive_dest_1='location=/export/home/oracle/oradg/arch'
*.log_archive_dest_2='service=standby lgwr sync'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_start=true
*.open_cursors=300
*.os_authent_prefix=''
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='primary'
*.session_cached_cursors=50
*.sga_max_size=250m
*.sga_target=200m
*.smtp_out_server=' '
*.standby_archive_dest='/export/home/oracle/oradg/arch'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/export/home/oracle/oradg/admin/udump'
~
~
~
~
~
~
~
~
~
"initram.ora" 34 lines, 1252 characters
Step 5 : create a new password file

bash-3.00$ rm orapwram

bash-3.00$ orapwd file=orapwram password=ramesh entries=5

bash-3.00$ sqlplus sys/ramesh as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 4 13:57:46 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.

Step 6 : open the database with new initsid.ora file and find the new database name

SQL> startup pfile=initram.ora nomount;
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 1977976 bytes
Variable Size 138416520 bytes
Database Buffers 121634816 bytes
Redo Buffers 2211840 bytes

SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.

SQL> select name from v$database;

NAME
---------
MYDB -------------> successfully changed.

SQL> create spfile from pfile;
File created.

SQL> create pfile from spfile;
File created.

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

SQL> startup;
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 1977976 bytes
Variable Size 138416520 bytes
Database Buffers 121634816 bytes
Redo Buffers 2211840 bytes
Database mounted.
Database opened.

SQL> select name from v$database;
NAME
---------
MYDB




-------------------------------- END OF DOCUMENT --------------------------------