Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

step by step creating oracle cold cloning an existing Oracle Database(dbca created db )


          source db:        kiruba
          clone  db :         nijam
1)[oracle@anandan ~]$ export ORACLE_SID=kiruba
[oracle@anandan ~]$ sqlplus / as sysdba


2)SQL> alter database backup controlfile to trace as '/home/oracle/monkey.sql';

Database altered.

3) show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/spfilekiruba.ora

4)SQL>  create pfile='/home/oracle/initkiruba.ora'from spfile;

File created.

5) SQL> select FILE_NAME from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/kiruba/users01.dbf
/u01/app/oracle/oradata/kiruba/sysaux01.dbf
/u01/app/oracle/oradata/kiruba/undotbs01.dbf
/u01/app/oracle/oradata/kiruba/system01.dbf
/home/oracle/kiruba/db1a.dbf
/home/oracle/karan/temp012.dbf


6) SQL> shut immediate;

7)[oracle@anandan ~]$
 mkdir nijam
 cd nijam/
 mkdir admin oradata
 cd oradata/
 mkdir control pfile data log create
cd
cd nijam/admin
mkdir adump bdump cdump udump

7.1)nijam
|-- admin
|   |-- adump
|   |-- bdump
|   |-- cdump
|   `-- udump
`-- oradata
    |-- control
    |-- data
    |--pfile
    |--log
    |--create
