Tuesday, November 26, 2013
Monday, November 25, 2013
Thursday, November 21, 2013
Oracle 12c Database Online Move Datafile
Prior to Oracle 12c, if you wanted to move a database’s file, you either had to shutdown the database, or take the datafile/tablespace offline. Here is an example of the steps you might take
SQL> ALTER TABLESPACE test OFFLINE;
SQL> !mv /ora/test1/test01.dbf /ora/test2/test01.dbf
SQL> ALTER DATABASE RENAME FILE ‘/ora/test1/test01' TO ‘/ora/test2/test01.dbf’;
SQL>ALTER TABLESPACE test ONLINE;
[oracle@oracle12c bin]$ export ORACLE_SID=cdb1
[oracle@oracle12c bin]$ pwd
/ora/oracle/app/oracle/product/12.1.0/dbhome_1/bin
[oracle@oracle12c bin]$ ./sqlplus /nolog
SQL*Plus: Release 12.1.0.1.0 Production on Fri 6 Sept 22 01:38:02 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdb1;
Session altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL> select file_name from dba_data_Files;
FILE_NAME
--------------------------------------------------------------------------------
/ora/oracle/app/oracle/oradata/cdb1/pdb1/system01.dbf
/ora/oracle/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/ora/oracle/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> select tablespace_name from dba_data_Files;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS
Creating Tablespace user_data
Creating Tablespace user_data
SQL> create tablespace user_dat
2 datafile '/ora/oracle/app/oracle/oradata/cdb1/pdb1/userdat.dbf'
3 size 1g;
Tablespace created.
SQL> select tablespace_name from dba_data_files;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS
USER_DAT
Inserting Rows in test table
SQL> create table system.test(id number) tablespace user_dat;
Table created.
SQL> insert into system.test values(05);
1 row created.
SQL> commit;
Commit complete.
Move Tablespace datafile to another location
Move Tablespace datafile to another location
SQL> alter database move datafile
2 '/ora/oracle/app/oracle/oradata/cdb1/pdb1/userdat.dbf' to
3 '/ora/oracle/app/oracle/oradata/cdb1/movefile/userdat.dbf';
Database altered.
SQL> insert into system.test values(20);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from system.test;
ID
----------
5
20
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oracle12c bin]$ pwd
/ora/oracle/app/oracle/product/12.1.0/dbhome_1/bin
oracle@oracle12c bin]$ cd /
oracle@oracle12c cdb1]$ ls
control01.ctl pdb1.xml pdbseed redo03.log temp01.dbf
movefile pdb2 redo01.log sysaux01.dbf undotbs01.dbf
pdb1 pdb3 redo02.log system01.dbf users01.dbf
oracle@oracle12c cdb1]$ cd movefile
oracle@oracle12c movefile]$ pwd
/ora/oracle/app/oracle/oradata/cdb1/movefile
oracle@oracle12c movefile]$ ls
userdat.dbf
[oracle@oracle12c movefile]$ cd ..
[oracle@oracle12c cdb1]$ cd pdb1
[oracle@oracle12c pdb1]$ ls
pdb1_users01.dbf
sysaux01.dbf
system01.dbf
temp01.dbf
Wednesday, November 20, 2013
Migrate a Non-Container Database (CDB) to a Pluggable Database (PDB) in Oracle Database 12c
Migrate a Non-Container DB (CDB) to PDB
Container DB: CDB2
Non-PDB: PDB7
Shutdown the non-CDB and start it in read-only mode.
$ export ORACLE_SID=pdb7
$ sqlplus / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2293880 bytes
Variable Size 595595144 bytes
Database Buffers 234881024 bytes
Redo Buffers 2334720 bytes
Database mounted.
Database opened.
This procedure creates an XML file in the same way that the unplug operation does for a PDB.
SQL>
SQL> begin
2 DBMS_PDB.DESCRIBE(
3 pdb_descr_file => '/tmp/db12c.xml');
4 end;
5 /
PL/SQL procedure successfully completed.
Shutdown the non-CDB database.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2293880 bytes
Variable Size 595595144 bytes
Database Buffers 234881024 bytes
Redo Buffers 2334720 bytes
Database mounted.
Database opened.
This procedure creates an XML file in the same way that the unplug operation does for a PDB.
SQL>
SQL> begin
2 DBMS_PDB.DESCRIBE(
3 pdb_descr_file => '/tmp/db12c.xml');
4 end;
5 /
PL/SQL procedure successfully completed.
Shutdown the non-CDB database.
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$ oracle@oracle12c bin]$ export ORACLE_SID=cdb2
$ oracle@oracle12c bin]$ ./sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on SEPT 21 01:15:21 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connect to an existing CDB and create a new PDB using the file describing the non-CDB database. Remember to configure the
FILE_NAME_CONVERT parameter to convert the existing files to the new location.SQL> create pluggable database pdb7 using '/tmp/db12c.xml'
2 copy
3 file_name_convert = ('/ora/oracle/app/oracle/oradata/pdb7/','/ora/oracle/app/oracle/oradata/cdb2/pdb7/');
Pluggable database created.
System altered.
Switch to the PDB container and run the "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql" script to clean up the new PDB, removing any items that should not be present in a PDB. You can see an example of the output produced by this script here.
SQL> alter session set container = pdb7;
Session altered.
SQL>@ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
..........
...........
SQL> -- leave the PDB in the same state it was when we started
SQL> BEGIN
2 execute immediate '&open_sql &restricted_state';
3 EXCEPTION
4 WHEN OTHERS THEN
5 BEGIN
6 IF (sqlcode <> -900) THEN
7 RAISE;
8 END IF;
9 END;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> WHENEVER SQLERROR CONTINUE;
Startup the PDB and check the open mode.
SQL> alter session set container=pdb7;
Session altered.
SQL> alter pluggable database open;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ------------------------
PDB7 READ WRITE
1 row selected.
NcFTP Client for copying files to remote location
http://www.ncftp.com/ncftp/
Download ncftp client
# vi xyz.sh
cd /ncftp-3.2.4/bin
./ncftpput -DD -u admin -p xyz123 192.0.0.120 /Archivelog1 /ora_temp/archive1/*.dbf
Username: admin
Password: xyz123
Remotely Location IP: 192.0.0.120
Remotely Folder: Archivelog1
host Location Folder: /ora_temp/archive1
Sunday, November 17, 2013
Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c R.1
Connecting to Container Databases (CDB) and Pluggable Databases (PDB)
$ export ORACLE_SID=cdb1
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat 17 15:20:10 2013
Copyright (c) 1982, 2013, Oracle. All rigths reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
with the partitioning, OLAP, Advance Analytics and real Application Testing options
SQL>
Switching Between Containers:
SQL> ALTER SESSION SET CONTAINER = pdb1;
session altered.
SQL> SHOW con_name
CON_NAME
---------------------------------
PDB1
SQL> ALTER SESSION SET container = cdb$root;
Session Altered.
SQL> SHOW CON_NAME
CON_NAME
-----------------------------
CDB$ROOT
$ export ORACLE_SID=cdb1
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat 17 15:20:10 2013
Copyright (c) 1982, 2013, Oracle. All rigths reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
with the partitioning, OLAP, Advance Analytics and real Application Testing options
SQL>
Switching Between Containers:
SQL> ALTER SESSION SET CONTAINER = pdb1;
session altered.
SQL> SHOW con_name
CON_NAME
---------------------------------
PDB1
SQL> ALTER SESSION SET container = cdb$root;
Session Altered.
SQL> SHOW CON_NAME
CON_NAME
-----------------------------
CDB$ROOT
Connecting to a Pluggable Database (PDB):
SQL> conn sys/xyz55555@localhost:1521/pdb1 as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
PDB1
Displaying the Current Container:
The
SHOW CON_NAME command in SQL*Plus displays the current container name.
SQL> show con_name
CON_NAME
-------------------------------------------------
CDB$ROOT
retrieved using the
SYS_CONTEXT function.
SQL> SELECT SYS_CONTEXT('USERENV','CON_NAME')
FROM DUAL;
SYS_CONTEXT('USERENV','CON_NAME')
-----------------------------------------------------------------------
CDB$ROOT
Oracle RMAN Hot backup Script
#!/bin/sh
# $Header: hot_database_backup.sh,v 1.2 2002/08/06 23:51:42 $
#
#bcpyrght
#***************************************************************************
#* $VRTScprght: Copyright 1993 - 2007 Symantec Corporation, All Rights Reserved $ *
#***************************************************************************
#ecpyrght
#
# ---------------------------------------------------------------------------
# hot_database_backup.sh
# ---------------------------------------------------------------------------
# This script uses Recovery Manager to take a hot (inconsistent) database
# backup. A hot backup is inconsistent because portions of the database are
# being modified and written to the disk while the backup is progressing.
# You must run your database in ARCHIVELOG mode to make hot backups. It is
# assumed that this script will be executed by user root. In order for RMAN
# to work properly we switch user (su -) to the oracle dba account before
# execution. If this script runs under a user account that has Oracle dba
# privilege, it will be executed using this user's account.
# ---------------------------------------------------------------------------
# ---------------------------------------------------------------------------
# Determine the user which is executing this script.
# ---------------------------------------------------------------------------
CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`
# ---------------------------------------------------------------------------
# Put output in <this file name>.out. Change as desired.
# Note: output directory requires write permission.
# ---------------------------------------------------------------------------
RMAN_LOG_FILE=${0}.out
# ---------------------------------------------------------------------------
# You may want to delete the output file so that backup information does
# not accumulate. If not, delete the following lines.
# ---------------------------------------------------------------------------
if [ -f "$RMAN_LOG_FILE" ]
then
rm -f "$RMAN_LOG_FILE"
fi
# -----------------------------------------------------------------
# Initialize the log file.
# -----------------------------------------------------------------
echo >> $RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE
# ---------------------------------------------------------------------------
# Log the start of this script.
# ---------------------------------------------------------------------------
echo Script $0 >> $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
# ---------------------------------------------------------------------------
# Replace /db/oracle/product/ora81, below, with the Oracle home path.
# ---------------------------------------------------------------------------
#ORACLE_HOME=/db/oracle/product/ora81
ORACLE_HOME=/ora/crs/oracle/product/10/app
export ORACLE_HOME
# ---------------------------------------------------------------------------
# Replace ora81, below, with the Oracle SID of the target database.
# ---------------------------------------------------------------------------
#ORACLE_SID=ora81
ORACLE_SID=test
export ORACLE_SID
# ---------------------------------------------------------------------------
# Replace ora81, below, with the Oracle DBA user id (account).
# ---------------------------------------------------------------------------
#ORACLE_USER=ora81
ORACLE_USER=oracle
# ---------------------------------------------------------------------------
# Set the target connect string.
# Replace "sys/manager", below, with the target connect string.
# ---------------------------------------------------------------------------
#TARGET_CONNECT_STR=sys/manager
TARGET_CONNECT_STR=sys/oracle
# ---------------------------------------------------------------------------
# Set the Oracle Recovery Manager name.
# ---------------------------------------------------------------------------
RMAN=$ORACLE_HOME/bin/rman
# ---------------------------------------------------------------------------
# Print out the value of the variables set by this script.
# ---------------------------------------------------------------------------
echo >> $RMAN_LOG_FILE
echo "RMAN: $RMAN" >> $RMAN_LOG_FILE
echo "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE
echo "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE
echo "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE
# ---------------------------------------------------------------------------
# Print out the value of the variables set by bphdb.
# ---------------------------------------------------------------------------
echo >> $RMAN_LOG_FILE
echo "NB_ORA_FULL: $NB_ORA_FULL" >> $RMAN_LOG_FILE
echo "NB_ORA_INCR: $NB_ORA_INCR" >> $RMAN_LOG_FILE
echo "NB_ORA_CINC: $NB_ORA_CINC" >> $RMAN_LOG_FILE
echo "NB_ORA_SERV: $NB_ORA_SERV" >> $RMAN_LOG_FILE
set NB_ORA_POLICY Oracle_ssaerp_hot
NB_ORA_POLICY=Oracle_test_hot;export NB_ORA_POLICY
echo "NB_ORA_POLICY: $NB_ORA_POLICY" >> $RMAN_LOG_FILE
# ---------------------------------------------------------------------------
# NOTE: This script assumes that the database is properly opened. If desired,
# this would be the place to verify that.
# ---------------------------------------------------------------------------
echo >> $RMAN_LOG_FILE
# ---------------------------------------------------------------------------
# If this script is executed from a NetBackup schedule, NetBackup
# sets an NB_ORA environment variable based on the schedule type.
# The NB_ORA variable is then used to dynamically set BACKUP_TYPE
# For example, when:
# schedule type is BACKUP_TYPE is
# ---------------- --------------
# Automatic Full INCREMENTAL LEVEL=0
# Automatic Differential Incremental INCREMENTAL LEVEL=1
# Automatic Cumulative Incremental INCREMENTAL LEVEL=1 CUMULATIVE
#
# For user initiated backups, BACKUP_TYPE defaults to incremental
# level 0 (full). To change the default for a user initiated
# backup to incremental or incremental cumulative, uncomment
# one of the following two lines.
# BACKUP_TYPE="INCREMENTAL LEVEL=1"
# BACKUP_TYPE="INCREMENTAL LEVEL=1 CUMULATIVE"
#
# Note that we use incremental level 0 to specify full backups.
# That is because, although they are identical in content, only
# the incremental level 0 backup can have incremental backups of
# level > 0 applied to it.
# ---------------------------------------------------------------------------
if [ "$NB_ORA_FULL" = "1" ]
then
echo "Full backup requested" >> $RMAN_LOG_FILE
BACKUP_TYPE="INCREMENTAL LEVEL=0"
elif [ "$NB_ORA_INCR" = "1" ]
then
echo "Differential incremental backup requested" >> $RMAN_LOG_FILE
BACKUP_TYPE="INCREMENTAL LEVEL=1"
elif [ "$NB_ORA_CINC" = "1" ]
then
echo "Cumulative incremental backup requested" >> $RMAN_LOG_FILE
BACKUP_TYPE="INCREMENTAL LEVEL=1 CUMULATIVE"
elif [ "$BACKUP_TYPE" = "" ]
then
echo "Default - Full backup requested" >> $RMAN_LOG_FILE
BACKUP_TYPE="INCREMENTAL LEVEL=0"
fi
# ---------------------------------------------------------------------------
# Call Recovery Manager to initiate the backup. This example does not use a
# Recovery Catalog. If you choose to use one, replace the option 'nocatalog'
# from the rman command line below with the
# 'rcvcat <userid>/<passwd>@<tns alias>' statement.
#
# Note: Any environment variables needed at run time by RMAN
# must be set and exported within the switch user (su) command.
# ---------------------------------------------------------------------------
# Backs up the whole database. This backup is part of the incremental
# strategy (this means it can have incremental backups of levels > 0
# applied to it).
#
# We do not need to explicitly request the control file to be included
# in this backup, as it is automatically included each time file 1 of
# the system tablespace is backed up (the inference: as it is a whole
# database backup, file 1 of the system tablespace will be backed up,
# hence the controlfile will also be included automatically).
#
# Typically, a level 0 backup would be done at least once a week.
#
# The scenario assumes:
# o you are backing your database up to two tape drives
# o you want each backup set to include a maximum of 5 files
# o you wish to include offline datafiles, and read-only tablespaces,
# in the backup
# o you want the backup to continue if any files are inaccessible.
# o you are not using a Recovery Catalog
# o you are explicitly backing up the control file. Since you are
# specifying nocatalog, the controlfile backup that occurs
# automatically as the result of backing up the system file is
# not sufficient; it will not contain records for the backup that
# is currently in progress.
# o you want to archive the current log, back up all the
# archive logs using two channels, putting a maximum of 20 logs
# in a backup set, and deleting them once the backup is complete.
#
# Note that the format string is constructed to guarantee uniqueness and
# to enhance NetBackup for Oracle backup and restore performance.
#
#
# NOTE WHEN USING TNS ALIAS: When connecting to a database
# using a TNS alias, you must use a send command or a parms operand to
# specify environment variables. In other words, when accessing a database
# through a listener, the environment variables set at the system level are not
# visible when RMAN is running. For more information on the environment
# variables, please refer to the NetBackup for Oracle Admin. Guide.
#
# ---------------------------------------------------------------------------
CMD_STR="
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
$RMAN target $TARGET_CONNECT_STR rcvcat rman/rman123@cattest msglog $RMAN_LOG_FILE append << EOF
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=backupserver, NB_ORA_CLIENT=lh-ora-rs, NB_ORA_POLICY=Oracle_test_hot';
BACKUP
$BACKUP_TYPE
SKIP INACCESSIBLE
TAG hot_db_bk_level0
FILESPERSET 5
# recommended format
FORMAT 'bk_%s_%p_%t'
DATABASE;
sql 'alter system archive log current';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
# backup all archive logs
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=backupserver-baan, NB_ORA_CLIENT=lh-ora-rs, NB_ORA_POLICY=Oracle_test_hot';
BACKUP
filesperset 20
FORMAT 'al_%s_%p_%t'
#ARCHIVELOG ALL;
ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
#Tell to backup all archive logs that haven't backed up one time
#prevents from backing up more then once to
#BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
#Delete archive log more than 7 days old.
#DELETE ARCHIVE ALL COMPLETED AFTER SYSDATE-7;
#
# Note: During the process of backing up the database, RMAN also backs up the
# control file. This version of the control file does not contain the
# information about the current backup because "nocatalog" has been specified.
# To include the information about the current backup, the control file should
# be backed up as the last step of the RMAN section. This step would not be
# necessary if we were using a recovery catalog.
#
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=backupserver-baan, NB_ORA_CLIENT=lh-ora-rs, NB_ORA_POLICY=Oracle_ssaerp_hot';
BACKUP
# recommended format
FORMAT 'cntrl_%s_%p_%t'
CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
}
EOF
"
# Initiate the command string
if [ "$CUSER" = "root" ]
then
su - $ORACLE_USER -c "$CMD_STR" >> $RMAN_LOG_FILE
RSTAT=$?
else
/usr/bin/sh -c "$CMD_STR" >> $RMAN_LOG_FILE
RSTAT=$?
fi
# ---------------------------------------------------------------------------
# Log the completion of this script.
# ---------------------------------------------------------------------------
if [ "$RSTAT" = "0" ]
then
LOGMSG="ended successfully"
else
LOGMSG="ended in error"
fi
echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
exit $RSTAT
Subscribe to:
Posts (Atom)