11g Database Hot Backup Process



***************11g Database Hot Backup ******************

Database version:-  11g Enterprise Edition Release 11.2.0.1.0

11g Database Hot Backup:-

Database version:-  11g Enterprise Edition Release 11.2.0.1.0

Steps:-
1. export the environmental values.
2. put database begin backup mode.
3. check the datafiles status.
4. copy datafiles from production to Development(Destination).
5. make up database to the end backup.
6. check the datafiles status
7. switch log file once again
8. make backup controlfile as .trc format or .txt format.
9. copy archives files from production and development.



Processes:-

1 step:-

export ORACLE_SID=sid
export ORACLE_HOME=/u01/app/oracle/product/11.2.1.0
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

2 Step:-

SQL> alter database begin backup;

Database altered.


Note:- Archive log enabled. (Media recovery)


3. Step:-

SQL> select * from v$backup;


     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                 791998 01-JAN-11
         2 ACTIVE                 791998 01-JAN-11
         3 ACTIVE                 791998 01-JAN-11
         4 ACTIVE                 791998 01-JAN-11



Note: should be status ACTIVE all.

4 Step:-

cp -rp *.dbf  /Dev/datafile/.


5 Step:-

SQL> alter database end backup;

Database altered.


6 Step:-

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE             791998 01-JAN-11
         2 NOT ACTIVE             791998 01-JAN-11
         3 NOT ACTIVE             791998 01-JAN-11
         4 NOT ACTIVE             791998 01-JAN-11


Note:- Sould be NOT ACTIVE all.

7 Step:-

SQL> alter system switch logfile;
System altered.



8. Step:-

SQL> alter database backup controlfile to trace;

Database altered.


9 Step:-

cp *arc  /Dev/arch/.



Hotbackup  activity completed as manual.

I will be update the scripts for this hotbackup and  crontab (schedule jobs).

Plz, send  me your vaulable updates.


Creating Oracle 11R2 database on ASM.


   Creating Oracle  11R2 database on ASM.

Instance Environment Parameter:-
ORACLE_SID=TEST
ORACLE_HOME=/u01/app/oracle/11g
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID
export ORACLE_HOME
export PATH
export LD_LIBRARY_PATH

Parameter of Instance (init.ora)
db_name=TEST
db_block_size=8192
sga_target= 4096m
background_dump_dest = /u01/app/oracle/11g/log/diag/rdbms/test/TEST/trace
core_dump_dest  = /u01/app/oracle/11g/log/diag/rdbms/test/TEST/cdump
user_dump_dest = /u01/app/oracle/11g/log/diag/rdbms/test/TEST/trace
control_files = '+DATA/test/control01.ctl', '/u01/app/oracle/11g/dbs/TEST_contorlfile/control02.ctl'
undo_management=auto
undo_tablespace=undotbs
undo_retention=900
compatible=11.2.0.0.0

Database creation
create database TEST datafile '+DATA/test/system01.dbf' size 1024m
sysaux datafile '+DATA/test/sysaux01.dbf' size 1024m
default temporary tablespace temp tempfile '+DATA/test/temp01.dbf' size 500m
undo tablespace UNDOTBS datafile '+DATA/test/undotbs01.dbf' size 500m
logfile
group 1 ('+DATA/test/redo1a.log','+DATA/test/redo1b.log') size 40m,
group 2 ('+DATA/test/redo2a.log','+DATA/test/redo2b.log') size 40m
character set UTF8;

Post Steps:-
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
conn system/manager
@?/sqlplus/admin/pupbld.sql


How to switch ARCHIVELOG on Database

Action Plan For Archive Switching  On:-

1. Check the log mode of database

SELECT LOG_MODE FROM V$DATABASE;

2. Change from spfile to pfile

sho parameter spfile

create pfile from spfile (if spfile is enabled).

3. Stop the database

SQL> select  name,open_mode from  v$database;

NAME      OPEN_MODE
--------- ----------
PRERANA   READ WRITE

SQL> Shutdown immediate

4. Add below parameters  to  pfile

*.log_archive_dest_1='LOCATION=/xxxxxx/xxxx/arch'
*.log_archive_dest_state_1='enable'
*.log_archive_format='XXXXX_%r_%t_%s.arc'
*.log_archive_max_processes=2

5. Start the database with mount state

SQL> startup  mount

6. Enable the archive log mode to Database


SQL>  alter database archivelog;


7. Check log mode at database level

SELECT LOG_MODE FROM V$DATABASE;

SQL > archive log list

8. Open the database with normal

SQL > alter database open;

SQL > select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST;

DEST_NAME            STATUS               DESTINATION
-------------------- -------------------- ------------------------------
LOG_ARCHIVE_DEST_1   VALID                /datamart/data_prerana/archive
LOG_ARCHIVE_DEST_2   INACTIVE
LOG_ARCHIVE_DEST_3   INACTIVE
LOG_ARCHIVE_DEST_4   INACTIVE
LOG_ARCHIVE_DEST_5   INACTIVE
LOG_ARCHIVE_DEST_6   INACTIVE
LOG_ARCHIVE_DEST_7   INACTIVE
LOG_ARCHIVE_DEST_8   INACTIVE
LOG_ARCHIVE_DEST_9   INACTIVE
LOG_ARCHIVE_DEST_10  INACTIVE

10 rows selected.

Startup Error after Instance SGA Size around 8GB

Startup Error after Instance SGA  Size around 8GB

Recently, We have planned to increase the Instance SGA 6GB to 8GB. After changed, Instance failed to started.
Error:-  “SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device” .  

Solution: -
1.       1. cat /proc/sys/kernel/shmall
output:- Nill
2.      2.  Login as ROOT user
3.      3.  Cat  /etc/sysctl.conf
No entry of kernel.shmall
4.      4.    Vi  /etc/sysctl.conf
5.      5.  Added kernel.shmall = 4194304 to “/etc/sysctl.conf”
6.      6.  Executed    “/sbin/sysctl –p”
7.      7.  Checked  “cat /proc/sys/kernel/shmall”
       Output  :- 4194304
8.       8. Checked  “$ getconf PAGE_SIZE”
Output :-   4096
Note:-  Not required to system bounce.

Finally  started the database  without  any error.
 
************************************************************************

No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...