8)
cp -r /u01/app/oracle/oradata/kiruba/*  /home/oracle/nijam/oradata/data
cp -r /home/oracle/kiruba/*  /home/oracle/nijam/oradata/data

9)
[oracle@anandan data]$ ll
total 1246292
-rw-r----- 1 oracle oinstall   7061504 Nov 18 02:12 control01.ctl
-rw-r----- 1 oracle oinstall   7061504 Nov 18 02:12 control02.ctl
-rw-r----- 1 oracle oinstall   7061504 Nov 18 02:12 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Nov 18 02:19 db1a.dbf
-rw-r----- 1 oracle oinstall  52429312 Nov 18 02:12 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Nov 18 02:13 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Nov 18 02:13 redo03.log
-rw-r----- 1 oracle oinstall 251666432 Nov 18 02:14 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Nov 18 02:15 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Nov 18 02:15 temp01.dbf
-rw-r----- 1 oracle oinstall  26222592 Nov 18 02:16 undotbs01.dbf
-rw-r----- 1 oracle oinstall 209723392 Nov 18 02:16 users01.dbf

10)
rm -r /home/oracle/nijam/oradata/data/control*       ---------------->remove unWANTED REDOLOG AND CONROLFILE
rm -r /home/oracle/nijam/oradata/data/redo*

11)
[oracle@anandan data]$ ll
total 1071764
-rw-r----- 1 oracle oinstall 104865792 Nov 18 02:19 db1a.dbf
-rw-r----- 1 oracle oinstall 251666432 Nov 18 02:14 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Nov 18 02:15 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Nov 18 02:15 temp01.dbf
-rw-r----- 1 oracle oinstall  26222592 Nov 18 02:16 undotbs01.dbf
-rw-r----- 1 oracle oinstall 209723392 Nov 18 02:16 users01.dbf



12)
mv /home/oracle/initkiruba.ora /home/oracle/nijam/oradata/pfile/initnijam.ora
vi /home/oracle/nijam/oradata/pfile/initnijam.ora

kiruba.__db_cache_size=218103808
kiruba.__java_pool_size=4194304
kiruba.__large_pool_size=4194304
kiruba.__shared_pool_size=54525952
kiruba.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/kiruba/adump'
*.background_dump_dest='/u01/app/oracle/admin/kiruba/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/kiruba/control01.ctl','/u01/app/oracle/oradata/kiruba/control02.ctl','/u01/app/oracle/oradata/kiruba/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/kiruba/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='kiruba'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=kirubaXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/kiruba/udump'



edit this like.....



*.audit_file_dest='/home/oracle/nijam/admin/adump'
*.background_dump_dest='/home/oracle/nijam/admin/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle/nijam/oradata/control/control.ctl'
*.core_dump_dest='/home/oracle/nijam/admin/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name=nijam
#*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
#*.db_recovery_file_dest_size=2147483648
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=kirubaXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle/nijam/admin/udump'


13)vi /home/oracle/monkey.sql

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "KIRUBA" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/kiruba/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/kiruba/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/kiruba/redo03.log'  SIZE 50M
-- STANDBY LOGFILE

DATAFILE
  '/u01/app/oracle/oradata/kiruba/system01.dbf',
  '/u01/app/oracle/oradata/kiruba/undotbs01.dbf',
  '/u01/app/oracle/oradata/kiruba/sysaux01.dbf',
  '/u01/app/oracle/oradata/kiruba/users01.dbf',
  '/home/oracle/kiruba/db1a.dbf'
CHARACTER SET WE8ISO8859P1
;


edit like this----------------


CREATE CONTROLFILE SET DATABASE "nijam" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/nijam/oradata/log/redo01.log'  SIZE 50M,
  GROUP 2 '/home/oracle/nijam/oradata/log/redo02.log'  SIZE 50M,
  GROUP 3 '/home/oracle/nijam/oradata/log/redo03.log'  SIZE 50M
-- STANDBY LOGFILE

DATAFILE
  '/home/oracle/nijam/oradata/data/system01.dbf',
  '/home/oracle/nijam/oradata/data/undotbs01.dbf',
  '/home/oracle/nijam/oradata/data/sysaux01.dbf',
  '/home/oracle/nijam/oradata/data/users01.dbf',
  '/home/oracle/nijam/oradata/data/db1a.dbf'
CHARACTER SET WE8ISO8859P1
;



14) mv /home/oracle/monkey.sql /home/oracle/nijam/oradata/create/

15)
[oracle@anandan ~]$ export ORACLE_SID=nijam
[oracle@anandan ~]$ sqlplus / as sysdba


16)
SQL> startup pfile='/home/oracle/nijam/oradata/pfile/initnijam.ora' nomount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes


17)03:36:33 SQL>  @/home/oracle/nijam/oradata/create/monkey.sql;
SP2-0042: unknown command "DATAFILE" - rest of line ignored.
SP2-0734: unknown command beginning "'/home/ora..." - rest of line ignored.
SP2-0734: unknown command beginning "'/home/ora..." - rest of line ignored.
SP2-0734: unknown command beginning "'/home/ora..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "'/home/ora..." - rest of line ignored.
SP2-0734: unknown command beginning "'/home/ora..." - rest of line ignored.
SP2-0734: unknown command beginning "CHARACTER ..." - rest of line ignored.
  1  CREATE CONTROLFILE SET DATABASE "nijam" RESETLOGS ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/home/oracle/nijam/oradata/log/redo01.log'  SIZE 50M,
  9    GROUP 2 '/home/oracle/nijam/oradata/log/redo02.log'  SIZE 50M,
 10    GROUP 3 '/home/oracle/nijam/oradata/log/redo03.log'  SIZE 50M
 11* -- STANDBY LOGFILE

solution:-

03:36:41 SQL> set sqlblanklines ON;  --->Or remove the blank lines.
03:38:55 SQL> @/home/oracle/nijam/oradata/create/monkey.sql;


18)SQL> alter database open resetlogs;

Database altered.


19)SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
NIJAM     READ WRITE


20)SQL> select file_name,TABLESPACE_NAME from dba_data_files;
FILE_NAME                                               TABLESPACE_NAME
------------------------------------------------------- ------------------------------
/home/oracle/nijam/oradata/data/db1a.dbf                TBS1
/home/oracle/nijam/oradata/data/users01.dbf             USERS
/home/oracle/nijam/oradata/data/sysaux01.dbf            SYSAUX
/home/oracle/nijam/oradata/data/undotbs01.dbf           UNDOTBS1
/home/oracle/nijam/oradata/data/system01.dbf            SYSTEM
/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006    TBQ1



21)SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1


Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

What's is the difference between streaming replication Vs hot standby vs warm standby ?

Script to kill ALL IDLE Connection In postgreSQL