Useful shortcuts for vi editor

Wednesday 2 November 2016

How to Clone Database Manually without any tool such as RMAN?

Collect live database paths before start
$ ps -ef | grep pmon
$ echo $ORACLE_SID
$ echo $ORACLE_BASE
$ echo $ORACLE_HOME
$ . oraenv

SQL> select name from v$datafile; --db files

SQL> select member from v$logfile; --redo log files
SQL> select name from v$controlfile; --control files
SQL> select name from v$tempfile; --temp files

Copy parameter file (pfile) from live database

SQL> create pfile='/tmp/Alper-init.ora' from spfile;
or
* Copy livedb pfile under $ORACLE_HOME/dbs directory as desired file name like 'Alper-init.ora'

Edit pfile

* Change related parameter such as db_name and control files path (Alper-init.ora)

Copy control file from live database

SQL> alter database backup controlfile to trace as '/tmp/Alper-ctl';
or
SQL> alter database backup controlfile to trace; 
-- Run SQL> show parameter user_dump_dest; to find it

Edit control file

* Prepare a new script with this script
SQL> CREATE CONTROLFILE REUSE SET DATABASE "ALPERDB" RESETLOGS  NOARCHIVELOG
-- Use this if you want to re-use livedb controlfiles in new clone db (if you already moved the controlfiles to new clone db)
or
SQL> CREATE CONTROLFILE SET DATABASE "ALPERDB" RESETLOGS  NOARCHIVELOG
-- Use this if you want to use fresh control file in new clone db
-- SQL> show parameter control; -- to find control file

Copy all database files to remote machine
$ scp -r /oradata/ALPERDB/ oracle@10.10.10.10:/oradata/NEW_ALPERDB

Open database
SQL> alter database open resetlogs;

Define temp tablespace for new database
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/NEW_ALPERDB/temp_01.dbf'
     SIZE 10240M REUSE AUTOEXTEND OFF;
For detail information please click this

That's all :)